diff options
Diffstat (limited to 'mysql-test')
37 files changed, 2145 insertions, 126 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index b3a2c66e3d0..dd82ef46229 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -5603,6 +5603,178 @@ r 3 drop table t1,t2,t3,x; # +# MDEV-30248: Embedded non-recursive CTE referring to base table 'x' +# within a CTE with name 'x' used in a subquery from +# select list of another CTE +# +CREATE TABLE x (a int) ENGINE=MyISAM; +INSERT INTO x VALUES (3),(7),(1); +CREATE TABLE t1 (b int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1); +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +3 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +3 +WITH x AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT x.c from x; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) +SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b +) AS c +) +SELECT cte.c from cte; +c +2 +DROP TABLE x; +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH x AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT x.c from x; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) +SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b +) AS c +) +SELECT cte.c from cte; +ERROR 42S02: Table 'test.x' doesn't exist +DROP TABLE t1; +# # End of 10.3 tests # # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 8efbdbf9677..ebea3b96754 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3875,6 +3875,129 @@ select * from cte; drop table t1,t2,t3,x; --echo # +--echo # MDEV-30248: Embedded non-recursive CTE referring to base table 'x' +--echo # within a CTE with name 'x' used in a subquery from +--echo # select list of another CTE +--echo # + +CREATE TABLE x (a int) ENGINE=MyISAM; +INSERT INTO x VALUES (3),(7),(1); +CREATE TABLE t1 (b int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1); + +let $q1= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q1; + +let $q2= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q2; + +let $q3= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q3; + + +let $q4= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q4; + +let $q5= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q5; + +let $q6= +WITH x AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) + SELECT b FROM x AS r + ) AS c +) +SELECT x.c from x; +eval $q6; + +let $q7= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) + SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b + ) AS c +) +SELECT cte.c from cte; +eval $q7; + + +DROP TABLE x; + +--ERROR ER_NO_SUCH_TABLE +eval $q1; +--ERROR ER_NO_SUCH_TABLE +eval $q2; +--ERROR ER_NO_SUCH_TABLE +eval $q3; +--ERROR ER_NO_SUCH_TABLE +eval $q4; +--ERROR ER_NO_SUCH_TABLE +eval $q5; +--ERROR ER_NO_SUCH_TABLE +eval $q6; +--ERROR ER_NO_SUCH_TABLE +eval $q7; + +DROP TABLE t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result index 704b7415ea1..46b9a25b332 100644 --- a/mysql-test/main/ctype_utf8.result +++ b/mysql-test/main/ctype_utf8.result @@ -11383,6 +11383,181 @@ a # End of 10.3 tests # # +# Start of 10.4 tests +# +# +# MDEV-27653 long uniques don't work with unicode collations +# +SET NAMES utf8mb3; +CREATE TABLE t1 ( +a CHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a CHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`(10)) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a VARCHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a VARCHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`(10)) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a LONGTEXT COLLATE utf8mb3_general_ci, +UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`(10)) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +INSERT INTO t1 VALUES ('A'); +ERROR 23000: Duplicate entry 'A' for key 'a' +INSERT INTO t1 VALUES ('Ä'); +ERROR 23000: Duplicate entry 'Ä' for key 'a' +INSERT INTO t1 VALUES ('Ấ'); +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +Ấ 3 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +ALTER TABLE t1 FORCE; +ERROR 23000: Duplicate entry 'ä' for key 'a' +DELETE FROM t1 WHERE OCTET_LENGTH(a)>1; +ALTER TABLE t1 FORCE; +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +DROP TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +ALTER IGNORE TABLE t1 FORCE; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +DROP TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair Warning Number of rows changed from 2 to 1 +test.t1 repair status OK +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +DROP TABLE t1; +# +# End of 10.4 tests +# +# # Start of 10.5 tests # # diff --git a/mysql-test/main/ctype_utf8.test b/mysql-test/main/ctype_utf8.test index 1e3cbffa622..3461a5cb206 100644 --- a/mysql-test/main/ctype_utf8.test +++ b/mysql-test/main/ctype_utf8.test @@ -2314,6 +2314,164 @@ VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin); --echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-27653 long uniques don't work with unicode collations +--echo # + +SET NAMES utf8mb3; + +# CHAR + +CREATE TABLE t1 ( + a CHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + a CHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + + +# VARCHAR + +CREATE TABLE t1 ( + a VARCHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + a VARCHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + + +# TEXT + +CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + a LONGTEXT COLLATE utf8mb3_general_ci, + UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + + +# Testing upgrade: +# Prior to MDEV-27653, the UNIQUE HASH function errorneously +# took into account string octet length. +# Old tables should still open and work, but with wrong results. + +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI; +SHOW CREATE TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +CHECK TABLE t1; + +# There is already a one byte value 'a' in the table +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('A'); + +# There is already a two-byte value 'ä' in the table +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('Ä'); + +# There were no three-byte values in the table so far. +# The below value violates UNIQUE, but it gets inserted. +# This is wrong but expected for a pre-MDEV-27653 table. +INSERT INTO t1 VALUES ('Ấ'); +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +CHECK TABLE t1; + +# ALTER FORCE fails: it tries to rebuild the table +# with a correct UNIQUE HASH function, but there are duplicates! +--error ER_DUP_ENTRY +ALTER TABLE t1 FORCE; + +# Let's remove all duplicate values, so only the one-byte 'a' stays. +# ALTER..FORCE should work after that. +DELETE FROM t1 WHERE OCTET_LENGTH(a)>1; +ALTER TABLE t1 FORCE; + +# Make sure that 'a' and 'ä' cannot co-exists any more, +# because the table was recreated with a correct UNIQUE HASH function. +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +DROP TABLE t1; + +# +# Testing an old table with ALTER IGNORE. +# The table is expected to rebuild with a new hash function, +# duplicates go away. +# +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI; +SHOW CREATE TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +ALTER IGNORE TABLE t1 FORCE; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +DROP TABLE t1; + +# +# Testing an old table with REPAIR. +# The table is expected to rebuild with a new hash function, +# duplicates go away. +# +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI; +SHOW CREATE TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +REPAIR TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # + + +--echo # --echo # Start of 10.5 tests --echo # diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 275459bd868..4fc9a0a213f 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -18162,6 +18162,69 @@ DROP TABLE transaction_items; DROP TABLE transactions; DROP TABLE charges; DROP TABLE ledgers; +# +# MDEV-30081: Splitting from a constant mergeable derived table +# used in inner part of an outer join. +# +CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(4),(7); +CREATE TABLE t2 ( +id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,4,6),(7,7,7); +CREATE TABLE t3 ( +wid int, wtid int, otid int, oid int, +PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7); +CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); +CREATE TABLE t5 ( +id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) +) ENGINE=MyISAM ; +INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); +ANALYZE TABLE t1,t2,t3,t4,t5; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +test.t4 analyze status Engine-independent statistics collected +test.t4 analyze status OK +test.t5 analyze status Engine-independent statistics collected +test.t5 analyze status OK +CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1); +SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM +t1, t2, t3 +LEFT JOIN +(SELECT t4.* FROM t4 WHERE t4.a=3) dt +ON t3.oid = dt.id AND t3.otid = 14 +LEFT JOIN v1 +ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; +wid wtid otid oid t1_id t2_id id a id1 +7 17 7 7 7 7 NULL NULL NULL +EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM +t1, t2, t3 +LEFT JOIN +(SELECT t4.* FROM t4 WHERE t4.a=3) dt +ON t3.oid = dt.id AND t3.otid = 14 +LEFT JOIN v1 +ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 const PRIMARY,oid PRIMARY 4 const 1 +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t2 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t4 const PRIMARY,a NULL NULL NULL 1 Impossible ON condition +1 PRIMARY <derived3> ref key0 key0 5 const 0 Using where +3 LATERAL DERIVED t5 ref id1 id1 5 const 0 Using index +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4,t5; # End of 10.3 tests # # MDEV-18679: materialized view with SELECT S containing materialized diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index bd38716f2a6..6cfe23b7866 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3870,6 +3870,55 @@ DROP TABLE transactions; DROP TABLE charges; DROP TABLE ledgers; + +--echo # +--echo # MDEV-30081: Splitting from a constant mergeable derived table +--echo # used in inner part of an outer join. +--echo # + + CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(4),(7); + +CREATE TABLE t2 ( + id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,4,6),(7,7,7); + +CREATE TABLE t3 ( + wid int, wtid int, otid int, oid int, + PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7); + +CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); + +CREATE TABLE t5 ( + id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) +) ENGINE=MyISAM ; +INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); + +ANALYZE TABLE t1,t2,t3,t4,t5; + +CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1); + +let $q= +SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM + t1, t2, t3 + LEFT JOIN + (SELECT t4.* FROM t4 WHERE t4.a=3) dt + ON t3.oid = dt.id AND t3.otid = 14 + LEFT JOIN v1 + ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; + +eval $q; +eval EXPLAIN $q; + +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4,t5; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 4b47deee7f1..2cb54e0acff 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -1,3 +1,6 @@ +# +# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list +# create table t1 (a int, b int, c int, d int, e int); insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), @@ -11,6 +14,9 @@ load data infile 'load.data' into table tmp; delete from tmp; drop table t1; drop table tmp; +# +# MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob +# create table t1 (b blob) engine=innodb; alter table t1 add unique (b); alter table t1 force; @@ -21,12 +27,18 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`) USING HASH ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; +# +# MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob +# create table t1 (pk int, b blob, primary key(pk), unique(b)) engine=myisam; insert into t1 values (1,'foo'); replace into t1 (pk) values (1); alter table t1 force; replace into t1 (pk) values (1); drop table t1; +# +# MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob +# create table t1 (t time, unique(t)) engine=innodb; insert into t1 values (null),(null); alter ignore table t1 modify t text not null default ''; @@ -34,6 +46,9 @@ Warnings: Warning 1265 Data truncated for column 't' at row 1 Warning 1265 Data truncated for column 't' at row 2 drop table t1; +# +# MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob +# create table t1 ( pk int, f text, primary key (pk), unique(f)) with system versioning; insert into t1 values (1,'foo'); update t1 set f = 'bar'; @@ -50,20 +65,32 @@ pk f row_end > DATE'2030-01-01' 1 foo 0 1 bar 0 drop table t1; +# +# MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob +# create temporary table t1 (f blob, unique(f)) engine=innodb; insert into t1 values (1); replace into t1 values (1); drop table t1; +# +# MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table +# create table t (b blob, unique(b)) engine=myisam; insert into t values ('foo'); replace into t values ('foo'); drop table t; +# +# MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index +# CREATE TABLE t1 (f INT, x BLOB, UNIQUE (x)); INSERT INTO t1 VALUES (1,'foo'); ALTER TABLE t1 DROP x, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY UPDATE t1 SET x = 'bar'; DROP TABLE t1; +# +# MDEV-18799 Long unique does not work after failed alter table +# create table t1(a blob unique , b blob); insert into t1 values(1,1),(2,1); alter table t1 add unique(b); @@ -85,16 +112,26 @@ Index_comment insert into t1 values(1,1); ERROR 23000: Duplicate entry '1' for key 'a' DROP TABLE t1; +# +# MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key +# CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=MyISAM; ALTER TABLE t1 DROP x; ERROR 42000: Can't DROP COLUMN `x`; check that it exists UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; +# +# MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in +# row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search" +# CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=InnoDB; ALTER TABLE t1 DROP x; ERROR 42000: Can't DROP COLUMN `x`; check that it exists UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; +# +# MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index +# CREATE TEMPORARY TABLE t1 (f BLOB, UNIQUE(f)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD KEY (f); ERROR HY000: Index column size too large. The maximum column size is 767 bytes @@ -102,17 +139,29 @@ TRUNCATE TABLE t1; SELECT * FROM t1 WHERE f LIKE 'foo'; f DROP TABLE t1; +# +# MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in +# ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key +# CREATE TABLE t1 (a INT, UNIQUE ind USING HASH (a)) ENGINE=InnoDB; ALTER TABLE t1 CHANGE COLUMN IF EXISTS b a INT; Warnings: Note 1054 Unknown column 'b' in 't1' DROP TABLE t1; +# +# MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow +# in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index +# CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; ALTER TABLE t1 DROP x; ERROR 42000: Can't DROP COLUMN `x`; check that it exists SELECT * FROM t1 WHERE f LIKE 'foo'; f DROP TABLE t1; +# +# MDEV-18800 Server crash in instant_alter_column_possible or +# Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key +# CREATE TABLE t1 (pk INT, PRIMARY KEY USING HASH (pk)) ENGINE=InnoDB; show keys from t1;; Table t1 @@ -130,6 +179,9 @@ Comment Index_comment ALTER TABLE t1 ADD INDEX (pk); DROP TABLE t1; +# +# MDEV-18922 Alter on long unique varchar column makes result null +# CREATE TABLE t1 (b int, a varchar(4000)); INSERT INTO t1 VALUES (1, 2),(2,3),(3,4); ALTER TABLE t1 ADD UNIQUE INDEX (a); @@ -144,6 +196,10 @@ a 3 4 drop table t1; +# +# MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags +# & (1<< 30)' failed in setup_keyinfo_hash +# CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY @@ -159,6 +215,9 @@ insert into t1 values(1,1); ERROR 23000: Duplicate entry '1-1' for key 'a' alter table t1 add column c int; drop table t1; +# +# MDEV-18889 Long unique on virtual fields crashes server +# create table t1(a blob , b blob as (a) unique); insert into t1 values(1, default); insert into t1 values(1, default); @@ -172,6 +231,9 @@ insert into t1(a,b) values(2,2); insert into t1(a,b) values(2,3); insert into t1(a,b) values(3,2); drop table t1; +# +# MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN +# CREATE TABLE t1 ( a CHAR(128), b CHAR(128) AS (a), @@ -187,6 +249,9 @@ c varchar(5000), UNIQUE(c,b(64)) ) ENGINE=InnoDB; drop table t1; +# +# MDEV-18967 Load data in system version with long unique does not work +# CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning; INSERT INTO t1 VALUES ('A'); SELECT * INTO OUTFILE 'load.data' from t1; @@ -196,6 +261,9 @@ select * from t1; data A DROP TABLE t1; +# +# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) +# CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('f'), ('o'), ('o'); SELECT * INTO OUTFILE 'load.data' from t1; @@ -214,12 +282,16 @@ SELECT * FROM t1; data f o +# This should be equivalent to the REPLACE above LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; SELECT * FROM t1; data f o DROP TABLE t1; +# +# MDEV-18953 Hash index on partial char field not working +# create table t1 ( c char(10) character set utf8mb4, unique key a using hash (c(1)) @@ -236,10 +308,16 @@ ERROR 23000: Duplicate entry 'Ð' for key 'a' insert into t1 values ('ббб'); ERROR 23000: Duplicate entry 'Ð' for key 'a' drop table t1; +# +# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map +# CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2; INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; +# +# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' +# set innodb_lock_wait_timeout= 10; CREATE TABLE t1 ( id int primary key, @@ -266,11 +344,20 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction disconnect con1; connection default; DROP TABLE t1, t2; +# +# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +# CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; ERROR 42000: Specified key was too long; max key length is 2000 bytes +# +# MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes +# create table t1(a int, unique(a) using hash); #BULK insert > 100 rows (MI_MIN_ROWS_TO_DISABLE_INDEXES) drop table t1; +# +# MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB +# SET binlog_row_image= NOBLOB; CREATE TABLE t1 (pk INT PRIMARY KEY, a text ,UNIQUE(a) using hash); INSERT INTO t1 VALUES (1,'foo'); @@ -278,6 +365,9 @@ create table t2(id int primary key, a blob, b varchar(20) as (LEFT(a,2))); INSERT INTO t2 VALUES (1, 'foo', default); DROP TABLE t1, t2; SET binlog_row_image= FULL; +# +# MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length +# CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM; show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment @@ -315,6 +405,9 @@ ERROR 23000: Duplicate entry '1' for key 'v2' update t1,t2 set v1 = v2 , v5 = 0; ERROR 23000: Duplicate entry '-128' for key 'v1' drop table t1, t2; +# +# MDEV-23264 Unique blobs allow duplicate values upon UPDATE +# CREATE TABLE t1 (f TEXT UNIQUE); INSERT INTO t1 VALUES (NULL),(NULL); UPDATE t1 SET f = ''; @@ -344,6 +437,18 @@ partition n0 values less than (10), partition n1 values less than (50)); drop table t1; # +# MDEV-29199 Unique hash key is ignored upon INSERT ... SELECT into non-empty MyISAM table +# +create table t1 (a int, b text, unique(b)) engine=MyISAM; +insert into t1 values (0,'aa'); +insert into t1 (a,b) select 1,'xxx' from seq_1_to_5; +ERROR 23000: Duplicate entry 'xxx' for key 'b' +select * from t1; +a b +0 aa +1 xxx +drop table t1; +# # End of 10.4 tests # # diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index f2160bb7f68..86003a6881c 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -1,9 +1,10 @@ --source include/have_innodb.inc --source include/have_partition.inc +--source include/have_sequence.inc -# -# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list -# +--echo # +--echo # MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list +--echo # create table t1 (a int, b int, c int, d int, e int); insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), @@ -20,18 +21,18 @@ drop table t1; --remove_file $datadir/test/load.data drop table tmp; -# -# MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob -# +--echo # +--echo # MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob +--echo # create table t1 (b blob) engine=innodb; alter table t1 add unique (b); alter table t1 force; show create table t1; drop table t1; -# -# MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob -# +--echo # +--echo # MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob +--echo # create table t1 (pk int, b blob, primary key(pk), unique(b)) engine=myisam; insert into t1 values (1,'foo'); replace into t1 (pk) values (1); @@ -39,17 +40,17 @@ alter table t1 force; replace into t1 (pk) values (1); drop table t1; -# -# MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob -# +--echo # +--echo # MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob +--echo # create table t1 (t time, unique(t)) engine=innodb; insert into t1 values (null),(null); alter ignore table t1 modify t text not null default ''; drop table t1; -# -# MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob -# +--echo # +--echo # MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob +--echo # create table t1 ( pk int, f text, primary key (pk), unique(f)) with system versioning; insert into t1 values (1,'foo'); update t1 set f = 'bar'; @@ -59,25 +60,25 @@ select * from t1; select pk, f, row_end > DATE'2030-01-01' from t1 for system_time all; drop table t1; -# -# MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob -# +--echo # +--echo # MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob +--echo # create temporary table t1 (f blob, unique(f)) engine=innodb; insert into t1 values (1); replace into t1 values (1); drop table t1; -# -# MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table -# +--echo # +--echo # MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table +--echo # create table t (b blob, unique(b)) engine=myisam; insert into t values ('foo'); replace into t values ('foo'); drop table t; -# -# MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index -# +--echo # +--echo # MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index +--echo # CREATE TABLE t1 (f INT, x BLOB, UNIQUE (x)); INSERT INTO t1 VALUES (1,'foo'); --error ER_ALTER_OPERATION_NOT_SUPPORTED @@ -85,9 +86,9 @@ ALTER TABLE t1 DROP x, ALGORITHM=INPLACE; UPDATE t1 SET x = 'bar'; DROP TABLE t1; -# -# MDEV-18799 Long unique does not work after failed alter table -# +--echo # +--echo # MDEV-18799 Long unique does not work after failed alter table +--echo # create table t1(a blob unique , b blob); insert into t1 values(1,1),(2,1); --error ER_DUP_ENTRY @@ -97,28 +98,28 @@ alter table t1 add unique(b); insert into t1 values(1,1); DROP TABLE t1; -# -# MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key -# +--echo # +--echo # MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key +--echo # CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=MyISAM; --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP x; UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; -# -# MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in -# row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search" -# +--echo # +--echo # MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in +--echo # row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search" +--echo # CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=InnoDB; --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP x; UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; -# -# MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index -# +--echo # +--echo # MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index +--echo # CREATE TEMPORARY TABLE t1 (f BLOB, UNIQUE(f)) ENGINE=InnoDB ROW_FORMAT=COMPACT; --error ER_INDEX_COLUMN_TOO_LONG ALTER TABLE t1 ADD KEY (f); @@ -126,36 +127,36 @@ TRUNCATE TABLE t1; SELECT * FROM t1 WHERE f LIKE 'foo'; DROP TABLE t1; -# -# MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in -# ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key -# +--echo # +--echo # MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in +--echo # ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key +--echo # CREATE TABLE t1 (a INT, UNIQUE ind USING HASH (a)) ENGINE=InnoDB; ALTER TABLE t1 CHANGE COLUMN IF EXISTS b a INT; DROP TABLE t1; -# -# MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow -# in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index -# +--echo # +--echo # MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow +--echo # in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index +--echo # CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP x; SELECT * FROM t1 WHERE f LIKE 'foo'; DROP TABLE t1; -# -# MDEV-18800 Server crash in instant_alter_column_possible or -# Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key -# +--echo # +--echo # MDEV-18800 Server crash in instant_alter_column_possible or +--echo # Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key +--echo # CREATE TABLE t1 (pk INT, PRIMARY KEY USING HASH (pk)) ENGINE=InnoDB; --query_vertical show keys from t1; ALTER TABLE t1 ADD INDEX (pk); DROP TABLE t1; -# -# MDEV-18922 Alter on long unique varchar column makes result null -# +--echo # +--echo # MDEV-18922 Alter on long unique varchar column makes result null +--echo # CREATE TABLE t1 (b int, a varchar(4000)); INSERT INTO t1 VALUES (1, 2),(2,3),(3,4); ALTER TABLE t1 ADD UNIQUE INDEX (a); @@ -163,10 +164,10 @@ SELECT * FROM t1; SELECT a FROM t1; drop table t1; -# -# MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags -# & (1<< 30)' failed in setup_keyinfo_hash -# +--echo # +--echo # MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags +--echo # & (1<< 30)' failed in setup_keyinfo_hash +--echo # CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT; @@ -181,9 +182,9 @@ insert into t1 values(1,1); alter table t1 add column c int; drop table t1; -# -# MDEV-18889 Long unique on virtual fields crashes server -# +--echo # +--echo # MDEV-18889 Long unique on virtual fields crashes server +--echo # create table t1(a blob , b blob as (a) unique); insert into t1 values(1, default); --error ER_DUP_ENTRY @@ -199,9 +200,9 @@ insert into t1(a,b) values(2,3); insert into t1(a,b) values(3,2); drop table t1; -# -# MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN -# +--echo # +--echo # MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN +--echo # CREATE TABLE t1 ( a CHAR(128), b CHAR(128) AS (a), @@ -218,9 +219,9 @@ CREATE TABLE t1 ( ) ENGINE=InnoDB; drop table t1; -# -# MDEV-18967 Load data in system version with long unique does not work -# +--echo # +--echo # MDEV-18967 Load data in system version with long unique does not work +--echo # CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning; INSERT INTO t1 VALUES ('A'); SELECT * INTO OUTFILE 'load.data' from t1; @@ -231,9 +232,9 @@ DROP TABLE t1; --let $datadir= `select @@datadir` --remove_file $datadir/test/load.data -# -# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) -# +--echo # +--echo # MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) +--echo # CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('f'), ('o'), ('o'); @@ -245,16 +246,16 @@ ALTER TABLE t1 ADD SYSTEM VERSIONING ; SELECT * FROM t1; REPLACE INTO t1 VALUES ('f'), ('o'), ('o'); SELECT * FROM t1; -# This should be equivalent to the REPLACE above +--echo # This should be equivalent to the REPLACE above LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; SELECT * FROM t1; DROP TABLE t1; --let $datadir= `select @@datadir` --remove_file $datadir/test/load.data -# -# MDEV-18953 Hash index on partial char field not working -# +--echo # +--echo # MDEV-18953 Hash index on partial char field not working +--echo # create table t1 ( c char(10) character set utf8mb4, unique key a using hash (c(1)) @@ -267,17 +268,17 @@ insert into t1 values ('бб'); insert into t1 values ('ббб'); drop table t1; -# -# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map -# +--echo # +--echo # MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map +--echo # CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2; INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; -# -# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' -# +--echo # +--echo # MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' +--echo # set innodb_lock_wait_timeout= 10; @@ -317,15 +318,15 @@ INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/; --connection default DROP TABLE t1, t2; -# -# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB -# +--echo # +--echo # MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +--echo # --error ER_TOO_LONG_KEY CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; -# -# MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes -# +--echo # +--echo # MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes +--echo # create table t1(a int, unique(a) using hash); --let $count=150 --let insert_stmt= insert into t1 values(200) @@ -340,9 +341,9 @@ while ($count) --enable_query_log drop table t1; -# -# MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB -# +--echo # +--echo # MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB +--echo # --source include/have_binlog_format_row.inc SET binlog_row_image= NOBLOB; @@ -352,20 +353,17 @@ INSERT INTO t1 VALUES (1,'foo'); create table t2(id int primary key, a blob, b varchar(20) as (LEFT(a,2))); INSERT INTO t2 VALUES (1, 'foo', default); -# Cleanup DROP TABLE t1, t2; SET binlog_row_image= FULL; -# -# MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length -# +--echo # +--echo # MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length +--echo # CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM; show index from t1; CREATE TABLE t2 (a varchar(900), b VARCHAR(900), UNIQUE (a,b)) ENGINE=MyISAM; show index from t2; - -# Cleanup DROP TABLE t1,t2; --echo # @@ -397,9 +395,9 @@ update t1 set v2 = 1, v3 = -128; update t1,t2 set v1 = v2 , v5 = 0; drop table t1, t2; -# -# MDEV-23264 Unique blobs allow duplicate values upon UPDATE -# +--echo # +--echo # MDEV-23264 Unique blobs allow duplicate values upon UPDATE +--echo # CREATE TABLE t1 (f TEXT UNIQUE); INSERT INTO t1 VALUES (NULL),(NULL); @@ -436,6 +434,16 @@ alter table t1 reorganize partition p1 into ( drop table t1; --echo # +--echo # MDEV-29199 Unique hash key is ignored upon INSERT ... SELECT into non-empty MyISAM table +--echo # +create table t1 (a int, b text, unique(b)) engine=MyISAM; +insert into t1 values (0,'aa'); +--error ER_DUP_ENTRY +insert into t1 (a,b) select 1,'xxx' from seq_1_to_5; +select * from t1; +drop table t1; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 209dfc44d7b..9860b2e9ad3 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -338,7 +338,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition -1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -371,6 +371,14 @@ EXPLAIN "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 69, + "selectivity_pct": 4.6 + }, "rows": 1, "filtered": 4.599999905, "attached_condition": "orders.o_totalprice between 200000 and 230000" @@ -383,7 +391,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition -1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.60 11.22 Using where +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) 0.11 (10%) 4.60 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -423,13 +431,26 @@ ANALYZE "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 69, + "selectivity_pct": 4.6, + "r_rows": 71, + "r_lookups": 96, + "r_selectivity_pct": 10.41666667, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 98, "rows": 1, - "r_rows": 1, + "r_rows": 0.112244898, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 4.599999905, - "r_filtered": 11.2244898, + "r_filtered": 100, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } @@ -575,7 +596,7 @@ l_quantity > 45 AND o_totalprice between 180000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (12%) Using index condition; Using where; Using rowid filter -1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (9%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -619,6 +640,14 @@ EXPLAIN "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 139, + "selectivity_pct": 9.266666667 + }, "rows": 1, "filtered": 9.266666412, "attached_condition": "orders.o_totalprice between 180000 and 230000" @@ -632,7 +661,7 @@ l_quantity > 45 AND o_totalprice between 180000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (12%) 60.00 (11%) 11.69 100.00 Using index condition; Using where; Using rowid filter -1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 9.27 26.67 Using where +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (9%) 0.27 (25%) 9.27 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -688,13 +717,26 @@ ANALYZE "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 139, + "selectivity_pct": 9.266666667, + "r_rows": 144, + "r_lookups": 59, + "r_selectivity_pct": 25.42372881, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 60, "rows": 1, - "r_rows": 1, + "r_rows": 0.266666667, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 9.266666412, - "r_filtered": 26.66666667, + "r_filtered": 100, "attached_condition": "orders.o_totalprice between 180000 and 230000" } } diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 726e20889fe..35520d1bd23 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); @@ -5639,4 +5639,60 @@ EXECUTE stmt; COUNT(DISTINCT a) 3 DROP TABLE t1; +# +# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +# failed in Item_cond::remove_eq_conds on SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +a +1 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; +a +1 +# Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +a +1 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; +a +1 +DROP TABLE t1; +DROP VIEW v1, v2; End of 10.0 tests diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test index d17d8d626b8..f7b379861e8 100644 --- a/mysql-test/main/select.test +++ b/mysql-test/main/select.test @@ -4742,4 +4742,44 @@ EXECUTE stmt; --enable_warnings DROP TABLE t1; +--echo # +--echo # MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +--echo # failed in Item_cond::remove_eq_conds on SELECT +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); + +--echo # Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); + +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +EXECUTE stmt; + +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; + +--echo # Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); + +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +EXECUTE stmt; + +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; + +DROP TABLE t1; +DROP VIEW v1, v2; + --echo End of 10.0 tests diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 9a610852c80..790e94488cf 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -3627,7 +3627,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3635,7 +3635,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3643,7 +3643,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3651,7 +3651,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); @@ -5650,6 +5650,62 @@ EXECUTE stmt; COUNT(DISTINCT a) 3 DROP TABLE t1; +# +# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +# failed in Item_cond::remove_eq_conds on SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +a +1 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; +a +1 +# Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +a +1 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; +a +1 +DROP TABLE t1; +DROP VIEW v1, v2; End of 10.0 tests set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch_jcl6; diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 726e20889fe..35520d1bd23 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); @@ -5639,4 +5639,60 @@ EXECUTE stmt; COUNT(DISTINCT a) 3 DROP TABLE t1; +# +# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +# failed in Item_cond::remove_eq_conds on SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +a +1 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; +a +1 +# Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +a +1 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; +a +1 +DROP TABLE t1; +DROP VIEW v1, v2; End of 10.0 tests diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index 7dc3c2f463c..e6363610301 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -132,7 +132,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where -1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +1 PRIMARY t3 ref|filter PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX FFOLDERID_IDX|CMFLDRPARNT_IDX 34|35 test.t3.PARENTID 1 (29%) Using where; Using rowid filter drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index 6ca529cf0c4..f23c66e10a5 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1320,6 +1320,28 @@ CASE WHEN a THEN DEFAULT(a) END DROP TABLE t1; SET timestamp=DEFAULT; # +# MDEV-27653 long uniques don't work with unicode collations +# +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +INSERT INTO t1 SELECT MAX(a) FROM t1; +ERROR 23000: Duplicate entry '2001-01-01 11:20:30' for key 'a' +SELECT * FROM t1; +a +2001-01-01 11:20:30 +DROP TABLE t1; +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +SET time_zone=DEFAULT; +# # End of 10.4 tests # # diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index 661ec56a04f..a0f1f2c3d78 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -878,6 +878,27 @@ DROP TABLE t1; SET timestamp=DEFAULT; --echo # +--echo # MDEV-27653 long uniques don't work with unicode collations +--echo # + +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +--error ER_DUP_ENTRY +INSERT INTO t1 SELECT MAX(a) FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +CHECK TABLE t1; +DROP TABLE t1; +SET time_zone=DEFAULT; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index 9a87c507c64..96b13010516 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -4273,11 +4273,13 @@ GROUP BY LEFT((SYSDATE()), 'foo') WITH ROLLUP; SUM(b) OVER (PARTITION BY a) ROW_NUMBER() OVER (PARTITION BY b) -NULL 1 -NULL 1 +0 1 +0 2 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'foo' Warning 1292 Truncated incorrect INTEGER value: 'foo' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' drop table t1; # # @@ -4336,6 +4338,46 @@ pk a bit_or DROP TABLE t2; DROP TABLE t1; # +# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +# +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); +SELECT +a, +RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM +t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY +a; +a RANK() OVER (ORDER BY SUM(DISTINCT i1)) +1 1 +2 2 +3 3 +DROP TABLE t1, t2; +# +# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +# +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, +t2 TT2, +t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +1 +1 +DROP TABLE t1, t2, t3; +# # End of 10.3 tests # # diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index 5a216123369..472f3529e98 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2816,6 +2816,46 @@ DROP TABLE t2; DROP TABLE t1; +--echo # +--echo # MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +--echo # + +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); + +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); + +SELECT + a, + RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM + t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY + a; + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +--echo # +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); + +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, + t2 TT2, + t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; + +DROP TABLE t1, t2, t3; --echo # --echo # End of 10.3 tests diff --git a/mysql-test/main/win_orderby.result b/mysql-test/main/win_orderby.result index bf4a40a4db3..1a9860c1c76 100644 --- a/mysql-test/main/win_orderby.result +++ b/mysql-test/main/win_orderby.result @@ -24,3 +24,66 @@ pk count(a) over (order by pk rows between 2 preceding and 2 following) 28 5 27 5 drop table t0,t1; +# +# MDEV-30052: Crash with a query containing nested WINDOW clauses +# +CREATE TABLE t1 (c INT); +insert into t1 values (1),(2); +UPDATE t1 SET c=1 +WHERE c=2 +ORDER BY +(1 IN (( +SELECT * +FROM (SELECT * FROM t1) AS v1 +GROUP BY c +WINDOW v2 AS (ORDER BY +(SELECT * +FROM t1 +GROUP BY c +WINDOW v3 AS (PARTITION BY c) +) +) +)) +); +drop table t1; +# +# MDEV-29359: Server crashed with heap-use-after-free in +# Field::is_null(long long) const (Just testcase) +# +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (-1),(0),(84); +SELECT +id IN (SELECT id +FROM t1 +WINDOW w AS (ORDER BY (SELECT 1 +FROM t1 +WHERE +EXISTS ( SELECT id +FROM t1 +GROUP BY id +WINDOW w2 AS (ORDER BY id) +) +) +) +) +FROM t1; +id IN (SELECT id +FROM t1 +WINDOW w AS (ORDER BY (SELECT 1 +FROM t1 +WHERE +EXISTS ( SELECT id +FROM t1 +GROUP BY id +WINDOW w2 AS (ORDER BY id) +) +) +) +) +1 +1 +1 +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/win_orderby.test b/mysql-test/main/win_orderby.test index 7f02a582ea0..65421fc095c 100644 --- a/mysql-test/main/win_orderby.test +++ b/mysql-test/main/win_orderby.test @@ -33,3 +33,58 @@ limit 4; --disable_view_protocol drop table t0,t1; + + +--echo # +--echo # MDEV-30052: Crash with a query containing nested WINDOW clauses +--echo # + +CREATE TABLE t1 (c INT); +insert into t1 values (1),(2); +UPDATE t1 SET c=1 +WHERE c=2 +ORDER BY + (1 IN (( + SELECT * + FROM (SELECT * FROM t1) AS v1 + GROUP BY c + WINDOW v2 AS (ORDER BY + (SELECT * + FROM t1 + GROUP BY c + WINDOW v3 AS (PARTITION BY c) + ) + ) + )) + ); +drop table t1; + +--echo # +--echo # MDEV-29359: Server crashed with heap-use-after-free in +--echo # Field::is_null(long long) const (Just testcase) +--echo # + +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (-1),(0),(84); + +SELECT + id IN (SELECT id + FROM t1 + WINDOW w AS (ORDER BY (SELECT 1 + FROM t1 + WHERE + EXISTS ( SELECT id + FROM t1 + GROUP BY id + WINDOW w2 AS (ORDER BY id) + ) + ) + ) + ) +FROM t1; + +DROP TABLE t1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD Binary files differnew file mode 100644 index 00000000000..5f1ad998c00 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD diff --git a/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI Binary files differnew file mode 100644 index 00000000000..7a748abbae5 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI diff --git a/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm Binary files differnew file mode 100644 index 00000000000..5a4cec72324 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm diff --git a/mysql-test/suite/binlog/r/binlog_recovery_after_checksum_change.result b/mysql-test/suite/binlog/r/binlog_recovery_after_checksum_change.result new file mode 100644 index 00000000000..85b0b84c684 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_recovery_after_checksum_change.result @@ -0,0 +1,8 @@ +connection default; +set @@global.binlog_checksum=none; +set @@session.debug_dbug='d,crash_before_write_second_checkpoint_event'; +set @@global.binlog_checksum=crc32; +ERROR HY000: Lost connection to MySQL server during query +connection default; +NOT FOUND /Replication event checksum verification failed/ in mysqld.1.err +End of the tests diff --git a/mysql-test/suite/binlog/t/innodb_rc_insert_before_delete.test b/mysql-test/suite/binlog/t/innodb_rc_insert_before_delete.test new file mode 100644 index 00000000000..891a7b8ba2f --- /dev/null +++ b/mysql-test/suite/binlog/t/innodb_rc_insert_before_delete.test @@ -0,0 +1,89 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_binlog_format_mixed.inc +--source include/count_sessions.inc + +# MDEV-30010 merely adds is a Read-Committed version MDEV-30225 test +# solely to prove the RC isolation yields ROW binlog format as it is +# supposed to: +# https://mariadb.com/kb/en/unsafe-statements-for-statement-based-replication/#isolation-levels. +# The original MDEV-30225 test is adapted to the RC to create +# a similar safisticated scenario which does not lead to any deadlock though. + +--connect (pause_purge,localhost,root) +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connection default +CREATE TABLE t (pk int PRIMARY KEY, sk INT UNIQUE) ENGINE=InnoDB; +INSERT INTO t VALUES (10, 100); + +--connect (con1,localhost,root) +BEGIN; # trx 0 +SELECT * FROM t WHERE sk = 100 FOR UPDATE; + +--connect (con2,localhost,root) +SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL insert_wait_started"; +# trx 1 is locked on try to read the record in secondary index during duplicates +# check. It's the first in waiting queue, that's why it will be woken up firstly +# when trx 0 commits. +--send INSERT INTO t VALUES (5, 100) # trx 1 + +--connect (con3,localhost,root) +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET DEBUG_SYNC="now WAIT_FOR insert_wait_started"; +SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL delete_started_waiting"; +# trx 2 can delete (5, 100) on master, but not on slave, as on slave trx 1 +# can insert (5, 100) after trx 2 positioned it's cursor. Trx 2 lock is placed +# in waiting queue after trx 1 lock, but its persistent cursor position was +# stored on (100, 10) record in secondary index before suspending. After trx 1 +# is committed, trx 2 will restore persistent cursor position on (100, 10). As +# (100, 5) secondary index record was inserted before (100, 10) in logical +# order, and (100, 10) record is delete-marked, trx 2 just continues scanning. +# +# Note. There can be several records with the same key in unique secondary +# index, but only one of them must be non-delete-marked. That's why when we do +# point query, cursor position is set in the first record in logical order, and +# then records are iterated until either non-delete-marked record is found or +# all records with the same unique fields are iterated. + +# to prepare showing interesting binlog events +--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +BEGIN; +--send UPDATE t SET sk = 200 WHERE sk = 100; # trx 2 + +--connection con1 +SET DEBUG_SYNC="now WAIT_FOR delete_started_waiting"; +DELETE FROM t WHERE sk=100; # trx 0 +COMMIT; +--disconnect con1 + +--connection con2 +--reap +--disconnect con2 + +--connection con3 +--error 0 +--reap +if (`SELECT ROW_COUNT() > 0`) +{ + --echo unexpected effective UPDATE + --die +} +--echo must be logged in ROW format as the only event of trx 2 (con3) +INSERT INTO t VALUES (11, 101); +COMMIT; +--source include/show_binlog_events.inc +--disconnect con3 + +--connection default +# If the bug is not fixed, we will see the row inserted by trx 1 here. This can +# cause duplicate key error on slave, when some other trx tries in insert row +# with the same secondary key, as was inserted by trx 1, and not deleted by trx +# 2. +SELECT * FROM t; + +--disconnect pause_purge +SET DEBUG_SYNC="RESET"; +DROP TABLE t; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index 60df9ca5260..5b9dab62c01 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -4279,11 +4279,13 @@ GROUP BY LEFT((SYSDATE()), 'foo') WITH ROLLUP; SUM(b) OVER (PARTITION BY a) ROW_NUMBER() OVER (PARTITION BY b) -NULL 1 -NULL 1 +0 1 +0 2 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'foo' Warning 1292 Truncated incorrect INTEGER value: 'foo' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' drop table t1; # # @@ -4342,6 +4344,46 @@ pk a bit_or DROP TABLE t2; DROP TABLE t1; # +# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +# +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); +SELECT +a, +RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM +t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY +a; +a RANK() OVER (ORDER BY SUM(DISTINCT i1)) +1 1 +2 2 +3 3 +DROP TABLE t1, t2; +# +# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +# +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, +t2 TT2, +t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +1 +1 +DROP TABLE t1, t2, t3; +# # End of 10.3 tests # # diff --git a/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test b/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test index 8f0f0ed65ea..214d5166781 100644 --- a/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test +++ b/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test @@ -205,8 +205,3 @@ CALL mtr.add_suppression("conflict state 7 after post commit"); # Warning happens when the cluster is started for the first time CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); - ---connection node_2 -call mtr.add_suppression("Error in Log_event::read_log_event():.*"); -CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); - diff --git a/mysql-test/suite/mariabackup/incremental_drop_db.result b/mysql-test/suite/mariabackup/incremental_drop_db.result new file mode 100644 index 00000000000..3a6c89f8de0 --- /dev/null +++ b/mysql-test/suite/mariabackup/incremental_drop_db.result @@ -0,0 +1,30 @@ +call mtr.add_suppression("InnoDB: New log files created"); +# +# Start of 10.3 tests +# +# +# MDEV-23335 MariaBackup Incremental Does Not Reflect Dropped/Created Databases +# +CREATE DATABASE db1; +CREATE DATABASE db2; +CREATE TABLE db1.t1 (a INT) ENGINE=MyISAM; +CREATE TABLE db1.t2 (a INT) ENGINE=InnoDB; +# Create base backup +DROP DATABASE db1; +# Create incremental backup +# Remove incremental_dir/db2/db.opt file to make incremental_dir/db2/ empty +# Prepare base backup, apply incremental one +# shutdown server +# remove datadir +# xtrabackup move back +# restart +# Expect no 'db1' in the output, because it was really dropped. +# Expect 'db2' in the ouput, because it was not dropped! +# (its incremental directory was emptied only) +SHOW DATABASES LIKE 'db%'; +Database (db%) +db2 +DROP DATABASE db2; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/mariabackup/incremental_drop_db.test b/mysql-test/suite/mariabackup/incremental_drop_db.test new file mode 100644 index 00000000000..de270435e9d --- /dev/null +++ b/mysql-test/suite/mariabackup/incremental_drop_db.test @@ -0,0 +1,68 @@ +--source include/have_innodb.inc +call mtr.add_suppression("InnoDB: New log files created"); + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-23335 MariaBackup Incremental Does Not Reflect Dropped/Created Databases +--echo # + +--let $datadir=`SELECT @@datadir` +--let $basedir=$MYSQLTEST_VARDIR/tmp/backup +--let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1 + +# Create two databases: +# - db1 is dropped normally below +# - db2 is used to cover a corner case: its db.opt file is removed + +# Incremental backup contains: +# - no directory for db1 +# - an empty directory for db2 (after we remove db2/db.opt) + + +CREATE DATABASE db1; +CREATE DATABASE db2; + +# Add some tables to db1 +CREATE TABLE db1.t1 (a INT) ENGINE=MyISAM; +CREATE TABLE db1.t2 (a INT) ENGINE=InnoDB; + +--echo # Create base backup +--disable_result_log +--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir +--enable_result_log + +DROP DATABASE db1; + +--echo # Create incremental backup +--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$incremental_dir --incremental-basedir=$basedir + +--echo # Remove incremental_dir/db2/db.opt file to make incremental_dir/db2/ empty +--remove_file $incremental_dir/db2/db.opt + + +--echo # Prepare base backup, apply incremental one +--disable_result_log +--exec $XTRABACKUP --prepare --target-dir=$basedir +--exec $XTRABACKUP --prepare --target-dir=$basedir --incremental-dir=$incremental_dir +--enable_result_log + +--let $targetdir=$basedir +--source include/restart_and_restore.inc +--enable_result_log + +--echo # Expect no 'db1' in the output, because it was really dropped. +--echo # Expect 'db2' in the ouput, because it was not dropped! +--echo # (its incremental directory was emptied only) + +SHOW DATABASES LIKE 'db%'; +DROP DATABASE db2; + +--rmdir $basedir +--rmdir $incremental_dir + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result index 9768040aa94..fc9d98f5d0a 100644 --- a/mysql-test/suite/plugins/r/server_audit.result +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -521,3 +521,10 @@ TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_ TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show variables like \'server_audit%\'',0 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,plugin, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'uninstall plugin server_audit',0 +# +# MDEV-27631 Assertion `global_status_var.global_memory_used == 0' failed in mysqld_exit +# +install plugin server_audit soname 'server_audit'; +uninstall plugin server_audit; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test index d8f8b9d0165..5048ccc2446 100644 --- a/mysql-test/suite/plugins/t/server_audit.test +++ b/mysql-test/suite/plugins/t/server_audit.test @@ -235,3 +235,8 @@ uninstall plugin server_audit; cat_file $MYSQLD_DATADIR/server_audit.log; remove_file $MYSQLD_DATADIR/server_audit.log; +--echo # +--echo # MDEV-27631 Assertion `global_status_var.global_memory_used == 0' failed in mysqld_exit +--echo # +install plugin server_audit soname 'server_audit'; +uninstall plugin server_audit; diff --git a/mysql-test/suite/rpl/include/create_or_drop_sync_func.inc b/mysql-test/suite/rpl/include/create_or_drop_sync_func.inc new file mode 100644 index 00000000000..74cde5de1db --- /dev/null +++ b/mysql-test/suite/rpl/include/create_or_drop_sync_func.inc @@ -0,0 +1,75 @@ +# Creates or drops a stored function as a part of debug-sync based +# synchronization mechanism between replication servers. +# +# Parameters: +# $create_or_drop= [create] +# $server_master = [master] +# $server_slave = [slave] +if (!$create_or_drop) +{ + --let $create_or_drop=create +} + +if (`select strcmp('$create_or_drop', 'create') = 0`) +{ + if (!$server_master) + { + --let $server_master=master + } + if (!$server_slave) + { + --let $server_slave=slave + } + + --connection $server_master + # Use a stored function to inject a debug_sync into the appropriate THD. + # The function does nothing on the master, and on the slave it injects the + # desired debug_sync action(s). + SET sql_log_bin=0; + --delimiter || + CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) + RETURNS INT DETERMINISTIC + BEGIN + RETURN x; + END + || + --delimiter ; + SET sql_log_bin=1; + + --connection $server_slave + + SET sql_log_bin=0; + --delimiter || + CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) + RETURNS INT DETERMINISTIC + BEGIN + IF d1 != '' THEN + SET debug_sync = d1; + END IF; + IF d2 != '' THEN + SET debug_sync = d2; + END IF; + RETURN x; + END + || + --delimiter ; +SET sql_log_bin=1; +} + +if (`select strcmp('$create_or_drop', 'drop') = 0`) +{ + if (!$server_slave) + { + --let $server_slave=slave= + } + if (!$server_master) + { + --let $server_master=master + } + --connection $server_slave + SET DEBUG_SYNC='RESET'; + + --connection $server_master + SET DEBUG_SYNC='RESET'; + DROP FUNCTION foo; +} diff --git a/mysql-test/suite/rpl/r/rpl_delayed_parallel_slave_sbm.result b/mysql-test/suite/rpl/r/rpl_delayed_parallel_slave_sbm.result new file mode 100644 index 00000000000..f783b1e0783 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_delayed_parallel_slave_sbm.result @@ -0,0 +1,60 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +change master to master_delay=3, master_use_gtid=Slave_Pos; +set @@GLOBAL.slave_parallel_threads=2; +include/start_slave.inc +connection master; +create table t1 (a int); +include/sync_slave_sql_with_master.inc +# +# Pt 1) Ensure SBM is updated immediately upon arrival of the next event +# Lock t1 on slave so the first received transaction does not complete/commit +connection slave; +LOCK TABLES t1 WRITE; +connection master; +# Sleep 2 to allow a buffer between events for SBM check +insert into t1 values (0); +include/save_master_gtid.inc +connection slave; +# Waiting for transaction to arrive on slave and begin SQL Delay.. +# Validating SBM is updated on event arrival.. +# ..done +connection slave; +UNLOCK TABLES; +include/sync_with_master_gtid.inc +# +# Pt 2) If the SQL thread has not entered an idle state, ensure +# following events do not update SBM +# Stop slave IO thread so it receives both events together on restart +connection slave; +include/stop_slave_io.inc +connection master; +# Sleep 2 to allow a buffer between events for SBM check +insert into t1 values (1); +# Sleep 3 to create gap between events +insert into t1 values (2); +connection slave; +LOCK TABLES t1 WRITE; +START SLAVE IO_THREAD; +# Wait for first transaction to complete SQL delay and begin execution.. +# Validate SBM calculation doesn't use the second transaction because SQL thread shouldn't have gone idle.. +# ..and that SBM wasn't calculated using prior committed transactions +# ..done +connection slave; +UNLOCK TABLES; +# +# Cleanup +# Reset master_delay +include/stop_slave.inc +CHANGE MASTER TO master_delay=0; +set @@GLOBAL.slave_parallel_threads=4; +include/start_slave.inc +connection master; +DROP TABLE t1; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/rpl_end.inc +# End of rpl_delayed_parallel_slave_sbm.test diff --git a/mysql-test/suite/rpl/r/rpl_parallel_analyze.result b/mysql-test/suite/rpl/r/rpl_parallel_analyze.result new file mode 100644 index 00000000000..c0a2abbc1ee --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel_analyze.result @@ -0,0 +1,76 @@ +include/master-slave.inc +[connection master] +# Initialize +connection slave; +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +# Setup data +connection master; +CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE ta (a int); +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +connection master; +SET sql_log_bin=0; +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) +RETURNS INT DETERMINISTIC +BEGIN +RETURN x; +END +|| +SET sql_log_bin=1; +connection slave; +SET sql_log_bin=0; +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) +RETURNS INT DETERMINISTIC +BEGIN +IF d1 != '' THEN +SET debug_sync = d1; +END IF; +IF d2 != '' THEN +SET debug_sync = d2; +END IF; +RETURN x; +END +|| +SET sql_log_bin=1; +include/stop_slave.inc +SET @old_parallel_threads =@@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode =@@GLOBAL.slave_parallel_mode; +SET @old_gtid_strict_mode =@@GLOBAL.gtid_strict_mode; +SET GLOBAL slave_parallel_threads=10; +SET GLOBAL slave_parallel_mode=conservative; +SET GLOBAL gtid_strict_mode=ON; +include/start_slave.inc +connection master; +SET @old_format= @@SESSION.binlog_format; +SET binlog_format=statement; +INSERT INTO t1 VALUES (foo(1, 'rpl_parallel_after_mark_start_commit WAIT_FOR sig_go', '')); +ANALYZE TABLE ta; +Table Op Msg_type Msg_text +test.ta analyze status Engine-independent statistics collected +test.ta analyze status Table is already up to date +include/save_master_gtid.inc +connection slave; +SELECT info FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit"; +info +ANALYZE TABLE ta +set @@debug_sync="now signal sig_go"; +include/sync_with_master_gtid.inc +# Cleanup +connection master; +DROP TABLE t1,ta; +connection slave; +SET DEBUG_SYNC='RESET'; +connection master; +SET DEBUG_SYNC='RESET'; +DROP FUNCTION foo; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +SET @@GLOBAL.slave_parallel_threads=@old_parallel_threads; +SET @@GLOBAL.slave_parallel_mode =@old_parallel_mode; +SET @@GLOBAL.gtid_strict_mode =@old_gtid_strict_mode; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm-slave.opt b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm-slave.opt new file mode 100644 index 00000000000..9eea6a54b68 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm-slave.opt @@ -0,0 +1 @@ +--slave-parallel-threads=4 diff --git a/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm.test b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm.test new file mode 100644 index 00000000000..4bcb7ad9e3f --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm.test @@ -0,0 +1,133 @@ +# +# This test ensures that after a delayed parallel slave has idled, i.e. +# executed everything in its relay log, the next event group that the SQL +# thread reads from the relay log will immediately be used in the +# Seconds_Behind_Master. In particular, it ensures that the calculation for +# Seconds_Behind_Master is based on the timestamp of the new transaction, +# rather than the last committed transaction. +# +# References: +# MDEV-29639: Seconds_Behind_Master is incorrect for Delayed, Parallel +# Replicas +# + +--source include/master-slave.inc + +--connection slave +--source include/stop_slave.inc +--let $master_delay= 3 +--eval change master to master_delay=$master_delay, master_use_gtid=Slave_Pos +--let $old_slave_threads= `SELECT @@GLOBAL.slave_parallel_threads` +set @@GLOBAL.slave_parallel_threads=2; +--source include/start_slave.inc + +--connection master +create table t1 (a int); +--source include/sync_slave_sql_with_master.inc + +--echo # +--echo # Pt 1) Ensure SBM is updated immediately upon arrival of the next event + +--echo # Lock t1 on slave so the first received transaction does not complete/commit +--connection slave +LOCK TABLES t1 WRITE; + +--connection master +--echo # Sleep 2 to allow a buffer between events for SBM check +sleep 2; + +--let $ts_trx_before_ins= `SELECT UNIX_TIMESTAMP()` +--let insert_ctr= 0 +--eval insert into t1 values ($insert_ctr) +--inc $insert_ctr +--source include/save_master_gtid.inc + +--connection slave + +--echo # Waiting for transaction to arrive on slave and begin SQL Delay.. +--let $wait_condition= SELECT count(*) FROM information_schema.processlist WHERE state LIKE 'Waiting until MASTER_DELAY seconds after master executed event'; +--source include/wait_condition.inc + +--echo # Validating SBM is updated on event arrival.. +--let $sbm_trx1_arrive= query_get_value(SHOW SLAVE STATUS, Seconds_Behind_Master, 1) +--let $seconds_since_idling= `SELECT UNIX_TIMESTAMP() - $ts_trx_before_ins` +if (`SELECT $sbm_trx1_arrive > ($seconds_since_idling + 1)`) +{ + --echo # SBM was $sbm_trx1_arrive yet shouldn't have been larger than $seconds_since_idling + 1 (for possible negative clock_diff_with_master) + --die Seconds_Behind_Master should reset after idling +} +--echo # ..done + +--connection slave +UNLOCK TABLES; +--source include/sync_with_master_gtid.inc + +--echo # +--echo # Pt 2) If the SQL thread has not entered an idle state, ensure +--echo # following events do not update SBM + +--echo # Stop slave IO thread so it receives both events together on restart +--connection slave +--source include/stop_slave_io.inc + +--connection master + +--echo # Sleep 2 to allow a buffer between events for SBM check +sleep 2; +--let $ts_trxpt2_before_ins= `SELECT UNIX_TIMESTAMP()` +--eval insert into t1 values ($insert_ctr) +--inc $insert_ctr +--echo # Sleep 3 to create gap between events +sleep 3; +--eval insert into t1 values ($insert_ctr) +--inc $insert_ctr +--let $ts_trx_after_ins= `SELECT UNIX_TIMESTAMP()` + +--connection slave +LOCK TABLES t1 WRITE; + +START SLAVE IO_THREAD; + +--echo # Wait for first transaction to complete SQL delay and begin execution.. +--let $wait_condition= SELECT count(*) FROM information_schema.processlist WHERE state LIKE 'Waiting for table metadata lock%' AND command LIKE 'Slave_Worker'; +--source include/wait_condition.inc + +--echo # Validate SBM calculation doesn't use the second transaction because SQL thread shouldn't have gone idle.. +--let $sbm_after_trx_no_idle= query_get_value(SHOW SLAVE STATUS, Seconds_Behind_Master, 1) +--let $timestamp_trxpt2_arrive= `SELECT UNIX_TIMESTAMP()` +if (`SELECT $sbm_after_trx_no_idle < $timestamp_trxpt2_arrive - $ts_trx_after_ins`) +{ + --let $cmpv= `SELECT $timestamp_trxpt2_arrive - $ts_trx_after_ins` + --echo # SBM $sbm_after_trx_no_idle was more recent than time since last transaction ($cmpv seconds) + --die Seconds_Behind_Master should not have used second transaction timestamp +} +--let $seconds_since_idling= `SELECT ($timestamp_trxpt2_arrive - $ts_trxpt2_before_ins)` +--echo # ..and that SBM wasn't calculated using prior committed transactions +if (`SELECT $sbm_after_trx_no_idle > ($seconds_since_idling + 1)`) +{ + --echo # SBM was $sbm_after_trx_no_idle yet shouldn't have been larger than $seconds_since_idling + 1 (for possible negative clock_diff_with_master) + --die Seconds_Behind_Master calculation should not have used prior committed transaction +} +--echo # ..done + +--connection slave +UNLOCK TABLES; + +--echo # +--echo # Cleanup + +--echo # Reset master_delay +--source include/stop_slave.inc +--eval CHANGE MASTER TO master_delay=0 +--eval set @@GLOBAL.slave_parallel_threads=$old_slave_threads +--source include/start_slave.inc + +--connection master +DROP TABLE t1; +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--source include/rpl_end.inc +--echo # End of rpl_delayed_parallel_slave_sbm.test diff --git a/mysql-test/suite/rpl/t/rpl_parallel_analyze.test b/mysql-test/suite/rpl/t/rpl_parallel_analyze.test new file mode 100644 index 00000000000..921c6f02904 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_analyze.test @@ -0,0 +1,84 @@ +# The test file is created to prove fixes to +# MDEV-30323 Some DDLs like ANALYZE can complete on parallel slave out of order +# Debug-sync tests aiming at parallel replication of ADMIN commands +# are welcome here. + +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/master-slave.inc + +--echo # Initialize +--connection slave +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; + +--echo # Setup data +--connection master +CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE ta (a int); +--let $pre_load_gtid=`SELECT @@last_gtid` +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--source suite/rpl/include/create_or_drop_sync_func.inc + +# configure MDEV-30323 slave +--source include/stop_slave.inc +SET @old_parallel_threads =@@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode =@@GLOBAL.slave_parallel_mode; +SET @old_gtid_strict_mode =@@GLOBAL.gtid_strict_mode; +SET GLOBAL slave_parallel_threads=10; +SET GLOBAL slave_parallel_mode=conservative; +SET GLOBAL gtid_strict_mode=ON; +--source include/start_slave.inc + +# MDEV-30323 setup needs two group of events the first of which is a DML +# and ANALYZE is the 2nd. +# The latter is made to race in slave execution over the DML thanks +# to a DML latency simulation. +# In the fixed case the race-over should not be a problem: ultimately +# ANALYZE must wait for its turn to update slave@@global.gtid_binlog_pos. +# Otherwise the reported OOO error must be issued. + +--connection master +SET @old_format= @@SESSION.binlog_format; +SET binlog_format=statement; +INSERT INTO t1 VALUES (foo(1, 'rpl_parallel_after_mark_start_commit WAIT_FOR sig_go', '')); + +ANALYZE TABLE ta; +--source include/save_master_gtid.inc + +--connection slave +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +SELECT info FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit"; +if (`select strcmp(@@global.gtid_binlog_pos, '$pre_load_gtid') <> 0 or strcmp(@@global.gtid_slave_pos, '$pre_load_gtid') <> 0`) +{ + --let $bs=`SELECT @@global.gtid_binlog_pos` + --let $es=`SELECT @@global.gtid_slave_pos` + --echo Mismatch between expected $pre_load_gtid state and the actual binlog state " @@global.gtid_binlog_pos = $bs or/and slave execution state @@global.gtid_slave_pos = $es. + --die +} + +set @@debug_sync="now signal sig_go"; +--source include/sync_with_master_gtid.inc + +--echo # Cleanup +--connection master +DROP TABLE t1,ta; +--let $create_or_drop=drop +--source suite/rpl/include/create_or_drop_sync_func.inc + +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc +--source include/stop_slave.inc +SET @@GLOBAL.slave_parallel_threads=@old_parallel_threads; +SET @@GLOBAL.slave_parallel_mode =@old_parallel_mode; +SET @@GLOBAL.gtid_strict_mode =@old_gtid_strict_mode; +--source include/start_slave.inc + +--source include/rpl_end.inc |