Details
-
Task
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
In TODO-4513 a customer noticed that their previous MySQL 5.5 simplified LEFT JOIN to INNER JOIN in the subquery from the test case below, whereas MariaDB did not.
CREATE TABLE t1 (a INT, b INT);
|
CREATE TABLE t2 (a INT, b INT);
|
CREATE TABLE t3 (a INT, b INT);
|
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
|
INSERT INTO t3 VALUES (1,1), (2,2), (3,3);
|
|
|
set optimizer_switch='semijoin=off';
|
|
|
EXPLAIN EXTENDED SELECT 1
|
FROM t1
|
WHERE a = 1 AND b IN
|
(SELECT t3.a
|
FROM t2 LEFT JOIN t3 ON t3.b = t2.a
|
WHERE t2.b = 1);
|
|
|
DROP TABLE t1, t2, t3;
|
While investigating the difference we have found out that MySQL's logic of processing subqueries is different, and that allows them to simplify the join. Is it possible to implement the simplification for MariaDB?