3389 Ole John Aske 2010-12-07
SPJ-scan-scan: Cherry picked fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
Fixed incorrect checks in join_read_const_table() for when to
accept a non-existing, or empty const-row as a part of the const'ified
set of tables.
Intention of this test is to only accept NULL-rows if this table is outer joined
into the resultset. (In case of an inner-join we can conclude at this point that
resultset will be empty, end we want to return 'error' to signal this.)
Initially 'maybe_null' is set to the same value as 'outer_join' in
setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
attemp to replace outer joins by inner join whenever possible. This
will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
to reflect this rewrite as this field is used to currectly set the 'nullability'
property for the columns in the resultset.
We should therefore change join_read_const_table() to check the 'outer_join'
property instead of 'maybe_null', as this correctly reflect the nullability of
the *execution plan* (not *resultset*).
modified:
mysql-test/r/select.result
mysql-test/t/select.test
sql/sql_select.cc
3388 Ole John Aske 2010-12-06
SPJ-scan-scan: Cherry picked fix for bug#58750.
If ha_ndbcluster::read_range_first_to_buf() had to do a ::full_table_scan()
on a UNIQUE_INDEX, the resulting ScanOperation was not properly closed at end
of its lifetime. If lots of such operations is executed within the
same transaction, we will eventually run out of operation or transaction objects.
(Transaction objects is due to a dumnmy Hupp'ed transaction object is allocated as
part of a ScanOperation)
This fix ensures that ::close_scan() is called for any open cursors at start of
ha_ndbcluster::read_range_first_to_buf().
modified:
mysql-test/suite/ndb/r/ndb_index_unique.result
mysql-test/suite/ndb/t/ndb_index_unique.test
sql/ha_ndbcluster.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2010-06-24 08:00:48 +0000
+++ b/mysql-test/r/select.result 2010-12-07 11:56:10 +0000
@@ -4797,4 +4797,68 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
1
1
DROP TABLE t1;
+#
+# Bug #58422: Incorrect result when OUTER JOIN'ing
+# with an empty table
+#
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+EXPLAIN
+SELECT *
+FROM
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT *
+FROM
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk i pk i pk i
+EXPLAIN
+SELECT *
+FROM
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT *
+FROM
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk i pk i pk i
+EXPLAIN
+SELECT *
+FROM
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT *
+FROM
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk i pk i pk i
+DROP TABLE t1,t2,t_empty;
End of 5.1 tests
=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test 2010-06-24 17:13:08 +0000
+++ b/mysql-test/t/select.test 2010-12-07 11:56:10 +0000
@@ -4088,4 +4088,74 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
DROP TABLE t1;
+
+--echo #
+--echo # Bug #58422: Incorrect result when OUTER JOIN'ing
+--echo # with an empty table
+--echo #
+
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+
+EXPLAIN
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON TRUE)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON TRUE)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 CROSS JOIN t_empty)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 CROSS JOIN t_empty)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+
+
+DROP TABLE t1,t2,t_empty;
+
+
--echo End of 5.1 tests
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-12-03 14:55:15 +0000
+++ b/sql/sql_select.cc 2010-12-07 11:56:10 +0000
@@ -11920,7 +11920,7 @@ join_read_const_table(JOIN_TAB *tab, POS
/* Mark for EXPLAIN that the row was not found */
pos->records_read=0.0;
pos->ref_depend_map= 0;
- if (!table->maybe_null || error > 0)
+ if (!table->pos_in_table_list->outer_join || error > 0)
DBUG_RETURN(error);
}
}
@@ -11941,7 +11941,7 @@ join_read_const_table(JOIN_TAB *tab, POS
/* Mark for EXPLAIN that the row was not found */
pos->records_read=0.0;
pos->ref_depend_map= 0;
- if (!table->maybe_null || error > 0)
+ if (!table->pos_in_table_list->outer_join || error > 0)
DBUG_RETURN(error);
}
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3388 to 3389) Bug#58422 | Ole John Aske | 7 Dec |
