bzr commit into mysql-trunk branch (ole.john.aske:3588) Bug#56690

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-trunk/ based on revid:ole.john.aske@stripped

 3588 Ole John Aske	2011-02-02
      Fix for bug#56690 'Wrong results with subquery with 
                         GROUP BY inside < ANY clause'
            
      A subquery of the form :
      
       1) SELECT ... WHERE <column> < ANY(select <column>...)
      
      Is transformed into the form by the optimizer:
      
        2) SELECT ... WHERE <column> < (select MAX(<column>)...)
      
      The Min/Max aggregation of subquery columns is implemented in 
      'class select_max_min_finder_subselect'. 
      
      The handling of NULL values in this implementation was incorrect as
      they was interpreted as a NULL-value being '>' than any other value being
      compared. This is incorrect wrt. SQL semantics which specifies that
      NULL values are 'undefined' and should be removed as soon as a non-NULL
      value is encountered.
                  
      This fix changes implementation of all 
      select_max_min_finder_subselect::cmp_<type>() methods to follow the
      correct SQL semantics as described above. (Which also simplifies the
      logic IMHO)
                  
      It also changes the methods to be 'private' within 
      class select_max_min_finder_subselect.

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_none.result
      sql/sql_class.cc
      sql/sql_class.h
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-01-18 11:42:09 +0000
+++ b/mysql-test/include/subquery.inc	2011-02-02 09:04:55 +0000
@@ -4999,6 +4999,34 @@ SELECT * FROM t1
 
 DROP TABLE t1,t1s,t2s;
 
+--echo #
+--echo # Bug #56690  Wrong results with subquery with 
+--echo # GROUP BY inside < ANY clause
+--echo #
+
+CREATE TABLE t1 (
+ pk INT NOT NULL PRIMARY KEY,
+ number INT,
+ KEY key_number (number)
+);
+INSERT INTO t1 VALUES (8,8);
+
+CREATE TABLE t2 (
+ pk INT NOT NULL PRIMARY KEY,
+ number INT,
+ KEY key_number (number)
+);
+
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (2,8);
+INSERT INTO t2 VALUES (3,NULL);
+INSERT INTO t2 VALUES (4,166);
+
+SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
+SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
+
+DROP TABLE t1,t2;
+
 --echo End of 5.1 tests
  
 --echo #

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-01-24 09:02:03 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-02-02 09:04:55 +0000
@@ -6152,6 +6152,32 @@ HAVING t2s.i = 999
 ) IS UNKNOWN;
 i
 DROP TABLE t1,t1s,t2s;
+#
+# Bug #56690  Wrong results with subquery with 
+# GROUP BY inside < ANY clause
+#
+CREATE TABLE t1 (
+pk INT NOT NULL PRIMARY KEY,
+number INT,
+KEY key_number (number)
+);
+INSERT INTO t1 VALUES (8,8);
+CREATE TABLE t2 (
+pk INT NOT NULL PRIMARY KEY,
+number INT,
+KEY key_number (number)
+);
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (2,8);
+INSERT INTO t2 VALUES (3,NULL);
+INSERT INTO t2 VALUES (4,166);
+SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
+pk	number
+8	8
+SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
+pk	number
+8	8
+DROP TABLE t1,t2;
 End of 5.1 tests
 #
 # BUG#50257: Missing info in REF column of the EXPLAIN 

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-01-24 09:02:03 +0000
+++ b/mysql-test/r/subquery_none.result	2011-02-02 09:04:55 +0000
@@ -6151,6 +6151,32 @@ HAVING t2s.i = 999
 ) IS UNKNOWN;
 i
 DROP TABLE t1,t1s,t2s;
+#
+# Bug #56690  Wrong results with subquery with 
+# GROUP BY inside < ANY clause
+#
+CREATE TABLE t1 (
+pk INT NOT NULL PRIMARY KEY,
+number INT,
+KEY key_number (number)
+);
+INSERT INTO t1 VALUES (8,8);
+CREATE TABLE t2 (
+pk INT NOT NULL PRIMARY KEY,
+number INT,
+KEY key_number (number)
+);
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (2,8);
+INSERT INTO t2 VALUES (3,NULL);
+INSERT INTO t2 VALUES (4,166);
+SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
+pk	number
+8	8
+SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
+pk	number
+8	8
+DROP TABLE t1,t2;
 End of 5.1 tests
 #
 # BUG#50257: Missing info in REF column of the EXPLAIN 

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2011-02-01 07:52:46 +0000
+++ b/sql/sql_class.cc	2011-02-02 09:04:55 +0000
@@ -2558,26 +2558,24 @@ bool select_max_min_finder_subselect::cm
 {
   Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0);
   double val1= cache->val_real(), val2= maxmin->val_real();
-  if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       val1 > val2);
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     val1 < val2);
+  if (cache->null_value)
+    return false;
+  else if (maxmin->null_value)
+    return true;
+  else 
+    return (fmax) ? (val1 > val2) : (val1 < val2);
 }
 
 bool select_max_min_finder_subselect::cmp_int()
 {
   Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0);
   longlong val1= cache->val_int(), val2= maxmin->val_int();
-  if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       val1 > val2);
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     val1 < val2);
+  if (cache->null_value)
+    return false;
+  else if (maxmin->null_value)
+    return true;
+  else 
+    return (fmax) ? (val1 > val2) : (val1 < val2);
 }
 
 bool select_max_min_finder_subselect::cmp_decimal()
@@ -2585,13 +2583,14 @@ bool select_max_min_finder_subselect::cm
   Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0);
   my_decimal cval, *cvalue= cache->val_decimal(&cval);
   my_decimal mval, *mvalue= maxmin->val_decimal(&mval);
-  if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       my_decimal_cmp(cvalue, mvalue) > 0) ;
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     my_decimal_cmp(cvalue,mvalue) < 0);
+  if (cache->null_value)
+    return false;
+  else if (maxmin->null_value)
+    return true;
+  else 
+    return (fmax) 
+      ? (my_decimal_cmp(cvalue,mvalue) > 0)
+      : (my_decimal_cmp(cvalue,mvalue) < 0);
 }
 
 bool select_max_min_finder_subselect::cmp_str()
@@ -2604,13 +2603,14 @@ bool select_max_min_finder_subselect::cm
   */
   val1= cache->val_str(&buf1);
   val2= maxmin->val_str(&buf1);
-  if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       sortcmp(val1, val2, cache->collation.collation) > 0) ;
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     sortcmp(val1, val2, cache->collation.collation) < 0);
+  if (cache->null_value)
+    return false;
+  else if (maxmin->null_value)
+    return true;
+  else 
+    return (fmax) 
+      ? (sortcmp(val1, val2, cache->collation.collation) > 0)
+      : (sortcmp(val1, val2, cache->collation.collation) < 0);
 }
 
 bool select_exists_subselect::send_data(List<Item> &items)

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2011-02-01 07:52:46 +0000
+++ b/sql/sql_class.h	2011-02-02 09:04:55 +0000
@@ -3393,6 +3393,7 @@ public:
   {}
   void cleanup();
   bool send_data(List<Item> &items);
+private:
   bool cmp_real();
   bool cmp_int();
   bool cmp_decimal();


Attachment: [text/bzr-bundle] bzr/[e-mail]