List:Commits« Previous MessageNext Message »
From:Magnus Blåudd Date:February 2 2011 1:39pm
Subject:bzr commit into mysql-5.1-telco-7.1 branch (magnus.blaudd:4075)
View as plain text  
#At file:///home/msvensson/mysql/tmp/zNvjepgmQQ/7.1/ based on revid:martin.zaun@strippedp84cp1co

 4075 Magnus Blåudd	2011-02-02 [merge]
      Merge 7.0 -> 7.1

    modified:
      mysql-test/r/join_outer.result
      mysql-test/r/subselect.result
      mysql-test/suite/ndb/t/ndb_restore_rewrite_db.test
      mysql-test/t/join_outer.test
      mysql-test/t/subselect.test
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-01-14 14:26:14 +0000
+++ b/mysql-test/r/join_outer.result	2011-02-02 07:47:52 +0000
@@ -1507,4 +1507,146 @@ WHERE t3.pk2 = t2.i
 IS UNKNOWN;
 I	I
 DROP TABLE t1,t2,t3;
+#
+# Bug#58490: Incorrect result in multi level OUTER JOIN
+# in combination with IS NULL
+#
+CREATE TABLE t1 (i INT NOT NULL);
+INSERT INTO t1 VALUES (0),    (2),(3),(4);
+CREATE TABLE t2 (i INT NOT NULL);
+INSERT INTO t2 VALUES (0),(1),    (3),(4);
+CREATE TABLE t3 (i INT NOT NULL);
+INSERT INTO t3 VALUES (0),(1),(2),    (4);
+CREATE TABLE t4 (i INT NOT NULL);
+INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
+SELECT * FROM
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3 LEFT JOIN
+t4
+ON t4.i = t3.i
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+;
+i	i	i	i
+0	0	0	0
+2	NULL	NULL	NULL
+3	3	NULL	NULL
+4	4	4	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3 LEFT JOIN
+t4
+ON t4.i = t3.i
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+WHERE t4.i IS NULL;
+i	i	i	i
+2	NULL	NULL	NULL
+3	3	NULL	NULL
+4	4	4	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i
+2	NULL	NULL
+3	3	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN t4
+ON t4.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i
+2	NULL	NULL	NULL
+3	3	NULL	3
+4	NULL	NULL	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
+ON t4a.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i	i
+2	NULL	NULL	NULL	NULL
+3	3	NULL	3	3
+4	NULL	NULL	NULL	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN (t4 AS t4a, t4 AS t4b)
+ON t4a.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i	i
+2	NULL	NULL	NULL	NULL
+3	3	NULL	3	0
+3	3	NULL	3	1
+3	3	NULL	3	2
+3	3	NULL	3	3
+4	NULL	NULL	NULL	NULL
+DROP TABLE t1,t2,t3,t4;
+#
+# Bug#49322(Duplicate): Server is adding extra NULL row
+# on processing a WHERE clause
+#
+CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
+INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
+CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
+INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
+CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
+INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
+pk
+2
+4
+2
+4
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
+WHERE TABLE1.pk IS NULL;
+pk
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+DROP TABLE h,m,k;
 End of 5.1 tests

=== 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/suite/ndb/t/ndb_restore_rewrite_db.test'
--- a/mysql-test/suite/ndb/t/ndb_restore_rewrite_db.test	2011-02-02 06:52:12 +0000
+++ b/mysql-test/suite/ndb/t/ndb_restore_rewrite_db.test	2011-02-02 10:17:58 +0000
@@ -88,15 +88,10 @@ CREATE TABLE db2.t0_data ENGINE=MYISAM A
 --source include/ndb_backup.inc
 
 # command shortcuts, cover rebuilding of indexes
-#--let $restore_cmd=$NDB_RESTORE --no-defaults
-#--let $restore_cmd=$restore_cmd --disable-indexes --rebuild-indexes
-#--let $restore_cmd=$restore_cmd -b $the_backup_id -r
-#--let $restore_cmd=$restore_cmd --backup_path=$NDB_BACKUPS-$the_backup_id
-# for ndb 6.3:
---let $restore_cmd=$NDB_TOOLS_DIR/ndb_restore --no-defaults
+--let $restore_cmd=$NDB_RESTORE --no-defaults
 --let $restore_cmd=$restore_cmd --disable-indexes --rebuild-indexes
 --let $restore_cmd=$restore_cmd -b $the_backup_id -r
