From df54a502cce47b7b24c275897d585f517b37b24b Mon Sep 17 00:00:00 2001 From: tianfengli Date: Thu, 8 Aug 2024 09:33:44 +0800 Subject: [PATCH] When an EXISTS subselect creates an Item_cache on its left_expr, and then the left_expr is replaced by equal items in substitute_for_best_equal_field(), the Item_cache is left behind, with its example item still being the old item. When using EXPLAIN FORMAT=TREE, users may encounter a seemingly incorrect plan taht the old item in Item_cache belongs to tables that cannot be evaluated yet. Fortunately, this discrepancy is merely an illusion when explaining. Upon execution, the cache item will store the newest left_expr again. This patch aims to fix the illusion mismatch between the explain and execute phases, ensuring that the displayed plan accurately reflects the actual execution process. --- mysql-test/r/subquery_sj_none.result | 22 ++++++++--------- mysql-test/r/subquery_sj_none_bka.result | 22 ++++++++--------- .../r/subquery_sj_none_bka_nobnl.result | 22 ++++++++--------- .../opt_trace/r/general_ps_prot_none.result | 24 +++++++++---------- sql/item_cmpfunc.cc | 8 +++++++ sql/item_cmpfunc.h | 5 ++++ 6 files changed, 58 insertions(+), 45 deletions(-) diff --git a/mysql-test/r/subquery_sj_none.result b/mysql-test/r/subquery_sj_none.result index a2873495248..ae06e60fb07 100644 --- a/mysql-test/r/subquery_sj_none.result +++ b/mysql-test/r/subquery_sj_none.result @@ -212,7 +212,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY t10 NULL 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 (`test`.`t1`.`A`,(((`test`.`t2`.`A`) in t10 on PRIMARY))))) where true +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 (`test`.`t1`.`A`,(((`test`.`t1`.`A`) in t10 on PRIMARY))))) where true we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, @@ -5651,7 +5651,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it3 NULL ALL NULL NULL NULL NULL 3 33.33 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot1`.`a`) = `test`.`it3`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) SELECT * FROM ot1 JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it3); a a @@ -5667,7 +5667,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 3 DEPENDENT SUBQUERY it3 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))) and (`test`.`ot2`.`a`,(/* select#3 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))) and (`test`.`ot2`.`a`,(/* select#3 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) SELECT * FROM ot1 JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it2) AND ot2.a IN (SELECT a FROM it3); @@ -5736,7 +5736,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot2 NULL ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))))) where true SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a AND ot2.a IN (SELECT a FROM it2); a a @@ -5758,7 +5758,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 3 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`))) and (`test`.`ot1`.`a`,(/* select#3 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`))) and (`test`.`ot1`.`a`,(/* select#3 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))))) where true SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it1) AND ot2.a IN (SELECT a FROM it2); @@ -5883,7 +5883,7 @@ EXPLAIN -> Table scan on ot3 (cost=0.0172 rows=3) -> Select #3 (subquery in extra conditions; dependent) -> Limit: 1 row(s) (cost=0.35 rows=1) - -> Filter: ((ot3.a) = it3.a) (cost=0.35 rows=1) + -> Filter: ((ot2.a) = it3.a) (cost=0.35 rows=1) -> Table scan on it3 (cost=0.35 rows=3) SELECT * @@ -5950,7 +5950,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` join `test`.`ot3` where ((`test`.`ot2`.`a` = `test`.`ot3`.`a`) and (`test`.`ot1`.`a` = `test`.`ot3`.`a`) and (`test`.`ot3`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` join `test`.`ot3` where ((`test`.`ot2`.`a` = `test`.`ot3`.`a`) and (`test`.`ot1`.`a` = `test`.`ot3`.`a`) and (`test`.`ot3`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot3`.`a`) = `test`.`it1`.`a`)))) SELECT * FROM ot1 JOIN @@ -5971,7 +5971,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` left join `test`.`ot3` on((`test`.`ot3`.`a` = `test`.`ot2`.`a`)) where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` left join `test`.`ot3` on((`test`.`ot3`.`a` = `test`.`ot2`.`a`)) where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot2`.`a`) = `test`.`it1`.`a`)))) SELECT * FROM ot1 JOIN @@ -8275,7 +8275,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY t3 NULL 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 (`test`.`t1`.`a`,(((`test`.`t2`.`a`) in t3 on PRIMARY))))) where true +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 (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t3 on PRIMARY))))) where true drop table t0, t1, t2, t3; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -10975,7 +10975,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join) Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`pk` AS `pk`,`test`.`ot2`.`pk` AS `pk` from `test`.`t1` `ot1` left join `test`.`t1` `ot2` on(((`test`.`ot2`.`pk` = `test`.`ot1`.`pk`) and (`test`.`ot1`.`pk`,(/* select#2 */ select `test`.`it1`.`pk` from `test`.`t1` `it1` left join `test`.`t1` `it2` on((`test`.`it2`.`pk` = `test`.`it1`.`pk`)) where ((`test`.`ot2`.`pk`) = `test`.`it1`.`pk`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`pk` AS `pk`,`test`.`ot2`.`pk` AS `pk` from `test`.`t1` `ot1` left join `test`.`t1` `ot2` on(((`test`.`ot2`.`pk` = `test`.`ot1`.`pk`) and (`test`.`ot1`.`pk`,(/* select#2 */ select `test`.`it1`.`pk` from `test`.`t1` `it1` left join `test`.`t1` `it2` on((`test`.`it2`.`pk` = `test`.`it1`.`pk`)) where ((`test`.`ot1`.`pk`) = `test`.`it1`.`pk`))))) where true SELECT * FROM t1 AS ot1 LEFT JOIN t1 AS ot2 ON ot1.pk=ot2.pk AND @@ -11099,7 +11099,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 4 DEPENDENT SUBQUERY subquery1_t3 NULL ref col_varchar_key col_varchar_key 7 test.subquery1_t2.col_varchar_nokey 1 10.00 Using where 4 DEPENDENT SUBQUERY subquery1_t1 NULL index PRIMARY col_int_key 5 NULL 20 100.00 Using where; Using index; Using join buffer (hash join) Warnings: -Note 1003 /* select#1 */ select `test`.`table2`.`col_varchar_nokey` AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` straight_join `test`.`t2` `table3` where ((`test`.`table3`.`col_int_key` = `test`.`table2`.`pk`) and (`test`.`table2`.`pk`,(/* select#3 */ select `test`.`subquery2_t1`.`col_int_key` AS `subquery2_field1` from `test`.`t3` `subquery2_t2` join `test`.`t1` `subquery2_t3` join `test`.`t2` `subquery2_t1` where ((`test`.`subquery2_t2`.`col_varchar_key` = `test`.`subquery2_t3`.`col_varchar_key`) and (`test`.`subquery2_t2`.`pk` = `test`.`subquery2_t3`.`pk`) and ((`test`.`table3`.`col_int_key`) = `test`.`subquery2_t1`.`col_int_key`)))) and (`test`.`table3`.`col_int_nokey`,(/* select#4 */ select `test`.`subquery1_t2`.`col_int_nokey` AS `subquery1_field1` from `test`.`t1` `subquery1_t2` join `test`.`t1` `subquery1_t3` join `test`.`t2` `subquery1_t1` where ((`test`.`subquery1_t3`.`col_int_key` = `test`.`subquery1_t2`.`pk`) and (`test`.`subquery1_t3`.`col_varchar_key` = `test`.`subquery1_t2`.`col_varchar_nokey`) and (`test`.`subquery1_t1`.`pk` > 1) and ((`test`.`table3`.`col_int_nokey`) = `test`.`subquery1_t2`.`col_int_nokey`))))) +Note 1003 /* select#1 */ select `test`.`table2`.`col_varchar_nokey` AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` straight_join `test`.`t2` `table3` where ((`test`.`table3`.`col_int_key` = `test`.`table2`.`pk`) and (`test`.`table2`.`pk`,(/* select#3 */ select `test`.`subquery2_t1`.`col_int_key` AS `subquery2_field1` from `test`.`t3` `subquery2_t2` join `test`.`t1` `subquery2_t3` join `test`.`t2` `subquery2_t1` where ((`test`.`subquery2_t2`.`col_varchar_key` = `test`.`subquery2_t3`.`col_varchar_key`) and (`test`.`subquery2_t2`.`pk` = `test`.`subquery2_t3`.`pk`) and ((`test`.`table2`.`pk`) = `test`.`subquery2_t1`.`col_int_key`)))) and (`test`.`table3`.`col_int_nokey`,(/* select#4 */ select `test`.`subquery1_t2`.`col_int_nokey` AS `subquery1_field1` from `test`.`t1` `subquery1_t2` join `test`.`t1` `subquery1_t3` join `test`.`t2` `subquery1_t1` where ((`test`.`subquery1_t3`.`col_int_key` = `test`.`subquery1_t2`.`pk`) and (`test`.`subquery1_t3`.`col_varchar_key` = `test`.`subquery1_t2`.`col_varchar_nokey`) and (`test`.`subquery1_t1`.`pk` > 1) and ((`test`.`table3`.`col_int_nokey`) = `test`.`subquery1_t2`.`col_int_nokey`))))) SELECT * FROM v1; field1 v diff --git a/mysql-test/r/subquery_sj_none_bka.result b/mysql-test/r/subquery_sj_none_bka.result index 42bdf77aba1..00dbe37307b 100644 --- a/mysql-test/r/subquery_sj_none_bka.result +++ b/mysql-test/r/subquery_sj_none_bka.result @@ -213,7 +213,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY t10 NULL 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 (`test`.`t1`.`A`,(((`test`.`t2`.`A`) in t10 on PRIMARY))))) where true +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 (`test`.`t1`.`A`,(((`test`.`t1`.`A`) in t10 on PRIMARY))))) where true we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, @@ -5652,7 +5652,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it3 NULL ALL NULL NULL NULL NULL 3 33.33 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot1`.`a`) = `test`.`it3`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) SELECT * FROM ot1 JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it3); a a @@ -5668,7 +5668,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 3 DEPENDENT SUBQUERY it3 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))) and (`test`.`ot2`.`a`,(/* select#3 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))) and (`test`.`ot2`.`a`,(/* select#3 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) SELECT * FROM ot1 JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it2) AND ot2.a IN (SELECT a FROM it3); @@ -5737,7 +5737,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot2 NULL ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))))) where true SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a AND ot2.a IN (SELECT a FROM it2); a a @@ -5759,7 +5759,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 3 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`))) and (`test`.`ot1`.`a`,(/* select#3 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`))) and (`test`.`ot1`.`a`,(/* select#3 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))))) where true SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it1) AND ot2.a IN (SELECT a FROM it2); @@ -5884,7 +5884,7 @@ EXPLAIN -> Table scan on ot3 (cost=0.0172 rows=3) -> Select #3 (subquery in extra conditions; dependent) -> Limit: 1 row(s) (cost=0.35 rows=1) - -> Filter: ((ot3.a) = it3.a) (cost=0.35 rows=1) + -> Filter: ((ot2.a) = it3.a) (cost=0.35 rows=1) -> Table scan on it3 (cost=0.35 rows=3) SELECT * @@ -5951,7 +5951,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` join `test`.`ot3` where ((`test`.`ot2`.`a` = `test`.`ot3`.`a`) and (`test`.`ot1`.`a` = `test`.`ot3`.`a`) and (`test`.`ot3`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` join `test`.`ot3` where ((`test`.`ot2`.`a` = `test`.`ot3`.`a`) and (`test`.`ot1`.`a` = `test`.`ot3`.`a`) and (`test`.`ot3`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot3`.`a`) = `test`.`it1`.`a`)))) SELECT * FROM ot1 JOIN @@ -5972,7 +5972,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` left join `test`.`ot3` on((`test`.`ot3`.`a` = `test`.`ot2`.`a`)) where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` left join `test`.`ot3` on((`test`.`ot3`.`a` = `test`.`ot2`.`a`)) where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot2`.`a`) = `test`.`it1`.`a`)))) SELECT * FROM ot1 JOIN @@ -8276,7 +8276,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (hash join) 2 DEPENDENT SUBQUERY t3 NULL 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 (`test`.`t1`.`a`,(((`test`.`t2`.`a`) in t3 on PRIMARY))))) where true +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 (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t3 on PRIMARY))))) where true drop table t0, t1, t2, t3; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -10976,7 +10976,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (hash join) Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`pk` AS `pk`,`test`.`ot2`.`pk` AS `pk` from `test`.`t1` `ot1` left join `test`.`t1` `ot2` on(((`test`.`ot2`.`pk` = `test`.`ot1`.`pk`) and (`test`.`ot1`.`pk`,(/* select#2 */ select `test`.`it1`.`pk` from `test`.`t1` `it1` left join `test`.`t1` `it2` on((`test`.`it2`.`pk` = `test`.`it1`.`pk`)) where ((`test`.`ot2`.`pk`) = `test`.`it1`.`pk`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`pk` AS `pk`,`test`.`ot2`.`pk` AS `pk` from `test`.`t1` `ot1` left join `test`.`t1` `ot2` on(((`test`.`ot2`.`pk` = `test`.`ot1`.`pk`) and (`test`.`ot1`.`pk`,(/* select#2 */ select `test`.`it1`.`pk` from `test`.`t1` `it1` left join `test`.`t1` `it2` on((`test`.`it2`.`pk` = `test`.`it1`.`pk`)) where ((`test`.`ot1`.`pk`) = `test`.`it1`.`pk`))))) where true SELECT * FROM t1 AS ot1 LEFT JOIN t1 AS ot2 ON ot1.pk=ot2.pk AND @@ -11100,7 +11100,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 4 DEPENDENT SUBQUERY subquery1_t3 NULL ref col_varchar_key col_varchar_key 7 test.subquery1_t2.col_varchar_nokey 1 10.00 Using where 4 DEPENDENT SUBQUERY subquery1_t1 NULL index PRIMARY col_int_key 5 NULL 20 100.00 Using where; Using index; Using join buffer (hash join) Warnings: -Note 1003 /* select#1 */ select `test`.`table2`.`col_varchar_nokey` AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` straight_join `test`.`t2` `table3` where ((`test`.`table3`.`col_int_key` = `test`.`table2`.`pk`) and (`test`.`table2`.`pk`,(/* select#3 */ select `test`.`subquery2_t1`.`col_int_key` AS `subquery2_field1` from `test`.`t3` `subquery2_t2` join `test`.`t1` `subquery2_t3` join `test`.`t2` `subquery2_t1` where ((`test`.`subquery2_t2`.`col_varchar_key` = `test`.`subquery2_t3`.`col_varchar_key`) and (`test`.`subquery2_t2`.`pk` = `test`.`subquery2_t3`.`pk`) and ((`test`.`table3`.`col_int_key`) = `test`.`subquery2_t1`.`col_int_key`)))) and (`test`.`table3`.`col_int_nokey`,(/* select#4 */ select `test`.`subquery1_t2`.`col_int_nokey` AS `subquery1_field1` from `test`.`t1` `subquery1_t2` join `test`.`t1` `subquery1_t3` join `test`.`t2` `subquery1_t1` where ((`test`.`subquery1_t3`.`col_int_key` = `test`.`subquery1_t2`.`pk`) and (`test`.`subquery1_t3`.`col_varchar_key` = `test`.`subquery1_t2`.`col_varchar_nokey`) and (`test`.`subquery1_t1`.`pk` > 1) and ((`test`.`table3`.`col_int_nokey`) = `test`.`subquery1_t2`.`col_int_nokey`))))) +Note 1003 /* select#1 */ select `test`.`table2`.`col_varchar_nokey` AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` straight_join `test`.`t2` `table3` where ((`test`.`table3`.`col_int_key` = `test`.`table2`.`pk`) and (`test`.`table2`.`pk`,(/* select#3 */ select `test`.`subquery2_t1`.`col_int_key` AS `subquery2_field1` from `test`.`t3` `subquery2_t2` join `test`.`t1` `subquery2_t3` join `test`.`t2` `subquery2_t1` where ((`test`.`subquery2_t2`.`col_varchar_key` = `test`.`subquery2_t3`.`col_varchar_key`) and (`test`.`subquery2_t2`.`pk` = `test`.`subquery2_t3`.`pk`) and ((`test`.`table2`.`pk`) = `test`.`subquery2_t1`.`col_int_key`)))) and (`test`.`table3`.`col_int_nokey`,(/* select#4 */ select `test`.`subquery1_t2`.`col_int_nokey` AS `subquery1_field1` from `test`.`t1` `subquery1_t2` join `test`.`t1` `subquery1_t3` join `test`.`t2` `subquery1_t1` where ((`test`.`subquery1_t3`.`col_int_key` = `test`.`subquery1_t2`.`pk`) and (`test`.`subquery1_t3`.`col_varchar_key` = `test`.`subquery1_t2`.`col_varchar_nokey`) and (`test`.`subquery1_t1`.`pk` > 1) and ((`test`.`table3`.`col_int_nokey`) = `test`.`subquery1_t2`.`col_int_nokey`))))) SELECT * FROM v1; field1 v diff --git a/mysql-test/r/subquery_sj_none_bka_nobnl.result b/mysql-test/r/subquery_sj_none_bka_nobnl.result index fb7016fde44..3aa88c371f2 100644 --- a/mysql-test/r/subquery_sj_none_bka_nobnl.result +++ b/mysql-test/r/subquery_sj_none_bka_nobnl.result @@ -213,7 +213,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY t10 NULL 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 (`test`.`t1`.`A`,(((`test`.`t2`.`A`) in t10 on PRIMARY))))) where true +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 (`test`.`t1`.`A`,(((`test`.`t1`.`A`) in t10 on PRIMARY))))) where true we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, @@ -5651,7 +5651,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where 2 DEPENDENT SUBQUERY it3 NULL ALL NULL NULL NULL NULL 3 33.33 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot1`.`a`) = `test`.`it3`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) SELECT * FROM ot1 JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it3); a a @@ -5667,7 +5667,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 3 DEPENDENT SUBQUERY it3 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))) and (`test`.`ot2`.`a`,(/* select#3 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))) and (`test`.`ot2`.`a`,(/* select#3 */ select `test`.`it3`.`a` from `test`.`it3` where ((`test`.`ot2`.`a`) = `test`.`it3`.`a`)))) SELECT * FROM ot1 JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it2) AND ot2.a IN (SELECT a FROM it3); @@ -5736,7 +5736,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot2 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))))) where true SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a AND ot2.a IN (SELECT a FROM it2); a a @@ -5758,7 +5758,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 3 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`))) and (`test`.`ot1`.`a`,(/* select#3 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot2`.`a`) = `test`.`it2`.`a`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a` from `test`.`ot1` left join `test`.`ot2` on(((`test`.`ot2`.`a` = `test`.`ot1`.`a`) and (`test`.`ot1`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`))) and (`test`.`ot1`.`a`,(/* select#3 */ select `test`.`it2`.`a` from `test`.`it2` where ((`test`.`ot1`.`a`) = `test`.`it2`.`a`))))) where true SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a AND ot1.a IN (SELECT a FROM it1) AND ot2.a IN (SELECT a FROM it2); @@ -5883,7 +5883,7 @@ EXPLAIN -> Table scan on ot3 (cost=0.259 rows=3) -> Select #3 (subquery in condition; dependent) -> Limit: 1 row(s) (cost=0.35 rows=1) - -> Filter: ((ot3.a) = it3.a) (cost=0.35 rows=1) + -> Filter: ((ot2.a) = it3.a) (cost=0.35 rows=1) -> Table scan on it3 (cost=0.35 rows=3) SELECT * @@ -5950,7 +5950,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` join `test`.`ot3` where ((`test`.`ot2`.`a` = `test`.`ot3`.`a`) and (`test`.`ot1`.`a` = `test`.`ot3`.`a`) and (`test`.`ot3`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` join `test`.`ot3` where ((`test`.`ot2`.`a` = `test`.`ot3`.`a`) and (`test`.`ot1`.`a` = `test`.`ot3`.`a`) and (`test`.`ot3`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot3`.`a`) = `test`.`it1`.`a`)))) SELECT * FROM ot1 JOIN @@ -5971,7 +5971,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY ot3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` left join `test`.`ot3` on((`test`.`ot3`.`a` = `test`.`ot2`.`a`)) where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot1`.`a`) = `test`.`it1`.`a`)))) +Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a`,`test`.`ot2`.`a` AS `a`,`test`.`ot3`.`a` AS `a` from `test`.`ot1` join `test`.`ot2` left join `test`.`ot3` on((`test`.`ot3`.`a` = `test`.`ot2`.`a`)) where ((`test`.`ot1`.`a` = `test`.`ot2`.`a`) and (`test`.`ot2`.`a`,(/* select#2 */ select `test`.`it1`.`a` from `test`.`it1` where ((`test`.`ot2`.`a`) = `test`.`it1`.`a`)))) SELECT * FROM ot1 JOIN @@ -8275,7 +8275,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY t3 NULL 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 (`test`.`t1`.`a`,(((`test`.`t2`.`a`) in t3 on PRIMARY))))) where true +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 (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t3 on PRIMARY))))) where true drop table t0, t1, t2, t3; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -10975,7 +10975,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 2 DEPENDENT SUBQUERY it1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 2 DEPENDENT SUBQUERY it2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`ot1`.`pk` AS `pk`,`test`.`ot2`.`pk` AS `pk` from `test`.`t1` `ot1` left join `test`.`t1` `ot2` on(((`test`.`ot2`.`pk` = `test`.`ot1`.`pk`) and (`test`.`ot1`.`pk`,(/* select#2 */ select `test`.`it1`.`pk` from `test`.`t1` `it1` left join `test`.`t1` `it2` on((`test`.`it2`.`pk` = `test`.`it1`.`pk`)) where ((`test`.`ot2`.`pk`) = `test`.`it1`.`pk`))))) where true +Note 1003 /* select#1 */ select `test`.`ot1`.`pk` AS `pk`,`test`.`ot2`.`pk` AS `pk` from `test`.`t1` `ot1` left join `test`.`t1` `ot2` on(((`test`.`ot2`.`pk` = `test`.`ot1`.`pk`) and (`test`.`ot1`.`pk`,(/* select#2 */ select `test`.`it1`.`pk` from `test`.`t1` `it1` left join `test`.`t1` `it2` on((`test`.`it2`.`pk` = `test`.`it1`.`pk`)) where ((`test`.`ot1`.`pk`) = `test`.`it1`.`pk`))))) where true SELECT * FROM t1 AS ot1 LEFT JOIN t1 AS ot2 ON ot1.pk=ot2.pk AND @@ -11099,7 +11099,7 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 4 DEPENDENT SUBQUERY subquery1_t3 NULL ref col_varchar_key col_varchar_key 7 test.subquery1_t2.col_varchar_nokey 1 10.00 Using where 4 DEPENDENT SUBQUERY subquery1_t1 NULL index PRIMARY col_int_key 5 NULL 20 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`table2`.`col_varchar_nokey` AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` straight_join `test`.`t2` `table3` where ((`test`.`table3`.`col_int_key` = `test`.`table2`.`pk`) and (`test`.`table2`.`pk`,(/* select#3 */ select `test`.`subquery2_t1`.`col_int_key` AS `subquery2_field1` from `test`.`t3` `subquery2_t2` join `test`.`t1` `subquery2_t3` join `test`.`t2` `subquery2_t1` where ((`test`.`subquery2_t2`.`col_varchar_key` = `test`.`subquery2_t3`.`col_varchar_key`) and (`test`.`subquery2_t2`.`pk` = `test`.`subquery2_t3`.`pk`) and ((`test`.`table3`.`col_int_key`) = `test`.`subquery2_t1`.`col_int_key`)))) and (`test`.`table3`.`col_int_nokey`,(/* select#4 */ select `test`.`subquery1_t2`.`col_int_nokey` AS `subquery1_field1` from `test`.`t1` `subquery1_t2` join `test`.`t1` `subquery1_t3` join `test`.`t2` `subquery1_t1` where ((`test`.`subquery1_t3`.`col_int_key` = `test`.`subquery1_t2`.`pk`) and (`test`.`subquery1_t3`.`col_varchar_key` = `test`.`subquery1_t2`.`col_varchar_nokey`) and (`test`.`subquery1_t1`.`pk` > 1) and ((`test`.`table3`.`col_int_nokey`) = `test`.`subquery1_t2`.`col_int_nokey`))))) +Note 1003 /* select#1 */ select `test`.`table2`.`col_varchar_nokey` AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` straight_join `test`.`t2` `table3` where ((`test`.`table3`.`col_int_key` = `test`.`table2`.`pk`) and (`test`.`table2`.`pk`,(/* select#3 */ select `test`.`subquery2_t1`.`col_int_key` AS `subquery2_field1` from `test`.`t3` `subquery2_t2` join `test`.`t1` `subquery2_t3` join `test`.`t2` `subquery2_t1` where ((`test`.`subquery2_t2`.`col_varchar_key` = `test`.`subquery2_t3`.`col_varchar_key`) and (`test`.`subquery2_t2`.`pk` = `test`.`subquery2_t3`.`pk`) and ((`test`.`table2`.`pk`) = `test`.`subquery2_t1`.`col_int_key`)))) and (`test`.`table3`.`col_int_nokey`,(/* select#4 */ select `test`.`subquery1_t2`.`col_int_nokey` AS `subquery1_field1` from `test`.`t1` `subquery1_t2` join `test`.`t1` `subquery1_t3` join `test`.`t2` `subquery1_t1` where ((`test`.`subquery1_t3`.`col_int_key` = `test`.`subquery1_t2`.`pk`) and (`test`.`subquery1_t3`.`col_varchar_key` = `test`.`subquery1_t2`.`col_varchar_nokey`) and (`test`.`subquery1_t1`.`pk` > 1) and ((`test`.`table3`.`col_int_nokey`) = `test`.`subquery1_t2`.`col_int_nokey`))))) SELECT * FROM v1; field1 v diff --git a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result index 00fed00504b..580635372f7 100644 --- a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result +++ b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result @@ -3392,13 +3392,13 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ) { }, { "attaching_conditions_to_tables": { - "original_condition": "((`t2`.`c2` = `t1`.`c1`) and (`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t2`.`c2`) = `t2`.`c2`)))))", + "original_condition": "((`t2`.`c2` = `t1`.`c1`) and (`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t1`.`c1`) = `t2`.`c2`)))))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", - "attached": "(`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t2`.`c2`) = `t2`.`c2`))))" + "attached": "(`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t1`.`c1`) = `t2`.`c2`))))" }, { "table": "`t2`", @@ -3411,8 +3411,8 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ) { "finalizing_table_conditions": [ { "table": "`t1`", - "original_table_condition": "(`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t2`.`c2`) = `t2`.`c2`))))", - "final_table_condition ": "(`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t2`.`c2`) = `t2`.`c2`))))" + "original_table_condition": "(`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t1`.`c1`) = `t2`.`c2`))))", + "final_table_condition ": "(`t1`.`c1`,(/* select#2 */ select `t2`.`c2` from `t2` where ((`t2`.`c2` = 1) and ((`t1`.`c1`) = `t2`.`c2`))))" }, { "table": "`t2`", @@ -3441,19 +3441,19 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ) { { "condition_processing": { "condition": "WHERE", - "original_condition": "((`t2`.`c2` = 1) and ((`t2`.`c2`) = `t2`.`c2`))", + "original_condition": "((`t2`.`c2` = 1) and ((`t1`.`c1`) = `t2`.`c2`))", "steps": [ { "transformation": "equality_propagation", - "resulting_condition": "(((`t2`.`c2`) = 1) and multiple equal(1, `t2`.`c2`))" + "resulting_condition": "(((`t1`.`c1`) = 1) and multiple equal(1, `t2`.`c2`))" }, { "transformation": "constant_propagation", - "resulting_condition": "(((`t2`.`c2`) = 1) and multiple equal(1, `t2`.`c2`))" + "resulting_condition": "(((`t1`.`c1`) = 1) and multiple equal(1, `t2`.`c2`))" }, { "transformation": "trivial_condition_removal", - "resulting_condition": "(((`t2`.`c2`) = 1) and multiple equal(1, `t2`.`c2`))" + "resulting_condition": "(((`t1`.`c1`) = 1) and multiple equal(1, `t2`.`c2`))" } ] /* steps */ } /* condition_processing */ @@ -3517,13 +3517,13 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ) { }, { "attaching_conditions_to_tables": { - "original_condition": "((`t2`.`c2` = 1) and ((`t2`.`c2`) = 1))", + "original_condition": "((`t2`.`c2` = 1) and ((`t1`.`c1`) = 1))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", - "attached": "((`t2`.`c2` = 1) and ((`t2`.`c2`) = 1))" + "attached": "((`t2`.`c2` = 1) and ((`t1`.`c1`) = 1))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ @@ -3532,8 +3532,8 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ) { "finalizing_table_conditions": [ { "table": "`t2`", - "original_table_condition": "((`t2`.`c2` = 1) and ((`t2`.`c2`) = 1))", - "final_table_condition ": "((`t2`.`c2` = 1) and ((`t2`.`c2`) = 1))" + "original_table_condition": "((`t2`.`c2` = 1) and ((`t1`.`c1`) = 1))", + "final_table_condition ": "((`t2`.`c2` = 1) and ((`t1`.`c1`) = 1))" } ] /* finalizing_table_conditions */ }, diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 5c5d58d3aab..216d1fc774d 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2566,6 +2566,14 @@ void Item_in_optimizer::update_used_tables() { } } +Item *Item_in_optimizer::replace_equal_field(uchar *) { + Item_in_subselect *const subqpred = down_cast(args[0]); + if (cache->get_example() != subqpred->left_expr) { + cache->store(subqpred->left_expr); + } + return this; +} + longlong Item_func_eq::val_int() { assert(fixed); const int value = cmp.compare(); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index efd2274c852..0b6ec8e8b65 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -519,6 +519,11 @@ class Item_in_optimizer final : public Item_bool_func { const char *func_name() const override { return ""; } Item_cache **get_cache() { return &cache; } void update_used_tables() override; + /* + To display the correct cached left value for explain while the left item + might be modified in substitute_for_best_equal_field(). + */ + Item *replace_equal_field(uchar *) override; }; /// Abstract factory interface for creating comparison predicates. -- 2.19.1