Debian Codegraph @

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

raw
1 --let $MYSQLD_DATADIR= `select @@datadir`
2
3 --echo #
4 --echo # MDEV-27744 LPAD in vcol created in ORACLE mode makes table corrupted in non-ORACLE
5 --echo #
6
7 #
8 # Testing that the error message for DECODE preserves
9 # the exact letter case as typed by the user
10 #
11
12 SET sql_mode=DEFAULT;
13 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
14 SELECT decode_oracle(1);
15 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
16 SELECT DECODE_ORACLE(1);
17
18 SET sql_mode=ORACLE;
19 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
20 SELECT decode_oracle(1);
21 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
22 SELECT DECODE_ORACLE(1);
23
24 SET sql_mode=DEFAULT;
25 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
26 SELECT decode(1);
27 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
28 SELECT DECODE(1);
29
30 SET sql_mode=ORACLE;
31 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
32 SELECT decode(1);
33 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
34 SELECT DECODE(1);
35
36 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
37 SELECT mariadb_schema.decode(1);
38 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
39 SELECT mariadb_schema.DECODE(1);
40 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
41 SELECT mariadb_schema.decode_oracle(1);
42 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
43 SELECT mariadb_schema.DECODE_ORACLE(1);
44
45 #
46 # Testing that REPLACE, SUBSTR, TRIM print the exact name
47 # as typed by the user in "Function .. is not defined"
48 #
49
50 SET sql_mode=DEFAULT;
51
52 --error ER_FUNC_INEXISTENT_NAME_COLLISION
53 SELECT unknown.TRIM(1);
54 --error ER_FUNC_INEXISTENT_NAME_COLLISION
55 SELECT unknown.trim(1);
56
57 --error ER_FUNCTION_NOT_DEFINED
58 SELECT oracle_schema.TRIM();
59 --error ER_FUNCTION_NOT_DEFINED
60 SELECT oracle_schema.TRIM('a','b');
61 --error ER_FUNCTION_NOT_DEFINED
62 SELECT oracle_schema.TRIM('a','b','c','d');
63
64 --error ER_FUNC_INEXISTENT_NAME_COLLISION
65 SELECT unknown.SUBSTR('a',1,2);
66 --error ER_FUNC_INEXISTENT_NAME_COLLISION
67 SELECT unknown.substr('a',1,2);
68 --error ER_FUNC_INEXISTENT_NAME_COLLISION
69 SELECT unknown.SUBSTRING('a',1,2);
70 --error ER_FUNC_INEXISTENT_NAME_COLLISION
71 SELECT unknown.substring('a',1,2);
72
73 --error ER_FUNC_INEXISTENT_NAME_COLLISION
74 SELECT unknown.REPLACE('a','b','c');
75 --error ER_FUNC_INEXISTENT_NAME_COLLISION
76 SELECT unknown.replace('a','b','c');
77
78 --error ER_FUNCTION_NOT_DEFINED
79 SELECT oracle_schema.REPLACE();
80 --error ER_FUNCTION_NOT_DEFINED
81 SELECT oracle_schema.REPLACE('a');
82 --error ER_FUNCTION_NOT_DEFINED
83 SELECT oracle_schema.REPLACE('a','b');
84 --error ER_FUNCTION_NOT_DEFINED
85 SELECT oracle_schema.REPLACE('a','b','c','d');
86
87 #
88 # Testing EXPLAIN EXTENDED SELECT
89 #
90
91 SET sql_mode=DEFAULT;
92 DELIMITER $$;
93 CREATE PROCEDURE p1(sqlmode TEXT, qualifier TEXT, expr TEXT)
94 BEGIN
95 DECLARE query TEXT DEFAULT 'SELECT $(QUALIFIER)$(EXPR)';
96 DECLARE errmsg TEXT DEFAULT NULL;
97 DECLARE CONTINUE HANDLER FOR 1064, 1128, 1305, 1582, 1630
98 BEGIN
99 GET DIAGNOSTICS CONDITION 1 errmsg = MESSAGE_TEXT;
100 END;
101
102 SET sql_mode=sqlmode;
103 SET query=REPLACE(query, '$(QUALIFIER)', qualifier);
104 SET query=REPLACE(query, '$(EXPR)', expr);
105 SET query= CONCAT('EXPLAIN EXTENDED ', query);
106 SELECT CONCAT('sql_mode=''',sqlmode,'''', ' ',
107 'qualifier=''',qualifier,'''') AS `----------`;
108 SELECT query;
109 EXECUTE IMMEDIATE query;
110 IF errmsg IS NOT NULL THEN
111 SELECT CONCAT('ERROR: ', errmsg) AS errmsg;
112 ELSE
113 SHOW WARNINGS;
114 END IF;
115 END;
116 $$
117 CREATE PROCEDURE p2(sqlmode TEXT, expr TEXT)
118 BEGIN
119 CALL p1(sqlmode, '', expr);
120 CALL p1(sqlmode, 'unknown_schema.', expr);
121 CALL p1(sqlmode, 'mariadb_schema.', expr);
122 CALL p1(sqlmode, 'maxdb_schema.', expr);
123 CALL p1(sqlmode, 'oracle_schema.', expr);
124 END;
125 $$
126 CREATE PROCEDURE p3(expr TEXT)
127 BEGIN
128 CALL p2('', expr);
129 CALL p2('ORACLE', expr);
130 END;
131 $$
132 DELIMITER ;$$
133
134 CALL p3('CONCAT(''a'')');
135
136 # MariaDB style
137 CALL p3('DECODE(''1'',''2'')');
138 # Oracle style
139 CALL p3('DECODE(1,1,10)');
140
141 CALL p3('LTRIM(''a'')');
142 CALL p3('RTRIM(''a'')');
143
144 CALL p3('LPAD(''a'',3)');
145 CALL p3('LPAD(''a'',3, '' '')');
146
147 CALL p3('RPAD(''a'',3)');
148 CALL p3('RPAD(''a'',3, '' '')');
149
150 CALL p3('REPLACE()');
151 CALL p3('REPLACE(''a'',''b'')');
152 CALL p3('REPLACE(''a'',''b'',''c'',''d'')');
153 CALL p3('REPLACE(''a'',''b'',''c'')');
154
155 CALL p3('SUBSTR()');
156 CALL p3('SUBSTR(''a'',1,2,3)');
157 CALL p3('SUBSTR(''a'',1,2)');
158 CALL p3('SUBSTR(''a'' FROM 1)');
159
160 CALL p3('SUBSTRING(''a'',1,2)');
161 CALL p3('SUBSTRING(''a'' FROM 1)');
162
163 CALL p3('TRIM()');
164 CALL p3('TRIM(1,2)');
165 CALL p3('TRIM(''a'')');
166 CALL p3('TRIM(BOTH '' '' FROM ''a'')');
167
168 CALL p3('REGEXP_REPLACE(''test'',''t'','''')');
169
170 # Deprecated compatibility XXX_ORACLE functions.
171 # These functions are implemented as simple native functions
172 # and have no special grammar rules in sql_yacc.yy.
173 # So they support the qualified syntax automatically,
174 # which is not absolutely required, but is not harmful.
175
176 CALL p3('CONCAT_OPERATOR_ORACLE(''a'')');
177 CALL p3('DECODE_ORACLE(1,1,10)');
178 CALL p3('LTRIM_ORACLE(''a'')');
179 CALL p3('RTRIM_ORACLE(''a'')');
180 CALL p3('LPAD_ORACLE(''a'',3)');
181 CALL p3('RPAD_ORACLE(''a'',3)');
182 CALL p3('REPLACE_ORACLE(''a'',''b'',''c'')');
183 CALL p3('SUBSTR_ORACLE(''a'',1,2)');
184
185
186 # Deprecated compatibility XXX_ORACLE variants for functions
187 # with a special syntax in sql_yacc.yy.
188 # These compatibility functions do not support qualified syntax.
189 # One should use a qualified variant without the _ORACLE suffix instead.
190
191 --error ER_PARSE_ERROR
192 SELECT oracle_schema.SUBSTR_ORACLE('a' FROM 1 FOR 2);
193 # Use this instead:
194 SELECT oracle_schema.SUBSTR('a' FROM 1 FOR 2);
195
196 --error ER_PARSE_ERROR
197 SELECT oracle_schema.TRIM_ORACLE(LEADING ' ' FROM 'a');
198 # Use this instead:
199 SELECT oracle_schema.TRIM(LEADING ' ' FROM 'a');
200
201 --error ER_FUNCTION_NOT_DEFINED
202 SELECT oracle_schema.TRIM_ORACLE('a');
203 # Use this instead:
204 SELECT oracle_schema.TRIM('a');
205
206
207 DROP PROCEDURE p1;
208 DROP PROCEDURE p2;
209 DROP PROCEDURE p3;
210
211
212 SET sql_mode='';
213 CREATE VIEW v1 AS SELECT
214 concat('a','b'),
215 decode('1','2'),
216 ltrim('1'),
217 rtrim('1'),
218 lpad('1','2', 3),
219 rpad('1','2', 3),
220 replace('1','2','3'),
221 substr('a',1,2),
222 trim(both 'a' FROM 'b');
223 CREATE TABLE kv (v BLOB);
224 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
225 eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv;
226 SELECT v FROM kv WHERE v RLIKE '^(query|view_body_utf8)=' ORDER BY v;
227 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1' AND TABLE_SCHEMA='test';
228 DROP TABLE kv;
229 DROP VIEW v1;
230
231 SET sql_mode='ORACLE';
232 CREATE VIEW v1 AS SELECT
233 concat('a','b'),
234 decode('1',2,3),
235 ltrim('1'),
236 rtrim('1'),
237 lpad('1','2', 3),
238 rpad('1','2', 3),
239 replace('1','2','3'),
240 substr('a',1,2),
241 trim(both 'a' FROM 'b');
242 CREATE TABLE kv (v BLOB);
243 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
244 eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv;
245 SELECT v FROM kv WHERE v RLIKE '^(query|view_body_utf8)=' ORDER BY v;
246 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1' AND TABLE_SCHEMA='test';
247 DROP TABLE kv;
248 DROP VIEW v1;