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