List:Commits« Previous MessageNext Message »
From:Norvald H. Ryeng Date:April 27 2012 6:34am
Subject:bzr push into mysql-trunk branch (norvald.ryeng:3748 to 3749) Bug#13735712
View as plain text  
 3749 Norvald H. Ryeng	2012-04-27
      Bug#13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
      VARIABLES
      
      Outer join queries with ALL may return incorrect results because the
      optimizer incorrectly rewrites them to use inner join. E.g.:
      
      SELECT *
      FROM t2 RIGHT JOIN t3 ON(t3.c = t2.b)
      WHERE t2.b < ALL(SELECT t1.a FROM t1 WHERE t1.a <= 7);
      
      is first rewritten by Item_in_subselect::single_value_transformer()
      into:
      
      SELECT *
      FROM t2 RIGHT JOIN t3 ON(t3.c = t2.b)
      WHERE <not>(t2.b >= (SELECT MIN(t1.a) FROM t1 WHERE t1.a <= 7));
      
      When simplify_joins() checks not_null_tables() on the <not> condition
      to find out if the outer join can be transformed into an inner join,
      Item_func::not_null_tables() returns its arguments'
      not_null_tables(). This means that simplify_joins() is told that the
      condition will be false if t2.b is NULL. But the condition is actually
      true if t1 has no rows where t1.a <= 7. This leads to the optimizer
      incorrectly rewriting the query to use inner join.
      
      Fix: Let Item_func_not_all::not_null_tables() return a zero table map.
     @ mysql-test/include/subquery.inc
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_all.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_all_bka.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_all_bka_nixbnl.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_nomat_nosj.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_nomat_nosj_bka.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_none.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_none_bka.result
        Add test cases for bugs #13735712.
     @ mysql-test/r/subquery_none_bka_nixbnl.result
        Add test cases for bugs #13735712.
     @ sql/item_cmpfunc.h
        Return 0 from Item_func_not_all::not_null_tables().

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nixbnl.result
      sql/item_cmpfunc.h
 3748 Sunny Bains	2012-04-27
      Bug#14004341 - REDUCE MEMCPY() OVERHEAD IN ROW_SEARCH_FOR_MYSQL()
      
      InnoDB uses a read ahead cache for rows that can potentially be accessed in a 
      batch. This is to avoid the overhead of mini-transactions. Currently, all 
      records are copied to this fetch cache and when we exit from the function 
      we copy the first record from this queue to the MySQL buffer. Avoid this copy 
      to the fetch cache queue and back on normal exit from the function by doing the 
      changes directly in the MySQL recod buffer for the first record. 
      
      This eliminates two memcpy() calls but is only done for non-ICP code paths.
      
      rb://1046 Approved by Jimmy.

    modified:
      storage/innobase/row/row0sel.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2012-03-27 12:58:14 +0000
+++ b/mysql-test/include/subquery.inc	2012-04-27 06:33:39 +0000
@@ -6067,3 +6067,25 @@ eval SELECT *
 );
 
 DROP TABLE t2,t1;
+
+--echo #
+--echo # Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+--echo # VARIABLES
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+
+DROP TABLE t1, t2, t3;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-03-27 12:58:14 +0000
+++ b/mysql-test/r/subquery_all.result	2012-04-27 06:33:39 +0000
@@ -7375,4 +7375,26 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result	2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2012-04-27 06:33:39 +0000
@@ -7375,4 +7375,26 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result	2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_none.result	2012-04-27 06:33:39 +0000
@@ -7374,4 +7374,26 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_none_bka.result	2012-04-27 06:33:39 +0000
@@ -7375,5 +7375,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result	2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result	2012-04-27 06:33:39 +0000
@@ -7375,5 +7375,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
 );
 field1	field2	field3	field4	field5	field6
 DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b	c
+NULL	3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b	c
+NULL	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2012-03-06 14:29:42 +0000
+++ b/sql/item_cmpfunc.h	2012-04-27 06:33:39 +0000
@@ -546,6 +546,30 @@ public:
   void set_sum_test(Item_sum_hybrid *item) { test_sum_item= item; };
   void set_sub_test(Item_maxmin_subselect *item) { test_sub_item= item; };
   void set_subselect(Item_subselect *item) { subselect= item; }
+  table_map not_null_tables() const
+  {
+    /*
+      See handling of not_null_tables_cache in
+      Item_in_optimizer::fix_fields().
+
+      This item is the result of a transformation from an ALL clause
+      such as
+          left-expr < ALL(subquery)
+      into
+          <not>(left-expr >= (subquery)
+
+      An inequality usually rejects NULLs from both operands, so the
+      not_null_tables() of the inequality is the union of the
+      null-rejecting tables of both operands. However, since this is a
+      transformed ALL clause that should return true if the subquery
+      is empty (even if left-expr is NULL), it is not null rejecting
+      for left-expr. The not null tables mask for left-expr should be
+      removed, leaving only the null-rejecting tables of the
+      subquery. Item_subselect::not_null_tables() always returns 0 (no
+      null-rejecting tables). Therefore, always return 0.
+    */
+    return 0;
+  }
   bool empty_underlying_subquery();
   Item *neg_transformer(THD *thd);
 };
@@ -558,6 +582,7 @@ public:
   Item_func_nop_all(Item *a) :Item_func_not_all(a) {}
   longlong val_int();
   const char *func_name() const { return "<nop>"; }
+  table_map not_null_tables() const { return not_null_tables_cache; }
   Item *neg_transformer(THD *thd);
 };
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (norvald.ryeng:3748 to 3749) Bug#13735712Norvald H. Ryeng27 Apr