On 26/01/18 19:35, Clemens Ladisch wrote:
Mark Brand wrote:
Shouldn't we expect subquery flattening to happen in V2 below?
-- no flattening
CREATE VIEW V2 AS
SELECT * FROM X
LEFT JOIN (
SELECT * FROM X
LEFT JOIN Y ON Y.a = X.a
) Z
ON Z.a = X.a;
-- manually flattened version of V2
CREATE VIEW V2_FLATTENED AS
SELECT *
FROM X
LEFT JOIN X X2
ON X2.a = X.a
LEFT JOIN Y
ON Y.a = X2.a;
In the general case, left outer joins are not associative. Apparently,
SQLite does not try to prove the opposite for special cases.
The simplified case above does not make obvious the usefulness of
flattening. A real world situation is where the right operand of a LEFT
JOIN is a handy reusable view with a LEFT JOIN whose left side provides
a useful index. For example:
-- handy reusable view
CREATE VIEW W AS
SELECT X.*, X.cost + IFNULL(Y.fee, 0) price
FROM X
LEFT JOIN Y
ON Y.c = X.c;
SELECT *
FROM X
LEFT JOIN W -- based on X
ON W.a = X.a
WHERE X.color = 'red';
W, by itself, might produce many more rows than the outer query and be
expensive. In cases like this, it's critical for performance that the
index of W be used for the LEFT JOIN in the outer query.
Without flattening, we have to go to some trouble to avoid using
otherwise handy logic-encapsulating views on the right side of a LEFT
JOIN. I've only recently realized this.
Would it make sense for sqlite to flatten this pattern? As far as I can
see, it satisfies all the conditions listed in
http://www.sqlite.org/optoverview.html#flattening.
Mark
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
