Bug #94206 IFNULL expression leaks out of outer join when using "distinct" or "group by"
Submitted: 5 Feb 14:12 Modified: 5 Feb 15:44
Reporter: Jordi Salvat i Alabart Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7/8.0 OS:Ubuntu (5.7.25-0ubuntu0.18.04.2)
Assigned to: CPU Architecture:Any

[5 Feb 14:12] Jordi Salvat i Alabart
Description:
I have a query with a LEFT OUTER JOIN where some rows in the left table don't have a corresponding row in the right table. This produces nulls for the columns of the right table, exactly as expected.

But then, when I add a "GROUP BY" clause on a column of the right table which is defined using IFNULL, the value of the 2nd parameter of the IFNULL pops up.

I'm labeling this issue as critical because it is, to my understanding, a breach of the relational algebra: grouping should never produce value which were not in the ungrouped table. Note however that there is a workaround, at least for my use case.

How to repeat:
mysql> select
    ->   y
    -> from
    ->  (select 1 from dual) t1
    ->   left outer join (
    ->     select ifnull(x, 2) as y from (select 1 as x from dual) t3
    ->  ) t2 on 1=2
    -> group by y;
+------+
| y    |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

Whereas removing the "group by" clause will yield NULL instead of 2.

Suggested fix:
Wrapping the clause in a dummy SELECT before grouping works around the issue:

mysql> select * from (
    -> select
    ->   y
    -> from
    ->  (select 1 from dual) t1
    ->   left outer join (
    ->     select ifnull(x, 2) as y from (select 1 as x from dual) t3
    ->  ) t2 on 1=2
    -> ) t4
    -> group by y;
+------+
| y    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
[5 Feb 15:21] Miguel Solorzano
Thank you for the bug report. Verified as described.
[5 Feb 15:44] Jordi Salvat i Alabart
My workaround eventually led me to an error

  SQL Error [1062] [23000]: Duplicate entry '2019-01-28-NULL' for key '<group_key>'

Sorry I can't provide more details: I don't have the time to research further -- I need to move on. So I've replaced NULL values with a marker value so I won't need those IFNULL expressions.
[6 Feb 8:23] Roy Lyseng
Hi Jordi,

you can workaround the issue by forcing a materialization of the derived table, until there is a proper fix:

set optimizer_switch='derived_merge=off';

For 8.0, you can add an optimizer hint after the select keyword: /*+ no_merge(t2) */