List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:May 22 2012 12:43pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3908 to 3909) Bug#14058892
View as plain text  
 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).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3908 to 3909) Bug#14058892Roy Lyseng22 May