| 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 |