1--source include/have_binlog_format_statement.inc
2
3--disable_query_log
4reset master; # get rid of previous tests binlog
5--enable_query_log
6
7--echo #
8--echo # MDEV-11815 SP variables of temporal data types do not replicate correctly
9--echo #
10
11CREATE TABLE t1(a INT);
12DELIMITER $$;
13CREATE PROCEDURE p1()
14BEGIN
15  DECLARE i   INT          DEFAULT 123;
16  DECLARE b8  BIT(8)       DEFAULT 0x61;
17  DECLARE t0  TIME         DEFAULT '01:01:01';
18  DECLARE t6  TIME(6)      DEFAULT '01:01:01.123456';
19  DECLARE d   DATE         DEFAULT '2001-01-01';
20  DECLARE dt0 DATETIME     DEFAULT '2001-01-01 01:01:01';
21  DECLARE dt6 DATETIME(6)  DEFAULT '2001-01-01 01:01:01.123456';
22  DECLARE ts0 TIMESTAMP    DEFAULT '2001-01-01 01:01:01';
23  DECLARE ts6 TIMESTAMP(6) DEFAULT '2001-01-01 01:01:01.123456';
24  INSERT INTO t1 VALUES (i=0x61);
25  INSERT INTO t1 VALUES (b8=0x61);
26  INSERT INTO t1 VALUES (t0=10101);
27  INSERT INTO t1 VALUES (t6=10101);
28  INSERT INTO t1 VALUES (d=20010101);
29  INSERT INTO t1 VALUES (dt0=20010101010101);
30  INSERT INTO t1 VALUES (dt6=20010101010101);
31  INSERT INTO t1 VALUES (ts0=20010101010101);
32  INSERT INTO t1 VALUES (ts6=20010101010101);
33END;
34$$
35DELIMITER ;$$
36CALL p1;
37DROP TABLE t1;
38DROP PROCEDURE p1;
39
40--let $binlog_file = LAST
41source include/show_binlog_events.inc;
42
43--echo #
44--echo # MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication
45--echo #
46
47FLUSH LOGS;
48CREATE TABLE t1 (d DATE);
49INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
50CREATE TABLE t2 (d DATE, c BIGINT);
51DELIMITER $$;
52BEGIN NOT ATOMIC
53  BEGIN
54    DECLARE var INT DEFAULT 10;
55    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
56    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
57  END;
58  BEGIN
59    DECLARE atomic INT DEFAULT 20;
60    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic;
61    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic WITH ROLLUP;
62    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`;
63    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic` WITH ROLLUP;
64  END;
65  BEGIN
66    DECLARE atomic ROW (atomic INT, xxx INT) DEFAULT (31,32);
67    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.atomic;
68    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.atomic WITH ROLLUP;
69    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`atomic`;
70    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`atomic` WITH ROLLUP;
71    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.xxx;
72    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.xxx WITH ROLLUP;
73    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`xxx`;
74    INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`xxx` WITH ROLLUP;
75  END;
76END;
77$$
78DELIMITER ;$$
79DROP TABLE t1,t2;
80
81--let $binlog_file = LAST
82source include/show_binlog_events.inc;
83