Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-10801 sql_mode: dynamic SQL placeholders
4 #
5 SET @a=10, @b=20;
6 PREPARE stmt FROM 'SELECT ?,?';
7 EXECUTE stmt USING @a, @b;
8 ? ?
9 10 20
10 PREPARE stmt FROM 'SELECT :a,:b';
11 EXECUTE stmt USING @a, @b;
12 :a :b
13 10 20
14 PREPARE stmt FROM 'SELECT :aaa,:bbb';
15 EXECUTE stmt USING @a, @b;
16 :aaa :bbb
17 10 20
18 PREPARE stmt FROM 'SELECT :"a",:"b"';
19 EXECUTE stmt USING @a, @b;
20 :"a" :"b"
21 10 20
22 PREPARE stmt FROM 'SELECT :"aaa",:"bbb"';
23 EXECUTE stmt USING @a, @b;
24 :"aaa" :"bbb"
25 10 20
26 PREPARE stmt FROM 'SELECT :1,:2';
27 EXECUTE stmt USING @a, @b;
28 :1 :2
29 10 20
30 PREPARE stmt FROM 'SELECT :222,:111';
31 EXECUTE stmt USING @a, @b;
32 :222 :111
33 10 20
34 PREPARE stmt FROM 'SELECT :0,:65535';
35 EXECUTE stmt USING @a, @b;
36 :0 :65535
37 10 20
38 PREPARE stmt FROM 'SELECT :65535,:0';
39 EXECUTE stmt USING @a, @b;
40 :65535 :0
41 10 20
42 #
43 # MDEV-10709 Expressions as parameters to Dynamic SQL
44 #
45 #
46 # Testing disallowed expressions in USING
47 #
48 PREPARE stmt FROM 'SELECT :1 FROM DUAL';
49 EXECUTE stmt USING (SELECT 1);
50 ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
51 DEALLOCATE PREPARE stmt;
52 CREATE FUNCTION f1() RETURN VARCHAR
53 AS
54 BEGIN
55 RETURN 'test';
56 END;
57 $$
58 PREPARE stmt FROM 'SELECT ? FROM DUAL';
59 EXECUTE stmt USING f1();
60 ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
61 DEALLOCATE PREPARE stmt;
62 DROP FUNCTION f1;
63 #
64 # Using a user variable as a EXECUTE..USING out parameter
65 #
66 CREATE PROCEDURE p1(a OUT INT)
67 AS
68 BEGIN
69 a:= 10;
70 END;
71 /
72 SET @a=1;
73 CALL p1(@a);
74 SELECT @a;
75 @a
76 10
77 SET @a=2;
78 PREPARE stmt FROM 'CALL p1(?)';
79 EXECUTE stmt USING @a;
80 SELECT @a;
81 @a
82 10
83 DROP PROCEDURE p1;
84 #
85 # Using an SP variable as a EXECUTE..USING out parameter
86 #
87 CREATE PROCEDURE p1 (a OUT INT)
88 AS
89 BEGIN
90 a:=10;
91 END;
92 /
93 CREATE PROCEDURE p2 (a OUT INT)
94 AS
95 BEGIN
96 PREPARE stmt FROM 'CALL p1(?)';
97 EXECUTE stmt USING a;
98 END;
99 /
100 SET @a= 1;
101 CALL p2(@a);
102 SELECT @a;
103 @a
104 10
105 DROP PROCEDURE p2;
106 DROP PROCEDURE p1;
107 #
108 # Using a trigger field as a EXECUTE..USING out parameter
109 #
110 CREATE PROCEDURE p1 (a OUT INT)
111 AS
112 BEGIN
113 a:= 10;
114 END;
115 /
116 CREATE TABLE t1 (a INT);
117 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1(:NEW.a);
118 INSERT INTO t1 VALUES (1);
119 SELECT * FROM t1;
120 a
121 10
122 DROP TABLE t1;
123 DROP PROCEDURE p1;
124 #
125 # Testing re-prepare on a table metadata update between PREPARE and EXECUTE
126 #
127 CREATE TABLE t1 (a INT);
128 CREATE PROCEDURE p1(a IN INT)
129 AS
130 BEGIN
131 INSERT INTO t1 VALUES (a);
132 END;
133 /
134 PREPARE stmt FROM 'CALL p1(?)';
135 EXECUTE stmt USING 10;
136 SELECT * FROM t1;
137 a
138 10
139 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW NEW.a:=NEW.a+1;
140 EXECUTE stmt USING 20;
141 SELECT * FROM t1;
142 a
143 10
144 21
145 DEALLOCATE PREPARE stmt;
146 DROP PROCEDURE p1;
147 DROP TABLE t1;
148 #
149 # End of MDEV-10709 Expressions as parameters to Dynamic SQL
150 #
151 #
152 # MDEV-10585 EXECUTE IMMEDIATE statement
153 #
154 #
155 # Testing disallowed expressions in USING
156 #
157 EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING (SELECT 1);
158 ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
159 CREATE FUNCTION f1() RETURN VARCHAR
160 AS
161 BEGIN
162 RETURN 'test';
163 END;
164 $$
165 EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1();
166 ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions
167 DROP FUNCTION f1;
168 #
169 # Testing simple expressions
170 #
171 EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING 10;
172 :1
173 10
174 #
175 # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
176 #
177 #
178 # Testing erroneous and diallowed prepare source
179 #
180 EXECUTE IMMEDIATE _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL';
181 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
182 PREPARE stmt FROM _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL';
183 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
184 EXECUTE IMMEDIATE (SELECT 'SELECT 1');
185 ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions
186 PREPARE stmt FROM (SELECT 'SELECT 1');
187 ERROR 42000: PREPARE..FROM does not support subqueries or stored functions
188 EXECUTE IMMEDIATE a;
189 ERROR 42S22: Unknown column 'a' in 'EXECUTE IMMEDIATE'
190 PREPARE stmt FROM a;
191 ERROR 42S22: Unknown column 'a' in 'PREPARE..FROM'
192 EXECUTE IMMEDIATE NULL;
193 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
194 PREPARE stmt FROM NULL;
195 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
196 EXECUTE IMMEDIATE COALESCE(NULL);
197 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
198 PREPARE stmt FROM COALESCE(NULL);
199 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
200 CREATE FUNCTION f1() RETURN VARCHAR
201 AS
202 BEGIN
203 RETURN 't1';
204 END;
205 $$
206 EXECUTE IMMEDIATE f1();
207 ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions
208 PREPARE stmt FROM f1();
209 ERROR 42000: PREPARE..FROM does not support subqueries or stored functions
210 DROP FUNCTION f1;
211 #
212 # Testing user variables in prepare source
213 #
214 SET @table_name='DUAL';
215 EXECUTE IMMEDIATE 'SELECT 1 AS a FROM ' || @table_name;
216 a
217 1
218 PREPARE stmt FROM 'SELECT 1 AS a FROM ' || @table_name;
219 EXECUTE stmt;
220 a
221 1
222 DEALLOCATE PREPARE stmt;
223 #
224 # Testing SP parameters and variables in prepare source
225 #
226 CREATE PROCEDURE p1(table_name VARCHAR)
227 AS
228 BEGIN
229 EXECUTE IMMEDIATE 'SELECT 1 AS c FROM '|| table_name;
230 END;
231 $$
232 CALL p1('DUAL');
233 c
234 1
235 DROP PROCEDURE p1;
236 CREATE PROCEDURE p1()
237 AS
238 table_name VARCHAR(64):='DUAL';
239 BEGIN
240 EXECUTE IMMEDIATE 'SELECT 1 AS c FROM ' || table_name;
241 END;
242 $$
243 CALL p1();
244 c
245 1
246 DROP PROCEDURE p1;
247 #
248 # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
249 #
250 #
251 # MDEV-12846 sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash
252 #
253 SELECT ? FROM DUAL;
254 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? FROM DUAL' at line 1
255 SELECT :a FROM DUAL;
256 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':a FROM DUAL' at line 1
257 SELECT :1 FROM DUAL;
258 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':1 FROM DUAL' at line 1
259 SELECT 1+? FROM DUAL;
260 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? FROM DUAL' at line 1
261 SELECT 1+:a FROM DUAL;
262 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':a FROM DUAL' at line 1
263 SELECT 1+:1 FROM DUAL;
264 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':1 FROM DUAL' at line 1