bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4164) Bug#56690

[Ole John Aske]

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

 4164 Ole John Aske	2011-02-02
      Fix for Bug#56690: Wrong results with subquery with GROUP BY inside < ANY clause.
      
      Cherry picked into mysql-5.1-telco-7.0
      
      Se original commit comments in http://lists.mysql.com/commits/130184

    modified:
      mysql-test/r/subselect.result
      mysql-test/t/subselect.test
      sql/sql_class.cc
      sql/sql_class.h
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-01-14 13:36:47 +0000
+++ b/mysql-test/r/subselect.result	2011-02-02 09:15:56 +0000
@@ -4785,4 +4785,30 @@ 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

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-01-14 13:36:47 +0000
+++ b/mysql-test/t/subselect.test	2011-02-02 09:15:56 +0000
@@ -3774,4 +3774,32 @@ 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

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2010-10-20 11:10:46 +0000
+++ b/sql/sql_class.cc	2011-02-02 09:15:56 +0000
@@ -2394,6 +2394,14 @@ 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();
+#ifndef MCP_BUG56690
+  if (cache->null_value)
+    return false;
+  else if (maxmin->null_value)
+    return true;
+  else 
+    return (fmax) ? (val1 > val2) : (val1 < val2);
+#else
   if (fmax)
     return (cache->null_value && !maxmin->null_value) ||
       (!cache->null_value && !maxmin->null_value &&
@@ -2401,12 +2409,21 @@ bool select_max_min_finder_subselect::cm
   return (maxmin->null_value && !cache->null_value) ||
     (!cache->null_value && !maxmin->null_value &&
      val1 < val2);
+#endif
 }
 
 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();
+#ifndef MCP_BUG56690
+  if (cache->null_value)
+    return false;
+  else if (maxmin->null_value)
+    return true;
+  else 
+    return (fmax) ? (val1 > val2) : (val1 < val2);
+#else
   if (fmax)
     return (cache->null_value && !maxmin->null_value) ||
       (!cache->null_value && !maxmin->null_value &&
@@ -2414,6 +2431,7 @@ bool select_max_min_finder_subselect::cm
   return (maxmin->null_value && !cache->null_value) ||
     (!cache->null_value && !maxmin->null_value &&
      val1 < val2);
+#endif
 }
 
 bool select_max_min_finder_subselect::cmp_decimal()
@@ -2421,6 +2439,16 @@ 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);
+#ifndef MCP_BUG56690
+  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);
+#else
   if (fmax)
     return (cache->null_value && !maxmin->null_value) ||
       (!cache->null_value && !maxmin->null_value &&
@@ -2428,6 +2456,7 @@ bool select_max_min_finder_subselect::cm
   return (maxmin->null_value && !cache->null_value) ||
     (!cache->null_value && !maxmin->null_value &&
      my_decimal_cmp(cvalue,mvalue) < 0);
+#endif
 }
 
 bool select_max_min_finder_subselect::cmp_str()
@@ -2440,6 +2469,16 @@ bool select_max_min_finder_subselect::cm
   */
   val1= cache->val_str(&buf1);
   val2= maxmin->val_str(&buf1);
+#ifndef MCP_BUG56690
+  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);
+#else
   if (fmax)
     return (cache->null_value && !maxmin->null_value) ||
       (!cache->null_value && !maxmin->null_value &&
@@ -2447,6 +2486,7 @@ bool select_max_min_finder_subselect::cm
   return (maxmin->null_value && !cache->null_value) ||
     (!cache->null_value && !maxmin->null_value &&
      sortcmp(val1, val2, cache->collation.collation) < 0);
+#endif
 }
 
 bool select_exists_subselect::send_data(List<Item> &items)

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2010-10-20 11:10:46 +0000
+++ b/sql/sql_class.h	2011-02-02 09:15:56 +0000
@@ -2835,6 +2835,9 @@ public:
   {}
   void cleanup();
   bool send_data(List<Item> &items);
+#ifndef MCP_BUG56690
+private:
+#endif
   bool cmp_real();
   bool cmp_int();
   bool cmp_decimal();


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