MDEV-23178: Qualified asterisk not supported in INSERT .. RETURNINGbb-10.5-MDEV-23178

Anaylsis: When we have INSERT/REPLACE...RETURNING and have qualified asterisk,
table_name is not NULL and context->table_list is either NULL or has
incorrect reference because context->table_list has tables from the FROM clause.
context->table_list has incorrect reference (has table from the FROM clause
instead of table we are inserting into) for INSERT/REPLACE...SELECT...RETURNING
because we have a FROM clause from the SELECT statement.
For INSERT/REPLACE...RETURNING it is NULL because there is no FROM clause.
Fix: If table_name is not null, check if we have INSERT/REPLACE...RETURNING.
If so, the reference should be the table we are inserting into and not
the table in the FROM clause.

5 files changed, 83 insertions, 11 deletions

diff --git a/mysql-test/main/insert_returning.result b/mysql-test/main/insert_returning.result
index e664e02bedc..a29aa8d013b 100644
--- a/mysql-test/main/insert_returning.result
+++ b/mysql-test/main/insert_returning.result
@@ -89,6 +89,9 @@ total	val1	id1 && id1	id1	UPPER(val1)	f(id1)
 ANALYZE INSERT INTO t1(id1,val1) VALUES(13,'l') RETURNING *;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	NULL	100.00	100.00	NULL
+INSERT INTO t1(id1,val1) VALUES(14,'m') RETURNING t1.*;
+id1	val1
+14	m
 TRUNCATE TABLE t1;
 #
 # Multiple values in one insert statement...RETURNING
@@ -182,6 +185,9 @@ id	val1	id1 && id1	id1|id1	UPPER(val1)	f(id1)
 ANALYZE INSERT INTO t1 VALUES(25,'a'),(26,'b') RETURNING *;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	NULL	100.00	100.00	NULL
+ANALYZE INSERT INTO t1 VALUES(27,'c'),(28,'d') RETURNING t1.*;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	NULL	100.00	100.00	NULL
 #
 # INSERT...ON DUPLICATE KEY UPDATE...RETURNING
 #
@@ -250,10 +256,14 @@ ANALYZE INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE
 val='k' RETURNING *;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	INSERT	ins_duplicate	ALL	NULL	NULL	NULL	NULL	NULL	NULL	100.00	100.00	NULL
+INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE
+val='l' RETURNING ins_duplicate.*;
+id	val
+2	l
 SELECT * FROM ins_duplicate;
 id	val
 1	a
-2	k
+2	l
 3	c
 4	d
 #
@@ -327,6 +337,9 @@ total	val1	id1 && id1	id1|id1	UPPER(val1)	f(id1)
 ANALYZE INSERT INTO t1 SET id1=12, val1='l' RETURNING *;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	NULL	100.00	100.00	NULL
+INSERT INTO t1 SET id1= 13, val1= 'm' RETURNING t1.*;
+id1	val1
+13	m
 SELECT * FROM t1;
 id1	val1
 1	a
@@ -339,6 +352,7 @@ id1	val1
 8	n
 26	Z
 12	l
+13	m
 #
 # INSERT...SELECT...RETURNING
 #
@@ -372,6 +386,7 @@ id1	val1
 8	n
 26	Z
 12	l
+13	m
 EXECUTE stmt;
 (SELECT id1 FROM t1 WHERE val1='b')
 2
@@ -407,6 +422,7 @@ id2	val2
 5	e
 26	Z
 12	l
+13	m
 Warnings:
 Warning	1062	Duplicate entry '1' for key 'PRIMARY'
 Warning	1062	Duplicate entry '2' for key 'PRIMARY'
@@ -430,6 +446,18 @@ id2	val2
 5	e
 26	Z
 12	l
+13	m
+TRUNCATE TABLE t2;
+INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING t2.*;
+id2	val2
+1	a
+INSERT INTO t2 SELECT t1.* FROM t1 WHERE id1=2 RETURNING t2.*;
+id2	val2
+2	b
+SELECT * FROM t2;
+id2	val2
+1	a
+2	b
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE ins_duplicate;
diff --git a/mysql-test/main/insert_returning.test b/mysql-test/main/insert_returning.test
index b4fc75c28bb..2e9ea4e8b81 100644
--- a/mysql-test/main/insert_returning.test
+++ b/mysql-test/main/insert_returning.test
@@ -41,6 +41,7 @@ SELECT * FROM t1;
 INSERT INTO v1(id1, val1) VALUES (12, 'a') RETURNING id1+id1 as total,val1,
 id1 && id1, id1  id1, UPPER(val1),f(id1);
 ANALYZE INSERT INTO t1(id1,val1) VALUES(13,'l') RETURNING *;
