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.
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.