Debian Codegraph @

mysql-test/suite/compat/oracle/r/empty_string_literal.result

raw
1 USE test;
2 #
3 # MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL
4 #
5 set @mode='ORACLE,EMPTY_STRING_IS_NULL';
6 SET SESSION character_set_connection=latin2;
7 SET SESSION character_set_client=cp1250;
8 #
9 # Test litteral
10 #
11 SET sql_mode=@mode;
12 select @@sql_mode;
13 @@sql_mode
14 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT
15 SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
16 NULL CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x')
17 NULL latin2 NULL binary NULL latin2 x latin2
18 SELECT CHARSET(NULLIF('','')),NULLIF('','');
19 CHARSET(NULLIF('','')) NULLIF('','')
20 latin2 NULL
21 SET sql_mode=default;
22 SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
23 CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x')
24 latin2 NULL binary NULL latin2 x latin2
25 SELECT CHARSET(NULLIF('','')),NULLIF('','');
26 CHARSET(NULLIF('','')) NULLIF('','')
27 latin2 NULL
28 #
29 # Test NCHAR litteral
30 #
31 SET sql_mode=@mode;
32 SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
33 NULL CHARSET(N'') x CHARSET(N'x')
34 NULL utf8mb3 x utf8mb3
35 SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
36 CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'')
37 utf8mb3 NULL
38 SET sql_mode=default;
39 SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
40 CHARSET(N'') x CHARSET(N'x')
41 utf8mb3 x utf8mb3
42 SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
43 CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'')
44 utf8mb3 NULL
45 #
46 # Test CHARSET prefix litteral
47 #
48 SET sql_mode=@mode;
49 SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
50 NULL CHARSET(_cp1250 '') x CHARSET(_cp1250 'x')
51 NULL cp1250 x cp1250
52 SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
53 CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '')
54 cp1250 NULL
55 SET sql_mode=default;
56 SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
57 CHARSET(_cp1250 '') x CHARSET(_cp1250 'x')
58 cp1250 x cp1250
59 SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
60 CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '')
61 cp1250 NULL
62 SET sql_mode=@mode;
63 #
64 # Test litteral concat
65 #
66 SELECT 'a' 'b';
67 ab
68 ab
69 SELECT 'a' '';
70 a
71 a
72 SELECT '' 'b';
73 b
74 b
75 SELECT '' '';
76 NULL
77 NULL
78 SELECT '' 'b' 'c';
79 bc
80 bc
81 SELECT '' '' 'c';
82 c
83 c
84 SELECT 'a' '' 'c';
85 ac
86 ac
87 SELECT 'a' '' '';
88 a
89 a
90 SELECT '' '' '';
91 NULL
92 NULL
93 SELECT '' '' '',CHARSET('' '' '');
94 NULL CHARSET('' '' '')
95 NULL latin2
96 SELECT _latin1'' '' '',CHARSET(_latin1'' '' '');
97 NULL CHARSET(_latin1'' '' '')
98 NULL latin1
99 SELECT N'' '' '',CHARSET(N'' '' '');
100 NULL CHARSET(N'' '' '')
101 NULL utf8mb3
102 #
103 # UNION - implicit group by
104 #
105 SELECT 1, null
106 UNION
107 SELECT 1 , ''
108 ORDER BY 1;
109 1 NULL
110 1 NULL
111 SELECT 1, null
112 UNION
113 SELECT 1 , N''
114 ORDER BY 1;
115 1 NULL
116 1 NULL
117 SELECT 1, null
118 UNION
119 SELECT 1 , _cp1250 ''
120 ORDER BY 1;
121 1 NULL
122 1 NULL
123 SELECT NULLIF(_cp1250 '',_cp1250 '')
124 UNION
125 SELECT NULLIF(N'',N'');
126 NULLIF(_cp1250 '',_cp1250 '')
127 NULL
128 SELECT 1 , _latin2 ''
129 UNION
130 SELECT 1 , _cp1250 '';
131 ERROR HY000: Illegal mix of collations (latin2_general_ci,IGNORABLE) and (cp1250_general_ci,IGNORABLE) for operation 'UNION'
132 SELECT 1, null
133 UNION
134 SELECT 1 , ''
135 UNION
136 SELECT 1 , N'';
137 1 NULL
138 1 NULL
139 CREATE TABLE t1 (c1 INT,c2 VARCHAR(10));
140 INSERT INTO t1 VALUES (1,'one');
141 INSERT INTO t1 VALUES (1,'');
142 INSERT INTO t1 VALUES (1,null);
143 #
144 # Test in a view
145 #
146 CREATE VIEW v1
147 AS SELECT c1, c2
148 FROM t1
149 UNION
150 SELECT c1 , ''
151 FROM t1
152 ORDER BY 1,2;
153 SELECT * FROM v1;
154 c1 c2
155 1 NULL
156 1 one
157 SHOW CREATE VIEW v1;
158 View Create View character_set_client collation_connection
159 v1 CREATE VIEW "v1" AS select "__4"."c1" AS "c1","__4"."c2" AS "c2" from (select "__5"."c1" AS "c1","__5"."c2" AS "c2" from (select "t1"."c1" AS "c1","t1"."c2" AS "c2" from "t1" union select "t1"."c1" AS "c1",NULL AS "NULL" from "t1") "__5") "__4" order by 1,2 cp1250 latin2_general_ci
160 DROP VIEW v1;
161 DROP TABLE t1;
162 EXPLAIN EXTENDED SELECT '';
163 id select_type table type possible_keys key key_len ref rows filtered Extra
164 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
165 Warnings:
166 Note 1003 select NULL AS "NULL"
167 EXPLAIN EXTENDED SELECT _latin1'';
168 id select_type table type possible_keys key key_len ref rows filtered Extra
169 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
170 Warnings:
171 Note 1003 select NULL AS "NULL"
172 EXPLAIN EXTENDED SELECT N'';
173 id select_type table type possible_keys key key_len ref rows filtered Extra
174 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
175 Warnings:
176 Note 1003 select NULL AS "NULL"
177 EXPLAIN EXTENDED SELECT '' '';
178 id select_type table type possible_keys key key_len ref rows filtered Extra
179 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
180 Warnings:
181 Note 1003 select NULL AS "NULL"