Am Fri, 14 Apr 2017 10:59:25 -0400 schrieb Richard Hipp: > 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.
Thank you Richard. I have to admit that it took me quite a while and also reading the comment for check-in [1838a59c] several times to really understand your explanation. Duh, that's tricky indeed! Wolfgang _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
