Debian Codegraph @

mysql-test/suite/rpl/t/rpl_window_functions.test

raw
1 # WL#9236, WL#9603, WL#9727
2 # Sanity tests for window funtions with replication
3
4 --source include/master-slave.inc
5
6 --echo # Create table with columns of different data types and insert values
7
8 --connection master
9
10 CREATE TABLE t1 (pk INT PRIMARY KEY,a DECIMAL(5,2),
11 b DOUBLE, c CHAR(10), d VARCHAR(20),
12 e DATE, f DATETIME, g TEXT, h BLOB, i VARBINARY(32));
13
14 INSERT INTO t1 VALUES
15 (1, 258, 6.8, 'Orange', 'Australia', '2002-02-20', '2011-07-29 02:43:24', 'Cookley', 'FgaBAi419MbgSXyFgaBAi4QYYtKp4EotEr6nHrFyw', x'24c38d4ac1e8def7c9beff99d2ef7bd0'),
16 (2, null, 2, 'Blue', 'Australia', '2004-07-17', '2010-06-04 21:51:57', 'Temp', '1cNnegQruhm9r6M7s96Lt8t2vDP5LLoz5', x'5fb4efd5f7e90783a792c6134ceb539f'),
17 (3, 72, 44.44, 'Blue', 'Madagascar', '1999-09-09', '2006-08-11 06:55:50', 'Bigtax', '199utjJtezqMHCewY', x'f9e4f8c6d7b5657b0854ceb1d628c9e7'),
18 (4, 9.2, 253, 'Green', 'Russia', '2004-09-16', '1996-10-22 11:59:17', 'Stim', '1JmpHmWbpyBNBJavMBt7NS48UEoWcJ7GSvJavMas', x'018f672ec2ab8c9bdf8160f79b7bf283'),
19 (5, 6, 44.44, 'Crimson', 'Netherlands', '2003-01-27', '2017-01-02 01:43:16', 'Bytecard', '1FZaNCRe6fzyFiBVyqwfg72eHvo3hZXQXFasasf', x'64ed41f90735e3ab48513ab920d1c6d7'),
20 (6, 70, 83932, 'Indigo', 'India', '2011-08-09', '2008-08-12 13:18:47', 'Alpha', '1Phng2CeXWwmbmdY9qwdqwdnTqBv6ecWgZBj3BKs', x'71048e95216358a9d95e1ed946a457ad'),
21 (7, 96, 6, 'Khaki', 'Luxembourg', '2009-10-22', '1997-11-30 15:00:16', 'Zontrax', '1Ph2ZUtAVskwebMnyKLRG5GasfasfG4RkdTQujTc', x'88b67753fb0c181d1f7f21e31c3c92c9'),
22 (8, 8.43, 2, 'Pink', 'China', '2005-06-15', '1998-11-02 17:47:53', 'Gembucket', '14BbxEvaUK7nyCzXc9YvkxQasfdewefwefTiZrnVHVFj1', x'e7f08241ac2573e39be9bdc4c8f42a44'),
23 (9, 221, -8.67, 'Magenta', 'Australia', '2010-09-20', '2007-08-27 14:06:53', 'Y-find', '138GWMWSZTqkkm8eQk3sdfsdfsdJDpv72i6cFuyemc', x'583b4cb865f1f2644bf1420595a08c53'),
24 (10, 430, 1.23456, 'Turquoise', 'Philippines', '2002-08-02', '2000-02-19 02:49:17', 'Tin', '1CPkZjahzzRgoqz4W1NfmHsdfsdfsdf5vueJ3irb7Ae', x'7273d3dc246ecfe4bdc7c8155d1ee6b7'),
25 (11, 80, 6.8, 'Orange', 'Australia', '2002-02-20', '2011-07-29 02:43:24', 'Cookley', '19MbgSXyFgaBAi4sdfsdfQYYtKp4EotEr6nHrFyw', x'a17d1e699322177dfa7c415bb35f22a2'),
26 (12, 95 , 2, 'Blue', 'Australia', '2004-07-17', '2010-06-04 21:51:57', 'Temp', '1cNnegQr1234234uhm9r6M7s96Lt8t2vDP5LLoz5', x'435f41a9060f01f0669942eeb4ed2e80'),
27 (13, 72, 69179, 'Blue', 'Madagascar', '1999-09-09', '2006-08-11 06:55:50', 'Bigtax', 'nmklaw9utjJtezqMHCewR9YpKtm3LaXmt5h4PY', x'92d3b9ee039b388673e575c7f269ce60'),
28 (14, 6, 2531, 'Turquoise', 'Russia', '2004-09-16', '1996-10-22 11:59:17', 'Stim', 'avMBt7NasdasdsadS48UEoWcJ7GSv', x'f0688d9f99b6c80ec459bf9e4a79f411'),
29 (15, 9.2, 44, 'Indigo', 'Netherlands', '2003-01-27', '2017-01-02 01:43:16', 'Bytecard', '1asdfRe6fzyFiBVyqwfg72eHvo3hZXQXF', x'3d643793f96df74f4f680557eebf5326'),
30 (16, 72, 832, 'Green', 'India', '2011-08-09', '2008-08-12 13:18:47', 'Alpha', 'XWwmbmdY9nTqBv6ecWgZBj3BKs', x'fd7d92a8fe576067319ddd0fe44ddb44'),
31 (17, 70, 1.23456, 'Khaki', 'Luxembourg', '2009-10-22', '1997-11-30 15:00:16', 'Zontrax', '1Ph2ZUtAVskwebMnyKwEFWEFLRG5GG4RkdTQujTc', x'4063e204f4ac3578c71c1f33a7968a53'),
32 (18, 96, 2, 'Pink', 'China', '2005-06-15', '1998-11-02 17:47:53', 'Gembucket', '14BbxEvaUK7nyCzXcewaf9YvkxQTiZrnVHVFj1', x'7c146d3cc41ad9a48be5864d3d05e6d0'),
33 (19, 21, -8.67, 'Magenta', 'Australia', '2010-09-20', '2007-08-27 14:06:53', 'Y-find', '138GWMWSZTqkkm8eQk3JaweffwDpv72i6cFuyemc', x'05534c032b05ad0527a6768d45d36568'),
34 (20, 8.43, 253, 'Crimson', 'Philippines', '2002-08-02', '2000-02-19 02:49:17', 'Tin', '1CPkZjahzzRgoqz4W1NfmH5vueJ3irb7Ae', x'e7f7807e521c9f9a2276b438cc0ca203');
35
36 --echo # INSERT ... SELECT ... using basic non-aggregate window functions
37
38 CREATE TABLE t2 (
39 c1 int unsigned NOT NULL DEFAULT '0',
40 c2 int unsigned NOT NULL DEFAULT '0',
41 c3 int unsigned NOT NULL DEFAULT '0',
42 c4 int unsigned DEFAULT NULL,
43 c5 double NOT NULL DEFAULT '0',
44 c6 double NOT NULL DEFAULT '0'
45 );
46
47 INSERT INTO t2 SELECT
48 ROW_NUMBER() OVER w1 c1,
49 RANK() OVER w1 c2,
50 DENSE_RANK() OVER w1 c3,
51 NTILE(3) OVER w1 c4,
52 PERCENT_RANK() OVER w1 c5,
53 CUME_DIST() OVER w1 c6
54 FROM t1 WINDOW w1 AS (PARTITION BY c ORDER BY f);
55
56 INSERT INTO t2 SELECT
57 ROW_NUMBER() OVER (PARTITION BY b) c1,
58 RANK() OVER (PARTITION BY a ORDER BY e) c2,
59 DENSE_RANK() OVER (PARTITION BY a ORDER BY i) c3,
60 NTILE(3) OVER (ORDER BY b) c4,
61 PERCENT_RANK() OVER (ORDER BY a,b,c,d) c5,
62 CUME_DIST() OVER (ORDER BY e,f,g,h,i) c6
63 FROM t1;
64
65 --echo # INSERT ... SELECT with window functions having frame specification
66
67 CREATE TABLE t3a (
68 c1 decimal(27,2) DEFAULT NULL,
69 c2 double DEFAULT NULL,
70 c3 bigint(21) NOT NULL DEFAULT '0',
71 c4 int DEFAULT NULL,
72 c5 int DEFAULT NULL,
73 c6 varbinary(32),
74 c7 date DEFAULT NULL,
75 c8 datetime DEFAULT NULL,
76 c9 double DEFAULT NULL,
77 c10 double DEFAULT NULL,
78 c11 double DEFAULT NULL,
79 c12 double DEFAULT NULL,
80 c13 json,
81 c14 json
82 );
83
84 INSERT INTO t3a SELECT
85 SUM(a) OVER (ROWS UNBOUNDED PRECEDING) c1,
86 AVG(b) OVER (ORDER BY pk ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
87 COUNT(*) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
88 BIT_AND(pk) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c4,
89 BIT_OR(a) OVER (PARTITION BY d ORDER BY e ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
90 BIT_XOR(i) OVER (PARTITION BY c) c6,
91 MIN(e) OVER (ORDER BY e ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c7,
92 MAX(f) OVER (ORDER BY f ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) c8,
93 STDDEV_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c9,
94 VAR_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c10,
95 STDDEV_POP(b) OVER () c11,
96 VAR_POP(a) OVER () c12,
97 JSON_ARRAYAGG(e) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c13,
98 JSON_OBJECTAGG(pk, a) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) c14
99 FROM t1;
100
101 INSERT INTO t3a SELECT
102 SUM(a) OVER (RANGE UNBOUNDED PRECEDING) c1,
103 AVG(b) OVER (ORDER BY pk RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
104 COUNT(*) OVER (PARTITION BY d ORDER BY pk RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
105 BIT_AND(pk) OVER (PARTITION BY d ORDER BY pk RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c4,
106 BIT_OR(a) OVER (PARTITION BY d ORDER BY e RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
107 BIT_XOR(i) OVER (PARTITION BY c) c6,
108 MIN(e) OVER (ORDER BY e RANGE INTERVAL 5 YEAR PRECEDING) c7,
109 MAX(f) OVER (ORDER BY f RANGE BETWEEN CURRENT ROW AND INTERVAL 5 YEAR FOLLOWING) c8,
110 STDDEV_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c9,
111 VAR_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c10,
112 STDDEV_POP(b) OVER () c11,
113 VAR_POP(a) OVER () c12,
114 JSON_ARRAYAGG(b) OVER (ORDER BY f ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) c13,
115 JSON_OBJECTAGG(b, e) OVER (PARTITION BY d ORDER BY e ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c14
116 FROM t1;
117
118 # Set 'windowing_use_high_precision'
119 SET @master_save_windowing_use_high_precision= (SELECT @@windowing_use_high_precision);
120 SET GLOBAL windowing_use_high_precision=0;
121
122 --source include/sync_slave_sql_with_master.inc
123 --echo [Connection Slave]
124
125 SET @slave_save_windowing_use_high_precision= (SELECT @@windowing_use_high_precision);
126 SET GLOBAL windowing_use_high_precision=0;
127
128 --echo [Connection Master]
129 --connection master
130
131 CREATE TABLE t3b (
132 c1 decimal(27,2) DEFAULT NULL,
133 c2 double DEFAULT NULL,
134 c3 bigint(21) NOT NULL DEFAULT '0',
135 c4 int DEFAULT NULL,
136 c5 int DEFAULT NULL,
137 c6 varbinary(32),
138 c7 date DEFAULT NULL,
139 c8 datetime DEFAULT NULL,
140 c9 double DEFAULT NULL,
141 c10 double DEFAULT NULL,
142 c11 double DEFAULT NULL,
143 c12 double DEFAULT NULL,
144 c13 json,
145 c14 json
146 );
147
148 INSERT INTO t3b SELECT
149 SUM(a) OVER (ROWS UNBOUNDED PRECEDING) c1,
150 AVG(b) OVER (ORDER BY pk ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
151 COUNT(*) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
152 BIT_AND(pk) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c4,
153 BIT_OR(a) OVER (PARTITION BY d ORDER BY e ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
154 BIT_XOR(i) OVER (PARTITION BY c) c6,
155 MIN(e) OVER (ORDER BY e ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c7,
156 MAX(f) OVER (ORDER BY f ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) c8,
157 STDDEV_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c9,
158 VAR_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c10,
159 STDDEV_POP(b) OVER () c11,
160 VAR_POP(a) OVER () c12,
161 JSON_ARRAYAGG(b) OVER (ROWS UNBOUNDED PRECEDING) c13,
162 JSON_OBJECTAGG(pk, f) OVER (PARTITION BY e ORDER BY pk RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) c14
163 FROM t1;
164
165 INSERT INTO t3b SELECT
166 SUM(a) OVER (RANGE UNBOUNDED PRECEDING) c1,
167 AVG(b) OVER (ORDER BY pk RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
168 COUNT(*) OVER (PARTITION BY d ORDER BY pk RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
169 BIT_AND(pk) OVER (PARTITION BY d ORDER BY f RANGE INTERVAL 2 YEAR PRECEDING) c4,
170 BIT_OR(a) OVER (PARTITION BY d ORDER BY e RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
171 BIT_XOR(i) OVER (PARTITION BY c) c6,
172 MIN(e) OVER (ORDER BY e RANGE INTERVAL 5 YEAR PRECEDING) c4,
173 MAX(f) OVER (ORDER BY f RANGE BETWEEN CURRENT ROW AND INTERVAL 5 YEAR FOLLOWING) c5,
174 STDDEV_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c6,
175 VAR_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c7,
176 STDDEV_POP(b) OVER () c8,
177 VAR_POP(a) OVER () c9,
178 JSON_ARRAYAGG(e) OVER (ORDER BY e RANGE INTERVAL 4 YEAR PRECEDING) c12,
179 JSON_OBJECTAGG(pk, e) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c13
180 FROM t1;
181
182 # Reset variable 'windowing_use_high_precision'
183 SET GLOBAL windowing_use_high_precision=@master_save_windowing_use_high_precision;
184
185 --source include/sync_slave_sql_with_master.inc
186 --echo [Connection Slave]
187
188 SET GLOBAL windowing_use_high_precision=@slave_save_windowing_use_high_precision;
189
190 --echo [Connection Master]
191 --connection master
192
193 --echo # INSERT ... SELECT using window functions which return values having
194 --echo # same type as that of the column
195
196 CREATE TABLE t4 (c1 decimal(5,2), c2 double, c3 char(10), c4 varchar(20), c5 date, c6 datetime, c7 text, c8 blob);
197
198 INSERT INTO t4 SELECT
199 FIRST_VALUE(a) OVER w1,
200 FIRST_VALUE(b) OVER w1,
201 FIRST_VALUE(c) OVER w1,
202 FIRST_VALUE(d) OVER w1,
203 FIRST_VALUE(e) OVER w1,
204 FIRST_VALUE(f) OVER w1,
205 FIRST_VALUE(g) OVER (PARTITION BY b ORDER BY pk ROWS 2 PRECEDING),
206 FIRST_VALUE(h) OVER (PARTITION BY b ORDER BY pk RANGE UNBOUNDED PRECEDING)
207 FROM t1
208 WINDOW w1 AS (PARTITION BY b ORDER BY pk);
209
210 INSERT INTO t4 SELECT
211 LAST_VALUE(a) OVER w1,
212 LAST_VALUE(b) OVER w1,
213 LAST_VALUE(c) OVER w1,
214 LAST_VALUE(d) OVER w2,
215 LAST_VALUE(e) OVER w2,
216 LAST_VALUE(f) OVER w1,
217 LAST_VALUE(g) OVER w2,
218 LAST_VALUE(h) OVER w1
219 FROM t1
220 WINDOW w1 AS (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
221 w2 AS (ORDER BY c,d);
222
223 INSERT INTO t4 SELECT
224 NTH_VALUE(a,1) OVER w1,
225 NTH_VALUE(b,2) OVER w1,
226 NTH_VALUE(c,3) OVER w1,
227 NTH_VALUE(d,1) OVER w1,
228 NTH_VALUE(e,2) OVER w1,
229 NTH_VALUE(f,3) OVER w1,
230 NTH_VALUE(g,1) OVER (PARTITION BY b ORDER BY pk ROWS 2 PRECEDING),
231 NTH_VALUE(h,2) OVER (PARTITION BY b ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)
232 FROM t1
233 WINDOW w1 AS (PARTITION BY b ORDER BY pk);
234
235 INSERT INTO t4 SELECT
236 LEAD(a,1,3) OVER w1,
237 LEAD(b,2) OVER w1,
238 LEAD(c) OVER w1,
239 LEAD(d,1) OVER w1,
240 LEAD(e,2) OVER w1,
241 LEAD(f,1) OVER w1,
242 LEAD(g,1) OVER w1,
243 LEAD(h,2) OVER w1
244 FROM t1
245 WINDOW w1 AS (PARTITION BY b ORDER BY pk);
246
247 INSERT INTO t4 SELECT
248 LAG(a,1,2) OVER w2,
249 LAG(b,2) OVER w1,
250 LAG(c) OVER w1,
251 LAG(d,1) OVER w2,
252 LAG(e,2) OVER w1,
253 LAG(f,1) OVER w2,
254 LAG(g,1) OVER w1,
255 LAG(h,2) OVER w3
256 FROM t1
257 WINDOW w1 AS (ORDER BY pk),
258 w2 AS (PARTITION BY g ORDER BY h),
259 w3 AS (PARTITION BY d ORDER BY NULL);
260
261 --source include/sync_slave_sql_with_master.inc
262 --echo [Connection Slave]
263
264 # Check that tables are replicated properly
265
266 --let $diff_tables = master:t4, slave:t4
267 --source include/diff_tables.inc
268
269 --let $diff_tables = master:t2, slave:t2
270 --source include/diff_tables.inc
271
272 --let $diff_tables = master:t3a, slave:t3a
273 --source include/diff_tables.inc
274
275 --let $diff_tables = master:t3b, slave:t3b
276 --source include/diff_tables.inc
277
278 --echo [Connection Master]
279 --connection master
280
281 --echo # Update with window function in subquery
282
283 UPDATE t2 SET c1 = c1 + 1 WHERE c1 NOT IN (SELECT ROW_NUMBER() OVER () FROM DUAL);
284 UPDATE t2 SET c2 = c2 + 2 WHERE c2 IN (SELECT RANK() OVER () FROM DUAL);
285 UPDATE t2 SET c3 = c3 + 1 WHERE c3 IN (SELECT CUME_DIST() OVER () FROM DUAL);
286 UPDATE t4 SET c1=100 WHERE c1 IN (SELECT FIRST_VALUE(221) OVER () FROM DUAL);
287 UPDATE t4 SET c2=100 WHERE c2 = ANY (SELECT LAST_VALUE(2) OVER () FROM DUAL);
288 UPDATE t4 SET c3='Updated' WHERE c3 = SOME (SELECT NTH_VALUE('Blue',1) OVER () FROM DUAL);
289 UPDATE t4 SET c4='Updated' WHERE c4 = ALL (SELECT LEAD('Russia',0) OVER () FROM DUAL);
290 UPDATE t4 SET c5='2017-01-01' WHERE c5 IN (SELECT LAG('2010-09-20',0) OVER () FROM DUAL);
291 UPDATE t4 SET c6='2017-01-01 12:30:45' WHERE c6 IN (SELECT CAST(FIRST_VALUE('2007-08-27 14:06:53') OVER () AS DATETIME) FROM DUAL);
292 UPDATE t4 SET c7='Updated' WHERE c7 IN (SELECT LAST_VALUE('Alpha') OVER () FROM DUAL);
293 UPDATE t4 SET c8='Updated' WHERE c8 IN (SELECT NTH_VALUE('199utjJtezqMHCewY',1) OVER () FROM DUAL);
294
295 --source include/sync_slave_sql_with_master.inc
296 --echo [Connection Slave]
297
298 # Check that the update's are replicated properly
299 --let $diff_tables = master:t2, slave:t2
300 --source include/diff_tables.inc
301
302 --echo [Connection Master]
303 --connection master
304
305 --echo # Delete with window function in subquery
306
307 DELETE FROM t2 WHERE c1 IN (SELECT 1 + DENSE_RANK() OVER () FROM DUAL);
308 DELETE FROM t2 WHERE c2 = ANY (SELECT 2 + PERCENT_RANK() OVER () FROM DUAL);
309 DELETE FROM t2 WHERE c3 > SOME (SELECT NTILE(1) OVER () FROM DUAL);
310 DELETE FROM t4 WHERE c2 <= ALL (SELECT FIRST_VALUE(100) OVER () FROM DUAL);
311 DELETE FROM t4 WHERE c3 = ANY (SELECT LAST_VALUE('Updated') OVER () FROM DUAL);
312 DELETE FROM t4 WHERE c4 = ALL (SELECT NTH_VALUE('Updated',1) OVER () FROM DUAL);
313 DELETE FROM t4 WHERE c5 IN (SELECT LAG('2017-01-01',0) OVER () FROM DUAL);
314 DELETE FROM t4 WHERE c6 IN (SELECT CAST(FIRST_VALUE('2017-01-01 12:30:45') OVER () AS DATETIME) FROM DUAL);
315 DELETE FROM t4 WHERE c7 IN (SELECT LEAD('Updated') OVER () FROM DUAL);
316 DELETE FROM t4 WHERE c8 IN (SELECT NTH_VALUE('Updated',1) OVER () FROM DUAL);
317
318 --source include/sync_slave_sql_with_master.inc
319 --echo [Connection Slave]
320
321 # Check that delete's replicated properly
322
323 SELECT * FROM t2 where c1 IN (SELECT ROW_NUMBER() OVER () FROM DUAL);
324
325 --let $diff_tables = master:t2, slave:t2
326 --source include/diff_tables.inc
327
328 --let $diff_tables = master:t4, slave:t4
329 --source include/diff_tables.inc
330
331 --echo [Connection Master]
332 --connection master
333
334 --echo # Clean up
335 DROP TABLE t1;
336 DROP TABLE t4;
337 DROP TABLE t2;
338 DROP TABLE t3a;
339 DROP TABLE t3b;
340
341 --source include/rpl_end.inc