On 4/3/18, Richard Hipp <d...@sqlite.org> wrote:
>
> Probably there will be a 3.23.1 patch release later today.
>
Or, maybe not.
If the series.c file is compiled with -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 then
the generate_series() virtual table behaves correctly, and Edzard's
example gives a correct answer both before and after the new LEFT JOIN
strength reduction optimization is added.
Without the -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 compile-time option,
the test case consistently gives the wrong answer. Here is an
alternative test case:
WITH
t1(x) AS (VALUES(1),(2)),
t2(y,z) AS (VALUES(2,1))
SELECT * FROM t1 LEFT JOIN t2 ON x=y JOIN generate_series
WHERE start=z AND stop=2;
The query above should return only two rows:
2 2 1 1
2 2 1 2
But it instead returns 5 rows, because the generate_series virtual
table is telling the code generate that it does not need to check the
start=z constraint. When the start=z constraint is not checked, then
indeed 5 rows are generated because the query becomes equivalent to
this:
WITH
t1(x) AS (VALUES(1),(2)),
t2(y,z) AS (VALUES(2,1))
SELECT * FROM t1 LEFT JOIN t2 ON x=y JOIN generate_series
WHERE start=coalesce(z,0) AND stop=2;
I'm testing a patch now that causes the LEFT JOIN strength reduction
optimization to assume that NULL arguments to a virtual table
constraint can return a TRUE result. But I'm wondering, since this is
really a work-around to problems in virtual table implementations, if
this change warrants a patch release?
Your thoughts?
Should we issue 3.23.1 just to work around dodgy virtual table
implementations? Or should we just check-in the change and let those
who want to continue using their dodgy virtual tables either patch the
issue themselves or wait for 3.24.0?
--
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
