Description:
A query of the form
(SELECT * FROM t1) l RIGHT JOIN (SELECT * FROM t0) r ON r.c0 = 1 LEFT JOIN t0 z ON l.c0 = r.c0
returns 7 rows instead of 11. The same query with the base tables instead of the derived tables returns the correct 11 rows, and the RIGHT JOIN alone returns the correct 7 rows.
The result contains a row that cannot exist: (l.c0 = 1, r.c0 = 5). r.c0 = 5 does not satisfy the ON condition r.c0 = 1, so l must be NULL-extended for that row. At the same time the rows of l with c0 = 2 and 3 are missing for r.c0 = 1.
EXPLAIN FORMAT=TREE shows the reason: the first join is "Left hash join (no condition)" and the inner table l gets "Filter: (t1.c0 = 1)". The ON condition r.c0 = 1 has been replaced by l.c0 = 1, apparently through the equality l.c0 = r.c0 of the following LEFT JOIN, which belongs to z's ON clause and must not be used to rewrite the earlier ON clause.
It is not specific to RIGHT JOIN: written as "(SELECT * FROM t0) r LEFT JOIN (SELECT * FROM t1) l ON r.c0 = 1 LEFT JOIN t0 z ON l.c0 = r.c0" the query also returns 7. A derived table on the outer side (r) is enough; with only l as a derived table the result is correct.
MariaDB 13.1 returns 11 rows for both forms. Reproduced on 26.7.1 and 9.7.2.
How to repeat:
CREATE TABLE t1 (c0 INT);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 VALUES (1), (1), (5);
SELECT COUNT(*) FROM t1 l RIGHT JOIN t0 r ON r.c0 = 1 LEFT JOIN t0 z ON l.c0 = r.c0;
-- 11 correct
SELECT COUNT(*) FROM (SELECT * FROM t1) l RIGHT JOIN (SELECT * FROM t0) r ON r.c0 = 1 LEFT JOIN t0 z ON l.c0 = r.c0;
-- expected: 11
-- actual: 7
SELECT l.c0, r.c0, z.c0
FROM (SELECT * FROM t1) l RIGHT JOIN (SELECT * FROM t0) r ON r.c0 = 1 LEFT JOIN t0 z ON l.c0 = r.c0
ORDER BY 1, 2, 3;
-- expected (as with base tables):
-- (NULL,5,NULL), (1,1,1) x4, (1,1,5) x2, (2,1,NULL) x2, (3,1,NULL) x2
-- actual:
-- (1,1,1) x4, (1,1,5) x2, (1,5,NULL)
EXPLAIN FORMAT=TREE
SELECT COUNT(*) FROM (SELECT * FROM t1) l RIGHT JOIN (SELECT * FROM t0) r ON r.c0 = 1 LEFT JOIN t0 z ON l.c0 = r.c0;
-- -> Aggregate: count(0)
-- -> Left hash join (no condition), extra conditions: (t1.c0 = t0.c0)
-- -> Left hash join (no condition)
-- -> Table scan on t0
-- -> Hash
-- -> Filter: (t1.c0 = 1)
-- -> Table scan on t1
-- -> Hash
-- -> Table scan on z
