summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect_sj.result
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2013-02-20 18:01:36 -0800
committerIgor Babaev <igor@askmonty.org>2013-02-20 18:01:36 -0800
commitc9b63e6a49618561cc960be06513279910582e29 (patch)
tree68fec0494beb6db890e61ed26af8864b0874c46f /mysql-test/r/subselect_sj.result
parent34e84c227f1cb76771eabf229b4cf1b5292eef25 (diff)
downloadmariadb-git-c9b63e6a49618561cc960be06513279910582e29.tar.gz
Fixed bug mdev-3913.
The wrong result set returned by the left join query from the bug test case happened due to several inconsistencies and bugs of the legacy mysql code. The bug test case uses an execution plan that employs a scan of a materialized IN subquery from the WHERE condition. When materializing such an IN- subquery the optimizer injects additional equalities into the WHERE clause. These equalities express the constraints imposed by the subquery predicate. The injected equality of the query in the test case happens to belong to the same equality class, and a new equality imposing a condition on the rows of the materialized subquery is inferred from this class. Simultaneously the multiple equality is added to the ON expression of the LEFT JOIN used in the main query. The inferred equality of the form f1=f2 is taken into account when optimizing the scan of the rows the temporary table that is the result of the subquery materialization: only the values of the field f1 are read from the table into the record buffer. Meanwhile the inferred equality is removed from the WHERE conditions altogether as a constraint on the fields of the temporary table that has been used when filling this table. This equality is supposed to be removed from the ON expression when the multiple equalities of the ON expression are converted into an optimal set of equality predicates. It supposed to be removed from the ON expression as an equality inferred from only equalities of the WHERE condition. Yet, it did not happened due to the following bug in the code. Erroneously the code tried to build multiple equality for ON expression twice: the first time, when it called optimize_cond() for the WHERE condition, the second time, when it called this function for the HAVING condition. When executing optimize_con() for the WHERE condition a reference to the multiple equality of the WHERE condition is set in the multiple equality of the ON expression. This reference would allow later to convert multiple equalities of the ON expression into equality predicates. However the the second call of build_equal_items() for the ON expression that happened when optimize_cond() was called for the HAVING condition reset this reference to NULL. This bug fix blocks calling build_equal_items() for ON expressions for the second time. In general, it will be beneficial for many queries as it removes from ON expressions any equalities that are to be checked for the WHERE condition. The patch also fixes two bugs in the list manipulation operations and a bug in the function substitute_for_best_equal_field() that resulted in passing wrong reference to the multiple equalities of where conditions when processing multiple equalities of ON expressions. The code of substitute_for_best_equal_field() and the code the helper function eliminate_item_equal() were also streamlined and cleaned up. Now the conversion of the multiple equalities into an optimal set of equality predicates first produces the sequence of the all equalities processing multiple equalities one by one, and, only after this, it inserts the equalities at the beginning of the other conditions. The multiple changes in the output of EXPLAIN EXTENDED are mainly the result of this streamlining, but in some cases is the result of the removal of unneeded equalities from ON expressions. In some test cases this removal were reflected in the output of EXPLAIN resulted in disappearance of “Using where” in some rows of the execution plans.
Diffstat (limited to 'mysql-test/r/subselect_sj.result')
-rw-r--r--mysql-test/r/subselect_sj.result28
1 files changed, 14 insertions, 14 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 01ad6f9712f..2f9f3da3cfd 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -74,7 +74,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t12`.`pk` = `test`.`t10`.`a`) and (`test`.`t10`.`pk` = `test`.`t1`.`a`))
subqueries within outer joins go into ON expr.
explAin extended
select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.A in (select pk from t10));
@@ -84,7 +84,7 @@ id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00 Using where
2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on((<in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `<suBquery2>`.`pk`))))) And (`test`.`A`.`A` = `test`.`t1`.`A`))) where 1
+Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `<suBquery2>`.`pk`))))))) where 1
t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
explAin extended
select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
@@ -93,7 +93,7 @@ id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
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
Warnings:
-Note 1003 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((<in_optimizer>(`test`.`t2`.`A`,`test`.`t2`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t2`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t2`.`A` = `<suBquery2>`.`pk`))))) And (`test`.`t2`.`A` = `test`.`t1`.`A`))) where 1
+Note 1003 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 `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
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,
@@ -501,7 +501,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where
1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`))
+Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t1`.`pk` = `test`.`t0`.`pk`) and (`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`))
SELECT vkey FROM t0 WHERE pk IN
(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey);
vkey
@@ -814,7 +814,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
@@ -824,7 +824,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
2
@@ -833,7 +833,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`e` = `test`.`t1`.`e`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`e` = `test`.`t1`.`e`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
pk
1
@@ -843,7 +843,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
@@ -853,7 +853,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
@@ -863,7 +863,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
@@ -873,7 +873,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
pk
1
@@ -883,7 +883,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
@@ -893,7 +893,7 @@ 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
1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
@@ -1984,7 +1984,7 @@ 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; 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)
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`.`t1`.`f2` = `test`.`t2`.`f2`) and (`test`.`t3`.`f1` = `test`.`t1`.`f1`) and (`test`.`t4`.`f2` = `test`.`t2`.`f3`))
+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`))
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4);
f1 f2 f3 f3
2 0 0 0