+INSERT INTO t1(id1,val1) VALUES(14,'m') RETURNING t1.*;
 TRUNCATE TABLE t1;
 
 --echo #
@@ -68,6 +69,7 @@ SELECT * FROM t1;
 INSERT INTO v1 VALUES(23,'y'),(24,'z') RETURNING id1 as id,val1,
 id1 && id1, id1|id1, UPPER(val1),f(id1);
 ANALYZE INSERT INTO t1 VALUES(25,'a'),(26,'b') RETURNING *;
+ANALYZE INSERT INTO t1 VALUES(27,'c'),(28,'d') RETURNING t1.*;
 
 --echo #
 --echo # INSERT...ON DUPLICATE KEY UPDATE...RETURNING
@@ -101,6 +103,8 @@ val1='d' RETURNING id1+id1 AS total, val1, id1 && id1, id1|id1, UPPER(val1),
 f(id1);
 ANALYZE INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE
 val='k' RETURNING *;
+INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE
+val='l' RETURNING ins_duplicate.*;
 SELECT * FROM ins_duplicate;
 
 --echo #
@@ -130,6 +134,7 @@ EXPLAIN FORMAT="json" INSERT INTO t1 SET id1=11, val1='k' RETURNING val1;
 INSERT INTO v1 SET id1=26, val1='Z' RETURNING id1+id1 AS total,val1,
 id1 && id1, id1|id1, UPPER(val1),f(id1);
 ANALYZE INSERT INTO t1 SET id1=12, val1='l' RETURNING *;
+INSERT INTO t1 SET id1= 13, val1= 'm' RETURNING t1.*;
 SELECT * FROM t1;
 
 --echo #
@@ -158,6 +163,10 @@ INSERT IGNORE INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING *;
 INSERT IGNORE INTO v2 SELECT * FROM v1 RETURNING *;
 ANALYZE INSERT INTO t2 SELECT * FROM t1 WHERE id1=11 RETURNING *;
 SELECT * FROM t2;
+TRUNCATE TABLE t2;
+INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING t2.*;
+INSERT INTO t2 SELECT t1.* FROM t1 WHERE id1=2 RETURNING t2.*;
+SELECT * FROM t2;
 
 DROP TABLE t1;
 DROP TABLE t2;
diff --git a/mysql-test/main/replace_returning.result b/mysql-test/main/replace_returning.result
index 628b70abad1..eb889b3a05d 100644
--- a/mysql-test/main/replace_returning.result
+++ b/mysql-test/main/replace_returning.result
@@ -33,9 +33,12 @@ EXECUTE stmt;
 id1	(SELECT id2 FROM t2 WHERE val2='b')
 1	2
 DEALLOCATE PREPARE stmt;
+REPLACE INTO t1 (id1, val1) VALUES (1, 'g') RETURNING t1.*;
+id1	val1
+1	g
 SELECT * FROM t1;
 id1	val1
-1	f
+1	g
 TRUNCATE TABLE t1;
 #
 # Multiple values in one replace statement...RETURNING
@@ -69,10 +72,14 @@ id1	(SELECT id2 FROM t2 WHERE val2='b')
 1	2
 2	2
 DEALLOCATE PREPARE stmt;
+REPLACE INTO t1 VALUES (1,'u'),(2,'v') RETURNING t1.*;
+id1	val1
+1	u
+2	v
 SELECT * FROM t1;
 id1	val1
-1	s
-2	t
+1	u
+2	v
 TRUNCATE TABLE t1;
 #
 # REPLACE...SET...RETURNING
@@ -101,9 +108,12 @@ EXECUTE stmt;
 id1	(SELECT id2 FROM t2 WHERE val2='b')
 3	2
 DEALLOCATE PREPARE stmt;
+REPLACE INTO t1 SET id1=1, val1 = 'o' RETURNING t1.*;
+id1	val1
+1	o
 SELECT * FROM t1;
 id1	val1
-1	i
+1	o
 2	j
 3	k
 #
@@ -113,7 +123,7 @@ TRUNCATE TABLE t2;
 REPLACE INTO t2(id2,val2) SELECT * FROM t1;
 REPLACE INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING *;
 id2	val2
-1	i
+1	o
 REPLACE INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING id2+id2 AS total,
 id2&&id2, id2|id2,UPPER(val2),f(id2);
 total	id2&&id2	id2|id2	UPPER(val2)	f(id2)
