diff options
author | Igor Babaev <igor@askmonty.org> | 2022-07-11 16:57:37 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2023-03-15 17:35:22 -0700 |
commit | 88ca62dc689fa66d798c129a101069946acc828c (patch) | |
tree | 2ed5405f9a24f2ca16e7e99cf097d6f3771d3619 /mysql-test/suite | |
parent | 3a9358a4106a1bd0ae2414f6f0bda50afdd65f0a (diff) | |
download | mariadb-git-88ca62dc689fa66d798c129a101069946acc828c.tar.gz |
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 <sanja@mariadb.com>
Diffstat (limited to 'mysql-test/suite')
-rw-r--r-- | mysql-test/suite/engines/iuds/r/update_delete_number.result | 22 | ||||
-rw-r--r-- | mysql-test/suite/engines/iuds/t/update_delete_number.test | 20 |
2 files changed, 28 insertions, 14 deletions
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 |