Fixed the bug MDEV-14755 Crash when executing prepared statement

for a query that uses CTE

The first reference to a CTE in the processed query uses the unit
built by the parser for the CTE specification. This unit is
considered as the specification of the derived table created for
the first reference of the CTE. This requires some transformation of
the original query tree: the unit of the specification must be moved
to a new position as a slave of the select where the first reference
to the CTE occurs. The transformation is performed by the function
st_select_lex_node::move_as_slave(). There was an obvious bug in this
function. As a result of this bug in many cases the moved unit turned
out to be lost in the query tree. This could cause different problems.
In particular the prepared statements for queries that used CTEs could
miss cleanup for some selects that was performed at the end of the
preparation/execution of the PSs. If such cleanup is not done for a PS
the next execution of the PS causes an assertion abort or a crash.

2 files changed, 181 insertions, 0 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index ebe1aae1e8f..5164c74a0bc 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -1147,3 +1147,121 @@ SELECT * FROM cte_test;
 a
 1
 DROP VIEW cte_test;
+#
+# mdev-14755 : PS for query using CTE in select with subquery
+#
+create table t1 (a int);
+insert into t1 values
+(7), (2), (8), (1), (3), (2), (7), (5), (4), (7), (9), (8);
+with cte as
+(select a from t1 where a between 4 and 7 group by a)
+(select a from cte where exists( select a from t1 where cte.a=t1.a ))
+union
+(select a from t1 where a < 2);
+a
+7
+5
+4
+1
+prepare stmt from "with cte as
+(select a from t1 where a between 4 and 7 group by a)
+(select a from cte where exists( select a from t1 where cte.a=t1.a ))
+union
+(select a from t1 where a < 2)";
+execute stmt;
+a
+7
+5
+4
+1
+execute stmt;
+a
+7
+5
+4
+1
+deallocate prepare stmt;
+with cte as
+(select a from t1 where a between 4 and 7 group by a)
+(select a from t1 where a < 2)
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ));
+a
+1
+7
+5
+4
+prepare stmt from "with cte as
+(select a from t1 where a between 4 and 7 group by a)
+(select a from t1 where a < 2)
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ))";
+execute stmt;
+a
+1
+7
+5
+4
+execute stmt;
+a
+1
+7
+5
+4
+deallocate prepare stmt;
+with cte as
+(select a from t1 where a between 4 and 7)
+(select a from t1 where a < 2)
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ));
+a
+1
+7
+5
+4
+prepare stmt from "with cte as
+(select a from t1 where a between 4 and 7)
+(select a from t1 where a < 2)
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ))";
+execute stmt;
+a
+1
+7
+5
+4
+execute stmt;
+a
+1
+7
+5
+4
+deallocate prepare stmt;
+with cte as
+(select a from t1 where a between 4 and 7)
+(select a from cte
+where exists( select a from t1 where t1.a < 2 and cte.a=t1.a ))
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ));
+a
+7
+5
+4
+prepare stmt from "with cte as
+(select a from t1 where a between 4 and 7)
+(select a from cte
+where exists( select a from t1 where t1.a < 2 and cte.a=t1.a ))
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ))";
+execute stmt;
+a
+7
+5
+4
+execute stmt;
+a
+7
+5
+4
+deallocate prepare stmt;
+drop table t1;
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 742e8f6e4d7..1f21dbcd36d 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -790,3 +790,66 @@ SHOW CREATE VIEW cte_test;
 SELECT * FROM cte_test;
 
 DROP VIEW cte_test;
+
+--echo #
+--echo # mdev-14755 : PS for query using CTE in select with subquery
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+  (7), (2), (8), (1), (3), (2), (7), (5), (4), (7), (9), (8);
+
+let $q1=
+with cte as
+(select a from t1 where a between 4 and 7 group by a)
+(select a from cte where exists( select a from t1 where cte.a=t1.a ))
+union
+(select a from t1 where a < 2);
+
+eval $q1;
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q2=
+with cte as
+(select a from t1 where a between 4 and 7 group by a)
+(select a from t1 where a < 2)
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ));
+
+eval $q2;
+eval prepare stmt from "$q2";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q3=
+with cte as
+(select a from t1 where a between 4 and 7)
+(select a from t1 where a < 2)
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ));
+
+eval $q3;
+eval prepare stmt from "$q3";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q4=
+with cte as
+(select a from t1 where a between 4 and 7)
+(select a from cte
+   where exists( select a from t1 where t1.a < 2 and cte.a=t1.a ))
+union
+(select a from cte where exists( select a from t1 where cte.a=t1.a ));
+
+eval $q4;
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table t1;