On 4/14/17, Wolfgang Enzinger <sql...@enzinger.net> wrote: > > CREATE VIEW z AS SELECT > fk, > (flags&1) AS odd, > (flags&2)>>1 AS even, > (flags&4)>>2 AS prime > FROM y; > > Now using the VIEW z in a JOIN results in a full table scan on TABLE y > despite a WHERE clause and an appropriate INDEX: > > EXPLAIN QUERY PLAN SELECT x.pk,z.odd,z.even,z.prime > FROM x LEFT JOIN z ON x.pk=z.fk WHERE x.pk=2; > > Bypassing the VIEW however uses INDEX yy: > > EXPLAIN QUERY PLAN > SELECT x.pk,(y.flags&1) AS odd,(y.flags&2)>>1 AS even,(y.flags&4)>>2 AS > prime FROM x LEFT JOIN y ON x.pk=y.fk WHERE x.pk=2; >
Performing this rewrite of a view into a simple LEFT JOIN is trickier than it seems at first glance. The rewrite works for the example you provide. But subtle changes to the view can make the rewrite invalid. For example: CREATE VIEW z AS SELECT fk, coalesce(flags&1,0) AS odd, -- Added coalesce() (flags&2)>>1 AS even, (flags&4)>>2 AS prime FROM y; The addition of the coalesce() function on one of the result columns of the view means that a transformation such as you suggest will give a different (and incorrect) answer. This is just one of many examples of the subtle pitfalls involved in trying to convert a LEFT JOIN into a form that can make better use of indexes. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
