Bug #58422 Incorrect result when OUTER JOIN'ing with an empty table
| Submitted: | 23 Nov 2010 16:39 | Modified: | 24 Jan 2011 15:12 |
|---|---|---|---|
| Reporter: | Ole John Aske | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | next-mr, 5.5, 5.1.x | OS: | Any |
| Assigned to: | Ole John Aske | CPU Architecture: | Any |
[23 Nov 2010 16:42]
Valeriy Kravchuk
Verified as described:
macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 306
Server version: 5.5.8-rc-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 't2'
mysql> create table t_empty(pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.06 sec)
mysql> create table t1(pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.17 sec)
mysql> insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table t2(pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t2 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> select *
-> from
-> t1
-> left outer join
-> (t2 inner join t_empty on true)
-> on t1.pk=t2.pk
-> where t2.pk <> 2;
+----+------+------+
| pk | pk | pk |
+----+------+------+
| 1 | 1 | NULL |
| 3 | 3 | NULL |
+----+------+------+
2 rows in set (0.04 sec)
[23 Nov 2010 16:43]
Ole John Aske
There is a similar problem for the query:
select *
from
t1
left outer join
(t2 inner join t_empty on t_empty.pk is NULL)
on t1.pk=t2.pk
where t2.pk <> 2;
While this variant return the correct result (Empty result as 'where t2.pk <> 2 will be 'not true' as all t2.pk==NULL)
select *
from
t1
left outer join
(t2 inner join t_empty on t_empty.pk is NOT NULL)
on t1.pk=t2.pk
where t2.pk <> 2;
[7 Dec 2010 11:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126195 3477 Ole John Aske 2010-12-07 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*).
[7 Dec 2010 11:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126196 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*).
[12 Jan 2011 16:26]
Omer Barnir
triage - discussion changing SRMRTBD to SR55MRU as risk is now and impact can be higher (not 5.1 as code will be somewhat different
[12 Jan 2011 20:24]
Guilhem Bichot
The discussion about "outer join converted to inner join" in the commit comment reminds me of http://bugs.mysql.com/bug.php?id=53793 where the conversion seems to not update table->null_row.
[13 Jan 2011 10:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128626 3498 Ole John Aske 2011-01-13 [merge] Merge of fix for bug#58422 from mysql-5.5 -> mysql-trunk
[13 Jan 2011 10:50]
Ole John Aske
Pushed into mysql-5.5 and mysql-trunk
[14 Jan 2011 11:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128721 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*).
[14 Jan 2011 11:15]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:ole.john.aske@oracle.com-20110114111426-esdkcnm8d0ovau6b) (version source revid:ole.john.aske@oracle.com-20110114111426-esdkcnm8d0ovau6b) (merge vers: 5.1.51-ndb-7.0.21) (pib:24)
[22 Jan 2011 22:46]
Paul DuBois
Noted in 5.1.51-ndb-7.0.21 changelog. Outer joins with an empty table could produce incorrect results. Setting report to Need Merge pending push to 5.5.x, 5.6.x.
[23 Jan 2011 12:26]
Ole John Aske
This fix *has* been pushed into 5.5.x and trunk by the pushes: http://lists.mysql.com/commits/128631 http://lists.mysql.com/commits/128630 3238 Ole John Aske 2011-01-13 Fix for #58422: Incorrect result when OUTER JOIN'ing with an empty table. However, as the commit message incorrectly contained only '#58422' it has not been picked up by bazaar as a fix for bug#58422. Please check, and change status from 'Need merge' -> 'Close'.
[23 Jan 2011 16:34]
Ole John Aske
Set back to 'Documenting' - see previous comment.
[24 Jan 2011 15:12]
Paul DuBois
Noted in 5.5.10, 5.6.2 changelogs.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When an empty table is part of the outer joined tables the result is incorrect. How to repeat: create table t_empty(pk int primary key) engine = myisam; create table t1(pk int primary key) engine = myisam; insert into t1 values (1), (2), (3); create table t2(pk int primary key) engine = myisam; insert into t2 values (1), (2), (3); select * from t1 left outer join (t2 inner join t_empty on true) on t1.pk=t2.pk where t2.pk <> 2; +----+------+------+ | pk | pk | pk | +----+------+------+ | 1 | 1 | NULL | | 3 | 3 | NULL | +----+------+------+ The sub join '(t2 inner join t_empty on true)' can't produce any rows as table 't_empty' is .... empty. The correct result for this join should be : T1.pk, NULL, NULL