Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL
On Friday, 1 November, 2019 10:20, Simon Slavin wrote: >On 1 Nov 2019, at 4:17pm, Simon Slavin 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL
On Friday, 1 November, 2019 09:12, And Clover 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL
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:a...@doxdesk.com https://www.doxdesk.com/ gtalk:chat?jid=bobi...@gmail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL
On 1 Nov 2019, at 4:17pm, Simon Slavin 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL
On 1 Nov 2019, at 3:12pm, And Clover 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug submission: left join filter on negated expression including NOTNULL
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-330 (via Python sqlite3). cheers, -- And Clover mailto:a...@doxdesk.com https://www.doxdesk.com/ gtalk:chat?jid=bobi...@gmail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
