bzr push into mysql-trunk branch (jorgen.loland:3500 to 3501) Bug#13068506

[Jorgen Loland]

 3501 Jorgen Loland	2011-10-17
      Bug#13068506: QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS 
                    WRONG RESULT
      
      Consider the query:
      
      SELECT (
        SELECT MAX( t2.i2 )
        FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
        WHERE t2.i3 <> t1.i1
      ) AS field1
      FROM t1
      GROUP BY field1;
      
      In simplify_joins(), the optimizer checks if the outer join can
      be transformed to an inner join, and if the ON conditions 
      can be transformed to WHERE conditions. This can be done if 
      there are conditions that reject null-extended rows (extensions
      to t3 in the query above)
      
      To check that the inner tables does have a join match for all
      qualifying outer rows, not_null_tables() is called on the 
      conditions and if all inner tables will produce rows the join
      is transformed (see simplify_joins()):
      
      if (conds)
        not_null_tables= conds->not_null_tables();
      ...
      if (!table->outer_join || (used_tables & not_null_tables))
      {
        <transformation happens here>
      }
      
      In the query above, t3 has table->map=1 and t2 has 
      table->map=2. Thus, when processing t2 of the subselect,
      used_tables=1. The WHERE condition is not null-rejecting for
      table t3, so not_null_tables shold not have bit 0 set. But 
      it has.
      
      Item_outer_ref inherits not_null_tables() from Item_ref. 
      Table t1 has table->map=1 in the outer select, so when 
      not_null_tables() is called on the t1.i1 part of the <> 
      condition, the map of t1 is returned. The bug is that
      Item_outer_ref::not_null_tables() returns the map of the outer
      reference in the first place. It should return 0.
     @ mysql-test/r/join_outer.result
        Added test for 13068506
     @ mysql-test/r/join_outer_bka.result
        Added test for 13068506
     @ mysql-test/r/join_outer_bka_nixbnl.result
        Added test for 13068506
     @ mysql-test/t/join_outer.test
        Added test for 13068506
     @ sql/item.h
        Implement Item_outer_ref::not_null_tables()

    modified:
      mysql-test/r/join_outer.result
      mysql-test/r/join_outer_bka.result
      mysql-test/r/join_outer_bka_nixbnl.result
      mysql-test/t/join_outer.test
      sql/item.h
 3500 Marko Mäkelä	2011-10-17
      Fix a memory leak caused by faulty merge of Bug#13006367 fix.
      
      buf_pool_free_instance(): Restore the ha_clear() call.

    modified:
      storage/innobase/buf/buf0buf.c
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-09-30 12:20:04 +0000
+++ b/mysql-test/r/join_outer.result	2011-10-17 13:52:27 +0000
@@ -1777,3 +1777,33 @@ WHERE t2.pk
 ORDER BY t2.col_int_key;
 col_int_key
 DROP TABLE t1,t2;
+#
+# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS WRONG RESULT
+#
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (100), (101);
+CREATE TABLE t2 (i2 int, i3 int);
+INSERT INTO t2 VALUES (20,1),(10,2);
+CREATE TABLE t3 (i4 int(11));
+INSERT INTO t3 VALUES (1),(2);
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1;;
+field1
+20
+20
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1 GROUP BY field1;;
+field1
+20
+
+drop table t1,t2,t3;

=== modified file 'mysql-test/r/join_outer_bka.result'
--- a/mysql-test/r/join_outer_bka.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/join_outer_bka.result	2011-10-17 13:52:27 +0000
@@ -1778,4 +1778,34 @@ WHERE t2.pk
 ORDER BY t2.col_int_key;
 col_int_key
 DROP TABLE t1,t2;
+#
+# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS WRONG RESULT
+#
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (100), (101);
+CREATE TABLE t2 (i2 int, i3 int);
+INSERT INTO t2 VALUES (20,1),(10,2);
+CREATE TABLE t3 (i4 int(11));
+INSERT INTO t3 VALUES (1),(2);
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1;;
+field1
+20
+20
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1 GROUP BY field1;;
+field1
+20
+
+drop table t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/join_outer_bka_nixbnl.result'
--- a/mysql-test/r/join_outer_bka_nixbnl.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/join_outer_bka_nixbnl.result	2011-10-17 13:52:27 +0000
@@ -1778,4 +1778,34 @@ WHERE t2.pk
 ORDER BY t2.col_int_key;
 col_int_key
 DROP TABLE t1,t2;
+#
+# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS WRONG RESULT
+#
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (100), (101);
+CREATE TABLE t2 (i2 int, i3 int);
+INSERT INTO t2 VALUES (20,1),(10,2);
+CREATE TABLE t3 (i4 int(11));
+INSERT INTO t3 VALUES (1),(2);
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1;;
+field1
+20
+20
+
+SELECT (
+SELECT MAX( t2.i2 )
+FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1 GROUP BY field1;;
+field1
+20
+
+drop table t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2011-09-30 12:20:04 +0000
+++ b/mysql-test/t/join_outer.test	2011-10-17 13:52:27 +0000
@@ -1381,3 +1381,30 @@ WHERE t2.pk
 ORDER BY t2.col_int_key;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS WRONG RESULT
+--echo #
+CREATE TABLE t1 (i1 int);
+INSERT INTO t1 VALUES (100), (101);
+
+CREATE TABLE t2 (i2 int, i3 int);
+INSERT INTO t2 VALUES (20,1),(10,2);
+
+CREATE TABLE t3 (i4 int(11));
+INSERT INTO t3 VALUES (1),(2);
+
+let $query= SELECT (
+  SELECT MAX( t2.i2 )
+  FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
+  WHERE t2.i3 <> t1.i1
+) AS field1
+FROM t1;
+
+--echo
+--eval $query;
+--echo
+--eval $query GROUP BY field1;
+
+--echo
+drop table t1,t2,t3;

=== modified file 'sql/item.h'
--- a/sql/item.h	2011-10-13 12:33:08 +0000
+++ b/sql/item.h	2011-10-17 13:52:27 +0000
@@ -2830,6 +2830,8 @@ public:
   {
     return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
   }
+  table_map not_null_tables() const { return 0; }
+
   virtual Ref_Type ref_type() { return OUTER_REF; }
 };
 

No bundle (reason: useless for push emails).