diff options
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/cte_recursive.result | 172 | ||||
-rw-r--r-- | mysql-test/main/cte_recursive.test | 123 | ||||
-rw-r--r-- | mysql-test/main/ctype_utf8.result | 175 | ||||
-rw-r--r-- | mysql-test/main/ctype_utf8.test | 158 | ||||
-rw-r--r-- | mysql-test/main/derived_cond_pushdown.result | 63 | ||||
-rw-r--r-- | mysql-test/main/derived_cond_pushdown.test | 49 | ||||
-rw-r--r-- | mysql-test/main/long_unique_bugs.result | 105 | ||||
-rw-r--r-- | mysql-test/main/long_unique_bugs.test | 200 | ||||
-rw-r--r-- | mysql-test/main/rowid_filter.result | 58 | ||||
-rw-r--r-- | mysql-test/main/select.result | 64 | ||||
-rw-r--r-- | mysql-test/main/select.test | 40 | ||||
-rw-r--r-- | mysql-test/main/select_jcl6.result | 64 | ||||
-rw-r--r-- | mysql-test/main/select_pkeycache.result | 64 | ||||
-rw-r--r-- | mysql-test/main/subselect2.result | 2 | ||||
-rw-r--r-- | mysql-test/main/type_timestamp.result | 22 | ||||
-rw-r--r-- | mysql-test/main/type_timestamp.test | 21 | ||||
-rw-r--r-- | mysql-test/main/win.result | 40 | ||||
-rw-r--r-- | mysql-test/main/win.test | 40 | ||||
-rw-r--r-- | mysql-test/main/win_orderby.result | 63 | ||||
-rw-r--r-- | mysql-test/main/win_orderby.test | 55 |
20 files changed, 1461 insertions, 117 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 8320c8d3c4d..de9a979dd94 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -5703,6 +5703,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 53d3be0b125..a10dbfea179 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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 utf8mb3 COLLATE utf8mb3_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 5ea4b17f259..99b952cdfb8 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 9bbd32a9c84..8c936f3374a 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -20611,6 +20611,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 39e82210e4c..17115c143ac 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3871,6 +3871,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 4c2fbcf1dd6..283ea870a5f 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); @@ -86,16 +113,26 @@ Ignored NO 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 @@ -103,17 +140,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 @@ -132,6 +181,9 @@ Index_comment Ignored NO 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); @@ -146,6 +198,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 @@ -161,6 +217,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); @@ -174,6 +233,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), @@ -189,6 +251,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; @@ -198,6 +263,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; @@ -216,12 +284,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)) @@ -238,10 +310,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, @@ -268,11 +346,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 2300 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'); @@ -280,6 +367,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 Ignored @@ -317,6 +407,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 = ''; @@ -346,6 +439,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 8d1accd6cd3..27bb402bd8b 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 2a546f126d1..5f1a4a1db76 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -354,7 +354,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 @@ -391,6 +391,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" @@ -405,7 +413,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 @@ -449,13 +457,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" } } @@ -615,7 +636,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 @@ -663,6 +684,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" @@ -678,7 +707,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 @@ -738,13 +767,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 93687056c91..99844d05f2a 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 823aa0d54c9..5c18c6547e7 100644 --- a/mysql-test/main/select.test +++ b/mysql-test/main/select.test @@ -4747,4 +4747,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 c1098e660cd..a19e98d49a3 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 93687056c91..99844d05f2a 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 508dd56f694..275d35ac476 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 3f4346bb17a..bff33c030b5 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 a60995ea836..109296ef2a1 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -4417,6 +4417,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 d7f52ec32e9..0cc929ce46b 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 # |