On 4/3/18, 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?
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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
