From: Roy Lyseng Date: May 4 2012 8:03am Subject: bzr push into mysql-trunk branch (roy.lyseng:3904 to 3905) Bug#13980954 List-Archive: http://lists.mysql.com/commits/143780 X-Bug: 13980954 Message-Id: <201205040803.q4483LkX018185@khepri07.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3905 Roy Lyseng 2012-05-04 Bug#13980954: Missing data on left join + null value + where..in Patch 1 of 2. The intention of this patch is to eliminate some redundant predicates from evaluation in outer join scenarios. Look at this outer join query (straight_join is needed to get a table order that shows the problem): CREATE TABLE t1 (ik INT, vc varchar(1)) ENGINE=Innodb; SELECT straight_join t1.vc, t1.ik FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; By using EXPLAIN FORMAT=JSON on the query, we see that the triggered condition attached to table t3 is trigcond_if(is_not_null_compl(t3), (t2.vc=t1.vc), true) and trigcond_if(is_not_null_compl(t3), (t3.vc=t1.vc) But the condition attached to table t2 is (t2.vc=t1.vc), so the triggered condition representing the predicate t2.vc=t1.vc is clearly redundant. The problem stems from the fact that when creating multiple equalities for a join condition, multiple equalities for "upper level" conditions are added, but when generating equality items after the table order is fixed, the already generated equalities are not deleted. For the query above, we first generate the multiple equality =(t1.vc, t2.vc) inside the WHERE condition. Then we generate a multiple equality for outer join =(t1.vc, t2.vc, t3.vc). (t1.vc is included because "upper level" multiple equalities are added. When creating equality items, we first generate t2.vc=t1.vc for table t2 from the first multiple equality. Then we create t2.vc=t1.vc and t3.vc=t1.vc (wrapped in triggered conditions) for table t3. The first of these predicates is redundant, as it has already been evaluated for table t2. We can eliminate this predicate by letting substitute_for_best_equal_field() consider the multiple equalities on the "upper level" when generating equality predicates. Here, the WHERE condition is considered "upper level" to the outer-most join condition, and an outer join condition is on "upper level" relative to an inner outer join if we have nested outer joins. mysql-test/r/join_outer.result mysql-test/r/join_outer_bka.result mysql-test/r/join_outer_bka_nixbnl.result Explain results for test case for bug#13980954. mysql-test/suite/opt_trace/r/general2_no_prot.result mysql-test/suite/opt_trace/r/general2_ps_prot.result Some simplified triggered conditions for nested outer joins. mysql-test/t/join_outer.test Test case for bug#13980954. sql/sql_optimizer.cc In substitute_for_best_equal_field(), consider cond_equal->upper_levels when calling eliminate_item_equal() for the first (or only) branch of a disjunct. modified: mysql-test/r/join_outer.result mysql-test/r/join_outer_bka.result mysql-test/r/join_outer_bka_nixbnl.result mysql-test/suite/opt_trace/r/general2_no_prot.result mysql-test/suite/opt_trace/r/general2_ps_prot.result mysql-test/t/join_outer.test sql/sql_optimizer.cc 3904 Roy Lyseng 2012-05-04 Bug#13974177: Assert !(tab->table->regginfo.not_exists_optimize... Followup fix with test result changes. mysql-test/suite/opt_trace/r/general_no_prot_all.result mysql-test/suite/opt_trace/r/general_ps_prot_all.result Test changes. modified: mysql-test/suite/opt_trace/r/general_no_prot_all.result mysql-test/suite/opt_trace/r/general_ps_prot_all.result === modified file 'mysql-test/r/join_outer.result' --- a/mysql-test/r/join_outer.result 2012-03-21 14:27:34 +0000 +++ b/mysql-test/r/join_outer.result 2012-05-04 08:01:13 +0000 @@ -2034,3 +2034,51 @@ p1 NULL DROP VIEW v1, v2; DROP TABLE t1, t2; +# +# Bug#13980954 Missing data on left join + null value + where..in +# +CREATE TABLE t1 (ik INT, vc varchar(1)) ENGINE=Innodb; +explain format=json SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 1, + "filtered": 100, + "using_join_buffer": "Block Nested Loop", + "attached_condition": "(`test`.`t2`.`vc` = `test`.`t1`.`vc`)" + } + }, + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1, + "filtered": 100, + "using_join_buffer": "Block Nested Loop", + "attached_condition": "trigcond_if(is_not_null_compl(t3), (`test`.`t3`.`vc` = `test`.`t1`.`vc`), true)" + } + } + ] + } +} +Warnings: +Note 1003 /* select#1 */ select straight_join `test`.`t1`.`vc` AS `vc`,`test`.`t1`.`ik` AS `ik` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on((`test`.`t3`.`vc` = `test`.`t1`.`vc`)) where (`test`.`t2`.`vc` = `test`.`t1`.`vc`) +SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; +vc ik +DROP TABLE t1; === modified file 'mysql-test/r/join_outer_bka.result' --- a/mysql-test/r/join_outer_bka.result 2012-03-21 14:27:34 +0000 +++ b/mysql-test/r/join_outer_bka.result 2012-05-04 08:01:13 +0000 @@ -2035,4 +2035,52 @@ p1 NULL DROP VIEW v1, v2; DROP TABLE t1, t2; +# +# Bug#13980954 Missing data on left join + null value + where..in +# +CREATE TABLE t1 (ik INT, vc varchar(1)) ENGINE=Innodb; +explain format=json SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 1, + "filtered": 100, + "using_join_buffer": "Block Nested Loop", + "attached_condition": "(`test`.`t2`.`vc` = `test`.`t1`.`vc`)" + } + }, + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1, + "filtered": 100, + "using_join_buffer": "Block Nested Loop", + "attached_condition": "trigcond_if(is_not_null_compl(t3), (`test`.`t3`.`vc` = `test`.`t1`.`vc`), true)" + } + } + ] + } +} +Warnings: +Note 1003 /* select#1 */ select straight_join `test`.`t1`.`vc` AS `vc`,`test`.`t1`.`ik` AS `ik` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on((`test`.`t3`.`vc` = `test`.`t1`.`vc`)) where (`test`.`t2`.`vc` = `test`.`t1`.`vc`) +SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; +vc ik +DROP TABLE t1; set optimizer_switch=default; === modified file 'mysql-test/r/join_outer_bka_nixbnl.result' --- a/mysql-test/r/join_outer_bka_nixbnl.result 2012-03-21 14:27:34 +0000 +++ b/mysql-test/r/join_outer_bka_nixbnl.result 2012-05-04 08:01:13 +0000 @@ -2035,4 +2035,50 @@ p1 NULL DROP VIEW v1, v2; DROP TABLE t1, t2; +# +# Bug#13980954 Missing data on left join + null value + where..in +# +CREATE TABLE t1 (ik INT, vc varchar(1)) ENGINE=Innodb; +explain format=json SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 1, + "filtered": 100, + "attached_condition": "(`test`.`t2`.`vc` = `test`.`t1`.`vc`)" + } + }, + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1, + "filtered": 100, + "attached_condition": "trigcond_if(is_not_null_compl(t3), (`test`.`t3`.`vc` = `test`.`t1`.`vc`), true)" + } + } + ] + } +} +Warnings: +Note 1003 /* select#1 */ select straight_join `test`.`t1`.`vc` AS `vc`,`test`.`t1`.`ik` AS `ik` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on((`test`.`t3`.`vc` = `test`.`t1`.`vc`)) where (`test`.`t2`.`vc` = `test`.`t1`.`vc`) +SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; +vc ik +DROP TABLE t1; set optimizer_switch=default; === modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result' --- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-03-28 13:39:57 +0000 +++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-05-04 08:01:13 +0000 @@ -613,7 +613,7 @@ TRACE }, { "table": "`t3`", - "attached": "((trigcond_if(found_match(t3), trigcond_if(found_match(t2..t3), isnull(`t3`.`i`), true), true) and trigcond_if(is_not_null_compl(t3), (`t2`.`i` = `t1`.`i`), true)) and trigcond_if(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))" + "attached": "(trigcond_if(found_match(t3), trigcond_if(found_match(t2..t3), isnull(`t3`.`i`), true), true) and trigcond_if(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ === modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result' --- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-03-28 13:39:57 +0000 +++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-05-04 08:01:13 +0000 @@ -641,7 +641,7 @@ TRACE }, { "table": "`t3`", - "attached": "((trigcond_if(found_match(t3), trigcond_if(found_match(t2..t3), isnull(`t3`.`i`), true), true) and trigcond_if(is_not_null_compl(t3), (`t2`.`i` = `t1`.`i`), true)) and trigcond_if(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))" + "attached": "(trigcond_if(found_match(t3), trigcond_if(found_match(t2..t3), isnull(`t3`.`i`), true), true) and trigcond_if(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ === modified file 'mysql-test/t/join_outer.test' --- a/mysql-test/t/join_outer.test 2012-03-21 14:27:34 +0000 +++ b/mysql-test/t/join_outer.test 2012-05-04 08:01:13 +0000 @@ -1526,3 +1526,18 @@ CREATE VIEW v2 AS SELECT * FROM t2; UNION (SELECT NULL LIMIT 0); DROP VIEW v1, v2; DROP TABLE t1, t2; + +--echo # +--echo # Bug#13980954 Missing data on left join + null value + where..in +--echo # + +CREATE TABLE t1 (ik INT, vc varchar(1)) ENGINE=Innodb; + +let $query= +SELECT straight_join t1.vc, t1.ik +FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc; + +eval explain format=json $query; +eval $query; + +DROP TABLE t1; === modified file 'sql/sql_optimizer.cc' --- a/sql/sql_optimizer.cc 2012-05-03 13:35:42 +0000 +++ b/sql/sql_optimizer.cc 2012-05-04 08:01:13 +0000 @@ -2107,7 +2107,7 @@ static Item* substitute_for_best_equal_f item_equal= (Item_equal *) cond; item_equal->sort(&compare_fields_by_table_order, table_join_idx); if (cond_equal && cond_equal->current_level.head() == item_equal) - cond_equal= 0; + cond_equal= cond_equal->upper_levels; return eliminate_item_equal(0, cond_equal, item_equal); } else No bundle (reason: useless for push emails).