| Bug #51598 | Inconsistent behaviour with a COALESCE statement inside an IN comparison | ||
|---|---|---|---|
| Submitted: | 1 Mar 2010 9:09 | Modified: | 18 Jun 2010 1:31 |
| Reporter: | Mark George | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.91, 5.1.37, 5.1.45 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | COALESCE, IN, inconsistent, join | ||
[1 Mar 2010 15:31]
Valeriy Kravchuk
Verified just as described:
mysql> CREATE TABLE X (userId INT, groupId INT, x INT);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO X VALUES (NULL, 1, 2), (1, NULL, 3);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE GroupUserMap (userId INT NOT NULL, groupId INT NOT NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO GroupUserMap VALUES (2, 1);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT *, (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)) FROM X LEFT JOIN
-> GroupUserMap G ON X.groupId = G.groupId;
+--------+---------+------+--------+---------+-------------------------------------------------------+
| userId | groupId | x | userId | groupId | (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)) |
+--------+---------+------+--------+---------+-------------------------------------------------------+
| NULL | 1 | 2 | 2 | 1 | 1 |
| 1 | NULL | 3 | NULL | NULL | 1 |
+--------+---------+------+--------+---------+-------------------------------------------------------+
2 rows in set (0.03 sec)
mysql> SELECT * FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId WHERE
-> (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2));
+--------+---------+------+--------+---------+
| userId | groupId | x | userId | groupId |
+--------+---------+------+--------+---------+
| NULL | 1 | 2 | 2 | 1 |
+--------+---------+------+--------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId;
+--------+---------+------+--------+---------+
| userId | groupId | x | userId | groupId |
+--------+---------+------+--------+---------+
| NULL | 1 | 2 | 2 | 1 |
| 1 | NULL | 3 | NULL | NULL |
+--------+---------+------+--------+---------+
2 rows in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.91-debug |
+--------------+
1 row in set (0.02 sec)
[3 Mar 2010 7: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/102143 3345 Sergey Glukhov 2010-03-03 Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison Optimizer erroneously translated LEFT JOIN into INNER JOIN. It leads to cutting rows with NULL right side. It happens because Item_row uses not_null_tables() method form the base(Item) class and does not calculate 'null tables' properly. The fix is adding calculation of 'null tables' to Item_row. @ mysql-test/r/join_outer.result test case @ mysql-test/t/join_outer.test test case @ sql/item_row.cc adding calculation of 'null tables' to Item_row. @ sql/item_row.h adding calculation of 'null tables' to Item_row.
[19 Mar 2010 6:21]
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/103756 3409 Sergey Glukhov 2010-03-19 Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison Optimizer erroneously translated LEFT JOIN into INNER JOIN. It leads to cutting rows with NULL right side. It happens because Item_row uses not_null_tables() method form the base(Item) class and does not calculate 'null tables' properly. The fix is adding calculation of 'not null tables' to Item_row. @ mysql-test/r/join_outer.result test result @ mysql-test/t/join_outer.test test case @ sql/item_row.cc adding calculation of 'not null tables' to Item_row. @ sql/item_row.h adding calculation of 'not null tables' to Item_row.
[26 Mar 2010 8:21]
Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:25]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 7:58]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:john.embretsen@sun.com-20100322090656-w4ixy7p67fb3vr29) (merge vers: 5.1.46) (pib:16)
[12 Apr 2010 22:36]
Paul DuBois
Noted in 5.1.46, 5.5.5, 6.0.14 changelogs. The optimizer performed an incorrect join type when COALESCE() appeared within an IN() operation.
[17 Jun 2010 12:04]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:48]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:31]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: The results of an IN comparison that contains a COALESCE call are different, depending on whether it appears as a result column or inside a WHERE condition. eg: These two queries appear to interpret the IN comparison differently: > SELECT *, (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)) FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId; > SELECT * FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId WHERE (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)); How to repeat: Create some simple test tables and data: CREATE TABLE X (userId INT, groupId INT, x INT); INSERT INTO X VALUES (NULL, 1, 2), (1, NULL, 3); CREATE TABLE GroupUserMap (userId INT NOT NULL, groupId INT NOT NULL); INSERT INTO GroupUserMap VALUES (2, 1); Next, run these two queries: mysql> SELECT *, (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)) FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId; +--------+---------+------+--------+---------+-------------------------------------------------------+ | userId | groupId | x | userId | groupId | (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)) | +--------+---------+------+--------+---------+-------------------------------------------------------+ | NULL | 1 | 2 | 2 | 1 | 1 | | 1 | NULL | 3 | NULL | NULL | 1 | +--------+---------+------+--------+---------+-------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId WHERE (COALESCE(X.userId, G.userId), x) IN ((1, 3), (2, 2)); +--------+---------+------+--------+---------+ | userId | groupId | x | userId | groupId | +--------+---------+------+--------+---------+ | NULL | 1 | 2 | 2 | 1 | +--------+---------+------+--------+---------+ 1 row in set (0.00 sec) Both queries contain the same COALESCE/IN comparison. In the first example, it is true for both rows, in the second example it is only true for one of the rows. Suggested fix: The correct result for both queries in the above examples, would evaluate TRUE for both COALESCE/IN clauses. In the mean time, you can work around this problem by using a temporary table to handle the COALESCE. eg: mysql> SELECT * FROM -> ( -> SELECT X.*, COALESCE(X.userId, G.userId) coUserId -> FROM X LEFT JOIN GroupUserMap G ON X.groupId = G.groupId -> ) X -> WHERE (coUserId, x) IN ((1, 3), (2, 2)); +--------+---------+------+----------+ | userId | groupId | x | coUserId | +--------+---------+------+----------+ | NULL | 1 | 2 | 2 | | 1 | NULL | 3 | 1 | +--------+---------+------+----------+ 2 rows in set (0.00 sec)