Debian Codegraph @

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

raw
1 --source include/not_embedded.inc
2 --source include/have_binlog_format_statement.inc
3
4 --disable_query_log
5 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
6 reset master; # get rid of previous tests binlog
7 --enable_query_log
8
9
10 SET sql_mode=ORACLE;
11
12 --echo #
13 --echo # MDEV-10914 ROW data type for stored routine variables
14 --echo #
15
16 CREATE TABLE t1 (a INT, b INT);
17 DELIMITER $$;
18 CREATE PROCEDURE p1
19 AS
20 rec ROW(a INT,b INT);
21 BEGIN
22 rec.a:=100;
23 rec.b:=200;
24 INSERT INTO t1 VALUES (rec.a,rec.b);
25 INSERT INTO t1 VALUES (10, rec=ROW(100,200));
26 INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
27 INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
28 INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
29 rec.a:=NULL;
30 INSERT INTO t1 VALUES (11, rec=ROW(100,200));
31 INSERT INTO t1 VALUES (11, rec=ROW(100,201));
32 INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
33 INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
34 INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
35 INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
36 rec.b:=NULL;
37 INSERT INTO t1 VALUES (12, rec=ROW(100,200));
38 INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
39 INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
40 INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
41 END;
42 $$
43 DELIMITER ;$$
44 CALL p1();
45 SELECT * FROM t1;
46 DROP TABLE t1;
47 DROP PROCEDURE p1;
48 --let $binlog_file = LAST
49 source include/show_binlog_events.inc;
50
51
52 --echo #
53 --echo # Testing ROW fields in LIMIT
54 --echo #
55
56 FLUSH LOGS;
57 CREATE TABLE t1 (a INT);
58 INSERT INTO t1 VALUES (10),(10);
59 CREATE TABLE t2 (a INT);
60 DELIMITER $$;
61 CREATE PROCEDURE p1()
62 AS
63 a INT:= 1;
64 rec ROW(a INT);
65 BEGIN
66 rec.a:= 1;
67 INSERT INTO t2 SELECT 1 FROM t1 LIMIT a;
68 INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a;
69 END;
70 $$
71 DELIMITER ;$$
72 CALL p1();
73 DROP TABLE t1,t2;
74 DROP PROCEDURE p1;
75 --let $binlog_file = LAST
76 source include/show_binlog_events.inc;
77
78
79 --echo #
80 --echo # End of MDEV-10914 ROW data type for stored routine variables
81 --echo #
82
83
84 --echo #
85 --echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
86 --echo #
87
88 CREATE TABLE t1 (a INT, b INT);
89 DELIMITER $$;
90 CREATE PROCEDURE p1
91 AS
92 rec t1%ROWTYPE;
93 BEGIN
94 rec.a:=100;
95 rec.b:=200;
96 SELECT rec=ROW(100,200) AS true1, ROW(100,200)=rec AS true2;
97 INSERT INTO t1 VALUES (rec.a,rec.b);
98 INSERT INTO t1 VALUES (10, rec=ROW(100,200));
99 INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
100 INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
101 INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
102 rec.a:=NULL;
103 INSERT INTO t1 VALUES (11, rec=ROW(100,200));
104 INSERT INTO t1 VALUES (11, rec=ROW(100,201));
105 INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
106 INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
107 INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
108 INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
109 rec.b:=NULL;
110 INSERT INTO t1 VALUES (12, rec=ROW(100,200));
111 INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
112 INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
113 INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
114 END;
115 $$
116 DELIMITER ;$$
117 CALL p1();
118 SELECT * FROM t1;
119 DROP TABLE t1;
120 DROP PROCEDURE p1;
121 --let $binlog_file = LAST
122 source include/show_binlog_events.inc;
123
124
125 --echo #
126 --echo # MDEV-12291 Allow ROW variables as SELECT INTO targets
127 --echo #
128
129 FLUSH LOGS;
130 CREATE TABLE t1 (a INT, b VARCHAR(32));
131 INSERT INTO t1 VALUES (10, 'b10');
132 CREATE TABLE t2 LIKE t1;
133 DELIMITER $$;
134 CREATE PROCEDURE p1
135 AS
136 rec1 ROW(a INT, b VARCHAR(32));
137 BEGIN
138 SELECT * INTO rec1 FROM t1;
139 INSERT INTO t2 VALUES (rec1.a, rec1.b);
140 END;
141 $$
142 DELIMITER ;$$
143 CALL p1();
144 SELECT * FROM t1;
145 DROP TABLE t1;
146 DROP TABLE t2;
147 DROP PROCEDURE p1;
148 --let $binlog_file = LAST
149 source include/show_binlog_events.inc;
150
151
152 FLUSH LOGS;
153 CREATE TABLE t1 (a INT, b VARCHAR(32));
154 INSERT INTO t1 VALUES (10, 'b10');
155 CREATE TABLE t2 LIKE t1;
156 DELIMITER $$;
157 CREATE PROCEDURE p1
158 AS
159 rec1 t1%ROWTYPE;
160 BEGIN
161 SELECT * INTO rec1 FROM t1;
162 INSERT INTO t2 VALUES (rec1.a, rec1.b);
163 END;
164 $$
165 DELIMITER ;$$
166 CALL p1();
167 SELECT * FROM t1;
168 DROP TABLE t1;
169 DROP TABLE t2;
170 DROP PROCEDURE p1;
171 --let $binlog_file = LAST
172 source include/show_binlog_events.inc;
173
174
175 FLUSH LOGS;
176 CREATE TABLE t1 (a INT, b VARCHAR(32));
177 INSERT INTO t1 VALUES (10, 'b10');
178 CREATE TABLE t2 LIKE t1;
179 DELIMITER $$;
180 CREATE PROCEDURE p1
181 AS
182 CURSOR cur1 IS SELECT * FROM t1;
183 rec1 cur1%ROWTYPE;
184 BEGIN
185 SELECT * INTO rec1 FROM t1;
186 INSERT INTO t2 VALUES (rec1.a, rec1.b);
187 END;
188 $$
189 DELIMITER ;$$
190 CALL p1();
191 SELECT * FROM t1;
192 DROP TABLE t1;
193 DROP TABLE t2;
194 DROP PROCEDURE p1;
195 --let $binlog_file = LAST
196 source include/show_binlog_events.inc;
197
198
199 --echo #
200 --echo # MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication
201 --echo #
202
203 FLUSH LOGS;
204 CREATE TABLE t1 (d DATE);
205 INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
206 CREATE TABLE t2 (d DATE, c BIGINT);
207 DELIMITER $$;
208 DECLARE
209 var INT;
210 BEGIN
211 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
212 INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
213 END;
214 $$
215 DELIMITER ;$$
216 DROP TABLE t1,t2;
217
218 --let $binlog_file = LAST
219 source include/show_binlog_events.inc;