Bug #76327 Literal selected from derived table mentioned in outer join should be NULL

• Submitted: 15 Mar 2015 19:37
• Modified: 13 Jul 2015 17:00
• Reporter: Roy Lyseng
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.6
• OS: Any
• CPU Architecture: Any

[15 Mar 2015 19:37] Roy Lyseng

Description:
When selecting expressions from the inner tables of an outer join, we should return only NULL values when a row from the outer tables (a in example) does not match a row from the inner tables (dt in example).

This has used to fail when using views in the outer join (see e.g bug#65936), and since we now merge derived tables into the outer query, it now also fail when using derived tables. Hence, this is a regression in 5.7 due to the inclusion of WL#5275.

There are several existing bug reports that mention the use of views: bug#65936, bug#67014, bug#67300, bug#73953.

Notice also that not only literals, but also some expressions that cancel out underlying NULL values are vulnerable to this problem. The IF function is mentioned in two of the bug reports. The COALESCE function is also vulnerable, as well as conditions that use e.g. IS TRUE.

How to repeat:
create table a (id int);
create table b (id int);
insert into a(id) values (1);
select * from a left join (select 1 as one, id from b) as dt on a.id = dt.id;

[13 Jul 2015 17:00] Paul DuBois

Noted in 5.7.8, 5.8.0 changelogs.

Incorrect results could be produced tor views and derived tables on
the inner side of an outer join and from which non-nullable
expressions such as literals were selected.