MySQL Bugs: #84812: Return inconsistent result when using inline SELECT vs VIEW for left join

Bug #84812	Return inconsistent result when using inline SELECT vs VIEW for left join
Submitted:	3 Feb 2017 16:47	Modified:	25 Sep 2018 22:45
Reporter:	Joshua Hui	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5/5.6/5.7?/8.0?	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We are trying to use left join with view to eliminate rows from one table. However, we experience some unexpected behavior (invalid result) using the view vs. the inline query.

How to repeat:
Here is an example.

CREATE TABLE T1 (c1 INT NOT NULL, c2 VARCHAR(10) NOT NULL);
CREATE TABLE T2 (c1 INT NOT NULL, c2 VARCHAR(10) NOT NULL);

INSERT INTO T1 VALUES (100, 't1 row 1'), (200, 't1 row 2'), (300, 't1 row 3');
COMMIT;

CREATE VIEW T2_V 
AS SELECT *, 'MATCH' matching FROM T2;

-- Different Result
-- See all the T1 rows. Expected
SELECT * FROM T1 t1 LEFT JOIN (SELECT *, 'MATCH' matching FROM T2) t2 
ON t1.c1 = t2.c2 AND t1.c2 = t2.c2 
WHERE t2.matching IS NULL;

-- See NO row return. Wrong??? Should return the same result as above
SELECT * FROM T1 t1 LEFT JOIN T2_V t2 
ON t1.c1 = t2.c2 AND t1.c2 = t2.c2 
WHERE t2.matching IS NULL;

Thank you for the bug report. Verified as described for 5.5/5.6 but for 5.7/8.0 both queries returned empty set.

To confirm, it is a bug in 5.7 and 8? since it should return all the rows in T1.

Posted by developer:
 
Hi Joshua,

Yes, this is a bug in 5.7/8.0.  As you say, all rows in T1 should be returned.

There are a few workarounds: 
In all version, the query using the view will give correct result if view is created with "CREATE ALGORITHM=TEMPTABLE VIEW ..."

For 5.7/8.0, both queries will give the correct result by setting optimizer_switch='derived_merge=off'

In 8.0 the NO_MERGE hint can be used to get correct behavior in both cases:
SELECT /*+ NO_MERGE(t2) */ ...

8.0.12 returns correct result now.

Version 5.7.21 too.

This is probably a duplicate of 26627181, which was fixed in 5.7.21 and 8.0.4.