Debian Codegraph @

mysql-test/suite/binlog/r/binlog_stm_sp.result

raw
1 #
2 # MDEV-11815 SP variables of temporal data types do not replicate correctly
3 #
4 CREATE TABLE t1(a INT);
5 CREATE PROCEDURE p1()
6 BEGIN
7 DECLARE i INT DEFAULT 123;
8 DECLARE b8 BIT(8) DEFAULT 0x61;
9 DECLARE t0 TIME DEFAULT '01:01:01';
10 DECLARE t6 TIME(6) DEFAULT '01:01:01.123456';
11 DECLARE d DATE DEFAULT '2001-01-01';
12 DECLARE dt0 DATETIME DEFAULT '2001-01-01 01:01:01';
13 DECLARE dt6 DATETIME(6) DEFAULT '2001-01-01 01:01:01.123456';
14 DECLARE ts0 TIMESTAMP DEFAULT '2001-01-01 01:01:01';
15 DECLARE ts6 TIMESTAMP(6) DEFAULT '2001-01-01 01:01:01.123456';
16 INSERT INTO t1 VALUES (i=0x61);
17 INSERT INTO t1 VALUES (b8=0x61);
18 INSERT INTO t1 VALUES (t0=10101);
19 INSERT INTO t1 VALUES (t6=10101);
20 INSERT INTO t1 VALUES (d=20010101);
21 INSERT INTO t1 VALUES (dt0=20010101010101);
22 INSERT INTO t1 VALUES (dt6=20010101010101);
23 INSERT INTO t1 VALUES (ts0=20010101010101);
24 INSERT INTO t1 VALUES (ts6=20010101010101);
25 END;
26 $$
27 CALL p1;
28 DROP TABLE t1;
29 DROP PROCEDURE p1;
30 include/show_binlog_events.inc
31 Log_name Pos Event_type Server_id End_log_pos Info
32 master-bin.000001 # Gtid # # GTID #-#-#
33 master-bin.000001 # Query # # use `test`; CREATE TABLE t1(a INT)
34 master-bin.000001 # Gtid # # GTID #-#-#
35 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
36 BEGIN
37 DECLARE i INT DEFAULT 123;
38 DECLARE b8 BIT(8) DEFAULT 0x61;
39 DECLARE t0 TIME DEFAULT '01:01:01';
40 DECLARE t6 TIME(6) DEFAULT '01:01:01.123456';
41 DECLARE d DATE DEFAULT '2001-01-01';
42 DECLARE dt0 DATETIME DEFAULT '2001-01-01 01:01:01';
43 DECLARE dt6 DATETIME(6) DEFAULT '2001-01-01 01:01:01.123456';
44 DECLARE ts0 TIMESTAMP DEFAULT '2001-01-01 01:01:01';
45 DECLARE ts6 TIMESTAMP(6) DEFAULT '2001-01-01 01:01:01.123456';
46 INSERT INTO t1 VALUES (i=0x61);
47 INSERT INTO t1 VALUES (b8=0x61);
48 INSERT INTO t1 VALUES (t0=10101);
49 INSERT INTO t1 VALUES (t6=10101);
50 INSERT INTO t1 VALUES (d=20010101);
51 INSERT INTO t1 VALUES (dt0=20010101010101);
52 INSERT INTO t1 VALUES (dt6=20010101010101);
53 INSERT INTO t1 VALUES (ts0=20010101010101);
54 INSERT INTO t1 VALUES (ts6=20010101010101);
55 END
56 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
57 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('i',123)=0x61)
58 master-bin.000001 # Query # # COMMIT
59 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
60 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('b8',_binary'a' COLLATE 'binary')=0x61)
61 master-bin.000001 # Query # # COMMIT
62 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
63 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('t0',TIME'01:01:01')=10101)
64 master-bin.000001 # Query # # COMMIT
65 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
66 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('t6',TIME'01:01:01.123456')=10101)
67 master-bin.000001 # Query # # COMMIT
68 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
69 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('d',DATE'2001-01-01')=20010101)
70 master-bin.000001 # Query # # COMMIT
71 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
72 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('dt0',TIMESTAMP'2001-01-01 01:01:01')=20010101010101)
73 master-bin.000001 # Query # # COMMIT
74 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
75 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('dt6',TIMESTAMP'2001-01-01 01:01:01.123456')=20010101010101)
76 master-bin.000001 # Query # # COMMIT
77 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
78 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('ts0',TIMESTAMP'2001-01-01 01:01:01')=20010101010101)
79 master-bin.000001 # Query # # COMMIT
80 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
81 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('ts6',TIMESTAMP'2001-01-01 01:01:01.123456')=20010101010101)
82 master-bin.000001 # Query # # COMMIT
83 master-bin.000001 # Gtid # # GTID #-#-#
84 master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
85 master-bin.000001 # Gtid # # GTID #-#-#
86 master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
87 #
88 # MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication
89 #
90 FLUSH LOGS;
91 CREATE TABLE t1 (d DATE);
92 INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
93 CREATE TABLE t2 (d DATE, c BIGINT);
94 BEGIN NOT ATOMIC
95 BEGIN
96 DECLARE var INT DEFAULT 10;
97 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
98 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
99 END;
100 BEGIN
101 DECLARE atomic INT DEFAULT 20;
102 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic;
103 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic WITH ROLLUP;
104 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`;
105 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic` WITH ROLLUP;
106 END;
107 BEGIN
108 DECLARE atomic ROW (atomic INT, xxx INT) DEFAULT (31,32);
109 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.atomic;
110 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.atomic WITH ROLLUP;
111 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`atomic`;
112 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`atomic` WITH ROLLUP;
113 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.xxx;
114 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.xxx WITH ROLLUP;
115 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`xxx`;
116 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`xxx` WITH ROLLUP;
117 END;
118 END;
119 $$
120 DROP TABLE t1,t2;
121 include/show_binlog_events.inc
122 Log_name Pos Event_type Server_id End_log_pos Info
123 master-bin.000002 # Binlog_checkpoint # # master-bin.000002
124 master-bin.000002 # Gtid # # GTID #-#-#
125 master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (d DATE)
126 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
127 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
128 master-bin.000002 # Query # # COMMIT
129 master-bin.000002 # Gtid # # GTID #-#-#
130 master-bin.000002 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
131 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
132 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',10)
133 master-bin.000002 # Query # # COMMIT
134 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
135 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',10) WITH ROLLUP
136 master-bin.000002 # Query # # COMMIT
137 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
138 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20)
139 master-bin.000002 # Query # # COMMIT
140 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
141 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20) WITH ROLLUP
142 master-bin.000002 # Query # # COMMIT
143 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
144 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20)
145 master-bin.000002 # Query # # COMMIT
146 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
147 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20) WITH ROLLUP
148 master-bin.000002 # Query # # COMMIT
149 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
150 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31)
151 master-bin.000002 # Query # # COMMIT
152 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
153 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31) WITH ROLLUP
154 master-bin.000002 # Query # # COMMIT
155 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
156 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31)
157 master-bin.000002 # Query # # COMMIT
158 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
159 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31) WITH ROLLUP
160 master-bin.000002 # Query # # COMMIT
161 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
162 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32)
163 master-bin.000002 # Query # # COMMIT
164 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
165 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32) WITH ROLLUP
166 master-bin.000002 # Query # # COMMIT
167 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
168 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32)
169 master-bin.000002 # Query # # COMMIT
170 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
171 master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32) WITH ROLLUP
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 */