MDEV-30073: Stored Procedure Returns Corrupt Results When Run a Second Time10.4-MDEV-30073

When name resolution is performed for a view involved in a query
executed as Prepared Statement, changes in an item list of view's select_lex
is resgistered for rollback with Item_change_list::rollback_item_tree_changes.
Later, EXISTS-to-IN transformation takes place and previously resolved items
are replace with new items created as part of EXISTS-to-IN transformation.
This change is supposed to be permanent, so it is not recorded for rollback.
On fininishing execution of a prepared statement clean-up is performed
that does rollback of changes made on phase of name resolutions.
In result, changes made by EXISTS-to-IN transformation is rolled back
that lead to output of incorrect results set.

To fix the original issue it is proposed to make permanent name resolution
for view's fields.

Unfortunately, this fix introduces another issue that takes plce when
join of a regular table and a view is run and there is condition
  'where exist (subquery)'
(see test case in the patch)

3 files changed, 220 insertions, 7 deletions

diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 408b1ec2666..246a97ee5a0 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5805,5 +5805,105 @@ END;
 $
 ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
 #
+# MDEV-30073: Stored Procedure Returns Corrupt Results When Run a Second Time
+#
+DROP TABLE IF EXISTS t1, t2, t3, t4, t5;
+CREATE TABLE t1 (
+product_key int
+);
+INSERT INTO t1 VALUES (3569);
+CREATE TABLE t2 (
+id int,
+product_key int,
+dealerid int
+);
+INSERT INTO t2 VALUES
+(16494, 3569, 4),
+(16949, 3569, 112);
+CREATE TABLE t3 (
+product_key int
+);
+INSERT INTO t3 VALUES (3569);
+CREATE TABLE t4 (
+group_id int,
+product_key int
+);
+INSERT INTO t4 VALUES (117, 3569);
+CREATE TABLE t5 (
+group_id int,
+dealerid int
+);
+INSERT INTO t5 VALUES (117, 4);
+PREPARE stmt FROM "
+SELECT * FROM
+(
+ t1
+ JOIN
+ (
+  SELECT t2.dealerid AS dealerid, t3.product_key AS product_key
+  FROM
+  (
+   t2 JOIN t3
+   ON(t3.product_key = t2.product_key)
+  )
+ ) pd
+ ON(t1.product_key = pd.product_key)
+)
+WHERE
+! EXISTS
+(
+ SELECT pd2.id, pd2.product_key
+ FROM
+ (
+  (
+   (
+    SELECT t2.id AS id, t3.product_key AS product_key
+    FROM
+    (
+     t2 JOIN t3
+     ON(t3.product_key = t2.product_key)
+    )
+   ) pd2
+   JOIN t4
+   ON (t4.product_key = pd2.product_key)
+  )
+  JOIN t5 icrgr
+  ON(icrgr.group_id = t4.group_id)
+ )
+ WHERE
+ pd2.product_key = pd.product_key
+ AND
+ icrgr.dealerid = pd.dealerid
+)";
+# It is expected a result set containing only one record (3569, 112, 3569)
+EXECUTE stmt;
+product_key	dealerid	product_key
+3569	112	3569
+# The second execution of this Prepared Statement should produce the same results set
+EXECUTE stmt;
+product_key	dealerid	product_key
+3569	112	3569
+Clean up
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2, t3, t4, t5;
+# Unfortunately, the current fix breaks the following test case
+CREATE TABLE t1 (i INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1), (2);
+PREPARE stmt FROM 'SELECT 100 FROM t1 JOIN v1 ON t1.i = v1.i WHERE EXISTS (SELECT 300 FROM t1 WHERE v1.i = 1)';
+# Expected result should contain one row (100) results set
+EXECUTE stmt;
+100
+# The same statement executed as a regular statement produces
+# correct results set
+SELECT 100 FROM t1 JOIN v1 ON t1.i = v1.i WHERE EXISTS (SELECT 300 FROM t1 WHERE v1.i = 1);
+100
+100
+# Clean up
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test cases for MDEV-30073
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index e260803a8d3..1594917eea6 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -5225,5 +5225,120 @@ $
 delimiter ;$
 
 --echo #
