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>

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