I think your left join reduction regression change happens on any vtable hidden column filter reference to an outer scope column. A CTE duplicates your finding below.
SQLite 3.23.0 2018-04-02 11:04:16 736b53f57f70b23172c30880186dce 7ad9baa3b74e3838cae5847cffb98f5cd2 sqlite> WITH t1(x) AS (VALUES(1),(2)), t2(x,y) AS (VALUES(1,4)) SELECT x,value FROM t1 LEFT JOIN t2 USING(x) JOIN generate_series ON start=t2.y AND stop=5; 1,4 1,5 SQLite 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25 e10677046ee3da1d5b1581e86726f2alt1 sqlite> WITH t1(x) AS (VALUES(1),(2)), t2(x,y) AS (VALUES(1,4)) SELECT x,value FROM t1 LEFT JOIN t2 USING(x) JOIN generate_series ON start=t2.y AND stop=5; x,value 1,4 1,5 2,0 2,1 2,2 2,3 2,4 2,5 Peter On Tue, Apr 3, 2018 at 12:22 AM, E.Pasma <[email protected]> wrote: > Hello, below is a case where the generate_series virtual table behaves > differently with SQLite 3.23. It is a purely artificial case. Does anyoone > have an opinion about it? > > The issue comes from the fact that generate_series does not check if a > supposed integer input value happens to be NULL. And continues as if zero > is passed in. This way a WHERE condition like > > <VT column>=<expression that gives NULL> > > can be true. Regular tables require "IS" instead of "=" here. > Theoretically the left join reduction prover can now be misleaded. As is > the case below, > > Is this a (obscure) bug in generate_series? > > E Pasma > > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
