Invalid results on join and duplicate where clause (in-memory db only)

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Invalid results on join and duplicate where clause (in-memory db only)

Sebastian Zwack
Hi,

when performing a simple join and duplicating the where clause I get invalid rows back.

SQLite version 3.25.2

CREATE TABLE user ( id INTEGER NOT NULL, active INTEGER NOT NULL, account INTEGER, PRIMARY KEY(id) );
CREATE TABLE account ( id INTEGER NOT NULL, name TEXT, PRIMARY KEY(id) );
INSERT INTO user VALUES (1, 0, 1);
INSERT INTO account VALUES (1, 'test');

-- returns user 1, expecting no results
SELECT user.id FROM user INNER JOIN account ON user.account = account.id WHERE user.active = 1 AND user.active = 1;
-- returns user 1, expecting no results
SELECT user.id FROM user INNER JOIN account ON user.account = account.id WHERE user.active = True AND user.active = True;
-- returns no results, expecting no results
SELECT user.id FROM user INNER JOIN account ON user.account = account.id WHERE user.active is True AND user.active is True;

INSERT INTO user VALUES (2, 1, 2);
INSERT INTO account VALUES (2, '');

-- returns [1, 2], expecting 1
SELECT user.id FROM user INNER JOIN account ON user.account = account.id WHERE user.active = 1 AND user.active = 1;

This only affects the in-memory db, file based is working correctly. Do I miss something?

Regards
Sebastian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

Richard Hipp-3
On 10/25/18, Sebastian Zwack <[hidden email]> wrote:
> Hi,
>
> when performing a simple join and duplicating the where clause I get invalid
> rows back.

Thanks for the bug report and test case.  The ticket for this issue is
here: https://www.sqlite.org/src/tktview/cf5ed20fc8621b165

>
> SQLite version 3.25.2
>
> CREATE TABLE user ( id INTEGER NOT NULL, active INTEGER NOT NULL, account
> INTEGER, PRIMARY KEY(id) );
> CREATE TABLE account ( id INTEGER NOT NULL, name TEXT, PRIMARY KEY(id) );
> INSERT INTO user VALUES (1, 0, 1);
> INSERT INTO account VALUES (1, 'test');
>
> -- returns user 1, expecting no results
> SELECT user.id FROM user INNER JOIN account ON user.account = account.id
> WHERE user.active = 1 AND user.active = 1;
> -- returns user 1, expecting no results
> SELECT user.id FROM user INNER JOIN account ON user.account = account.id
> WHERE user.active = True AND user.active = True;
> -- returns no results, expecting no results
> SELECT user.id FROM user INNER JOIN account ON user.account = account.id
> WHERE user.active is True AND user.active is True;
>
> INSERT INTO user VALUES (2, 1, 2);
> INSERT INTO account VALUES (2, '');
>
> -- returns [1, 2], expecting 1
> SELECT user.id FROM user INNER JOIN account ON user.account = account.id
> WHERE user.active = 1 AND user.active = 1;
>
> This only affects the in-memory db, file based is working correctly. Do I
> miss something?
>
> Regards
> Sebastian
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

R Smith-2
In case it helps...

This problem can be distilled to simply:

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;
   --       a
   -- ------------
   --       1 (Not excpected)



-- It seems to have to do with the JOIN condition. Any change in it
fixes things:

SELECT x.a FROM x JOIN y ON x.c = 1 WHERE x.b = 1 AND x.b = 1;
   -- No results  (Expected)



-- When I tried re-making it using CTE's, the CTE version worked just fine:

WITH x(a,b,c) AS (
     VALUES (1,0,1)
), y(a,b) AS (
     VALUES (1,2)
)
SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
   -- No results  (Expected)


HTH,
Ryan



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

Richard Hipp-3
On 10/25/18, R Smith <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

Richard Hipp-3
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 <[hidden email]> wrote:

> On 10/25/18, R Smith <[hidden email]> 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
> [hidden email]
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

Sebastian Zwack
On Thu, Oct 25, 2018, at 16:20, Richard Hipp wrote:
> 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?
>

You're right, of course the origin is an ORM generating this duplicate clauses. And as I couldn't think of a bug in sqlite it took me some time to narrow that down.

Can confirm, this patch fixes the problem for me and my tests are running fine again.

Out of curiosity, why do in-memory and file based differ here? Are these query optimizations only done for the in-memory db?

Thanks,
Sebastian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

Richard Hipp-3
On 10/26/18, Sebastian Zwack <[hidden email]> wrote:
>
> Out of curiosity, why do in-memory and file based differ here? Are these
> query optimizations only done for the in-memory db?
>

I observe the problem regardless of whether the database is on-disk or
in-memory.  Perhaps your system is somehow using a different (older)
version of SQLite that predates the problem when accessing database
file from disk?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Invalid results on join and duplicate where clause (in-memory db only)

Sebastian Zwack
In reply to this post by Sebastian Zwack
On 10/26/18, Richard Hipp wrote:
> I observe the problem regardless of whether the database is on-disk or
> in-memory.  Perhaps your system is somehow using a different (older)
> version of SQLite that predates the problem when accessing database
> file from disk?

Yes, was using an UI which connected to an older SQLite. Case closed.
 
Regards,
Sebastian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users