From 88ca62dc689fa66d798c129a101069946acc828c Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 11 Jul 2022 16:57:37 -0700 Subject: MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE This patch fixes not only the assertion failure in the function Field_iterator_table_ref::set_field_iterator() but also: - fixes the problem of forced materialization of derived tables used in subqueries contained in WHERE clauses of single-table and multi-table UPDATE and DELETE statements - fixes the problem of MDEV-17954 that prevented execution of multi-table DELETE statements if they use in their WHERE clauses references to the tables that are updated. The patch must be considered a complement to the patch for MDEV-28883. Approved by Oleksandr Byelkin --- .../engines/iuds/r/update_delete_number.result | 22 +++++++++++++++------- .../suite/engines/iuds/t/update_delete_number.test | 20 +++++++++++++------- 2 files changed, 28 insertions(+), 14 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/engines/iuds/r/update_delete_number.result b/mysql-test/suite/engines/iuds/r/update_delete_number.result index 1cd2a62cb56..1534f93b436 100644 --- a/mysql-test/suite/engines/iuds/r/update_delete_number.result +++ b/mysql-test/suite/engines/iuds/r/update_delete_number.result @@ -739,8 +739,21 @@ c1 c2 c3 c1 c2 c3 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); +DELETE FROM t1,t2 using t1,t2 +where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10; +SELECT * FROM t1; +c1 c2 c3 +1 127 3 +254 127 1 +3 NULL 5 +SELECT * FROM t2; +c1 c2 c3 +-1 NULL 5 +127 255 1 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 INT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -981,7 +994,6 @@ create table mt1 (col1 int); create table mt2 (col1 int); update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1; delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1; -ERROR HY000: Table 'mt1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table mt1,mt2; CREATE TABLE IF NOT EXISTS `mt1` (`id` int(11) NOT NULL auto_increment, `tst` text, `tsmt1` text, PRIMARY KEY (`id`)); CREATE TABLE IF NOT EXISTS `mt2` (`ID` int(11) NOT NULL auto_increment, `ParId` int(11) default NULL, `tst` text, `tsmt1` text, PRIMARY KEY (`ID`), KEY `IX_ParId_mt2` (`ParId`), FOREIGN KEY (`ParId`) REFERENCES `mt1` (`id`)); @@ -1852,7 +1864,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL PRIMARY KEY, c2 TINYINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 TINYINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -2599,7 +2610,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 SMALLINT UNSIGNED NOT NULL PRIMARY KEY, c2 SMALLINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 SMALLINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -3346,7 +3356,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, c2 MEDIUMINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 MEDIUMINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -4093,7 +4102,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL PRIMARY KEY, c2 BIGINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 BIGINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); diff --git a/mysql-test/suite/engines/iuds/t/update_delete_number.test b/mysql-test/suite/engines/iuds/t/update_delete_number.test index ce3f90140b2..4347d0606ae 100644 --- a/mysql-test/suite/engines/iuds/t/update_delete_number.test +++ b/mysql-test/suite/engines/iuds/t/update_delete_number.test @@ -285,8 +285,18 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); +# After the patch for MDEV-28883 this should not report +# ER_UPDATE_TABLE_USED anymore +DELETE FROM t1,t2 using t1,t2 + where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; # eq-ref join CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); @@ -496,7 +506,7 @@ drop table mt1, mt2, mt3; create table mt1 (col1 int); create table mt2 (col1 int); update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1; --- error ER_UPDATE_TABLE_USED +# -- error ER_UPDATE_TABLE_USED delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1; drop table mt1,mt2; @@ -865,7 +875,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1166,7 +1175,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1467,7 +1475,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1768,7 +1775,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join -- cgit v1.2.1