Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 EXPLAIN EXTENDED SELECT 'a'||'b'||'c';
3 id select_type table type possible_keys key key_len ref rows filtered Extra
4 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
5 Warnings:
6 Note 1003 select concat(concat('a','b'),'c') AS "'a'||'b'||'c'"
7 EXPLAIN EXTENDED SELECT CONCAT('a'||'b'||'c');
8 id select_type table type possible_keys key key_len ref rows filtered Extra
9 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
10 Warnings:
11 Note 1003 select concat(concat(concat('a','b'),'c')) AS "CONCAT('a'||'b'||'c')"
12 SELECT '' || '';
13 '' || ''
14
15 SELECT '' || 'b';
16 '' || 'b'
17 b
18 SELECT '' || NULL;
19 '' || NULL
20
21 SELECT 'a' || '';
22 'a' || ''
23 a
24 SELECT 'a' || 'b';
25 'a' || 'b'
26 ab
27 SELECT 'a' || NULL;
28 'a' || NULL
29 a
30 SELECT NULL || '';
31 NULL || ''
32
33 SELECT NULL || 'b';
34 NULL || 'b'
35 b
36 SELECT NULL || NULL;
37 NULL || NULL
38 NULL
39 SELECT '' || '' || '';
40 '' || '' || ''
41
42 SELECT '' || '' || 'c';
43 '' || '' || 'c'
44 c
45 SELECT '' || '' || NULL;
46 '' || '' || NULL
47
48 SELECT '' || 'b' || '';
49 '' || 'b' || ''
50 b
51 SELECT '' || 'b' || 'c';
52 '' || 'b' || 'c'
53 bc
54 SELECT '' || 'b' || NULL;
55 '' || 'b' || NULL
56 b
57 SELECT '' || NULL || '';
58 '' || NULL || ''
59
60 SELECT '' || NULL || 'c';
61 '' || NULL || 'c'
62 c
63 SELECT '' || NULL || NULL;
64 '' || NULL || NULL
65
66 SELECT 'a' || '' || '';
67 'a' || '' || ''
68 a
69 SELECT 'a' || '' || 'c';
70 'a' || '' || 'c'
71 ac
72 SELECT 'a' || '' || NULL;
73 'a' || '' || NULL
74 a
75 SELECT 'a' || 'b' || '';
76 'a' || 'b' || ''
77 ab
78 SELECT 'a' || 'b' || 'c';
79 'a' || 'b' || 'c'
80 abc
81 SELECT 'a' || 'b' || NULL;
82 'a' || 'b' || NULL
83 ab
84 SELECT 'a' || NULL || '';
85 'a' || NULL || ''
86 a
87 SELECT 'a' || NULL || 'c';
88 'a' || NULL || 'c'
89 ac
90 SELECT 'a' || NULL || NULL;
91 'a' || NULL || NULL
92 a
93 SELECT NULL || '' || '';
94 NULL || '' || ''
95
96 SELECT NULL || '' || 'c';
97 NULL || '' || 'c'
98 c
99 SELECT NULL || '' || NULL;
100 NULL || '' || NULL
101
102 SELECT NULL || 'b' || '';
103 NULL || 'b' || ''
104 b
105 SELECT NULL || 'b' || 'c';
106 NULL || 'b' || 'c'
107 bc
108 SELECT NULL || 'b' || NULL;
109 NULL || 'b' || NULL
110 b
111 SELECT NULL || NULL || '';
112 NULL || NULL || ''
113
114 SELECT NULL || NULL || 'c';
115 NULL || NULL || 'c'
116 c
117 SELECT NULL || NULL || NULL;
118 NULL || NULL || NULL
119 NULL
120 CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(10));
121 INSERT INTO t1 VALUES ('', '', '');
122 INSERT INTO t1 VALUES ('', '', 'c');
123 INSERT INTO t1 VALUES ('', '', NULL);
124 INSERT INTO t1 VALUES ('', 'b', '');
125 INSERT INTO t1 VALUES ('', 'b', 'c');
126 INSERT INTO t1 VALUES ('', 'b', NULL);
127 INSERT INTO t1 VALUES ('', NULL, '');
128 INSERT INTO t1 VALUES ('', NULL, 'c');
129 INSERT INTO t1 VALUES ('', NULL, NULL);
130 INSERT INTO t1 VALUES ('a', '', '');
131 INSERT INTO t1 VALUES ('a', '', 'c');
132 INSERT INTO t1 VALUES ('a', '', NULL);
133 INSERT INTO t1 VALUES ('a', 'b', '');
134 INSERT INTO t1 VALUES ('a', 'b', 'c');
135 INSERT INTO t1 VALUES ('a', 'b', NULL);
136 INSERT INTO t1 VALUES ('a', NULL, '');
137 INSERT INTO t1 VALUES ('a', NULL, 'c');
138 INSERT INTO t1 VALUES ('a', NULL, NULL);
139 INSERT INTO t1 VALUES (NULL, '', '');
140 INSERT INTO t1 VALUES (NULL, '', 'c');
141 INSERT INTO t1 VALUES (NULL, '', NULL);
142 INSERT INTO t1 VALUES (NULL, 'b', '');
143 INSERT INTO t1 VALUES (NULL, 'b', 'c');
144 INSERT INTO t1 VALUES (NULL, 'b', NULL);
145 INSERT INTO t1 VALUES (NULL, NULL, '');
146 INSERT INTO t1 VALUES (NULL, NULL, 'c');
147 INSERT INTO t1 VALUES (NULL, NULL, NULL);
148 SELECT LENGTH(a||b||c), a||b||c FROM t1 ORDER BY a,b,c;
149 LENGTH(a||b||c) a||b||c
150 NULL NULL
151 0
152 1 c
153 0
154 0
155 1 c
156 1 b
157 1 b
158 2 bc
159 0
160 0
161 1 c
162 0
163 0
164 1 c
165 1 b
166 1 b
167 2 bc
168 1 a
169 1 a
170 2 ac
171 1 a
172 1 a
173 2 ac
174 2 ab
175 2 ab
176 3 abc
177 SELECT LENGTH(CONCAT(a||b||c)), CONCAT(a||b||c) FROM t1 ORDER BY a,b,c;
178 LENGTH(CONCAT(a||b||c)) CONCAT(a||b||c)
179 NULL NULL
180 0
181 1 c
182 0
183 0
184 1 c
185 1 b
186 1 b
187 2 bc
188 0
189 0
190 1 c
191 0
192 0
193 1 c
194 1 b
195 1 b
196 2 bc
197 1 a
198 1 a
199 2 ac
200 1 a
201 1 a
202 2 ac
203 2 ab
204 2 ab
205 3 abc
206 DROP TABLE t1;
207 #
208 # MDEV-12478 CONCAT function inside view casts values incorrectly with Oracle sql_mode
209 #
210 SET sql_mode=ORACLE;
211 CREATE VIEW v1 AS SELECT 'foo'||NULL||'bar' AS test;
212 SHOW CREATE VIEW v1;
213 View Create View character_set_client collation_connection
214 v1 CREATE VIEW "v1" AS select concat(concat('foo',NULL),'bar') AS "test" latin1 latin1_swedish_ci
215 SELECT * FROM v1;
216 test
217 foobar
218 SET sql_mode=DEFAULT;
219 SHOW CREATE VIEW v1;
220 View Create View character_set_client collation_connection
221 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select oracle_schema.concat(oracle_schema.concat('foo',NULL),'bar') AS `test` latin1 latin1_swedish_ci
222 SELECT * FROM v1;
223 test
224 foobar
225 DROP VIEW v1;
226 SET sql_mode=DEFAULT;
227 CREATE VIEW v1 AS SELECT CONCAT('foo',NULL,'bar') AS test;
228 SHOW CREATE VIEW v1;
229 View Create View character_set_client collation_connection
230 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select concat('foo',NULL,'bar') AS `test` latin1 latin1_swedish_ci
231 SELECT * FROM v1;
232 test
233 NULL
234 SET sql_mode=ORACLE;
235 SHOW CREATE VIEW v1;
236 View Create View character_set_client collation_connection
237 v1 CREATE VIEW "v1" AS select mariadb_schema.concat('foo',NULL,'bar') AS "test" latin1 latin1_swedish_ci
238 SELECT * FROM v1;
239 test
240 NULL
241 DROP VIEW v1;
242 SET sql_mode=DEFAULT;
243 CREATE VIEW v1 AS SELECT '0'||'1' AS test;
244 SHOW CREATE VIEW v1;
245 View Create View character_set_client collation_connection
246 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select '0' or '1' AS `test` latin1 latin1_swedish_ci
247 SELECT * FROM v1;
248 test
249 1
250 SET sql_mode=ORACLE;
251 SHOW CREATE VIEW v1;
252 View Create View character_set_client collation_connection
253 v1 CREATE VIEW "v1" AS select '0' or '1' AS "test" latin1 latin1_swedish_ci
254 SELECT * FROM v1;
255 test
256 1
257 DROP VIEW v1;
258 #
259 # MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE
260 #
261 SELECT -1<<1||1 AS a FROM DUAL;
262 a
263 18446744073709549568
264 SELECT -1||0<<1 AS a FROM DUAL;
265 a
266 18446744073709551596
267 EXPLAIN EXTENDED SELECT -1<<1||1 AS a FROM DUAL;
268 id select_type table type possible_keys key key_len ref rows filtered Extra
269 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
270 Warnings:
271 Note 1003 select -1 << concat(1,1) AS "a"
272 EXPLAIN EXTENDED SELECT -1||0<<1 AS a FROM DUAL;
273 id select_type table type possible_keys key key_len ref rows filtered Extra
274 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
275 Warnings:
276 Note 1003 select concat(-1,0) << 1 AS "a"
277 SELECT -1+1||1 AS a FROM DUAL;
278 a
279 01
280 SELECT -1||0+1 AS a FROM DUAL;
281 a
282 -9
283 EXPLAIN EXTENDED SELECT -1+1||1 AS a FROM DUAL;
284 id select_type table type possible_keys key key_len ref rows filtered Extra
285 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
286 Warnings:
287 Note 1003 select concat(-1 + 1,1) AS "a"
288 EXPLAIN EXTENDED SELECT -1||0+1 AS a FROM DUAL;
289 id select_type table type possible_keys key key_len ref rows filtered Extra
290 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
291 Warnings:
292 Note 1003 select concat(-1,0) + 1 AS "a"
293 SELECT 1*1||-1 AS a FROM DUAL;
294 a
295 1-1
296 SELECT 1||1*-1 AS a FROM DUAL;
297 a
298 1-1
299 EXPLAIN EXTENDED SELECT 1*1||-1 AS a FROM DUAL;
300 id select_type table type possible_keys key key_len ref rows filtered Extra
301 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
302 Warnings:
303 Note 1003 select concat(1 * 1,-1) AS "a"
304 EXPLAIN EXTENDED SELECT 1||1*-1 AS a FROM DUAL;
305 id select_type table type possible_keys key key_len ref rows filtered Extra
306 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
307 Warnings:
308 Note 1003 select concat(1,1 * -1) AS "a"
309 SELECT -1^1||1 AS a FROM DUAL;
310 a
311 184467440737095516141
312 SELECT -1||0^1 AS a FROM DUAL;
313 a
314 -11
315 EXPLAIN EXTENDED SELECT -1^1||1 AS a FROM DUAL;
316 id select_type table type possible_keys key key_len ref rows filtered Extra
317 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
318 Warnings:
319 Note 1003 select concat(-1 ^ 1,1) AS "a"
320 EXPLAIN EXTENDED SELECT -1||0^1 AS a FROM DUAL;
321 id select_type table type possible_keys key key_len ref rows filtered Extra
322 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
323 Warnings:
324 Note 1003 select concat(-1,0 ^ 1) AS "a"
325 #
326 # MDEV-17359 Concatenation operator || in like expression failed in sql_mode=ORACLE
327 #
328 SELECT 'abc' LIKE 'a'||'%';
329 'abc' LIKE 'a'||'%'
330 1
331 EXPLAIN EXTENDED SELECT 'abc' LIKE 'a'||'%';
332 id select_type table type possible_keys key key_len ref rows filtered Extra
333 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
334 Warnings:
335 Note 1003 select 'abc' like concat('a','%') AS "'abc' LIKE 'a'||'%'"
336 SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1;
337 x
338 x
339 SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11;
340 x
341 x
342 SELECT 'x' FROM DUAL WHERE 1||1 LIKE 1||1;
343 x
344 x
345 CREATE TABLE t1 (c1 VARCHAR(10),c2 VARCHAR(10), ord INTEGER);
346 INSERT INTO t1 VALUES ('a', 'ab' ,1);
347 INSERT INTO t1 VALUES ('ab', 'ab', 2);
348 INSERT INTO t1 VALUES ('abc', 'ab', 3);
349 SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord;
350 c1
351 ab
352 EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord;
353 id select_type table type possible_keys key key_len ref rows filtered Extra
354 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
355 Warnings:
356 Note 1003 select "test"."t1"."c1" AS "c1" from "test"."t1" where "test"."t1"."c1" like <cache>(convert(concat('%','b') using utf8mb4)) order by "test"."t1"."ord"
357 SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord;
358 c1
359 abc
360 EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord;
361 id select_type table type possible_keys key key_len ref rows filtered Extra
362 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
363 Warnings:
364 Note 1003 select "test"."t1"."c1" AS "c1" from "test"."t1" where "test"."t1"."c1" like concat(concat("test"."t1"."c2",'%'),'c') order by "test"."t1"."ord"
365 SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%';
366 x
367 x
368 EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%';
369 id select_type table type possible_keys key key_len ref rows filtered Extra
370 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
371 Warnings:
372 Note 1003 select 'x' AS "x" from "test"."t1" where concat("test"."t1"."c1","test"."t1"."c2") like 'aa%'
373 SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1;
374 x
375 x
376 EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1;
377 id select_type table type possible_keys key key_len ref rows filtered Extra
378 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
379 Warnings:
380 Note 1003 select 'x' AS "x" from "test"."t1" where concat("test"."t1"."c1","test"."t1"."c2") like concat("test"."t1"."c2","test"."t1"."c1")
381 CREATE VIEW v1 AS SELECT c1, c2, c1 LIKE c2||'_' FROM t1 ORDER BY ord;
382 SELECT * FROM v1;
383 c1 c2 c1 LIKE c2||'_'
384 a ab 0
385 ab ab 0
386 abc ab 1
387 EXPLAIN EXTENDED SELECT * FROM v1;
388 id select_type table type possible_keys key key_len ref rows filtered Extra
389 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort
390 Warnings:
391 Note 1003 select "test"."t1"."c1" AS "c1","test"."t1"."c2" AS "c2","test"."t1"."c1" like concat("test"."t1"."c2",'_') AS "c1 LIKE c2||'_'" from "test"."t1" order by "test"."t1"."ord"
392 DROP VIEW v1;
393 DROP TABLE t1;
394 #
395 # Start of 10.6 tests
396 #
397 #
398 # MDEV-31910 ASAN memcpy-param-overlap upon CONCAT in ORACLE mode
399 #
400 SET SQL_MODE= ORACLE;
401 SELECT CONCAT(SUBSTR(123 FROM 2));
402 CONCAT(SUBSTR(123 FROM 2))
403 23
404 #
405 # End of 10.6 tests
406 #