+--echo # MDEV-30073: Stored Procedure Returns Corrupt Results When Run a Second Time
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3, t4, t5;
+--enable_warnings
+
+CREATE TABLE t1 (
+  product_key int
+);
+
+INSERT INTO t1 VALUES (3569);
+
+CREATE TABLE t2 (
+  id int,
+  product_key int,
+  dealerid int
+);
+INSERT INTO t2 VALUES
+(16494, 3569, 4),
+(16949, 3569, 112);
+
+CREATE TABLE t3 (
+  product_key int
+);
+
+INSERT INTO t3 VALUES (3569);
+
+CREATE TABLE t4 (
+  group_id int,
+  product_key int
+);
+
+INSERT INTO t4 VALUES (117, 3569);
+
+CREATE TABLE t5 (
+  group_id int,
+  dealerid int
+);
+
+INSERT INTO t5 VALUES (117, 4);
+
+PREPARE stmt FROM "
+SELECT * FROM
+(
+ t1
+ JOIN
+ (
+  SELECT t2.dealerid AS dealerid, t3.product_key AS product_key
+  FROM
+  (
+   t2 JOIN t3
+   ON(t3.product_key = t2.product_key)
+  )
+ ) pd
+ ON(t1.product_key = pd.product_key)
+)
+WHERE
+! EXISTS
+(
+ SELECT pd2.id, pd2.product_key
+ FROM
+ (
+  (
+   (
+    SELECT t2.id AS id, t3.product_key AS product_key
+    FROM
+    (
+     t2 JOIN t3
+     ON(t3.product_key = t2.product_key)
+    )
+   ) pd2
+   JOIN t4
+   ON (t4.product_key = pd2.product_key)
+  )
+  JOIN t5 icrgr
+  ON(icrgr.group_id = t4.group_id)
+ )
+ WHERE
+ pd2.product_key = pd.product_key
+ AND
+ icrgr.dealerid = pd.dealerid
+)";
+
+--echo # It is expected a result set containing only one record (3569, 112, 3569)
+EXECUTE stmt;
+--echo # The second execution of this Prepared Statement should produce the same results set
+EXECUTE stmt;
+
+--echo Clean up
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2, t3, t4, t5;
+
+--echo # Unfortunately, the current fix breaks the following test case
+
+CREATE TABLE t1 (i INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+INSERT INTO t1 VALUES (1), (2);
+
+PREPARE stmt FROM 'SELECT 100 FROM t1 JOIN v1 ON t1.i = v1.i WHERE EXISTS (SELECT 300 FROM t1 WHERE v1.i = 1)';
+--echo # Expected result should contain one row (100) results set
+EXECUTE stmt;
+--echo # The same statement executed as a regular statement produces
+--echo # correct results set
+SELECT 100 FROM t1 JOIN v1 ON t1.i = v1.i WHERE EXISTS (SELECT 300 FROM t1 WHERE v1.i = 1);
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo # End of test cases for MDEV-30073
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 37336a83bfb..7fbbecfc897 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5838,9 +5838,7 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list,
     if (!my_strcasecmp(system_charset_info, field_it.name()->str, name))
     {
       // in PS use own arena or data will be freed after prepare
-      if (register_tree_change &&
-          thd->stmt_arena->is_stmt_prepare_or_first_stmt_execute())
-        arena= thd->activate_stmt_arena_if_needed(&backup);
+      arena= thd->activate_stmt_arena_if_needed(&backup);
       /*
         create_item() may, or may not create a new Item, depending on
         the column reference. See create_view_field() for details.
@@ -5861,10 +5859,10 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list,
       if (*ref && !(*ref)->is_autogenerated_name)
         item->set_name(thd, (*ref)->name.str, (*ref)->name.length,
                        system_charset_info);
-      if (register_tree_change)
-        thd->change_item_tree(ref, item);
-      else
-        *ref= item;
+      /*
+        Do permanent name resolution.
+      */
+      *ref= item;
       DBUG_RETURN((Field*) view_ref_found);
     }
   }