#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åudd | 2 Feb |
