4108 Ole John Aske 2011-01-14
Backport to mysql-5.1-telco-7.0:
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 thatresultset will be empty,
and 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 partial *execution plan* (not *resultset*).
modified:
mysql-test/r/select.result
mysql-test/t/select.test
sql/sql_select.cc
4107 Ole John Aske 2011-01-14
Backport to mysql-5.1-telco-7.0:
Fix for bug#58134: 'Incorrectly condition pushdown inside subquery to NDB engine'
An incorrect 'table_map' containing both the table itself,
and possible any outer-refs if this was the last table in
the subquery, was presented to make_cond_for_table().
As a pushed condition is only able to refer column from the table
the condition is pushed to, nothing else than columns from the
table itself (tab->table->map) may be refered in the pushed
condition
constructed by 'push_cond= make_cond_for_table()'.
Also fix a minor 'copy and paste' bug in a comment
in make_cond_for_table().
modified:
mysql-test/suite/ndb/r/ndb_condition_pushdown.result
mysql-test/suite/ndb/t/ndb_condition_pushdown.test
sql/sql_select.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 2011-01-14 11:14:26 +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 2011-01-14 11:14:26 +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 2011-01-14 10:42:53 +0000
+++ b/sql/sql_select.cc 2011-01-14 11:14:26 +0000
@@ -11788,7 +11788,11 @@ 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;
+#ifndef MCP_BUG58422
+ if (!table->pos_in_table_list->outer_join || error > 0)
+#else
if (!table->maybe_null || error > 0)
+#endif
DBUG_RETURN(error);
}
}
@@ -11809,7 +11813,11 @@ 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;
+#ifndef MCP_BUG58422
+ if (!table->pos_in_table_list->outer_join || error > 0)
+#else
if (!table->maybe_null || error > 0)
+#endif
DBUG_RETURN(error);
}
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4107 to 4108)Bug#58422 | Ole John Aske | 14 Jan |
