From f8697642d20415ea2e6e85c4090d7ef0dff40241 Mon Sep 17 00:00:00 2001 From: Ole John Aske Date: Thu, 13 Jan 2011 11:42:48 +0100 Subject: Fix for #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*). --- mysql-test/t/select.test | 70 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 70 insertions(+) (limited to 'mysql-test/t/select.test') diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 3ed7213e8d7..043b03e4686 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4123,6 +4123,76 @@ SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 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 --echo # -- cgit v1.2.1