MySQL Bugs: #94206: IFNULL expression leaks out of outer join when using "distinct" or "group by"

Bug #94206	IFNULL expression leaks out of outer join when using "distinct" or "group by"
Submitted:	5 Feb 2019 14:12	Modified:	5 Feb 2019 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

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)

Thank you for the bug report. Verified as described.

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.

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) */