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

[3 Feb 2017 16:47] Joshua Hui
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;
[3 Feb 2017 22:03] Miguel Solorzano
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.
[3 Feb 2017 22:52] Joshua Hui
To confirm, it is a bug in 5.7 and 8? since it should return all the rows in T1.
[7 Feb 2017 10:02] Øystein Grøvlen
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) */ ...
[7 Sep 2018 11:44] Sveta Smirnova
8.0.12 returns correct result now.
[7 Sep 2018 11:46] Sveta Smirnova
Version 5.7.21 too.
[25 Sep 2018 22:45] Roy Lyseng
This is probably a duplicate of 26627181, which was fixed in 5.7.21 and 8.0.4.