List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:May 4 2012 8:03am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3904 to 3905) Bug#13980954
View as plain text  
 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).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3904 to 3905) Bug#13980954Roy Lyseng9 May