List:Commits« Previous MessageNext Message »
From:Norvald H. Ryeng Date:March 7 2012 8:02am
Subject:bzr push into mysql-trunk branch (norvald.ryeng:3725 to 3726) Bug#13736664
View as plain text  
 3726 Norvald H. Ryeng	2012-03-07
      Bug#13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
      SELECT * SUBQ FOR TABLES
      
      Consider the query from the test case:
      
      SELECT d
      FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
      WHERE d LIKE '_'
      ORDER BY d;
      
      The query is executed by reading t1 as a const table and calling
      create_sort_index() to read and sort t2 on column d. The join and
      WHERE conditions are checked during filesort().
      
      First, the optimizer decides to read a2 using the JT_REF access
      method, so the join condition is removed and replaced with a ref
      lookup. Later, when the optimizer decides to first do the filesort and
      then the join, the condition is reintroduced since filesort() always
      does a full table scan. The condition is added by
      add_ref_to_table_cond(), which calls create_cond_for_const_ref() to
      create the condition. An Item_func_equal is constructed to compare c
      and a. This is equivalent to checking c <=> a, so if a and c are both
      NULL, the rows match the join condition and are part of the
      result. This is clearly not the same as the original query, so the
      query returns rows that shouldn't match the join condition.
      
      Fix: In create_cond_for_const_ref(), create an Item_func_eq instead of
      Item_func_equal if join_tab->ref.null_rejecting is set for the key
      part.
     @ mysql-test/include/subquery.inc
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_all.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_all_bka.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_all_bka_nixbnl.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_nomat_nosj.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_nomat_nosj_bka.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_none.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_none_bka.result
        Add test case for bug #13736664.
     @ mysql-test/r/subquery_none_bka_nixbnl.result
        Add test case for bug #13736664.
     @ sql/sql_optimizer.cc
        Consider NULL values when adding conditions for const references.

    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/sql_optimizer.cc
 3725 Jorgen Loland	2012-03-07
      BUG#13731380 - RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() 
                     FOR OPEN RANGE
      
      Yet another case where the range optimizer did not realize 
      that the ORed range was open, i.e. always true. The case in 
      bug the query contained predicates:
      
      "WHERE col <> 3 or col < 4"
      
      The fix is to modify key_or() to return NULL for this case
      as well. See related bug 13354910.
     @ mysql-test/include/range.inc
        Added test for BUG#13731380
     @ mysql-test/r/range_all.result
        Added test for BUG#13731380
     @ mysql-test/r/range_icp.result
        Added test for BUG#13731380
     @ mysql-test/r/range_icp_mrr.result
        Added test for BUG#13731380
     @ mysql-test/r/range_mrr.result
        Added test for BUG#13731380
     @ mysql-test/r/range_mrr_cost.result
        Added test for BUG#13731380
     @ mysql-test/r/range_none.result
        Added test for BUG#13731380
     @ sql/handler.cc
        Reintroduce ASSERT that checks that we don't do records_in_range
        for open/always true ranges
     @ sql/opt_range.cc
        Reintroduce ASSERT that checks that we don't do records_in_range
        for open/always true ranges
        Return NULL from key_or() if key1 OR key2 form an open range.
     @ unittest/gunit/opt_range-t.cc
        Removed Debug_sel_arg since it doesn't hide much. Use SEL_ARG
        instead.
        Add test for key_or() where two keys form a full range.

    modified:
      mysql-test/include/range.inc
      mysql-test/r/range_all.result
      mysql-test/r/range_icp.result
      mysql-test/r/range_icp_mrr.result
      mysql-test/r/range_mrr.result
      mysql-test/r/range_mrr_cost.result
      mysql-test/r/range_none.result
      sql/handler.cc
      sql/opt_range.cc
      unittest/gunit/opt_range-t.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2012-03-06 10:44:14 +0000
+++ b/mysql-test/include/subquery.inc	2012-03-07 08:01:17 +0000
@@ -5867,3 +5867,26 @@ WHERE t1.c3 BETWEEN (SELECT i1 FROM inte
 
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+
+--echo #
+--echo # Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+--echo # SELECT * SUBQ FOR TABLES
+--echo #
+
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-03-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_all.result	2012-03-07 08:01:17 +0000
@@ -7152,4 +7152,23 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-03-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-03-07 08:01:17 +0000
@@ -7153,5 +7153,24 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 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-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result	2012-03-07 08:01:17 +0000
@@ -7153,5 +7153,24 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 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-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2012-03-07 08:01:17 +0000
@@ -7152,4 +7152,23 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 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-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result	2012-03-07 08:01:17 +0000
@@ -7153,5 +7153,24 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 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-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-03-07 08:01:17 +0000
@@ -7153,5 +7153,24 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 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-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_none.result	2012-03-07 08:01:17 +0000
@@ -7151,4 +7151,23 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result	2012-03-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_none_bka.result	2012-03-07 08:01:17 +0000
@@ -7152,5 +7152,24 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 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-06 10:44:14 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result	2012-03-07 08:01:17 +0000
@@ -7152,5 +7152,24 @@ NULL
 NULL
 DROP VIEW v1, v2;
 DROP TABLE m, o, integers;
+#
+# Bug #13736664 RIGHT JOIN+WHERE QUERY GIVES DIFF RESULTS WHEN USING
+# SELECT * SUBQ FOR TABLES
+#
+CREATE TABLE t1(a INT, b CHAR(1));
+INSERT INTO t1 VALUES (NULL, 'x');
+CREATE TABLE t2(c INT, d CHAR(1));
+INSERT INTO t2 VALUES (NULL, 'y'), (9, 'z');
+SELECT d
+FROM t2 AS a2 RIGHT JOIN t1 AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+SELECT d
+FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a
+WHERE d LIKE '_'
+ORDER BY d;
+d
+DROP TABLE t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-03-01 08:09:04 +0000
+++ b/sql/sql_optimizer.cc	2012-03-07 08:01:17 +0000
@@ -8588,13 +8588,18 @@ static Item_cond_and *create_cond_for_co
     Field *field= table->field[table->key_info[join_tab->ref.key].key_part[i].
                                fieldnr-1];
     Item *value= join_tab->ref.items[i];
-    cond->add(new Item_func_equal(new Item_field(field), value));
+    Item *item= new Item_field(field);
+    if (!item)
+      DBUG_RETURN(NULL);
+    item= join_tab->ref.null_rejecting & (1 << i) ?
+            (Item *)new Item_func_eq(item, value) :
+            (Item *)new Item_func_equal(item, value);
+    if (!item)
+      DBUG_RETURN(NULL);
+    if (cond->add(item))
+      DBUG_RETURN(NULL);
   }
-  if (thd->is_fatal_error)
-    DBUG_RETURN(NULL);
-
-  if (!cond->fixed)
-    cond->fix_fields(thd, (Item**)&cond);
+  cond->fix_fields(thd, (Item**)&cond);
 
   DBUG_RETURN(cond);
 }

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (norvald.ryeng:3725 to 3726) Bug#13736664Norvald H. Ryeng8 Mar