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:
None 
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
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[1 Mar 2010 9:09] Mark George
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)
[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)