Debian Codegraph @

mysql-test/suite/compat/oracle/t/func_concat.test

raw
1 #
2 # Testing CONCAT with null values
3 #
4
5 SET sql_mode=ORACLE;
6
7 EXPLAIN EXTENDED SELECT 'a'||'b'||'c';
8 EXPLAIN EXTENDED SELECT CONCAT('a'||'b'||'c');
9
10 SELECT '' || '';
11 SELECT '' || 'b';
12 SELECT '' || NULL;
13 SELECT 'a' || '';
14 SELECT 'a' || 'b';
15 SELECT 'a' || NULL;
16 SELECT NULL || '';
17 SELECT NULL || 'b';
18 SELECT NULL || NULL;
19
20 SELECT '' || '' || '';
21 SELECT '' || '' || 'c';
22 SELECT '' || '' || NULL;
23 SELECT '' || 'b' || '';
24 SELECT '' || 'b' || 'c';
25 SELECT '' || 'b' || NULL;
26 SELECT '' || NULL || '';
27 SELECT '' || NULL || 'c';
28 SELECT '' || NULL || NULL;
29
30 SELECT 'a' || '' || '';
31 SELECT 'a' || '' || 'c';
32 SELECT 'a' || '' || NULL;
33 SELECT 'a' || 'b' || '';
34 SELECT 'a' || 'b' || 'c';
35 SELECT 'a' || 'b' || NULL;
36 SELECT 'a' || NULL || '';
37 SELECT 'a' || NULL || 'c';
38 SELECT 'a' || NULL || NULL;
39
40 SELECT NULL || '' || '';
41 SELECT NULL || '' || 'c';
42 SELECT NULL || '' || NULL;
43 SELECT NULL || 'b' || '';
44 SELECT NULL || 'b' || 'c';
45 SELECT NULL || 'b' || NULL;
46 SELECT NULL || NULL || '';
47 SELECT NULL || NULL || 'c';
48 SELECT NULL || NULL || NULL;
49
50 CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(10));
51
52 INSERT INTO t1 VALUES ('', '', '');
53 INSERT INTO t1 VALUES ('', '', 'c');
54 INSERT INTO t1 VALUES ('', '', NULL);
55 INSERT INTO t1 VALUES ('', 'b', '');
56 INSERT INTO t1 VALUES ('', 'b', 'c');
57 INSERT INTO t1 VALUES ('', 'b', NULL);
58 INSERT INTO t1 VALUES ('', NULL, '');
59 INSERT INTO t1 VALUES ('', NULL, 'c');
60 INSERT INTO t1 VALUES ('', NULL, NULL);
61
62 INSERT INTO t1 VALUES ('a', '', '');
63 INSERT INTO t1 VALUES ('a', '', 'c');
64 INSERT INTO t1 VALUES ('a', '', NULL);
65 INSERT INTO t1 VALUES ('a', 'b', '');
66 INSERT INTO t1 VALUES ('a', 'b', 'c');
67 INSERT INTO t1 VALUES ('a', 'b', NULL);
68 INSERT INTO t1 VALUES ('a', NULL, '');
69 INSERT INTO t1 VALUES ('a', NULL, 'c');
70 INSERT INTO t1 VALUES ('a', NULL, NULL);
71
72 INSERT INTO t1 VALUES (NULL, '', '');
73 INSERT INTO t1 VALUES (NULL, '', 'c');
74 INSERT INTO t1 VALUES (NULL, '', NULL);
75 INSERT INTO t1 VALUES (NULL, 'b', '');
76 INSERT INTO t1 VALUES (NULL, 'b', 'c');
77 INSERT INTO t1 VALUES (NULL, 'b', NULL);
78 INSERT INTO t1 VALUES (NULL, NULL, '');
79 INSERT INTO t1 VALUES (NULL, NULL, 'c');
80 INSERT INTO t1 VALUES (NULL, NULL, NULL);
81
82 SELECT LENGTH(a||b||c), a||b||c FROM t1 ORDER BY a,b,c;
83 SELECT LENGTH(CONCAT(a||b||c)), CONCAT(a||b||c) FROM t1 ORDER BY a,b,c;
84
85 DROP TABLE t1;
86
87 --echo #
88 --echo # MDEV-12478 CONCAT function inside view casts values incorrectly with Oracle sql_mode
89 --echo #
90
91 SET sql_mode=ORACLE;
92 CREATE VIEW v1 AS SELECT 'foo'||NULL||'bar' AS test;
93 SHOW CREATE VIEW v1;
94 SELECT * FROM v1;
95 SET sql_mode=DEFAULT;
96 SHOW CREATE VIEW v1;
97 SELECT * FROM v1;
98 DROP VIEW v1;
99
100 SET sql_mode=DEFAULT;
101 CREATE VIEW v1 AS SELECT CONCAT('foo',NULL,'bar') AS test;
102 SHOW CREATE VIEW v1;
103 SELECT * FROM v1;
104 SET sql_mode=ORACLE;
105 SHOW CREATE VIEW v1;
106 SELECT * FROM v1;
107 DROP VIEW v1;
108
109 SET sql_mode=DEFAULT;
110 CREATE VIEW v1 AS SELECT '0'||'1' AS test;
111 SHOW CREATE VIEW v1;
112 SELECT * FROM v1;
113 SET sql_mode=ORACLE;
114 SHOW CREATE VIEW v1;
115 SELECT * FROM v1;
116 DROP VIEW v1;
117
118
119 --echo #
120 --echo # MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE
121 --echo #
122
123 # Concatenation operator || has the same precedence with +
124 # (stronger than << and weaker than * ^)
125
126 SELECT -1<<1||1 AS a FROM DUAL;
127 SELECT -1||0<<1 AS a FROM DUAL;
128
129 EXPLAIN EXTENDED SELECT -1<<1||1 AS a FROM DUAL;
130 EXPLAIN EXTENDED SELECT -1||0<<1 AS a FROM DUAL;
131
132 SELECT -1+1||1 AS a FROM DUAL;
133 SELECT -1||0+1 AS a FROM DUAL;
134
135 EXPLAIN EXTENDED SELECT -1+1||1 AS a FROM DUAL;
136 EXPLAIN EXTENDED SELECT -1||0+1 AS a FROM DUAL;
137
138 SELECT 1*1||-1 AS a FROM DUAL;
139 SELECT 1||1*-1 AS a FROM DUAL;
140
141 EXPLAIN EXTENDED SELECT 1*1||-1 AS a FROM DUAL;
142 EXPLAIN EXTENDED SELECT 1||1*-1 AS a FROM DUAL;
143
144 SELECT -1^1||1 AS a FROM DUAL;
145 SELECT -1||0^1 AS a FROM DUAL;
146
147 EXPLAIN EXTENDED SELECT -1^1||1 AS a FROM DUAL;
148 EXPLAIN EXTENDED SELECT -1||0^1 AS a FROM DUAL;
149
150
151 --echo #
152 --echo # MDEV-17359 Concatenation operator || in like expression failed in sql_mode=ORACLE
153 --echo #
154
155 SELECT 'abc' LIKE 'a'||'%';
156 EXPLAIN EXTENDED SELECT 'abc' LIKE 'a'||'%';
157
158 SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1;
159 SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11;
160 SELECT 'x' FROM DUAL WHERE 1||1 LIKE 1||1;
161
162 CREATE TABLE t1 (c1 VARCHAR(10),c2 VARCHAR(10), ord INTEGER);
163 INSERT INTO t1 VALUES ('a', 'ab' ,1);
164 INSERT INTO t1 VALUES ('ab', 'ab', 2);
165 INSERT INTO t1 VALUES ('abc', 'ab', 3);
166
167 SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord;
168 EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord;
169
170 SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord;
171 EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord;
172
173 SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%';
174 EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%';
175
176 SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1;
177 EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1;
178
179 CREATE VIEW v1 AS SELECT c1, c2, c1 LIKE c2||'_' FROM t1 ORDER BY ord;
180 SELECT * FROM v1;
181 EXPLAIN EXTENDED SELECT * FROM v1;
182
183 DROP VIEW v1;
184 DROP TABLE t1;
185
186
187 --echo #
188 --echo # Start of 10.6 tests
189 --echo #
190
191 --echo #
192 --echo # MDEV-31910 ASAN memcpy-param-overlap upon CONCAT in ORACLE mode
193 --echo #
194
195 SET SQL_MODE= ORACLE;
196 SELECT CONCAT(SUBSTR(123 FROM 2));
197
198 --echo #
199 --echo # End of 10.6 tests
200 --echo #