Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-10914 ROW data type for stored routine variables
4 #
5 CREATE TABLE t1 (a INT, b INT);
6 CREATE PROCEDURE p1
7 AS
8 rec ROW(a INT,b INT);
9 BEGIN
10 rec.a:=100;
11 rec.b:=200;
12 INSERT INTO t1 VALUES (rec.a,rec.b);
13 INSERT INTO t1 VALUES (10, rec=ROW(100,200));
14 INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
15 INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
16 INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
17 rec.a:=NULL;
18 INSERT INTO t1 VALUES (11, rec=ROW(100,200));
19 INSERT INTO t1 VALUES (11, rec=ROW(100,201));
20 INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
21 INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
22 INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
23 INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
24 rec.b:=NULL;
25 INSERT INTO t1 VALUES (12, rec=ROW(100,200));
26 INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
27 INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
28 INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
29 END;
30 $$
31 CALL p1();
32 SELECT * FROM t1;
33 a b
34 100 200
35 10 1
36 10 1
37 10 20
38 10 21
39 11 NULL
40 11 0
41 11 NULL
42 11 0
43 12 NULL
44 12 NULL
45 DROP TABLE t1;
46 DROP PROCEDURE p1;
47 include/show_binlog_events.inc
48 Log_name Pos Event_type Server_id End_log_pos Info
49 master-bin.000001 # Gtid # # GTID #-#-#
50 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT)
51 master-bin.000001 # Gtid # # GTID #-#-#
52 master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
53 AS
54 rec ROW(a INT,b INT);
55 BEGIN
56 rec.a:=100;
57 rec.b:=200;
58 INSERT INTO t1 VALUES (rec.a,rec.b);
59 INSERT INTO t1 VALUES (10, rec=ROW(100,200));
60 INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
61 INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
62 INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
63 rec.a:=NULL;
64 INSERT INTO t1 VALUES (11, rec=ROW(100,200));
65 INSERT INTO t1 VALUES (11, rec=ROW(100,201));
66 INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
67 INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
68 INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
69 INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
70 rec.b:=NULL;
71 INSERT INTO t1 VALUES (12, rec=ROW(100,200));
72 INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
73 INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
74 INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
75 END
76 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
77 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('rec.a',100), NAME_CONST('rec.b',200))
78 master-bin.000001 # Query # # COMMIT
79 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
80 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200))
81 master-bin.000001 # Query # # COMMIT
82 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
83 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200))
84 master-bin.000001 # Query # # COMMIT
85 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
86 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE ROW(100,200)=ROW(100,200)
87 master-bin.000001 # Query # # COMMIT
88 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
89 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=ROW(100,200)
90 master-bin.000001 # Query # # COMMIT
91 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
92 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,200))
93 master-bin.000001 # Query # # COMMIT
94 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
95 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,201))
96 master-bin.000001 # Query # # COMMIT
97 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
98 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(100,200)=ROW(NULL,200))
99 master-bin.000001 # Query # # COMMIT
100 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
101 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(100,201)=ROW(NULL,200))
102 master-bin.000001 # Query # # COMMIT
103 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
104 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE ROW(NULL,200)=ROW(100,200)
105 master-bin.000001 # Query # # COMMIT
106 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
107 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,200)
108 master-bin.000001 # Query # # COMMIT
109 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
110 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (12, ROW(NULL,NULL)=ROW(100,200))
111 master-bin.000001 # Query # # COMMIT
112 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
113 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (12, ROW(100,200)=ROW(NULL,NULL))
114 master-bin.000001 # Query # # COMMIT
115 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
116 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE ROW(NULL,NULL)=ROW(100,200)
117 master-bin.000001 # Query # # COMMIT
118 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
119 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,NULL)
120 master-bin.000001 # Query # # COMMIT
121 master-bin.000001 # Gtid # # GTID #-#-#
122 master-bin.000001 # Query # # use `test`; DROP TABLE "t1" /* generated by server */
123 master-bin.000001 # Gtid # # GTID #-#-#
124 master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
125 #
126 # Testing ROW fields in LIMIT
127 #
128 FLUSH LOGS;
129 CREATE TABLE t1 (a INT);
130 INSERT INTO t1 VALUES (10),(10);
131 CREATE TABLE t2 (a INT);
132 CREATE PROCEDURE p1()
133 AS
134 a INT:= 1;
135 rec ROW(a INT);
136 BEGIN
137 rec.a:= 1;
138 INSERT INTO t2 SELECT 1 FROM t1 LIMIT a;
139 INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a;
140 END;
141 $$
142 CALL p1();
143 Warnings:
144 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
145 DROP TABLE t1,t2;
146 DROP PROCEDURE p1;
147 include/show_binlog_events.inc
148 Log_name Pos Event_type Server_id End_log_pos Info
149 master-bin.000002 # Binlog_checkpoint # # master-bin.000002
150 master-bin.000002 # Gtid # # GTID #-#-#
151 master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (a INT)
152 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
153 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10),(10)
154 master-bin.000002 # Query # # COMMIT
155 master-bin.000002 # Gtid # # GTID #-#-#
156 master-bin.000002 # Query # # use `test`; CREATE TABLE t2 (a INT)
157 master-bin.000002 # Gtid # # GTID #-#-#
158 master-bin.000002 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
159 AS
160 a INT:= 1;
161 rec ROW(a INT);
162 BEGIN
163 rec.a:= 1;
164 INSERT INTO t2 SELECT 1 FROM t1 LIMIT a;
165 INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a;
166 END
167 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
168 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT 1 FROM t1 LIMIT 1
169 master-bin.000002 # Query # # COMMIT
170 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
171 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT 2 FROM t1 LIMIT 1
172 master-bin.000002 # Query # # COMMIT
173 master-bin.000002 # Gtid # # GTID #-#-#
174 master-bin.000002 # Query # # use `test`; DROP TABLE "t1","t2" /* generated by server */
175 master-bin.000002 # Gtid # # GTID #-#-#
176 master-bin.000002 # Query # # use `test`; DROP PROCEDURE p1
177 #
178 # End of MDEV-10914 ROW data type for stored routine variables
179 #
180 #
181 # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
182 #
183 CREATE TABLE t1 (a INT, b INT);
184 CREATE PROCEDURE p1
185 AS
186 rec t1%ROWTYPE;
187 BEGIN
188 rec.a:=100;
189 rec.b:=200;
190 SELECT rec=ROW(100,200) AS true1, ROW(100,200)=rec AS true2;
191 INSERT INTO t1 VALUES (rec.a,rec.b);
192 INSERT INTO t1 VALUES (10, rec=ROW(100,200));
193 INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
194 INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
195 INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
196 rec.a:=NULL;
197 INSERT INTO t1 VALUES (11, rec=ROW(100,200));
198 INSERT INTO t1 VALUES (11, rec=ROW(100,201));
199 INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
200 INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
201 INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
202 INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
203 rec.b:=NULL;
204 INSERT INTO t1 VALUES (12, rec=ROW(100,200));
205 INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
206 INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
207 INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
208 END;
209 $$
210 CALL p1();
211 true1 true2
212 1 1
213 SELECT * FROM t1;
214 a b
215 100 200
216 10 1
217 10 1
218 10 20
219 10 21
220 11 NULL
221 11 0
222 11 NULL
223 11 0
224 12 NULL
225 12 NULL
226 DROP TABLE t1;
227 DROP PROCEDURE p1;
228 include/show_binlog_events.inc
229 Log_name Pos Event_type Server_id End_log_pos Info
230 master-bin.000002 # Binlog_checkpoint # # master-bin.000002
231 master-bin.000002 # Gtid # # GTID #-#-#
232 master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (a INT)
233 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
234 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10),(10)
235 master-bin.000002 # Query # # COMMIT
236 master-bin.000002 # Gtid # # GTID #-#-#
237 master-bin.000002 # Query # # use `test`; CREATE TABLE t2 (a INT)
238 master-bin.000002 # Gtid # # GTID #-#-#
239 master-bin.000002 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
240 AS
241 a INT:= 1;
242 rec ROW(a INT);
243 BEGIN
244 rec.a:= 1;
245 INSERT INTO t2 SELECT 1 FROM t1 LIMIT a;
246 INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a;
247 END
248 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
249 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT 1 FROM t1 LIMIT 1
250 master-bin.000002 # Query # # COMMIT
251 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
252 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT 2 FROM t1 LIMIT 1
253 master-bin.000002 # Query # # COMMIT
254 master-bin.000002 # Gtid # # GTID #-#-#
255 master-bin.000002 # Query # # use `test`; DROP TABLE "t1","t2" /* generated by server */
256 master-bin.000002 # Gtid # # GTID #-#-#
257 master-bin.000002 # Query # # use `test`; DROP PROCEDURE p1
258 master-bin.000002 # Gtid # # GTID #-#-#
259 master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT)
260 master-bin.000002 # Gtid # # GTID #-#-#
261 master-bin.000002 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
262 AS
263 rec t1%ROWTYPE;
264 BEGIN
265 rec.a:=100;
266 rec.b:=200;
267 SELECT rec=ROW(100,200) AS true1, ROW(100,200)=rec AS true2;
268 INSERT INTO t1 VALUES (rec.a,rec.b);
269 INSERT INTO t1 VALUES (10, rec=ROW(100,200));
270 INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
271 INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
272 INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
273 rec.a:=NULL;
274 INSERT INTO t1 VALUES (11, rec=ROW(100,200));
275 INSERT INTO t1 VALUES (11, rec=ROW(100,201));
276 INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
277 INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
278 INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
279 INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
280 rec.b:=NULL;
281 INSERT INTO t1 VALUES (12, rec=ROW(100,200));
282 INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
283 INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
284 INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
285 END
286 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
287 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('rec.a',100), NAME_CONST('rec.b',200))
288 master-bin.000002 # Query # # COMMIT
289 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
290 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200))
291 master-bin.000002 # Query # # COMMIT
292 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
293 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200))
294 master-bin.000002 # Query # # COMMIT
295 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
296 master-bin.000002 # Query # # use `test`; INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE ROW(100,200)=ROW(100,200)
297 master-bin.000002 # Query # # COMMIT
298 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
299 master-bin.000002 # Query # # use `test`; INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=ROW(100,200)
300 master-bin.000002 # Query # # COMMIT
301 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
302 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,200))
303 master-bin.000002 # Query # # COMMIT
304 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
305 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,201))
306 master-bin.000002 # Query # # COMMIT
307 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
308 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(100,200)=ROW(NULL,200))
309 master-bin.000002 # Query # # COMMIT
310 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
311 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(100,201)=ROW(NULL,200))
312 master-bin.000002 # Query # # COMMIT
313 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
314 master-bin.000002 # Query # # use `test`; INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE ROW(NULL,200)=ROW(100,200)
315 master-bin.000002 # Query # # COMMIT
316 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
317 master-bin.000002 # Query # # use `test`; INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,200)
318 master-bin.000002 # Query # # COMMIT
319 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
320 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (12, ROW(NULL,NULL)=ROW(100,200))
321 master-bin.000002 # Query # # COMMIT
322 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
323 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (12, ROW(100,200)=ROW(NULL,NULL))
324 master-bin.000002 # Query # # COMMIT
325 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
326 master-bin.000002 # Query # # use `test`; INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE ROW(NULL,NULL)=ROW(100,200)
327 master-bin.000002 # Query # # COMMIT
328 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
329 master-bin.000002 # Query # # use `test`; INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,NULL)
330 master-bin.000002 # Query # # COMMIT
331 master-bin.000002 # Gtid # # GTID #-#-#
332 master-bin.000002 # Query # # use `test`; DROP TABLE "t1" /* generated by server */
333 master-bin.000002 # Gtid # # GTID #-#-#
334 master-bin.000002 # Query # # use `test`; DROP PROCEDURE p1
335 #
336 # MDEV-12291 Allow ROW variables as SELECT INTO targets
337 #
338 FLUSH LOGS;
339 CREATE TABLE t1 (a INT, b VARCHAR(32));
340 INSERT INTO t1 VALUES (10, 'b10');
341 CREATE TABLE t2 LIKE t1;
342 CREATE PROCEDURE p1
343 AS
344 rec1 ROW(a INT, b VARCHAR(32));
345 BEGIN
346 SELECT * INTO rec1 FROM t1;
347 INSERT INTO t2 VALUES (rec1.a, rec1.b);
348 END;
349 $$
350 CALL p1();
351 SELECT * FROM t1;
352 a b
353 10 b10
354 DROP TABLE t1;
355 DROP TABLE t2;
356 DROP PROCEDURE p1;
357 include/show_binlog_events.inc
358 Log_name Pos Event_type Server_id End_log_pos Info
359 master-bin.000003 # Binlog_checkpoint # # master-bin.000003
360 master-bin.000003 # Gtid # # GTID #-#-#
361 master-bin.000003 # Query # # use `test`; CREATE TABLE t1 (a INT, b VARCHAR(32))
362 master-bin.000003 # Gtid # # BEGIN GTID #-#-#
363 master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (10, 'b10')
364 master-bin.000003 # Query # # COMMIT
365 master-bin.000003 # Gtid # # GTID #-#-#
366 master-bin.000003 # Query # # use `test`; CREATE TABLE t2 LIKE t1
367 master-bin.000003 # Gtid # # GTID #-#-#
368 master-bin.000003 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
369 AS
370 rec1 ROW(a INT, b VARCHAR(32));
371 BEGIN
372 SELECT * INTO rec1 FROM t1;
373 INSERT INTO t2 VALUES (rec1.a, rec1.b);
374 END
375 master-bin.000003 # Gtid # # BEGIN GTID #-#-#
376 master-bin.000003 # Query # # use `test`; INSERT INTO t2 VALUES ( NAME_CONST('rec1.a',10), NAME_CONST('rec1.b',_utf8mb4'b10' COLLATE 'utf8mb4_uca1400_ai_ci'))
377 master-bin.000003 # Query # # COMMIT
378 master-bin.000003 # Gtid # # GTID #-#-#
379 master-bin.000003 # Query # # use `test`; DROP TABLE "t1" /* generated by server */
380 master-bin.000003 # Gtid # # GTID #-#-#
381 master-bin.000003 # Query # # use `test`; DROP TABLE "t2" /* generated by server */
382 master-bin.000003 # Gtid # # GTID #-#-#
383 master-bin.000003 # Query # # use `test`; DROP PROCEDURE p1
384 FLUSH LOGS;
385 CREATE TABLE t1 (a INT, b VARCHAR(32));
386 INSERT INTO t1 VALUES (10, 'b10');
387 CREATE TABLE t2 LIKE t1;
388 CREATE PROCEDURE p1
389 AS
390 rec1 t1%ROWTYPE;
391 BEGIN
392 SELECT * INTO rec1 FROM t1;
393 INSERT INTO t2 VALUES (rec1.a, rec1.b);
394 END;
395 $$
396 CALL p1();
397 SELECT * FROM t1;
398 a b
399 10 b10
400 DROP TABLE t1;
401 DROP TABLE t2;
402 DROP PROCEDURE p1;
403 include/show_binlog_events.inc
404 Log_name Pos Event_type Server_id End_log_pos Info
405 master-bin.000004 # Binlog_checkpoint # # master-bin.000004
406 master-bin.000004 # Gtid # # GTID #-#-#
407 master-bin.000004 # Query # # use `test`; CREATE TABLE t1 (a INT, b VARCHAR(32))
408 master-bin.000004 # Gtid # # BEGIN GTID #-#-#
409 master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (10, 'b10')
410 master-bin.000004 # Query # # COMMIT
411 master-bin.000004 # Gtid # # GTID #-#-#
412 master-bin.000004 # Query # # use `test`; CREATE TABLE t2 LIKE t1
413 master-bin.000004 # Gtid # # GTID #-#-#
414 master-bin.000004 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
415 AS
416 rec1 t1%ROWTYPE;
417 BEGIN
418 SELECT * INTO rec1 FROM t1;
419 INSERT INTO t2 VALUES (rec1.a, rec1.b);
420 END
421 master-bin.000004 # Gtid # # BEGIN GTID #-#-#
422 master-bin.000004 # Query # # use `test`; INSERT INTO t2 VALUES ( NAME_CONST('rec1.a',10), NAME_CONST('rec1.b',_utf8mb4'b10' COLLATE 'utf8mb4_uca1400_ai_ci'))
423 master-bin.000004 # Query # # COMMIT
424 master-bin.000004 # Gtid # # GTID #-#-#
425 master-bin.000004 # Query # # use `test`; DROP TABLE "t1" /* generated by server */
426 master-bin.000004 # Gtid # # GTID #-#-#
427 master-bin.000004 # Query # # use `test`; DROP TABLE "t2" /* generated by server */
428 master-bin.000004 # Gtid # # GTID #-#-#
429 master-bin.000004 # Query # # use `test`; DROP PROCEDURE p1
430 FLUSH LOGS;
431 CREATE TABLE t1 (a INT, b VARCHAR(32));
432 INSERT INTO t1 VALUES (10, 'b10');
433 CREATE TABLE t2 LIKE t1;
434 CREATE PROCEDURE p1
435 AS
436 CURSOR cur1 IS SELECT * FROM t1;
437 rec1 cur1%ROWTYPE;
438 BEGIN
439 SELECT * INTO rec1 FROM t1;
440 INSERT INTO t2 VALUES (rec1.a, rec1.b);
441 END;
442 $$
443 CALL p1();
444 SELECT * FROM t1;
445 a b
446 10 b10
447 DROP TABLE t1;
448 DROP TABLE t2;
449 DROP PROCEDURE p1;
450 include/show_binlog_events.inc
451 Log_name Pos Event_type Server_id End_log_pos Info
452 master-bin.000005 # Binlog_checkpoint # # master-bin.000005
453 master-bin.000005 # Gtid # # GTID #-#-#
454 master-bin.000005 # Query # # use `test`; CREATE TABLE t1 (a INT, b VARCHAR(32))
455 master-bin.000005 # Gtid # # BEGIN GTID #-#-#
456 master-bin.000005 # Query # # use `test`; INSERT INTO t1 VALUES (10, 'b10')
457 master-bin.000005 # Query # # COMMIT
458 master-bin.000005 # Gtid # # GTID #-#-#
459 master-bin.000005 # Query # # use `test`; CREATE TABLE t2 LIKE t1
460 master-bin.000005 # Gtid # # GTID #-#-#
461 master-bin.000005 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
462 AS
463 CURSOR cur1 IS SELECT * FROM t1;
464 rec1 cur1%ROWTYPE;
465 BEGIN
466 SELECT * INTO rec1 FROM t1;
467 INSERT INTO t2 VALUES (rec1.a, rec1.b);
468 END
469 master-bin.000005 # Gtid # # BEGIN GTID #-#-#
470 master-bin.000005 # Query # # use `test`; INSERT INTO t2 VALUES ( NAME_CONST('rec1.a',10), NAME_CONST('rec1.b',_utf8mb4'b10' COLLATE 'utf8mb4_uca1400_ai_ci'))
471 master-bin.000005 # Query # # COMMIT
472 master-bin.000005 # Gtid # # GTID #-#-#
473 master-bin.000005 # Query # # use `test`; DROP TABLE "t1" /* generated by server */
474 master-bin.000005 # Gtid # # GTID #-#-#
475 master-bin.000005 # Query # # use `test`; DROP TABLE "t2" /* generated by server */
476 master-bin.000005 # Gtid # # GTID #-#-#
477 master-bin.000005 # Query # # use `test`; DROP PROCEDURE p1
478 #
479 # MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication
480 #
481 FLUSH LOGS;
482 CREATE TABLE t1 (d DATE);
483 INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
484 CREATE TABLE t2 (d DATE, c BIGINT);
485 DECLARE
486 var INT;
487 BEGIN
488 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
489 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
490 END;
491 $$
492 DROP TABLE t1,t2;
493 include/show_binlog_events.inc
494 Log_name Pos Event_type Server_id End_log_pos Info
495 master-bin.000006 # Binlog_checkpoint # # master-bin.000006
496 master-bin.000006 # Gtid # # GTID #-#-#
497 master-bin.000006 # Query # # use `test`; CREATE TABLE t1 (d DATE)
498 master-bin.000006 # Gtid # # BEGIN GTID #-#-#
499 master-bin.000006 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
500 master-bin.000006 # Query # # COMMIT
501 master-bin.000006 # Gtid # # GTID #-#-#
502 master-bin.000006 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
503 master-bin.000006 # Gtid # # BEGIN GTID #-#-#
504 master-bin.000006 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',NULL)
505 master-bin.000006 # Query # # COMMIT
506 master-bin.000006 # Gtid # # BEGIN GTID #-#-#
507 master-bin.000006 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',NULL) WITH ROLLUP
508 master-bin.000006 # Query # # COMMIT
509 master-bin.000006 # Gtid # # GTID #-#-#
510 master-bin.000006 # Query # # use `test`; DROP TABLE "t1","t2" /* generated by server */