diff options
Diffstat (limited to 'mysql-test/main')
| -rw-r--r-- | mysql-test/main/contributors.result | 13 | ||||
| -rw-r--r-- | mysql-test/main/cte_nonrecursive.result | 42 | ||||
| -rw-r--r-- | mysql-test/main/cte_nonrecursive.test | 31 | ||||
| -rw-r--r-- | mysql-test/main/join_cache.result | 54 | ||||
| -rw-r--r-- | mysql-test/main/join_cache.test | 36 | ||||
| -rw-r--r-- | mysql-test/main/status2.result | 10 | ||||
| -rw-r--r-- | mysql-test/main/status2.test | 11 | ||||
| -rw-r--r-- | mysql-test/main/subselect.result | 38 | ||||
| -rw-r--r-- | mysql-test/main/subselect.test | 35 | ||||
| -rw-r--r-- | mysql-test/main/subselect4.result | 27 | ||||
| -rw-r--r-- | mysql-test/main/subselect4.test | 24 | ||||
| -rw-r--r-- | mysql-test/main/subselect_no_exists_to_in.result | 38 | ||||
| -rw-r--r-- | mysql-test/main/subselect_no_mat.result | 38 | ||||
| -rw-r--r-- | mysql-test/main/subselect_no_opts.result | 38 | ||||
| -rw-r--r-- | mysql-test/main/subselect_no_scache.result | 38 | ||||
| -rw-r--r-- | mysql-test/main/subselect_no_semijoin.result | 38 |
16 files changed, 498 insertions, 13 deletions
diff --git a/mysql-test/main/contributors.result b/mysql-test/main/contributors.result index 3e4bf5f0d43..0c7ca03a2c5 100644 --- a/mysql-test/main/contributors.result +++ b/mysql-test/main/contributors.result @@ -1,21 +1,16 @@ SHOW CONTRIBUTORS; Name Location Comment -Booking.com https://www.booking.com Founding member, Platinum Sponsor of the MariaDB Foundation Alibaba Cloud https://www.alibabacloud.com/ Platinum Sponsor of the MariaDB Foundation Tencent Cloud https://cloud.tencent.com Platinum Sponsor of the MariaDB Foundation Microsoft https://microsoft.com/ Platinum Sponsor of the MariaDB Foundation MariaDB Corporation https://mariadb.com Founding member, Platinum Sponsor of the MariaDB Foundation +ServiceNow https://servicenow.com Platinum Sponsor of the MariaDB Foundation Visma https://visma.com Gold Sponsor of the MariaDB Foundation DBS https://dbs.com Gold Sponsor of the MariaDB Foundation IBM https://www.ibm.com Gold Sponsor of the MariaDB Foundation -Tencent Games http://game.qq.com/ Gold Sponsor of the MariaDB Foundation -Nexedi https://www.nexedi.com Silver Sponsor of the MariaDB Foundation -Acronis https://www.acronis.com Silver Sponsor of the MariaDB Foundation -Verkkokauppa.com https://www.verkkokauppa.com Bronze Sponsor of the MariaDB Foundation -Virtuozzo https://virtuozzo.com Bronze Sponsor of the MariaDB Foundation -Tencent Game DBA http://tencentdba.com/about Bronze Sponsor of the MariaDB Foundation -Tencent TDSQL http://tdsql.org Bronze Sponsor of the MariaDB Foundation -Percona https://www.percona.com/ Bronze Sponsor of the MariaDB Foundation +Automattic https://automattic.com Silver Sponsor of the MariaDB Foundation +Percona https://www.percona.com/ Sponsor of the MariaDB Foundation +Galera Cluster https://galeracluster.com Sponsor of the MariaDB Foundation Google USA Sponsoring encryption, parallel replication and GTID Facebook USA Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc Ronald Bradford Brisbane, Australia EFF contribution for UC2006 Auction diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 84537c0e280..6f0210c29da 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1, t r2 where r1.c=r2.c and r2.c=4; show create view v4; View Create View character_set_client collation_connection -v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci select * from v4; c d 4 4 @@ -1126,7 +1126,7 @@ NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union9,12> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with cte_e as (with cte_o as (with cte_i as (/* select#4 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union /* select#5 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)/* select#1 */ select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union /* select#6 */ select `cte_e2`.`a` AS `a` from (with cte_o as (with cte_i as (/* select#11 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#10 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#9 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 union /* select#12 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7) `cte_e2` +Note 1003 with cte_e as (with cte_o as (with cte_i as (/* select#4 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union /* select#5 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)/* select#1 */ select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union /* select#6 */ select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2` drop table t1; # # MDEV-13753: embedded CTE in a VIEW created in prepared statement @@ -1725,6 +1725,44 @@ drop table db1.t1; drop database db1; create database test; use test; +# +# MDEV-24597: CTE with union used multiple times in query +# +with cte(a) as +(select 1 as d union select 2 as d) +select a from cte as r1 +union +select a from cte as r2; +a +1 +2 +create table t1 (a int, b int) engine=myisam; +insert into t1 values +(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44), +(5,50), (4,40), (3,33), (4,42), (4,43), (5,51); +with cte(c) as +(select a from t1 where b < 30 union select a from t1 where b > 40) +select * from cte as r1, cte as r2 where r1.c = r2.c; +c c +1 1 +2 2 +7 7 +4 4 +5 5 +with cte(a,c) as +( +select a, count(*) from t1 group by a having count(*) = 1 +union +select a, count(*) from t1 group by a having count(*) = 3 +) +select a, c from cte as r1 where a < 3 +union +select a, c from cte as r2 where a > 4; +a c +1 1 +2 1 +7 3 +drop table t1; # End of 10.2 tests # # MDEV-21673: several references to CTE that uses diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index aecec0f5496..7f78b7a4f48 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1230,6 +1230,37 @@ drop database db1; create database test; use test; +--echo # +--echo # MDEV-24597: CTE with union used multiple times in query +--echo # + +with cte(a) as +(select 1 as d union select 2 as d) +select a from cte as r1 +union +select a from cte as r2; + +create table t1 (a int, b int) engine=myisam; +insert into t1 values +(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44), +(5,50), (4,40), (3,33), (4,42), (4,43), (5,51); + +with cte(c) as +(select a from t1 where b < 30 union select a from t1 where b > 40) +select * from cte as r1, cte as r2 where r1.c = r2.c; + +with cte(a,c) as +( + select a, count(*) from t1 group by a having count(*) = 1 + union + select a, count(*) from t1 group by a having count(*) = 3 +) +select a, c from cte as r1 where a < 3 +union +select a, c from cte as r2 where a > 4; + +drop table t1; + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 3d1d91df997..83b321d8ad7 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6057,6 +6057,59 @@ f2 drop table t1, t2; set join_buffer_size=@save_join_buffer_size; # +# MDEV-21104: BNLH used for multi-join query with embedded outer join +# and possible 'not exists' optimization +# +set join_cache_level=4; +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2),(2,4); +CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1),(2); +ANALYZE TABLE t1,t2,t3,t4; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +test.t4 analyze status OK +SELECT * FROM t2 LEFT JOIN t3 ON c = d; +b c d +1 2 2 +2 4 NULL +SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; +b c d e +1 2 2 1 +2 4 NULL 1 +1 2 2 2 +2 4 NULL 2 +EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using index; Using join buffer (incremental, BNLH join) +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +a b c d e +1 1 2 2 1 +2 1 2 2 1 +1 2 4 NULL 2 +2 2 4 NULL 2 +EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e +WHERE e IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using where; Using index; Not exists; Using join buffer (incremental, BNLH join) +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e +WHERE e IS NULL; +a b c d e +DROP TABLE t1,t2,t3,t4; +set join_cache_level=@save_join_cache_level; +# # MDEV-21243: Join buffer: condition is checked in wrong place for range access # create table t1(a int primary key); @@ -6128,4 +6181,5 @@ EXPLAIN } } drop table t1,t2,t3; +# End of 10.3 tests set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 91339c2cb21..1797dd02e71 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4016,6 +4016,41 @@ set join_buffer_size=@save_join_buffer_size; --echo # +--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join +--echo # and possible 'not exists' optimization +--echo # + +set join_cache_level=4; + +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2),(2,4); +CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1),(2); +ANALYZE TABLE t1,t2,t3,t4; + +SELECT * FROM t2 LEFT JOIN t3 ON c = d; +SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; + +let $q1= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +eval EXPLAIN $q1; +eval $q1; + +let $q2= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e + WHERE e IS NULL; +eval EXPLAIN $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +set join_cache_level=@save_join_cache_level; + +--echo # --echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access --echo # create table t1(a int primary key); @@ -4054,5 +4089,6 @@ where drop table t1,t2,t3; +--echo # End of 10.3 tests # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/status2.result b/mysql-test/main/status2.result index fa0fc4e1061..60309e14fe3 100644 --- a/mysql-test/main/status2.result +++ b/mysql-test/main/status2.result @@ -74,4 +74,12 @@ DROP TRIGGER trigg1; DROP FUNCTION testQuestion; DROP EVENT ev1; DROP TABLE t1,t2; -End of 6.0 tests +# +# End of 5.5 tests +# +select variable_value < 1024*1024*1024 from information_schema.global_status where variable_name='memory_used'; +variable_value < 1024*1024*1024 +1 +# +# End of 10.2 tests +# diff --git a/mysql-test/main/status2.test b/mysql-test/main/status2.test index fa3b718efaa..ea674c2ed7c 100644 --- a/mysql-test/main/status2.test +++ b/mysql-test/main/status2.test @@ -64,5 +64,14 @@ DROP TRIGGER trigg1; DROP FUNCTION testQuestion; DROP EVENT ev1; DROP TABLE t1,t2; ---echo End of 6.0 tests + +--echo # +--echo # End of 5.5 tests +--echo # + +select variable_value < 1024*1024*1024 from information_schema.global_status where variable_name='memory_used'; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 0dc8692cfae..9809248e83b 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7330,4 +7330,42 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index c425a98324e..3dcff34e773 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -6190,4 +6190,39 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) DROP TABLE t; +--echo # +--echo # MDEV-25002: Outer reference in ON clause of subselect +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=myisam; +insert into t1 values (1,1), (2,2); + +create table t2 ( + pk int primary key, + b int +) engine=myisam; +insert into t2 values (1,1), (2,3); + +create table t3 (a int); +insert into t3 values (1),(2); + +select a, + (select count(*) from t1, t2 + where t2.pk=t3.a and t1.pk=1) as sq +from t3; +select a, + (select count(*) from t1 join t2 on t2.pk=t3.a + where t1.pk=1) as sq +from t3; + +select a from t3 + where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +select a from t3 + where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); + +drop table t1,t2,t3; + --echo # End of 10.2 tests diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index b89ed3d5ddd..156e78e7778 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2694,6 +2694,33 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); ERROR 42S22: Unknown column 's.id' in 'group statement' DROP TABLE t1; +# +# MDEV-24519: Server crashes in Charset::set_charset upon SELECT +# +CREATE TABLE t1 (a VARBINARY(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (b VARBINARY(8)); +EXPLAIN +SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2); +a +DROP TABLE t1,t2; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b VARBINARY(8)); +EXPLAIN +SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); +a +DROP TABLE t1,t2; # End of 10.2 tests # # MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index cdea43a1aa1..a5fcc507905 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2212,6 +2212,30 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); DROP TABLE t1; +--echo # +--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT +--echo # + +CREATE TABLE t1 (a VARBINARY(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (b VARBINARY(8)); + +EXPLAIN +SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2); +SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2); + +DROP TABLE t1,t2; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b VARBINARY(8)); + +EXPLAIN +SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); +SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); + +DROP TABLE t1,t2; + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index b9c302abc76..a391b79037c 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7330,6 +7330,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index a97b3555c98..12239aae5e8 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7323,6 +7323,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index a80f086555f..8c18ec4c063 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7321,5 +7321,43 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index d2a8f8e1713..3590cd20cc6 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7336,6 +7336,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index f8e44b4faa8..f2d7077959b 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7321,6 +7321,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON |