diff options
Diffstat (limited to 'mysql-test')
40 files changed, 386 insertions, 302 deletions
diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc index d22310c9813..9ce620a72de 100644 --- a/mysql-test/include/explain_non_select.inc +++ b/mysql-test/include/explain_non_select.inc @@ -73,11 +73,11 @@ INSERT INTO t2 VALUES (1), (2), (3); --source include/explain_utils.inc DROP TABLE t1, t2; ---echo #7 +--echo #7a CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); -INSERT INTO t2 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3), (1000); --let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) --let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) --source include/explain_utils.inc @@ -197,7 +197,7 @@ DROP TABLE t1, t2, t3; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (a INT); -INSERT INTO t2 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3), (1000); --let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) --let $select = SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) --source include/explain_utils.inc diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index 43f57375a4e..6cb029c2050 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -509,7 +509,7 @@ DROP TABLE t1; create table t1 (a int); insert into t1 values (1),(2); create table t2(a int, b int); -insert into t2 values (1,1), (2, 1000); +insert into t2 values (1,1), (2, 1000),(5000,5000); create table t3 (a int, b int, filler char(100), key(a), key(b)); insert into t3 select 1000, 1000,'filler' from seq_1_to_1000; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 164a1ee0e7b..f24ae2fcbac 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17348,7 +17348,7 @@ create table t1 (id int, a int, index (a), index (id, a)) engine=myisam; insert into t1 values (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); create table t2 (a int) engine=myisam; -insert into t2 values (1),(2),(3); +insert into t2 values (1),(2),(3),(1000),(2000),(3000); create table t3 (id int) engine=myisam; insert into t3 values (1),(2); analyze table t1,t2,t3; @@ -17372,7 +17372,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref a a 5 test.t3.id 1 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 -3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 2 DERIVED cp2 index NULL a 5 NULL 7 Using index explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); @@ -17415,7 +17415,7 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", - "rows": 3, + "rows": 6, "filtered": 100 } } @@ -17462,7 +17462,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref a a 5 test.t3.id 1 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 -3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using where; Using index explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); @@ -17505,7 +17505,7 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", - "rows": 3, + "rows": 6, "filtered": 100 } } diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index a09fd1b59e3..83e784c3450 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3601,7 +3601,7 @@ insert into t1 values (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); create table t2 (a int) engine=myisam; -insert into t2 values (1),(2),(3); +insert into t2 values (1),(2),(3),(1000),(2000),(3000); create table t3 (id int) engine=myisam; insert into t3 values (1),(2); diff --git a/mysql-test/main/explain_json.result b/mysql-test/main/explain_json.result index bb9f0c4d3a2..f3ee13ee17f 100644 --- a/mysql-test/main/explain_json.result +++ b/mysql-test/main/explain_json.result @@ -902,17 +902,19 @@ EXPLAIN "access_type": "ALL", "rows": 2, "filtered": 100, - "attached_condition": "!<in_optimizer>(t1.a,t1.a in (subquery#2))" + "attached_condition": "!<in_optimizer>(t1.a,<exists>(subquery#2))" }, "subqueries": [ { "query_block": { "select_id": 2, + "having_condition": "trigcond(t2.b is null)", "table": { "table_name": "t2", "access_type": "ALL", "rows": 2, - "filtered": 100 + "filtered": 100, + "attached_condition": "trigcond(<cache>(t1.a) = t2.b or t2.b is null)" } } } diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 6cd82d2a96a..2e489077a78 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -554,7 +554,7 @@ DROP TABLE t1; create table t1 (a int); insert into t1 values (1),(2); create table t2(a int, b int); -insert into t2 values (1,1), (2, 1000); +insert into t2 values (1,1), (2, 1000),(5000,5000); create table t3 (a int, b int, filler char(100), key(a), key(b)); insert into t3 select 1000, 1000,'filler' from seq_1_to_1000; insert into t3 values (1,1,'data'); @@ -566,7 +566,7 @@ where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) select * from t1 where exists (select 1 from t2, t3 diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 2b1ac415265..98bb644fd5f 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -327,11 +327,11 @@ Handler_read_rnd_next 7 Handler_update 2 DROP TABLE t1, t2; -#7 +#7a CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); -INSERT INTO t2 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3), (1000); # # query: UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) # select: SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) @@ -342,16 +342,16 @@ EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -361,8 +361,8 @@ EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WH id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where `test`.`t2`.`b` < 3 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -373,11 +373,11 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 12 +Handler_read_rnd_next 14 # Status of testing query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 16 +Handler_read_rnd_next 19 Handler_update 2 DROP TABLE t1, t2; @@ -978,7 +978,7 @@ DROP TABLE t1, t2, t3; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (a INT); -INSERT INTO t2 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3), (1000); # # query: UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) # select: SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) @@ -988,13 +988,13 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -1004,7 +1004,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -1015,7 +1015,7 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 8 +Handler_read_rnd_next 9 # Status of testing query execution: Variable_name Value Handler_read_key 4 diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 93020416551..f3bc5e40ea9 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -3248,7 +3248,7 @@ explain extended select * from t1 where a in (select pk from t10) { { "strategy": "SJ-Materialization", "records": 3, - "cost": 6.914282227 + "cost": 8.664282227 }, { "strategy": "DuplicateWeedout", @@ -3314,7 +3314,7 @@ explain extended select * from t1 where a in (select pk from t10) { "best_join_order": ["t1", "<subquery2>"], "best_access_method": { "rows": 3, - "cost": 6.914282227 + "cost": 8.664282227 } }, { @@ -5038,7 +5038,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "strategy": "SJ-Materialization", "records": 3, - "cost": 9.10769043 + "cost": 10.75769043 }, { "strategy": "DuplicateWeedout", @@ -5171,7 +5171,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_join_order": ["t1", "<subquery2>"], "best_access_method": { "rows": 3, - "cost": 9.10769043 + "cost": 10.75769043 } }, { @@ -7565,7 +7565,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "strategy": "SJ-Materialization", "records": 3, - "cost": 16.01281738 + "cost": 19.46281738 }, { "strategy": "DuplicateWeedout", @@ -7607,7 +7607,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 27, - "cost_for_plan": 28.72050781, + "cost_for_plan": 32.17050781, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7642,7 +7642,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 81, - "cost_for_plan": 62.87307129, + "cost_for_plan": 66.32307129, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7678,25 +7678,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 729, - "cost_for_plan": 394.1807617, + "cost_for_plan": 397.6307617, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, - "cost": 339.9126465 + "cost": 343.3626465 }, { "strategy": "SJ-Materialization", "records": 27, - "cost": 44.18076172 + "cost": 57.08076172 }, { "strategy": "DuplicateWeedout", "records": 27, - "dups_cost": 394.1807617, + "dups_cost": 397.6307617, "write_cost": 5.05, "full_lookup_cost": 109.35, - "total_cost": 508.5807617 + "total_cost": 512.0307617 }, { "chosen_strategy": "SJ-Materialization" @@ -7737,7 +7737,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 243, - "cost_for_plan": 129.1781982, + "cost_for_plan": 132.6281982, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -7770,7 +7770,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 9, - "cost_for_plan": 20.91538086, + "cost_for_plan": 24.36538086, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7805,7 +7805,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 81, - "cost_for_plan": 56.57307129, + "cost_for_plan": 60.02307129, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -7841,7 +7841,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 81, - "cost_for_plan": 56.57307129, + "cost_for_plan": 60.02307129, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -7874,7 +7874,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, "rows_for_plan": 27, - "cost_for_plan": 28.72050781, + "cost_for_plan": 32.17050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -8387,7 +8387,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "best_access_method": { "rows": 27, - "cost": 44.18076172 + "cost": 57.08076172 } }, { @@ -10212,8 +10212,8 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "table": "t2", "key": "idx_a", "record_count": 4, - "cost": 3.651150919, - "unsplit_cost": 46.6794762 + "cost": 4.157170953, + "unsplit_cost": 60.1794762 } } ] @@ -10226,8 +10226,8 @@ json_detailed(json_extract(trace, '$**.lateral_derived')) [ { - "startup_cost": 14.60460368, - "splitting_cost": 3.651150919, + "startup_cost": 16.62868381, + "splitting_cost": 4.157170953, "records": 1 } ] diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index aa1ede9cbd3..33eedf3f103 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -4356,7 +4356,7 @@ CREATE TABLE t1 (a INT, b int, primary key(a)); CREATE TABLE t2 (a INT, b INT); INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6), (2354,7),(321421,3),(535,2),(4535,3); -INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3); +INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000); # Join order should have the SJM scan table as the first table for both # the queries with GROUP BY and ORDER BY clause. EXPLAIN SELECT t1.a @@ -4364,9 +4364,9 @@ FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) ORDER BY t1.a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using filesort 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where EXPLAIN FORMAT=JSON SELECT t1.a FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) @@ -4382,7 +4382,7 @@ EXPLAIN "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], - "rows": 3, + "rows": 5, "filtered": 100, "materialized": { "unique": 1, @@ -4391,7 +4391,7 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", - "rows": 3, + "rows": 5, "filtered": 100, "attached_condition": "t2.b = 3 and t2.a is not null" } @@ -4427,9 +4427,9 @@ FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using filesort 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where EXPLAIN FORMAT=JSON SELECT t1.a, group_concat(t1.b) FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) @@ -4445,7 +4445,7 @@ EXPLAIN "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], - "rows": 3, + "rows": 5, "filtered": 100, "materialized": { "unique": 1, @@ -4454,7 +4454,7 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", - "rows": 3, + "rows": 5, "filtered": 100, "attached_condition": "t2.b = 3 and t2.a is not null" } diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 815953f2c02..d40813cf223 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2682,7 +2682,7 @@ CREATE TABLE t2 (a INT, b INT); INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6), (2354,7),(321421,3),(535,2),(4535,3); -INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3); +INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000); --echo # Join order should have the SJM scan table as the first table for both --echo # the queries with GROUP BY and ORDER BY clause. diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index ed0ded47c1c..6b1ba12cea1 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -895,6 +895,9 @@ select (select a+1) from t1; NULL 4.5 drop table t1; +# +# Null with keys +# CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -4438,10 +4441,13 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +# +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 2 @@ -4451,7 +4457,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where 1 @@ -4460,8 +4466,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 3 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -5700,7 +5706,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3; CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); a @@ -5711,7 +5717,7 @@ WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a @@ -5720,9 +5726,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 2 Using index; FirstMatch(t1) DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index 2093b4ff2d7..e84b928f891 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -516,9 +516,9 @@ explain extended select (select a+1) from t1; select (select a+1) from t1; drop table t1; -# -# Null with keys -# +--echo # +--echo # Null with keys +--echo # CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); @@ -3321,15 +3321,15 @@ GROUP BY a; DROP TABLE t1; -# -# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 -# +--echo # +--echo # Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +--echo # CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); EXPLAIN EXTENDED @@ -4794,7 +4794,7 @@ CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); diff --git a/mysql-test/main/subselect3.inc b/mysql-test/main/subselect3.inc index 8ed1c9ee210..c2491b0360e 100644 --- a/mysql-test/main/subselect3.inc +++ b/mysql-test/main/subselect3.inc @@ -1107,6 +1107,7 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 as select a as a, a as b, a as c from t0 where a < 3; create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; +select count(*) from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); @@ -1152,7 +1153,7 @@ drop table t0, t1; create table t1 ( idIndividual int primary key ); -insert into t1 values (1),(2); +insert into t1 values (1),(2),(1000); create table t2 ( idContact int primary key, @@ -1167,7 +1168,7 @@ create table t3 ( postalStripped varchar(100) ); -insert into t3 values (1,1, 'foo'), (2,2,'bar'); +insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2'); --echo The following must be converted to a semi-join: set @save_optimizer_switch=@@optimizer_switch; @@ -1178,6 +1179,12 @@ WHERE a.idIndividual IN INNER JOIN t2 c ON c.idContact=cona.idContact WHERE cona.postalStripped='T2H3B2' ); +SELECT a.idIndividual FROM t1 a +WHERE a.idIndividual IN + ( SELECT c.idObj FROM t3 cona + INNER JOIN t2 c ON c.idContact=cona.idContact + WHERE cona.postalStripped='T2H3B2' + ); set @@optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index fe6ef646886..36063f3e594 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -1180,9 +1180,8 @@ create table t3 ( a int , filler char(100), key(a)); insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ref a a 5 test.t2.a 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t3 ref a a 5 test.t2.a 1 End temporary select * from t3 where a in (select a from t2); a filler 1 filler @@ -1336,6 +1335,9 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 as select a as a, a as b, a as c from t0 where a < 3; create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; +select count(*) from t2; +count(*) +6 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 @@ -1391,7 +1393,7 @@ drop table t0, t1; create table t1 ( idIndividual int primary key ); -insert into t1 values (1),(2); +insert into t1 values (1),(2),(1000); create table t2 ( idContact int primary key, contactType int, @@ -1403,7 +1405,7 @@ idAddress int primary key, idContact int, postalStripped varchar(100) ); -insert into t3 values (1,1, 'foo'), (2,2,'bar'); +insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2'); The following must be converted to a semi-join: set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='materialization=off'; @@ -1419,6 +1421,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.c.idObj 1 100.00 Using index; End temporary Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where `test`.`cona`.`postalStripped` = 'T2H3B2' and `test`.`a`.`idIndividual` = `test`.`c`.`idObj` and `test`.`c`.`idContact` = `test`.`cona`.`idContact` +SELECT a.idIndividual FROM t1 a +WHERE a.idIndividual IN +( SELECT c.idObj FROM t3 cona +INNER JOIN t2 c ON c.idContact=cona.idContact +WHERE cona.postalStripped='T2H3B2' + ); +idIndividual +2 set @@optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 25d76e6ee8c..41f280b5bd0 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -1183,9 +1183,8 @@ create table t3 ( a int , filler char(100), key(a)); insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t3 ref a a 5 test.t2.a 1 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan select * from t3 where a in (select a from t2); a filler 1 filler @@ -1339,6 +1338,9 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 as select a as a, a as b, a as c from t0 where a < 3; create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; +select count(*) from t2; +count(*) +6 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where @@ -1394,7 +1396,7 @@ drop table t0, t1; create table t1 ( idIndividual int primary key ); -insert into t1 values (1),(2); +insert into t1 values (1),(2),(1000); create table t2 ( idContact int primary key, contactType int, @@ -1406,7 +1408,7 @@ idAddress int primary key, idContact int, postalStripped varchar(100) ); -insert into t3 values (1,1, 'foo'), (2,2,'bar'); +insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2'); The following must be converted to a semi-join: set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='materialization=off'; @@ -1422,6 +1424,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.c.idObj 1 100.00 Using index; End temporary Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where `test`.`cona`.`postalStripped` = 'T2H3B2' and `test`.`a`.`idIndividual` = `test`.`c`.`idObj` and `test`.`c`.`idContact` = `test`.`cona`.`idContact` +SELECT a.idIndividual FROM t1 a +WHERE a.idIndividual IN +( SELECT c.idObj FROM t3 cona +INNER JOIN t2 c ON c.idContact=cona.idContact +WHERE cona.postalStripped='T2H3B2' + ); +idIndividual +2 set @@optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 3535d35a6f3..7c12b2f1aa5 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -1247,7 +1247,7 @@ drop table t1, t2; # CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL); -INSERT INTO t2 VALUES ('k'), ('d'); +INSERT INTO t2 VALUES ('k'), ('d'),('x'); CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL); INSERT INTO t3 VALUES ('a'), ('b'), ('c'); CREATE TABLE t4 (c1 varchar(1) primary key); @@ -1260,16 +1260,16 @@ EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 SET optimizer_switch='materialization=on'; @@ -1277,10 +1277,10 @@ EXPLAIN SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 c1 EXPLAIN @@ -1288,8 +1288,8 @@ SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t4 index NULL PRIMARY 3 NULL 2 Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 SET optimizer_switch=@save_optimizer_switch; @@ -2753,12 +2753,21 @@ INSERT INTO t4 VALUES ('w'),('w'),('x'),('x'), (NULL),(NULL); SET @save_join_cache_level=@@join_cache_level; SET join_cache_level=0; +explain select 1 +from t2 join t1 on +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 index NULL v1 9 NULL 5 Using index +2 SUBQUERY t3 ALL NULL NULL NULL NULL 4 +2 SUBQUERY t1 ref_or_null v1 v1 4 const 3 Using where; Using index +2 SUBQUERY t4 ALL NULL NULL NULL NULL 50 Using where select 1 from t2 join t1 on -('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 500; +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10; 1 Warnings: -Warning 1931 Query execution was interrupted. The query examined at least 3020 rows, which exceeds LIMIT ROWS EXAMINED (500). The query result may be incomplete +Warning 1931 Query execution was interrupted. The query examined at least 14 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete SET join_cache_level= @save_join_cache_level; DROP TABLE t1,t2,t3,t4; # @@ -2788,9 +2797,8 @@ set names 'utf8'; EXPLAIN SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abc',1), ('def', 2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <derived3> ref key1 key1 4 test.t2.b 2 Using where; FirstMatch(t2) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index a32d6ec69d5..8c075541114 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -943,7 +943,7 @@ drop table t1, t2; CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL); -INSERT INTO t2 VALUES ('k'), ('d'); +INSERT INTO t2 VALUES ('k'), ('d'),('x'); CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL); INSERT INTO t3 VALUES ('a'), ('b'), ('c'); CREATE TABLE t4 (c1 varchar(1) primary key); @@ -2281,9 +2281,13 @@ INSERT INTO t4 VALUES SET @save_join_cache_level=@@join_cache_level; SET join_cache_level=0; +explain select 1 +from t2 join t1 on +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10; + select 1 from t2 join t1 on -('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 500; +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10; SET join_cache_level= @save_join_cache_level; DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result index c01c28fc258..7b3966c3f93 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -297,23 +297,27 @@ d a b e412 e412 h412 d b a i421 i421 l421 d b b m422 m422 o422 drop table t1, t2, t3; +# +# LP BUG#901835 - incorrect semi-join conversion after exists2in +# CREATE TABLE t1 ( a INT ); -INSERT INTO t1 VALUES (7),(0); +INSERT INTO t1 VALUES (7),(0),(100); CREATE TABLE t2 ( b INT ); -INSERT INTO t2 VALUES (0),(8); +INSERT INTO t2 VALUES (0),(8),(1000),(2000),(3000),(4000),(5000); SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ) OR a > 0; a 7 0 +100 explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ) OR a > 0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`)))) or `test`.`t1`.`a` > 0 @@ -713,14 +717,14 @@ set optimizer_switch='exists_to_in=on'; # correct calculation of reserved items (postreview-fix) # create table t1 (col1 int, col2 int, col3 int); -insert into t1 values (1,2,3),(2,3,4),(4,5,6); +insert into t1 values (1,2,3),(2,3,4),(4,5,6),(7,8,9); create table t2 as select * from t1; explain extended select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 100.00 Warnings: Note 1276 Field or reference 'test.t1.col1' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.col2' of SELECT #2 was resolved in SELECT #1 @@ -730,6 +734,7 @@ col1 col2 col3 1 2 3 2 3 4 4 5 6 +7 8 9 drop table t1,t2; # # MDEV-3879: Exists2In: Wrong result (extra row) and unexpected diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test index e70d643138b..bf884cc1fa9 100644 --- a/mysql-test/main/subselect_exists2in.test +++ b/mysql-test/main/subselect_exists2in.test @@ -238,13 +238,13 @@ group by a1,a2,b; drop table t1, t2, t3; -# -# LP BUG#901835 - incorrect semi-join conversion after exists2in -# +--echo # +--echo # LP BUG#901835 - incorrect semi-join conversion after exists2in +--echo # CREATE TABLE t1 ( a INT ); -INSERT INTO t1 VALUES (7),(0); +INSERT INTO t1 VALUES (7),(0),(100); CREATE TABLE t2 ( b INT ); -INSERT INTO t2 VALUES (0),(8); +INSERT INTO t2 VALUES (0),(8),(1000),(2000),(3000),(4000),(5000); SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a ) @@ -562,7 +562,7 @@ set optimizer_switch='exists_to_in=on'; --echo # correct calculation of reserved items (postreview-fix) --echo # create table t1 (col1 int, col2 int, col3 int); -insert into t1 values (1,2,3),(2,3,4),(4,5,6); +insert into t1 values (1,2,3),(2,3,4),(4,5,6),(7,8,9); create table t2 as select * from t1; explain extended select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2); diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 29dcc706444..c9d68666ac7 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1510,8 +1510,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) 2 @@ -2193,9 +2192,8 @@ mysqltest1 EXPLAIN EXTENDED SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort -1 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index -2 MATERIALIZED schemata ALL NULL NULL NULL NULL NULL NULL +1 PRIMARY schemata ALL NULL NULL NULL NULL NULL NULL Start temporary; Using temporary; Using filesort +1 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index; End temporary Warnings: Note 1003 select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`schemata`) where `test`.`t1`.`db` = `information_schema`.`schemata`.`SCHEMA_NAME` order by `test`.`t1`.`db` desc drop table t1; @@ -2441,8 +2439,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index id id 4 NULL 9 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2498,8 +2496,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index id id 4 NULL 9 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/subselect_mat_cost.result b/mysql-test/main/subselect_mat_cost.result index d46fb616d5e..9de70573189 100644 --- a/mysql-test/main/subselect_mat_cost.result +++ b/mysql-test/main/subselect_mat_cost.result @@ -60,47 +60,19 @@ Q1.1m: MATERIALIZATION: there are too many rows in the outer query to be looked up in the inner table. set statement optimizer_cache_hit_ratio=20 for EXPLAIN -SELECT Name FROM Country +SELECT count(*) FROM Country WHERE (Code IN (select Country from City where City.Population > 100000) OR Name LIKE 'L%') AND -surfacearea > 1000000; +surfacearea > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Country ALL Name,SurfaceArea NULL NULL NULL 239 Using where 3 MATERIALIZED City ALL Population,Country NULL NULL NULL 4079 Using where -set statement optimizer_cache_hit_ratio=20 for SELECT Name FROM Country +set statement optimizer_cache_hit_ratio=20 for SELECT count(*) FROM Country WHERE (Code IN (select Country from City where City.Population > 100000) OR Name LIKE 'L%') AND -surfacearea > 1000000; -Name -Algeria -Angola -Argentina -Australia -Bolivia -Brazil -Egypt -South Africa -Ethiopia -Indonesia -India -Iran -Canada -Kazakstan -China -Colombia -Congo, The Democratic Republic of the -Libyan Arab Jamahiriya -Mali -Mauritania -Mexico -Mongolia -Niger -Peru -Saudi Arabia -Sudan -Chad -Russian Federation -United States +surfacearea > 100000; +count(*) +107 Q1.1e: IN-EXISTS: the materialization cost is the same as above, but there are much fewer outer rows to be looked up, thus the diff --git a/mysql-test/main/subselect_mat_cost.test b/mysql-test/main/subselect_mat_cost.test index 12263e75cfc..6b7fa4fee64 100644 --- a/mysql-test/main/subselect_mat_cost.test +++ b/mysql-test/main/subselect_mat_cost.test @@ -74,15 +74,15 @@ set @@optimizer_switch = 'in_to_exists=on,semijoin=on,materialization=on,partial -- echo MATERIALIZATION: there are too many rows in the outer query -- echo to be looked up in the inner table. set statement optimizer_cache_hit_ratio=20 for EXPLAIN -SELECT Name FROM Country +SELECT count(*) FROM Country WHERE (Code IN (select Country from City where City.Population > 100000) OR Name LIKE 'L%') AND - surfacearea > 1000000; + surfacearea > 100000; -set statement optimizer_cache_hit_ratio=20 for SELECT Name FROM Country +set statement optimizer_cache_hit_ratio=20 for SELECT count(*) FROM Country WHERE (Code IN (select Country from City where City.Population > 100000) OR Name LIKE 'L%') AND - surfacearea > 1000000; + surfacearea > 100000; -- echo Q1.1e: -- echo IN-EXISTS: the materialization cost is the same as above, but diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 29104376c46..e2ab6ae8a69 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -899,6 +899,9 @@ select (select a+1) from t1; NULL 4.5 drop table t1; +# +# Null with keys +# CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -4441,10 +4444,13 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +# +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 2 @@ -4453,7 +4459,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(/* select#2 */ select 1 from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` limit 1)) @@ -4462,8 +4468,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 3 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -5702,7 +5708,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3; CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); a @@ -5712,7 +5718,7 @@ SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index cc8c61c61dd..6078ac53feb 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -902,6 +902,9 @@ select (select a+1) from t1; NULL 4.5 drop table t1; +# +# Null with keys +# CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -4441,10 +4444,13 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +# +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 2 @@ -4453,7 +4459,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a` @@ -4462,8 +4468,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 3 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -5699,7 +5705,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3; CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); a @@ -5709,7 +5715,7 @@ SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 6e557485796..b3d382889a3 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -898,6 +898,9 @@ select (select a+1) from t1; NULL 4.5 drop table t1; +# +# Null with keys +# CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -4437,10 +4440,13 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +# +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 2 @@ -4449,7 +4455,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select 2 AS `2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where <cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)) @@ -4458,8 +4464,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 3 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -5695,7 +5701,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3; CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); a @@ -5705,7 +5711,7 @@ SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 6f457f76c0c..9246e09074d 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -901,6 +901,9 @@ select (select a+1) from t1; NULL 4.5 drop table t1; +# +# Null with keys +# CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -4444,10 +4447,13 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +# +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 2 @@ -4457,7 +4463,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where 1 @@ -4466,8 +4472,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 3 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -5706,7 +5712,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3; CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); a @@ -5717,7 +5723,7 @@ WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a @@ -5726,9 +5732,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 2 Using index; FirstMatch(t1) DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 5cc7b1cb22d..0c5a21303c7 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -348,10 +348,10 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where -2 MATERIALIZED t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t7 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(<in_optimizer>(`test`.`t6`.`clinic_uq`,`test`.`t6`.`clinic_uq` in ( <materialize> (/* select#2 */ select `test`.`t7`.`uq` from `test`.`t7` where 1 ), <primary_index_lookup>(`test`.`t6`.`clinic_uq` in <temporary table> on distinct_key where `test`.`t6`.`clinic_uq` = `<subquery2>`.`uq`)))) +Note 1003 /* select#1 */ select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(<in_optimizer>(`test`.`t6`.`clinic_uq`,<exists>(<primary_index_lookup>(<cache>(`test`.`t6`.`clinic_uq`) in t7 on PRIMARY)))) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous drop table t1,t2,t3; @@ -898,6 +898,9 @@ select (select a+1) from t1; NULL 4.5 drop table t1; +# +# Null with keys +# CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -1443,9 +1446,9 @@ a explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 MATERIALIZED t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1614,21 +1617,21 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 MATERIALIZED t2 index s1 s1 6 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 index s1 s1 6 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on distinct_key where `test`.`t1`.`s1` = `<subquery2>`.`s1`)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 MATERIALIZED t2 index s1 s1 6 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 index s1 s1 6 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on distinct_key where `test`.`t1`.`s1` = `<subquery2>`.`s1`)))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 MATERIALIZED t2 index s1 s1 6 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 index s1 s1 6 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on distinct_key where `test`.`t1`.`s1` = `<subquery2>`.`s1`)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index @@ -3179,7 +3182,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 MATERIALIZED t1 index a a 5 NULL 5 Using index +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -4437,10 +4440,13 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +# +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t2 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2),(1000); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 2 @@ -4449,7 +4455,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`a`)))) @@ -4458,8 +4464,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 3 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -5695,7 +5701,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3; CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (NULL), (1), (NULL), (2); CREATE TABLE t2 (a int, INDEX idx(a)) ; -INSERT INTO t2 VALUES (NULL), (1), (NULL); +INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a); a @@ -5705,7 +5711,7 @@ SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a @@ -5715,7 +5721,7 @@ SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index +2 DEPENDENT SUBQUERY t2 index_subquery idx idx 5 func 2 Using index DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' diff --git a/mysql-test/main/subselect_partial_match.result b/mysql-test/main/subselect_partial_match.result index 52c30492675..3d10e912f22 100644 --- a/mysql-test/main/subselect_partial_match.result +++ b/mysql-test/main/subselect_partial_match.result @@ -760,20 +760,18 @@ drop table t1,t2; # LP BUG#601156 # CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL); -INSERT INTO t1 VALUES (NULL,2); -INSERT INTO t1 VALUES (4,NULL); +INSERT INTO t1 VALUES (NULL,2), (4,NULL),(100,100); CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL); -INSERT INTO t2 VALUES (6,NULL); -INSERT INTO t2 VALUES (NULL,0); +INSERT INTO t2 VALUES (6,NULL), (NULL,0),(1000,1000); set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 -2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using where -3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 /* select#1 */ select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (/* select#2 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where !(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (/* select#3 */ select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery3>`.`b2`))))) `table1` set optimizer_switch=@tmp_optimizer_switch; @@ -783,11 +781,11 @@ DROP TABLE t1, t2; # set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off'; create table t1 (a1 char(3) DEFAULT NULL, a2 char(3) DEFAULT NULL); -insert into t1 values (NULL, 'a21'), (NULL, 'a22'); +insert into t1 values (NULL, 'a21'), (NULL, 'a22'), ('xxx','xxx'); explain select * from t1 where (a1, a2) not in (select a1, a2 from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t1 where (a1, a2) not in (select a1, a2 from t1); a1 a2 drop table t1; diff --git a/mysql-test/main/subselect_partial_match.test b/mysql-test/main/subselect_partial_match.test index fd1e6de716c..8fdd5e451ba 100644 --- a/mysql-test/main/subselect_partial_match.test +++ b/mysql-test/main/subselect_partial_match.test @@ -614,11 +614,9 @@ drop table t1,t2; --echo # CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL); -INSERT INTO t1 VALUES (NULL,2); -INSERT INTO t1 VALUES (4,NULL); +INSERT INTO t1 VALUES (NULL,2), (4,NULL),(100,100); CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL); -INSERT INTO t2 VALUES (6,NULL); -INSERT INTO t2 VALUES (NULL,0); +INSERT INTO t2 VALUES (6,NULL), (NULL,0),(1000,1000); set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; @@ -637,7 +635,7 @@ DROP TABLE t1, t2; set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off'; create table t1 (a1 char(3) DEFAULT NULL, a2 char(3) DEFAULT NULL); -insert into t1 values (NULL, 'a21'), (NULL, 'a22'); +insert into t1 values (NULL, 'a21'), (NULL, 'a22'), ('xxx','xxx'); explain select * from t1 where (a1, a2) not in (select a1, a2 from t1); select * from t1 where (a1, a2) not in (select a1, a2 from t1); drop table t1; diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index b5e55333e0d..67df72d2209 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -91,9 +91,9 @@ select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10)); id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where -2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index +2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (/* select#2 */ select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where `test`.`t1`.`A` = `<suBquery2>`.`pk`)))) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY)))) where 1 set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=8*1024; we shouldn't flatten if we're going to get a join of > MAX_TABLES. @@ -1627,7 +1627,7 @@ drop table t1,t2; # BUG#787299: Valgrind complains on a join query with two IN subqueries # create table t1 (a int); -insert into t1 values (1), (2), (3); +insert into t1 values (1), (2), (3),(1000),(2000); create table t2 as select * from t1; select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); @@ -1635,16 +1635,18 @@ a a 1 1 2 2 3 3 +1000 1000 +2000 2000 explain select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY A ALL NULL NULL NULL NULL 3 +1 PRIMARY A ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY B ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED C ALL NULL NULL NULL NULL 3 -3 MATERIALIZED D ALL NULL NULL NULL NULL 3 +2 MATERIALIZED C ALL NULL NULL NULL NULL 5 +3 MATERIALIZED D ALL NULL NULL NULL NULL 5 drop table t1, t2; # # BUG#784441: Abort on semijoin with a view as the inner table @@ -1996,13 +1998,12 @@ INSERT INTO t4 VALUES (0),(NULL),(-1),(-2),(-3); explain extended SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t4 ref_or_null f2 f2 5 const 4 100.00 Using where; Using index; FirstMatch(t2) 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ref_or_null f2 f2 5 const 4 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t4`.`f2` = 0 or `test`.`t4`.`f2` is null) +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t2`.`f3` = 0 or `test`.`t2`.`f3` is null) SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL); f1 f2 f3 f3 2 0 0 0 diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index bc109ba1b9a..417ec52812f 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -1425,7 +1425,7 @@ drop table t1,t2; --echo # BUG#787299: Valgrind complains on a join query with two IN subqueries --echo # create table t1 (a int); -insert into t1 values (1), (2), (3); +insert into t1 values (1), (2), (3),(1000),(2000); create table t2 as select * from t1; select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index 95f80b1a271..794c86aef55 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -618,7 +618,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index drop table t0, t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -934,20 +934,22 @@ INSERT INTO t1 VALUES (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( -pk INT, d VARCHAR(1), e INT, +pk INT, d VARCHAR(1), e INT, f int, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; -INSERT INTO t2 VALUES +INSERT INTO t2 (pk,d,e) VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); +update t2 set f=pk/2; analyze table t1,t2; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +# Original query, changed because of new optimizations explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 @@ -957,9 +959,18 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan 1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) 1 PRIMARY t1 ref b b 4 test.t2.d 1 +explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 -WHERE a = d AND ( pk < 2 OR d = 'z' ) +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where; Start temporary +1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index +1 PRIMARY t1 ref b b 4 test.t2.d 1 End temporary +SELECT * FROM t1 WHERE b IN ( +SELECT d FROM t2, t1 +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 ); a b c Warnings: diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test index 419deb6f7e7..31e6f3d7231 100644 --- a/mysql-test/main/subselect_sj2.test +++ b/mysql-test/main/subselect_sj2.test @@ -1115,26 +1115,35 @@ INSERT INTO t1 VALUES (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( - pk INT, d VARCHAR(1), e INT, + pk INT, d VARCHAR(1), e INT, f int, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; -INSERT INTO t2 VALUES +INSERT INTO t2 (pk,d,e) VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); +update t2 set f=pk/2; analyze table t1,t2; +--echo # Original query, changed because of new optimizations explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) ); + +explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 - WHERE a = d AND ( pk < 2 OR d = 'z' ) + WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 +); + +SELECT * FROM t1 WHERE b IN ( + SELECT d FROM t2, t1 + WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 ); DROP TABLE t1, t2; diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 39f5c372d00..c1c1b9478c1 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -630,7 +630,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index drop table t0, t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -906,11 +906,10 @@ EXPLAIN SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t3.a 1 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t3.b 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t3.a 1 Using where; Using join buffer (incremental, BNLH join) 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 3 DERIVED t1 ALL NULL NULL NULL NULL 1 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); @@ -946,20 +945,22 @@ INSERT INTO t1 VALUES (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( -pk INT, d VARCHAR(1), e INT, +pk INT, d VARCHAR(1), e INT, f int, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; -INSERT INTO t2 VALUES +INSERT INTO t2 (pk,d,e) VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); +update t2 set f=pk/2; analyze table t1,t2; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +# Original query, changed because of new optimizations explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 @@ -969,9 +970,18 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan 1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) 1 PRIMARY t1 ref b b 4 test.t2.d 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 -WHERE a = d AND ( pk < 2 OR d = 'z' ) +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where; Start temporary +1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index +1 PRIMARY t1 ref b b 4 test.t2.d 1 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +SELECT * FROM t1 WHERE b IN ( +SELECT d FROM t2, t1 +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 ); a b c Warnings: diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 3d4d9361440..87f549e5efe 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -620,7 +620,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index drop table t0, t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -936,20 +936,22 @@ INSERT INTO t1 VALUES (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( -pk INT, d VARCHAR(1), e INT, +pk INT, d VARCHAR(1), e INT, f int, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; -INSERT INTO t2 VALUES +INSERT INTO t2 (pk,d,e) VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); +update t2 set f=pk/2; analyze table t1,t2; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +# Original query, changed because of new optimizations explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 @@ -959,9 +961,18 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan 1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) 1 PRIMARY t1 ref b b 4 test.t2.d 1 +explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 -WHERE a = d AND ( pk < 2 OR d = 'z' ) +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where; Start temporary +1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index +1 PRIMARY t1 ref b b 4 test.t2.d 1 End temporary +SELECT * FROM t1 WHERE b IN ( +SELECT d FROM t2, t1 +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 ); a b c Warnings: @@ -1651,12 +1662,12 @@ set optimizer_switch='materialization=on,semijoin=on'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); i1 7 @@ -1664,12 +1675,12 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 @@ -1709,7 +1720,7 @@ set optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('qux'),('foo'); CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; -INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); +INSERT INTO t2 VALUES ('bar'),('foo'),('qux'),('qq1'),('qq2'); SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) HAVING f1 != 'foo' @@ -1723,7 +1734,7 @@ ORDER BY f1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index f1 f1 11 NULL 2 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where DROP TABLE t1,t2; # # MDEV-16225: wrong resultset from query with semijoin=on diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test index 4b768652670..9417f777442 100644 --- a/mysql-test/main/subselect_sj2_mat.test +++ b/mysql-test/main/subselect_sj2_mat.test @@ -300,7 +300,7 @@ set optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('qux'),('foo'); CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; -INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); +INSERT INTO t2 VALUES ('bar'),('foo'),('qux'),('qq1'),('qq2'); let $q= SELECT f1 FROM t1 diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index c56bf56289a..dafb6de702d 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -102,9 +102,9 @@ select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10)); id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer (flAt, BNL join) -2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index +2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (/* select#2 */ select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where `test`.`t1`.`A` = `<suBquery2>`.`pk`)))) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY)))) where 1 set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=8*1024; we shouldn't flatten if we're going to get a join of > MAX_TABLES. @@ -1638,7 +1638,7 @@ drop table t1,t2; # BUG#787299: Valgrind complains on a join query with two IN subqueries # create table t1 (a int); -insert into t1 values (1), (2), (3); +insert into t1 values (1), (2), (3),(1000),(2000); create table t2 as select * from t1; select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); @@ -1646,16 +1646,18 @@ a a 1 1 2 2 3 3 +1000 1000 +2000 2000 explain select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY A ALL NULL NULL NULL NULL 3 +1 PRIMARY A ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY B ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED C ALL NULL NULL NULL NULL 3 -3 MATERIALIZED D ALL NULL NULL NULL NULL 3 +2 MATERIALIZED C ALL NULL NULL NULL NULL 5 +3 MATERIALIZED D ALL NULL NULL NULL NULL 5 drop table t1, t2; # # BUG#784441: Abort on semijoin with a view as the inner table @@ -2007,13 +2009,12 @@ INSERT INTO t4 VALUES (0),(NULL),(-1),(-2),(-3); explain extended SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t4 ref_or_null f2 f2 5 const 4 100.00 Using where; Using index; FirstMatch(t2) 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (incremental, BNL join) -2 MATERIALIZED t4 ref_or_null f2 f2 5 const 4 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t4`.`f2` = 0 or `test`.`t4`.`f2` is null) +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t2`.`f3` = 0 or `test`.`t2`.`f3` is null) SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL); f1 f2 f3 f3 2 0 0 0 diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index afd8e3b0734..d5148030edb 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -107,11 +107,10 @@ a1 a2 explain extended select * from t1i where a1 in (select b1 from t2i where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index _it1_idx _it1_idx # NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key # func 1 100.00 -2 MATERIALIZED t2i index it2i1,it2i3 it2i1 # NULL 5 100.00 Using where; +1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 50.00 Using where; Using index; LooseScan +1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t2i`.`b1` > '0' +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` > '0' select * from t1i where a1 in (select b1 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -131,11 +130,10 @@ a1 a2 explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index _it1_idx _it1_idx # NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key # func,func 1 100.00 -2 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 100.00 Using where; +1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 50.00 Using where; Using index; LooseScan +1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t2i`.`b1` > '0' +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0' select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -278,11 +276,10 @@ a1 a2 explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 -2 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using index +1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan +1 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 test.t2i.b1,test.t2i.b2 1 100.00 Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where 1 +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -1546,8 +1543,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) 2 @@ -2232,9 +2228,8 @@ mysqltest1 EXPLAIN EXTENDED SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort -1 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index -2 MATERIALIZED schemata ALL NULL NULL NULL NULL NULL NULL +1 PRIMARY schemata ALL NULL NULL NULL NULL NULL NULL Start temporary; Using temporary; Using filesort +1 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index; End temporary Warnings: Note 1003 select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`schemata`) where `test`.`t1`.`db` = `information_schema`.`schemata`.`SCHEMA_NAME` order by `test`.`t1`.`db` desc drop table t1; @@ -2480,8 +2475,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index id id 4 NULL 9 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2537,8 +2532,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index id id 4 NULL 9 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/type_time_6065.result b/mysql-test/main/type_time_6065.result index 5df612e3001..c5fb790b656 100644 --- a/mysql-test/main/type_time_6065.result +++ b/mysql-test/main/type_time_6065.result @@ -2267,9 +2267,8 @@ outr.col_varchar_key IS NULL ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY outr system col_datetime_key NULL NULL NULL 1 100.00 -1 PRIMARY outr2 index col_time_key col_time_key 4 NULL 20 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED innr ref col_int_key col_int_key 4 const 2 100.00 Using where +1 PRIMARY innr ref col_int_key col_int_key 4 const 2 100.00 Using where; FirstMatch(outr) +1 PRIMARY outr2 index col_time_key col_time_key 4 NULL 20 100.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 select 1 AS `col_int_nokey` from `test`.`t3` `outr2` semi join (`test`.`t1` `innr`) where `test`.`innr`.`col_int_key` = 1 and `test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey` and `test`.`outr2`.`col_time_key` > '2001-11-04 19:07:55' SELECT outr.col_int_nokey diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index f5a4fc089da..fb6f5bae555 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -1093,10 +1093,11 @@ insert into t1 values (1); update v2 set s1 = 1; select * from v2; select * from t2; -# scheck how VIEWs with subqueries work with prepared statements +# check how VIEWs with subqueries work with prepared statements prepare stmt1 from "select * from v2;"; execute stmt1; insert into t1 values (0); +--sorted_result execute stmt1; deallocate prepare stmt1; drop view v2; |