---let $restore_cmd=$restore_cmd --backup_path=$NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id
+--let $restore_cmd=$restore_cmd --backup_path=$NDB_BACKUPS-$the_backup_id
 
 --echo ************************************************************
 --echo * Restoring databases with no rewrite (sanity check)

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2011-01-14 14:26:14 +0000
+++ b/mysql-test/t/join_outer.test	2011-02-02 07:47:52 +0000
@@ -1107,4 +1107,128 @@ SELECT * FROM
 
 DROP TABLE t1,t2,t3;
 
+
+--echo #
+--echo # Bug#58490: Incorrect result in multi level OUTER JOIN
+--echo # in combination with IS NULL
+--echo #
+
+CREATE TABLE t1 (i INT NOT NULL);
+INSERT INTO t1 VALUES (0),    (2),(3),(4);
+CREATE TABLE t2 (i INT NOT NULL);
+INSERT INTO t2 VALUES (0),(1),    (3),(4);
+CREATE TABLE t3 (i INT NOT NULL);
+INSERT INTO t3 VALUES (0),(1),(2),    (4);
+CREATE TABLE t4 (i INT NOT NULL);
+INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( t2 LEFT JOIN
+   ( t3 LEFT JOIN
+     t4
+     ON t4.i = t3.i
+   )
+   ON t3.i = t2.i
+ )
+ ON t2.i = t1.i
+ ;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( t2 LEFT JOIN
+   ( t3 LEFT JOIN
+     t4
+     ON t4.i = t3.i
+   )
+   ON t3.i = t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t4.i IS NULL;
+
+
+# Most simplified testcase to reproduce the bug.
+# (Has to be at least a two level nested outer join)
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+
+# Extended testing:
+# We then add some equi-join inside the query above:
+# (There Used to be some problems here with first
+#  proposed patch for this bug)
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN t4
+   ON t4.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
+   ON t4a.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN (t4 AS t4a, t4 AS t4b)
+   ON t4a.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+
+DROP TABLE t1,t2,t3,t4;
+
+## Bug#49322 & bug#58490 are duplicates. However, we include testcases
+## for both.
+--echo #
+--echo # Bug#49322(Duplicate): Server is adding extra NULL row
+--echo # on processing a WHERE clause
+--echo #
+
+CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
+INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
+
+CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
+INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
+CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
+INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
+
+# Baseline query wo/ 'WHERE ... IS NULL' - was correct
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
+
+# Adding 'WHERE ... IS NULL' -> incorrect result
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
+WHERE TABLE1.pk IS NULL;
+
+DROP TABLE h,m,k;
+
 --echo 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();

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-14 15:08:09 +0000
+++ b/sql/sql_select.cc	2011-02-02 07:47:52 +0000
@@ -11598,17 +11598,44 @@ evaluate_join_record(JOIN *join, JOIN_TA
       first_unmatched->found= 1;
       for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
       {
-        if (tab->table->reginfo.not_exists_optimize)
-          return NESTED_LOOP_NO_MORE_ROWS;
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
           will be re-evaluated again. It could be fixed, but, probably,
           it's not worth doing now.
         */
+#ifndef MCP_BUG58490
+        /*
+          not_exists_optimize has been created from a
+          select_cond containing 'is_null'. This 'is_null'
+          predicate is still present on any 'tab' with
+          'not_exists_optimize'. Furthermore, the usual rules
+          for condition guards also applies for
+          'not_exists_optimize' -> When 'is_null==false' we
+          know all cond. guards are open and we can apply
+          the 'not_exists_optimize'.
+        */
+        DBUG_ASSERT(!(tab->table->reginfo.not_exists_optimize &&
+                     !tab->select_cond));
+#endif
+
         if (tab->select_cond && !tab->select_cond->val_int())
         {
           /* The condition attached to table tab is false */
+
+#ifndef MCP_BUG58490
+          if (tab->table->reginfo.not_exists_optimize)
+          {
+            /*
+              When not_exists_optimize is set: No need to further
+              explore more rows of 'tab' for this partial result.
+              Any found 'tab' matches are known to evaluate to 'false'.
+              Returning .._NO_MORE_ROWS will skip rem. 'tab' rows.
+            */
+            return NESTED_LOOP_NO_MORE_ROWS;
+          }
+#endif
+
           if (tab == join_tab)
             found= 0;
           else

No bundle (reason: revision is a merge).
Thread
bzr commit into mysql-5.1-telco-7.1 branch (magnus.blaudd:4075) Magnus Blåudd2 Feb