Bug submission: left join filter on negated expression including NOTNULL

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

Bug submission: left join filter on negated expression including NOTNULL

And Clover
Hi,

     CREATE TABLE t0 (c0);
     CREATE TABLE t1 (c1);
     INSERT INTO t0 VALUES (1);
     SELECT c0
         FROM t0 LEFT JOIN t1 ON c1=c0
         WHERE NOT (c1 IS NOT NULL AND c1=2);

Expected result: (1)
Actual result: no rows returned

This appears to be a regression in 3.30; 3.29 and earlier give the
expected result.

Reproduced on Windows builds sqlite-dll-win32-x86-3300100,
sqlite-dll-win64-x64-3300100 and sqlite-dll-win64-x64-3300000 (via
Python sqlite3).

cheers,

--
And Clover
mailto:[hidden email]
https://www.doxdesk.com/
gtalk:chat?jid=[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: Bug submission: left join filter on negated expression including NOTNULL

Simon Slavin-3
On 1 Nov 2019, at 3:12pm, And Clover <[hidden email]> wrote:

>        WHERE NOT (c1 IS NOT NULL AND c1=2);
>
> Expected result: (1)
> Actual result: no rows returned

This is a cut-down example, right ?  You can't possibly mean to do that WHERE clause in production code.  It amounts to

    WHERE (c1 IS NULL) AND (C1 != 2)

I don't know how SQLite will evaluate that for all cases but I wouldn't be surprised to find zero rows returned.
_______________________________________________
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: Bug submission: left join filter on negated expression including NOTNULL

Simon Slavin-3
On 1 Nov 2019, at 4:17pm, Simon Slavin <[hidden email]> wrote:

> This is a cut-down example, right ?  You can't possibly mean to do that WHERE clause in production code.  It amounts to
>
>    WHERE (c1 IS NULL) AND (C1 != 2)

I'm so sorry.  It is actually

    WHERE (c1 IS NULL) OR (C1 != 2)

which could quite reasonably return rows.  However, the NULL possibility may be redundant.  I can't tell without tests.
_______________________________________________
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: Bug submission: left join filter on negated expression including NOTNULL

And Clover
On 01/11/2019 16:20, Simon Slavin wrote:
> It is actually
> WHERE (c1 IS NULL) OR (C1 != 2)
> which could quite reasonably return rows.

Yes, and with this OR filter the quoted example does indeed return rows.
The version with:

     WHERE NOT (c1 IS NOT NULL AND c1=2)

*should* be equivalent to your version, but in 3.30 does not return the
NULL values.

 > You can't possibly mean to do that WHERE clause in production code

I might not spell it like that myself, but a code generator would do it
(and much worse!). This example was simplified from a query generated by
a Django ORM queryset using `.exclude(nullable_joined_table__column=1)`,
for instance.

But yeah, expressions written in a less-than-tasteful style should
probably still work as specified by SQL92. ;-)

--
And Clover
mailto:[hidden email]
https://www.doxdesk.com/
gtalk:chat?jid=[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: Bug submission: left join filter on negated expression including NOTNULL

Keith Medcalf
In reply to this post by And Clover

On Friday, 1 November, 2019 09:12, And Clover <[hidden email]> wrote:

>     CREATE TABLE t0 (c0);
>     CREATE TABLE t1 (c1);
>     INSERT INTO t0 VALUES (1);
>     SELECT c0
>         FROM t0 LEFT JOIN t1 ON c1=c0
>         WHERE NOT (c1 IS NOT NULL AND c1=2);

>Expected result: (1)
>Actual result: no rows returned
>
>This appears to be a regression in 3.30; 3.29 and earlier give the
>expected result.

SQLite version 3.31.0 2019-11-01 16:38:18
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>      CREATE TABLE t0 (c0);
sqlite>      CREATE TABLE t1 (c1);
sqlite>      INSERT INTO t0 VALUES (1);
sqlite>      SELECT c0
   ...>          FROM t0 LEFT JOIN t1 ON c1=c0
   ...>          WHERE NOT (c1 IS NOT NULL AND c1=2);
1

Appears to be fixed in the current tip ...

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
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: Bug submission: left join filter on negated expression including NOTNULL

Keith Medcalf
In reply to this post by Simon Slavin-3

On Friday, 1 November, 2019 10:20, Simon Slavin <[hidden email]> wrote:

>On 1 Nov 2019, at 4:17pm, Simon Slavin <[hidden email]> wrote:

>    WHERE (c1 IS NULL) OR (C1 != 2)

> which could quite reasonably return rows.  However, the NULL possibility
> may be redundant.  I can't tell without tests.

The expression NOT (c1 IS NOT NULL AND c1 == 2)
is equivalent to c1 IS NULL OR c1 != 2
is equivalent to (c1 IS NOT 2)
or the original proper expression NOT (C1 IS 2)

and arises from the use of the '==' and '!=' rather that IS and IS NOT, and generating work-arounds to handle NULLs.  Work-arounds for handling NULLs are only required for non-equality tests since there is no standard operators handling nulls for the other comparison operators > < >= <=

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users