Bug #97477 incorrect results when left joining temp tables; null rows appear to have values
Submitted: 4 Nov 18:03 Modified: 4 Nov 18:42
Reporter: Greg Girty Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.20 OS:Linux
Assigned to: CPU Architecture:x86 (x86_64)
Tags: left join

[4 Nov 18:03] Greg Girty
Description:
Incorrect query results occur using SELECT DISTINCT
with LEFT JOIN onto a temp table with a column that is a constant value.

Null rows in the joined table, having values defined by constants, appear to have the constant instead of NULL 

How to repeat:

DROP TABLE IF EXISTS wat;
CREATE TABLE wat (
  `id` int(11)
) ENGINE=InnoDB;

insert into wat (id) values (1),(2);

SELECT distinct 
    wat.id,
    concat('',x.const) AS expression,
    x.const as constant
FROM wat
LEFT JOIN 
(SELECT `id`, 
  'x' AS const 
 FROM wat
 WHERE id > 1
 )
  AS x ON wat.id = x.id ;
;
## erroneous result in 5.7.20
+------+------------+----------+
| id   | expression | constant |
+------+------------+----------+
|    2 | x          | NULL     |
|    1 | x          | NULL     |
+------+------------+----------+

## correct result in 5.7.22
+------+------------+----------+
| id   | expression | constant |
+------+------------+----------+
|    2 | x          | NULL     |
|    1 | NULL       | NULL     |
+------+------------+----------+

Suggested fix:
The error appears specific to 5.7.20
Verified to not happen in 5.7.22 or 8.x.

As a workaround, omitting DISTINCT gives correct result.

The workaround we used in production was to replace 
   'x' AS const 
with
   if(id,'x',NULL) as const

The error appears to have been fixed in recent versions, this is submitted for future test cases.
[4 Nov 18:42] Miguel Solorzano
Thank you for the bug report. How you wrote it was already fixed on recent version.