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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
SET SESSION character_set_connection=latin2;
SET SESSION character_set_client=cp1250;
--echo #
--echo # Test litteral
--echo #
#enable view protocol after fix MDEV-27871 and
# it is necessary that the view protocol uses the same connection,
# not util connection
--disable_view_protocol
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'' '' '');
--enable_view_protocol
--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;
# it is necessary that the view protocol uses the same connection,
# not util connection
--disable_view_protocol
SELECT NULLIF(_cp1250 '',_cp1250 '')
UNION
SELECT NULLIF(N'',N'');
--enable_view_protocol
--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 '' '';
|
