1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
SET SESSION character_set_connection=latin2;
SET SESSION character_set_client=cp1250;
--echo #
--echo # Test litteral
--echo #
SET sql_mode=@mode;
select @@sql_mode;
SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
SELECT CHARSET(NULLIF('','')),NULLIF('','');
SET sql_mode=default;
SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
SELECT CHARSET(NULLIF('','')),NULLIF('','');
--echo #
--echo # Test NCHAR litteral
--echo #
SET sql_mode=@mode;
SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
SET sql_mode=default;
SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
--echo #
--echo # Test CHARSET prefix litteral
--echo #
SET sql_mode=@mode;
SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
SET sql_mode=default;
SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
SET sql_mode=@mode;
--echo #
--echo # Test litteral concat
--echo #
SELECT 'a' 'b';
SELECT 'a' '';
SELECT '' 'b';
SELECT '' '';
SELECT '' 'b' 'c';
SELECT '' '' 'c';
SELECT 'a' '' 'c';
SELECT 'a' '' '';
SELECT '' '' '';
SELECT '' '' '',CHARSET('' '' '');
SELECT _latin1'' '' '',CHARSET(_latin1'' '' '');
SELECT N'' '' '',CHARSET(N'' '' '');
--echo #
--echo # UNION - implicit group by
--echo #
SELECT 1, null
UNION
SELECT 1 , ''
ORDER BY 1;
SELECT 1, null
UNION
SELECT 1 , N''
ORDER BY 1;
SELECT 1, null
UNION
SELECT 1 , _cp1250 ''
ORDER BY 1;
SELECT NULLIF(_cp1250 '',_cp1250 '')
UNION
SELECT NULLIF(N'',N'');
--error ER_CANT_AGGREGATE_2COLLATIONS
SELECT 1 , _latin2 ''
UNION
SELECT 1 , _cp1250 '';
SELECT 1, null
UNION
SELECT 1 , ''
UNION
SELECT 1 , N'';
CREATE TABLE t1 (c1 INT,c2 VARCHAR(10));
INSERT INTO t1 VALUES (1,'one');
INSERT INTO t1 VALUES (1,'');
INSERT INTO t1 VALUES (1,null);
--echo #
--echo # Test in a view
--echo #
CREATE VIEW v1
AS SELECT c1, c2
FROM t1
UNION
SELECT c1 , ''
FROM t1
ORDER BY 1,2;
SELECT * FROM v1;
SHOW CREATE VIEW v1;
DROP VIEW v1;
DROP TABLE t1;
EXPLAIN EXTENDED SELECT '';
EXPLAIN EXTENDED SELECT _latin1'';
EXPLAIN EXTENDED SELECT N'';
EXPLAIN EXTENDED SELECT '' '';
|
