Mark Brand wrote:
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
Hello, may be I'm wrong or the documentation is wrong:
If the subquery is not the right operand of a LEFT JOIN then
the subquery may not be a join, and
the FROM clause of the subquery may not contain a virtual table, and
the outer query may not be an aggregate.
The "not" in the first line does not belong there?
Thanks.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
