A preliminary fix for this problem is here:
https://www.sqlite.org/src/info/5d5b596f152bb278
I have more testing and analysis to do before I close the ticket, but
my current belief is that this is the correct fix. I don't know yet
whether or not this justifies a patched 3.25.3 release.
Sebastian: Presumably this problem arose for you in a much larger and
more complex application. Can you please apply the patch from the
check-in above (or recompile using the latest trunk version of SQLite)
and verify for us that this fixes the problem for you?
On 10/25/18, Richard Hipp <[email protected]> wrote:
> On 10/25/18, R Smith <[email protected]> wrote:
>>
>> CREATE TABLE x(a, b, c);
>> CREATE TABLE y(a, b);
>> INSERT INTO x VALUES (1, 0, 1);
>> INSERT INTO y VALUES (1, 2);
>>
>> SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
>
> It also starts working if you remove the redundant x.b=1 constraint:
>
> SELECT x.a FROM x JOIN y ON x.c=y.a WHERE x.b=1;
>
> This came about due to an optimization that tries to propagate
> constants that are defined by WHERE clause constraints. So if you
> have a WHERE clause constraint like "x.b=1" you can go through and
> replace every "x.b" expression with the constant "1". But because of
> the way this is implemented, the original query gets transformed into:
>
> SELECT x.a FROM x JOIN y ON x.c=y.a WHERE 1=1 AND 1=1;
>
> The first "x.b=1" terms causes the second to change into "1=1" and the
> second causes the first to change into "1=1". This can only happen if
> there are two or more "x.b=" constraints in the WHERE clause, which is
> apparently not one of the millions of test cases in our test suite.
> (It will be soon!)
>
> --
> D. Richard Hipp
> [email protected]
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
