Hi, Shouldn't we expect subquery flattening to happen in V2 below?
Mark CREATE TABLE X ( a PRIMARY KEY, b ) WITHOUT ROWID; CREATE TABLE Y ( a PRIMARY KEY ) WITHOUT ROWID; /* WITH RECURSIVE Q AS ( SELECT 1 a UNION ALL SELECT a + 1 FROM Q WHERE a < 100000 ) INSERT INTO X (a, b) SELECT a, CHAR(a%255) FROM Q; */ -- flattening as expected CREATE VIEW V1 AS SELECT * FROM X LEFT JOIN ( SELECT X.* FROM X ) Z ON Z.a = X.a; -- 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; SELECT sqlite_version(); EXPLAIN QUERY PLAN SELECT * FROM V1; /* 0|0|0|SCAN TABLE X 0|1|1|SEARCH TABLE X USING PRIMARY KEY (a=?) */ EXPLAIN QUERY PLAN SELECT * FROM V2; /* 1|0|0|SCAN TABLE X 1|1|1|SEARCH TABLE Y USING PRIMARY KEY (a=?) 0|0|0|SCAN TABLE X 0|1|1|SEARCH SUBQUERY 1 AS Z USING AUTOMATIC COVERING INDEX (a=?) */ EXPLAIN QUERY PLAN SELECT * FROM V2_FLATTENED; /* 0|0|0|SCAN TABLE X 0|1|1|SEARCH TABLE X AS X2 USING PRIMARY KEY (a=?) 0|2|2|SEARCH TABLE Y USING PRIMARY KEY (a=?) */ _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
