On 4/3/18, E.Pasma <pasm...@concepts.nl> 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?
Having pondered this overnight, I decided that it should probably be fixed. There is now a ticket: https://www.sqlite.org/src/info/fac496b61722daf2 Probably there will be a 3.23.1 patch release later today. > > 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 > > .version > SQLite 3.23.0 2018-03-24 13:24:02 > cf171abe954a5f25262161dd69f2e8cecdbf9446c3f6b298201507dbc743567e > zlib version 1.2.3 > gcc-4.0.1 (Apple Inc. build 5465) > .load series > create table t1(x); > create table t2(x,y); > insert into t1 values(1),(2); > insert into t2 values(1,4); > select t1.x, value > from t1 > left join t2 using (x) > join generate_series on start=t2.y and stop=5; > 1|4 > 1|5 > > Same script in earlier version with echo off. > > .version > SQLite 3.22.0 2018-01-09 15:28:25 > 6b3f4349d7156501f05270d494d6002ee03a0860b6e77b46d17effcab027b753 > .echo off > 1|4 > 1|5 > 2|0 > 2|1 > 2|2 > 2|3 > 2|4 > 2|5 > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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