Add testcases for MDEV-20557, MDEV-21649

2 files changed, 121 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result
index 5a66aa2cd02..23b252c92d6 100644
--- a/mysql-test/r/subselect_exists2in.result
+++ b/mysql-test/r/subselect_exists2in.result
@@ -1042,4 +1042,61 @@ US	AAAA1111
 US	NULL
 US	NULL
 drop table t1,t2;
+# 
+# MDEV-20557: SQL query with duplicate table aliases consistently crashes server
+# (Just a testcase)
+#
+create table t1 (id int, id2 int);
+create table t2 (id int, id2 int, a int);
+create table t3 (id int);
+create table t4 (id int);
+select (select 1 from t1 where (exists 
+(select 1 from t2  
+where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
+from t3;
+ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY'
+drop table t1,t2,t3,t4;
+# 
+# MDEV-21649: Crash when using nested EXISTS
+# (Just a testcase)
+# 
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id));
+CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id));
+SELECT
+W0.`id`
+FROM
+`t1` W0
+WHERE (
+EXISTS(
+SELECT
+V0.`id`
+        FROM
+`t2` V0
+WHERE (
+EXISTS(
+SELECT
+U0.`id`
+                FROM
+`t2` U0
+INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`)
+WHERE (
+U0.`ip_id` = V0.`ip_id`
+                        AND U4.`storage_method_id` = (
+SELECT
+U5.`storage_method_id`
+                            FROM
+`t3` U5
+WHERE
+U5.`storage_target_id` = V0.`id`
+                            LIMIT
+1
+)
+)
+)
+)
+)
+);
+id
+drop table t1,t2,t3;
 set optimizer_switch=default;
diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test
index 877475f97ed..7ffb4bf0633 100644
--- a/mysql-test/t/subselect_exists2in.test
+++ b/mysql-test/t/subselect_exists2in.test
@@ -876,5 +876,69 @@ execute s;
 
 drop table t1,t2;
 
+--echo # 
+--echo # MDEV-20557: SQL query with duplicate table aliases consistently crashes server
+--echo # (Just a testcase)
+--echo #
+
+create table t1 (id int, id2 int);
+create table t2 (id int, id2 int, a int);
+create table t3 (id int);
+create table t4 (id int);
+
+--error ER_NOT_SUPPORTED_YET
+select (select 1 from t1 where (exists 
+	(select 1 from t2  
+	where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
+from t3;
+ 
+drop table t1,t2,t3,t4;
+
+
+--echo # 
+--echo # MDEV-21649: Crash when using nested EXISTS
+--echo # (Just a testcase)
+--echo # 
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id));
+CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id));
+ 
+SELECT
+    W0.`id`
+FROM
+    `t1` W0
+WHERE (
+    EXISTS(
+        SELECT
+            V0.`id`
+        FROM
+            `t2` V0
+        WHERE (
+            EXISTS(
+                SELECT
+                    U0.`id`
+                FROM
+                    `t2` U0
+                    INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`)
+                WHERE (
+                        U0.`ip_id` = V0.`ip_id`
+                        AND U4.`storage_method_id` = (
+                            SELECT
+                                U5.`storage_method_id`
+                            FROM
+                                `t3` U5
+                            WHERE
+                                U5.`storage_target_id` = V0.`id`
+                            LIMIT
+                                1
+                        )
+                )
+            )
+        )
+    )
+);
+
+drop table t1,t2,t3;
+
 #restore defaults
 set optimizer_switch=default;