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
.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
