diff options
Diffstat (limited to 'mysql-test/main/subselect_exists2in.result')
-rw-r--r-- | mysql-test/main/subselect_exists2in.result | 127 |
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; |