1 files changed, 127 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result
index 431d60b8c29..a06988a7f9c 100644
--- a/mysql-test/main/subselect_exists2in.result
+++ b/mysql-test/main/subselect_exists2in.result
@@ -972,4 +972,131 @@ id
 DROP PROCEDURE p1;
 DROP TABLE t1;
 # End of 10.0 tests
+#
+# MDEV-23221: A subquery causes crash
+#
+create table t1 (
+location_code varchar(10),
+country_id varchar(10)
+);
+insert into t1 values ('HKG', 'HK');
+insert into t1 values ('NYC', 'US');
+insert into t1 values ('LAX', 'US');
+create table t2 (
+container_id varchar(10),
+cntr_activity_type varchar(10),
+cntr_dest varchar(10)
+);
+insert into t2 values ('AAAA1111', 'VSL', 'NYC');
+insert into t2 values ('AAAA1111', 'CUV', 'NYC');
+insert into t2 values ('BBBB2222', 'VSL', 'LAX');
+insert into t2 values ('BBBB2222', 'XYZ', 'LAX');
+# Must not crash or return an error:
+select
+(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry,
+(select
+max(container_id)
+from t2 as cl2
+where
+cl2.container_id = cl1.container_id and
+cl2.cntr_activity_type = 'CUV' and
+exists (select location_code
+from t1
+where
+location_code = cl2.cntr_dest and
+country_id = dest_cntry)
+) as CUV
+from
+t2 cl1;
+dest_cntry	CUV
+US	AAAA1111
+US	AAAA1111
+US	NULL
+US	NULL
+prepare s from "select
+(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry,
+(select
+max(container_id)
+from t2 as cl2
+where
+cl2.container_id = cl1.container_id and
+cl2.cntr_activity_type = 'CUV' and
+exists (select location_code
+from t1
+where
+location_code = cl2.cntr_dest and
+country_id = dest_cntry)
+) as CUV
+from
+t2 cl1";
+execute s;
+dest_cntry	CUV
+US	AAAA1111
+US	AAAA1111
+US	NULL
+US	NULL
+execute s;
+dest_cntry	CUV
+US	AAAA1111
+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;