@@ -122,7 +132,7 @@ REPLACE INTO t2 SELECT * FROM t1 WHERE id1=3 RETURNING (SELECT
 GROUP_CONCAT(val1) FROM t1 WHERE id1=1);
 (SELECT
 GROUP_CONCAT(val1) FROM t1 WHERE id1=1)
-i
+o
 REPLACE INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING (SELECT
 GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1);
 (SELECT
@@ -144,9 +154,12 @@ FROM t2 WHERE id2=0);
 (SELECT id1+id2
 FROM t2 WHERE id2=0)
 NULL
+REPLACE INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING t2.*;
+id2	val2
+2	j
 SELECT * FROM t2;
 id2	val2
-1	i
+1	o
 2	j
 3	k
 DROP TABLE t1;
diff --git a/mysql-test/main/replace_returning.test b/mysql-test/main/replace_returning.test
index b2681585d31..99551cce5ee 100644
--- a/mysql-test/main/replace_returning.test
+++ b/mysql-test/main/replace_returning.test
@@ -28,6 +28,7 @@ PREPARE stmt FROM "REPLACE INTO t1 (id1,val1) VALUES (1,'f') RETURNING
 id1,(SELECT id2 FROM t2 WHERE val2='b')";
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
+REPLACE INTO t1 (id1, val1) VALUES (1, 'g') RETURNING t1.*;
 SELECT * FROM t1;
 TRUNCATE TABLE t1;
 
@@ -46,6 +47,7 @@ PREPARE stmt FROM "REPLACE INTO t1 VALUES (1,'s'),(2,'t') RETURNING id1,
 (SELECT id2 FROM t2 WHERE val2='b')";
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
+REPLACE INTO t1 VALUES (1,'u'),(2,'v') RETURNING t1.*;
 SELECT * FROM t1;
 TRUNCATE TABLE t1;
 
@@ -64,6 +66,7 @@ PREPARE stmt FROM "REPLACE INTO t1 SET id1=3, val1='k' RETURNING id1,
 (SELECT id2 FROM t2 WHERE val2='b')";
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
+REPLACE INTO t1 SET id1=1, val1 = 'o' RETURNING t1.*;
 SELECT * FROM t1;
 
 --echo #
@@ -86,6 +89,7 @@ REPLACE INTO t2 SELECT * FROM t1 WHERE id1=3 RETURNING (SELECT id1+id2
 FROM t1 WHERE id1=1);
 REPLACE INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING (SELECT id1+id2
 FROM t2 WHERE id2=0);
+REPLACE INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING t2.*;
 SELECT * FROM t2;
 
 DROP TABLE t1;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 45ce4be3eb5..4ce7b034029 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -7959,7 +7959,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
               bool any_privileges, uint *hidden_bit_fields)
 {
   Field_iterator_table_ref field_iterator;
-  bool found;
+  bool found, is_insert_or_replace_returning= false;
   char name_buff[SAFE_NAME_LEN+1];
   DBUG_ENTER("insert_fields");
   DBUG_PRINT("arena", ("stmt arena: %p",thd->stmt_arena));
@@ -7978,13 +7978,31 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
 
   found= FALSE;
 
+  if ((thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
+       thd->lex->sql_command == SQLCOM_REPLACE_SELECT ||
+       thd->lex->sql_command == SQLCOM_INSERT ||
+       thd->lex->sql_command == SQLCOM_REPLACE) &&
+       thd->lex->has_returning())
+      is_insert_or_replace_returning= true;
+
   /*
     If table names are qualified, then loop over all tables used in the query,
     else treat natural joins as leaves and do not iterate over their underlying
     tables.
+    Also, When we have INSERT/REPLACE...RETURNING and have qualified asterisk,
+    table_name is not NULL and context->table_list is either NULL or has
+    incorrect reference because context->table_list has tables from the
+    FROM clause.
+    context->table_list has incorrect reference (has table from the FROM clause
+    instead of table we are inserting into) for
+    INSERT/REPLACE...SELECT...RETURNING because we have a FROM clause from the
+    SELECT statement. For INSERT/REPLACE...RETURNING it is NULL because
+    there is no FROM clause.
   */
-  for (TABLE_LIST *tables= (table_name ? context->table_list :
-                            context->first_name_resolution_table);
+  for (TABLE_LIST *tables= (table_name ? (is_insert_or_replace_returning ?
+                                          context->first_name_resolution_table :
+                                          context->table_list) :
+                            (context->first_name_resolution_table));
        tables;
        tables= (table_name ? tables->next_local :
                 tables->next_name_resolution_table)