Bug #84812 Return inconsistent result when using inline SELECT vs VIEW for left join

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.