4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/rpl/t/rpl_window_functions.test

342 lines
15 KiB
Plaintext
Raw Permalink Blame History

# WL#9236, WL#9603, WL#9727
# Sanity tests for window funtions with replication
--source include/master-slave.inc
--echo # Create table with columns of different data types and insert values
--connection master
CREATE TABLE t1 (pk INT PRIMARY KEY,a DECIMAL(5,2),
b DOUBLE, c CHAR(10), d VARCHAR(20),
e DATE, f DATETIME, g TEXT, h BLOB, i VARBINARY(32));
INSERT INTO t1 VALUES
(1, 258, 6.8, 'Orange', 'Australia', '2002-02-20', '2011-07-29 02:43:24', 'Cookley', 'FgaBAi419MbgSXyFgaBAi4QYYtKp4EotEr6nHrFyw', x'24c38d4ac1e8def7c9beff99d2ef7bd0'),
(2, null, 2, 'Blue', 'Australia', '2004-07-17', '2010-06-04 21:51:57', 'Temp', '1cNnegQruhm9r6M7s96Lt8t2vDP5LLoz5', x'5fb4efd5f7e90783a792c6134ceb539f'),
(3, 72, 44.44, 'Blue', 'Madagascar', '1999-09-09', '2006-08-11 06:55:50', 'Bigtax', '199utjJtezqMHCewY', x'f9e4f8c6d7b5657b0854ceb1d628c9e7'),
(4, 9.2, 253, 'Green', 'Russia', '2004-09-16', '1996-10-22 11:59:17', 'Stim', '1JmpHmWbpyBNBJavMBt7NS48UEoWcJ7GSvJavMas', x'018f672ec2ab8c9bdf8160f79b7bf283'),
(5, 6, 44.44, 'Crimson', 'Netherlands', '2003-01-27', '2017-01-02 01:43:16', 'Bytecard', '1FZaNCRe6fzyFiBVyqwfg72eHvo3hZXQXFasasf', x'64ed41f90735e3ab48513ab920d1c6d7'),
(6, 70, 83932, 'Indigo', 'India', '2011-08-09', '2008-08-12 13:18:47', 'Alpha', '1Phng2CeXWwmbmdY9qwdqwdnTqBv6ecWgZBj3BKs', x'71048e95216358a9d95e1ed946a457ad'),
(7, 96, 6, 'Khaki', 'Luxembourg', '2009-10-22', '1997-11-30 15:00:16', 'Zontrax', '1Ph2ZUtAVskwebMnyKLRG5GasfasfG4RkdTQujTc', x'88b67753fb0c181d1f7f21e31c3c92c9'),
(8, 8.43, 2, 'Pink', 'China', '2005-06-15', '1998-11-02 17:47:53', 'Gembucket', '14BbxEvaUK7nyCzXc9YvkxQasfdewefwefTiZrnVHVFj1', x'e7f08241ac2573e39be9bdc4c8f42a44'),
(9, 221, -8.67, 'Magenta', 'Australia', '2010-09-20', '2007-08-27 14:06:53', 'Y-find', '138GWMWSZTqkkm8eQk3sdfsdfsdJDpv72i6cFuyemc', x'583b4cb865f1f2644bf1420595a08c53'),
(10, 430, 1.23456, 'Turquoise', 'Philippines', '2002-08-02', '2000-02-19 02:49:17', 'Tin', '1CPkZjahzzRgoqz4W1NfmHsdfsdfsdf5vueJ3irb7Ae', x'7273d3dc246ecfe4bdc7c8155d1ee6b7'),
(11, 80, 6.8, 'Orange', 'Australia', '2002-02-20', '2011-07-29 02:43:24', 'Cookley', '19MbgSXyFgaBAi4sdfsdfQYYtKp4EotEr6nHrFyw', x'a17d1e699322177dfa7c415bb35f22a2'),
(12, 95 , 2, 'Blue', 'Australia', '2004-07-17', '2010-06-04 21:51:57', 'Temp', '1cNnegQr1234234uhm9r6M7s96Lt8t2vDP5LLoz5', x'435f41a9060f01f0669942eeb4ed2e80'),
(13, 72, 69179, 'Blue', 'Madagascar', '1999-09-09', '2006-08-11 06:55:50', 'Bigtax', 'nmklaw9utjJtezqMHCewR9YpKtm3LaXmt5h4PY', x'92d3b9ee039b388673e575c7f269ce60'),
(14, 6, 2531, 'Turquoise', 'Russia', '2004-09-16', '1996-10-22 11:59:17', 'Stim', 'avMBt7NasdasdsadS48UEoWcJ7GSv', x'f0688d9f99b6c80ec459bf9e4a79f411'),
(15, 9.2, 44, 'Indigo', 'Netherlands', '2003-01-27', '2017-01-02 01:43:16', 'Bytecard', '1asdfRe6fzyFiBVyqwfg72eHvo3hZXQXF', x'3d643793f96df74f4f680557eebf5326'),
(16, 72, 832, 'Green', 'India', '2011-08-09', '2008-08-12 13:18:47', 'Alpha', 'XWwmbmdY9nTqBv6ecWgZBj3BKs', x'fd7d92a8fe576067319ddd0fe44ddb44'),
(17, 70, 1.23456, 'Khaki', 'Luxembourg', '2009-10-22', '1997-11-30 15:00:16', 'Zontrax', '1Ph2ZUtAVskwebMnyKwEFWEFLRG5GG4RkdTQujTc', x'4063e204f4ac3578c71c1f33a7968a53'),
(18, 96, 2, 'Pink', 'China', '2005-06-15', '1998-11-02 17:47:53', 'Gembucket', '14BbxEvaUK7nyCzXcewaf9YvkxQTiZrnVHVFj1', x'7c146d3cc41ad9a48be5864d3d05e6d0'),
(19, 21, -8.67, 'Magenta', 'Australia', '2010-09-20', '2007-08-27 14:06:53', 'Y-find', '138GWMWSZTqkkm8eQk3JaweffwDpv72i6cFuyemc', x'05534c032b05ad0527a6768d45d36568'),
(20, 8.43, 253, 'Crimson', 'Philippines', '2002-08-02', '2000-02-19 02:49:17', 'Tin', '1CPkZjahzzRgoqz4W1NfmH5vueJ3irb7Ae', x'e7f7807e521c9f9a2276b438cc0ca203');
--echo # INSERT ... SELECT ... using basic non-aggregate window functions
CREATE TABLE t2 (
c1 int unsigned NOT NULL DEFAULT '0',
c2 int unsigned NOT NULL DEFAULT '0',
c3 int unsigned NOT NULL DEFAULT '0',
c4 int unsigned DEFAULT NULL,
c5 double NOT NULL DEFAULT '0',
c6 double NOT NULL DEFAULT '0'
);
INSERT INTO t2 SELECT
ROW_NUMBER() OVER w1 c1,
RANK() OVER w1 c2,
DENSE_RANK() OVER w1 c3,
NTILE(3) OVER w1 c4,
PERCENT_RANK() OVER w1 c5,
CUME_DIST() OVER w1 c6
FROM t1 WINDOW w1 AS (PARTITION BY c ORDER BY f);
INSERT INTO t2 SELECT
ROW_NUMBER() OVER (PARTITION BY b) c1,
RANK() OVER (PARTITION BY a ORDER BY e) c2,
DENSE_RANK() OVER (PARTITION BY a ORDER BY i) c3,
NTILE(3) OVER (ORDER BY b) c4,
PERCENT_RANK() OVER (ORDER BY a,b,c,d) c5,
CUME_DIST() OVER (ORDER BY e,f,g,h,i) c6
FROM t1;
--echo # INSERT ... SELECT with window functions having frame specification
CREATE TABLE t3a (
c1 decimal(27,2) DEFAULT NULL,
c2 double DEFAULT NULL,
c3 bigint(21) NOT NULL DEFAULT '0',
c4 int DEFAULT NULL,
c5 int DEFAULT NULL,
c6 varbinary(32),
c7 date DEFAULT NULL,
c8 datetime DEFAULT NULL,
c9 double DEFAULT NULL,
c10 double DEFAULT NULL,
c11 double DEFAULT NULL,
c12 double DEFAULT NULL,
c13 json,
c14 json
);
INSERT INTO t3a SELECT
SUM(a) OVER (ROWS UNBOUNDED PRECEDING) c1,
AVG(b) OVER (ORDER BY pk ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
COUNT(*) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
BIT_AND(pk) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c4,
BIT_OR(a) OVER (PARTITION BY d ORDER BY e ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
BIT_XOR(i) OVER (PARTITION BY c) c6,
MIN(e) OVER (ORDER BY e ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c7,
MAX(f) OVER (ORDER BY f ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) c8,
STDDEV_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c9,
VAR_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c10,
STDDEV_POP(b) OVER () c11,
VAR_POP(a) OVER () c12,
JSON_ARRAYAGG(e) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c13,
JSON_OBJECTAGG(pk, a) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) c14
FROM t1;
INSERT INTO t3a SELECT
SUM(a) OVER (RANGE UNBOUNDED PRECEDING) c1,
AVG(b) OVER (ORDER BY pk RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
COUNT(*) OVER (PARTITION BY d ORDER BY pk RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
BIT_AND(pk) OVER (PARTITION BY d ORDER BY pk RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c4,
BIT_OR(a) OVER (PARTITION BY d ORDER BY e RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
BIT_XOR(i) OVER (PARTITION BY c) c6,
MIN(e) OVER (ORDER BY e RANGE INTERVAL 5 YEAR PRECEDING) c7,
MAX(f) OVER (ORDER BY f RANGE BETWEEN CURRENT ROW AND INTERVAL 5 YEAR FOLLOWING) c8,
STDDEV_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c9,
VAR_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c10,
STDDEV_POP(b) OVER () c11,
VAR_POP(a) OVER () c12,
JSON_ARRAYAGG(b) OVER (ORDER BY f ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) c13,
JSON_OBJECTAGG(b, e) OVER (PARTITION BY d ORDER BY e ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c14
FROM t1;
# Set 'windowing_use_high_precision'
SET @master_save_windowing_use_high_precision= (SELECT @@windowing_use_high_precision);
SET GLOBAL windowing_use_high_precision=0;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
SET @slave_save_windowing_use_high_precision= (SELECT @@windowing_use_high_precision);
SET GLOBAL windowing_use_high_precision=0;
--echo [Connection Master]
--connection master
CREATE TABLE t3b (
c1 decimal(27,2) DEFAULT NULL,
c2 double DEFAULT NULL,
c3 bigint(21) NOT NULL DEFAULT '0',
c4 int DEFAULT NULL,
c5 int DEFAULT NULL,
c6 varbinary(32),
c7 date DEFAULT NULL,
c8 datetime DEFAULT NULL,
c9 double DEFAULT NULL,
c10 double DEFAULT NULL,
c11 double DEFAULT NULL,
c12 double DEFAULT NULL,
c13 json,
c14 json
);
INSERT INTO t3b SELECT
SUM(a) OVER (ROWS UNBOUNDED PRECEDING) c1,
AVG(b) OVER (ORDER BY pk ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
COUNT(*) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
BIT_AND(pk) OVER (PARTITION BY d ORDER BY pk ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c4,
BIT_OR(a) OVER (PARTITION BY d ORDER BY e ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
BIT_XOR(i) OVER (PARTITION BY c) c6,
MIN(e) OVER (ORDER BY e ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) c7,
MAX(f) OVER (ORDER BY f ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) c8,
STDDEV_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c9,
VAR_SAMP(a) OVER (ORDER BY pk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c10,
STDDEV_POP(b) OVER () c11,
VAR_POP(a) OVER () c12,
JSON_ARRAYAGG(b) OVER (ROWS UNBOUNDED PRECEDING) c13,
JSON_OBJECTAGG(pk, f) OVER (PARTITION BY e ORDER BY pk RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) c14
FROM t1;
INSERT INTO t3b SELECT
SUM(a) OVER (RANGE UNBOUNDED PRECEDING) c1,
AVG(b) OVER (ORDER BY pk RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) c2a,
COUNT(*) OVER (PARTITION BY d ORDER BY pk RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) c3,
BIT_AND(pk) OVER (PARTITION BY d ORDER BY f RANGE INTERVAL 2 YEAR PRECEDING) c4,
BIT_OR(a) OVER (PARTITION BY d ORDER BY e RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c5,
BIT_XOR(i) OVER (PARTITION BY c) c6,
MIN(e) OVER (ORDER BY e RANGE INTERVAL 5 YEAR PRECEDING) c4,
MAX(f) OVER (ORDER BY f RANGE BETWEEN CURRENT ROW AND INTERVAL 5 YEAR FOLLOWING) c5,
STDDEV_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c6,
VAR_SAMP(a) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c7,
STDDEV_POP(b) OVER () c8,
VAR_POP(a) OVER () c9,
JSON_ARRAYAGG(e) OVER (ORDER BY e RANGE INTERVAL 4 YEAR PRECEDING) c12,
JSON_OBJECTAGG(pk, e) OVER (ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c13
FROM t1;
# Reset variable 'windowing_use_high_precision'
SET GLOBAL windowing_use_high_precision=@master_save_windowing_use_high_precision;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
SET GLOBAL windowing_use_high_precision=@slave_save_windowing_use_high_precision;
--echo [Connection Master]
--connection master
--echo # INSERT ... SELECT using window functions which return values having
--echo # same type as that of the column
CREATE TABLE t4 (c1 decimal(5,2), c2 double, c3 char(10), c4 varchar(20), c5 date, c6 datetime, c7 text, c8 blob);
INSERT INTO t4 SELECT
FIRST_VALUE(a) OVER w1,
FIRST_VALUE(b) OVER w1,
FIRST_VALUE(c) OVER w1,
FIRST_VALUE(d) OVER w1,
FIRST_VALUE(e) OVER w1,
FIRST_VALUE(f) OVER w1,
FIRST_VALUE(g) OVER (PARTITION BY b ORDER BY pk ROWS 2 PRECEDING),
FIRST_VALUE(h) OVER (PARTITION BY b ORDER BY pk RANGE UNBOUNDED PRECEDING)
FROM t1
WINDOW w1 AS (PARTITION BY b ORDER BY pk);
INSERT INTO t4 SELECT
LAST_VALUE(a) OVER w1,
LAST_VALUE(b) OVER w1,
LAST_VALUE(c) OVER w1,
LAST_VALUE(d) OVER w2,
LAST_VALUE(e) OVER w2,
LAST_VALUE(f) OVER w1,
LAST_VALUE(g) OVER w2,
LAST_VALUE(h) OVER w1
FROM t1
WINDOW w1 AS (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
w2 AS (ORDER BY c,d);
INSERT INTO t4 SELECT
NTH_VALUE(a,1) OVER w1,
NTH_VALUE(b,2) OVER w1,
NTH_VALUE(c,3) OVER w1,
NTH_VALUE(d,1) OVER w1,
NTH_VALUE(e,2) OVER w1,
NTH_VALUE(f,3) OVER w1,
NTH_VALUE(g,1) OVER (PARTITION BY b ORDER BY pk ROWS 2 PRECEDING),
NTH_VALUE(h,2) OVER (PARTITION BY b ORDER BY pk RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)
FROM t1
WINDOW w1 AS (PARTITION BY b ORDER BY pk);
INSERT INTO t4 SELECT
LEAD(a,1,3) OVER w1,
LEAD(b,2) OVER w1,
LEAD(c) OVER w1,
LEAD(d,1) OVER w1,
LEAD(e,2) OVER w1,
LEAD(f,1) OVER w1,
LEAD(g,1) OVER w1,
LEAD(h,2) OVER w1
FROM t1
WINDOW w1 AS (PARTITION BY b ORDER BY pk);
INSERT INTO t4 SELECT
LAG(a,1,2) OVER w2,
LAG(b,2) OVER w1,
LAG(c) OVER w1,
LAG(d,1) OVER w2,
LAG(e,2) OVER w1,
LAG(f,1) OVER w2,
LAG(g,1) OVER w1,
LAG(h,2) OVER w3
FROM t1
WINDOW w1 AS (ORDER BY pk),
w2 AS (PARTITION BY g ORDER BY h),
w3 AS (PARTITION BY d ORDER BY NULL);
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check that tables are replicated properly
--let $diff_tables = master:t4, slave:t4
--source include/diff_tables.inc
--let $diff_tables = master:t2, slave:t2
--source include/diff_tables.inc
--let $diff_tables = master:t3a, slave:t3a
--source include/diff_tables.inc
--let $diff_tables = master:t3b, slave:t3b
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
--echo # Update with window function in subquery
UPDATE t2 SET c1 = c1 + 1 WHERE c1 NOT IN (SELECT ROW_NUMBER() OVER () FROM DUAL);
UPDATE t2 SET c2 = c2 + 2 WHERE c2 IN (SELECT RANK() OVER () FROM DUAL);
UPDATE t2 SET c3 = c3 + 1 WHERE c3 IN (SELECT CUME_DIST() OVER () FROM DUAL);
UPDATE t4 SET c1=100 WHERE c1 IN (SELECT FIRST_VALUE(221) OVER () FROM DUAL);
UPDATE t4 SET c2=100 WHERE c2 = ANY (SELECT LAST_VALUE(2) OVER () FROM DUAL);
UPDATE t4 SET c3='Updated' WHERE c3 = SOME (SELECT NTH_VALUE('Blue',1) OVER () FROM DUAL);
UPDATE t4 SET c4='Updated' WHERE c4 = ALL (SELECT LEAD('Russia',0) OVER () FROM DUAL);
UPDATE t4 SET c5='2017-01-01' WHERE c5 IN (SELECT LAG('2010-09-20',0) OVER () FROM DUAL);
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);
UPDATE t4 SET c7='Updated' WHERE c7 IN (SELECT LAST_VALUE('Alpha') OVER () FROM DUAL);
UPDATE t4 SET c8='Updated' WHERE c8 IN (SELECT NTH_VALUE('199utjJtezqMHCewY',1) OVER () FROM DUAL);
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check that the update's are replicated properly
--let $diff_tables = master:t2, slave:t2
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
--echo # Delete with window function in subquery
DELETE FROM t2 WHERE c1 IN (SELECT 1 + DENSE_RANK() OVER () FROM DUAL);
DELETE FROM t2 WHERE c2 = ANY (SELECT 2 + PERCENT_RANK() OVER () FROM DUAL);
DELETE FROM t2 WHERE c3 > SOME (SELECT NTILE(1) OVER () FROM DUAL);
DELETE FROM t4 WHERE c2 <= ALL (SELECT FIRST_VALUE(100) OVER () FROM DUAL);
DELETE FROM t4 WHERE c3 = ANY (SELECT LAST_VALUE('Updated') OVER () FROM DUAL);
DELETE FROM t4 WHERE c4 = ALL (SELECT NTH_VALUE('Updated',1) OVER () FROM DUAL);
DELETE FROM t4 WHERE c5 IN (SELECT LAG('2017-01-01',0) OVER () FROM DUAL);
DELETE FROM t4 WHERE c6 IN (SELECT CAST(FIRST_VALUE('2017-01-01 12:30:45') OVER () AS DATETIME) FROM DUAL);
DELETE FROM t4 WHERE c7 IN (SELECT LEAD('Updated') OVER () FROM DUAL);
DELETE FROM t4 WHERE c8 IN (SELECT NTH_VALUE('Updated',1) OVER () FROM DUAL);
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check that delete's replicated properly
SELECT * FROM t2 where c1 IN (SELECT ROW_NUMBER() OVER () FROM DUAL);
--let $diff_tables = master:t2, slave:t2
--source include/diff_tables.inc
--let $diff_tables = master:t4, slave:t4
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
--echo # Clean up
DROP TABLE t1;
DROP TABLE t4;
DROP TABLE t2;
DROP TABLE t3a;
DROP TABLE t3b;
--source include/rpl_end.inc
Reference in New Issue View Git Blame Copy Permalink