From: Roy Lyseng Date: May 22 2012 12:43pm Subject: bzr push into mysql-trunk branch (roy.lyseng:3908 to 3909) Bug#14058892 List-Archive: http://lists.mysql.com/commits/143910 X-Bug: 14058892 Message-Id: <201205221243.q4MCh5um024821@khepri07.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3909 Roy Lyseng 2012-05-21 Bug#14058892: Extra rows returned when variable is used in subquery in ON clause of RIGHT JOIN. The fix for bug#13980954 simplified the way table bits were assigned for the tables involved in a join operation. Unfortunately, it missed that RAND_TABLE_BIT had to be set for the last inner table of an outer join operation, otherwise non-deterministic functions in the join condition would be missed. This fix adds back this bit. Notice that there is a proposed refactoring of pushdown_on_conditions() that will significantly reduce the size of this function. mysql-test/t/user_var.test Added test case for bug#14058892. mysql-test/r/user_var.result Added test case results for bug#14058892. sql/sql_optimizer.cc In pushdown_on_conditions(), add RAND_TABLE_BIT for last inner table of an outer join. modified: mysql-test/r/user_var.result mysql-test/t/user_var.test sql/sql_optimizer.cc 3908 Roy Lyseng 2012-05-18 Bug#14048292: Segfault in Item_field::result_type on 2nd execution of prep stmt This problem may occur for queries which uses a view as the sole inner table of an outer join operation and where the join condition contains an IN subquery predicate that is transformed to a semi-join. The problem lies in simplify_joins() which fails to record the transformed join condition for this kind of TABLE_LIST object into prep_join_cond. I have not investigated in detail why it fails, but rather made the recording as a post-processing stage after simplify_joins(), which seems like a simplification of the code. Thus, prep_join_cond is now populated unconditionally for all TABLE_LIST objects with a join condition after simplify_joins(). Notice that select_lex::fix_prepare_information() does a similar thing ahead of this call, unfortunately it cannot be eliminated because JOIN::optimize() (and thus simplify_joins()) is not necessarily called for every query block of a query expression. This is a redundancy that should be fixed as part of refactoring. mysql-test/include/subquery_sj.inc Added test case for bug#14048292. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result Added test case results for bug#14048292. sql/sql_optimizer.cc In record_join_nest_info(), also record permanent join conditions after simplify_joins() has been called. Renamed record_semijoin_nests() to record_join_nest_info(). Also completed earlier renaming of 'on_expr" to 'join_cond' by refreshing some comments in simplify_joins(). modified: mysql-test/include/subquery_sj.inc mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result sql/sql_optimizer.cc === modified file 'mysql-test/r/user_var.result' --- a/mysql-test/r/user_var.result 2012-03-06 14:29:42 +0000 +++ b/mysql-test/r/user_var.result 2012-05-21 09:14:15 +0000 @@ -516,3 +516,68 @@ SELECT @var; @var NULL DROP TABLE t1, t2; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (1), (3), (5), (7), (9); +CREATE TABLE t3(a INT); +INSERT INTO t3 VALUES (1), (4), (7), (10); +SET @var1 = 6; +explain format=json SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var +FROM t1 +LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1 +LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + } + }, + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "using_join_buffer": "Block Nested Loop", + "attached_condition": "trigcond_if(is_not_null_compl(t2), ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`a` < (@var1))), true)" + } + }, + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "using_join_buffer": "Block Nested Loop", + "attached_condition": "trigcond_if(is_not_null_compl(t3), ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`a` < (@var1))), true)" + } + } + ] + } +} +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,(@var1:=((@var1) + 0)) AS `var` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`a` < (@var1)))) left join `test`.`t3` on(((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`a` < (@var1)))) where 1 +SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var +FROM t1 +LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1 +LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1; +a a a var +1 1 1 6 +10 NULL NULL 6 +2 NULL NULL 6 +3 3 NULL 6 +4 NULL 4 6 +5 5 NULL 6 +6 NULL NULL 6 +7 NULL NULL 6 +8 NULL NULL 6 +9 NULL NULL 6 +DROP TABLE t1, t2, t3; === modified file 'mysql-test/t/user_var.test' --- a/mysql-test/t/user_var.test 2012-03-06 14:29:42 +0000 +++ b/mysql-test/t/user_var.test 2012-05-21 09:14:15 +0000 @@ -436,3 +436,31 @@ SELECT @var:=(SELECT f2 FROM t2 WHERE @v LIMIT 1; SELECT @var; DROP TABLE t1, t2; + +# +# Bug#14058892: Extra rows returned when variable is used in subquery in +# ON clause of RIGHT JOIN +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (1), (3), (5), (7), (9); + +CREATE TABLE t3(a INT); +INSERT INTO t3 VALUES (1), (4), (7), (10); + +SET @var1 = 6; + +let $query= +SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var +FROM t1 + LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1 + LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1; + +eval explain format=json $query; + +--sorted_result +eval $query; + +DROP TABLE t1, t2, t3; === modified file 'sql/sql_optimizer.cc' --- a/sql/sql_optimizer.cc 2012-05-18 09:50:03 +0000 +++ b/sql/sql_optimizer.cc 2012-05-21 09:14:15 +0000 @@ -6003,8 +6003,20 @@ static bool pushdown_on_conditions(JOIN* for (JOIN_TAB *join_tab= join->join_tab+join->const_tables; join_tab <= last_tab ; join_tab++) { - Item *tmp_cond= make_cond_for_table(on_expr, join_tab->prefix_tables(), - join_tab->added_tables(), 0); + table_map prefix_tables= join_tab->prefix_tables(); + table_map added_tables= join_tab->added_tables(); + + if (join_tab == last_tab) + { + /* + Need RAND_TABLE_BIT on the last inner table, in case there is a + non-deterministic function in the join condition. + */ + prefix_tables|= RAND_TABLE_BIT; + added_tables|= RAND_TABLE_BIT; + } + Item *tmp_cond= make_cond_for_table(on_expr, prefix_tables, added_tables, + false); if (!tmp_cond) continue; No bundle (reason: useless for push emails).