Hi, John-Jove,

I don't know what version you're using, but when I pasted your SQL into the CLI of the current pre-compiled windows binary, I do indeed get NULL.

Since the CLI for sqlite, by design, doesn't display the word "NULL" but instead prints an empty string, I wondered if this might be the source of your difficulty.

If that doesn't explain your problem, then one of the more expert folks will need to help out.

Note that at bottom I surrounded your select with "select (...) is null;"

sqlite> SELECT NULL;

sqlite> SELECT NULL IS NULL; 1

SQLite version 3.41.1 2023-03-10 12:13:52 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TEMPORARY TABLE IF NOT EXISTS t0 (c0 TEXT ); sqlite> CREATE TABLE IF NOT EXISTS t1 (c0 INT ); sqlite> CREATE TABLE IF NOT EXISTS t2 (c0 REAL ); sqlite> INSERT OR REPLACE INTO t0(c0) VALUES ('DM'); sqlite> CREATE INDEX i54 ON t0(((((c0)&(c0))) NOT BETWEEN ('?~') AND (x'')) DESC) WHERE ((NULL) BETWEEN (c0) AND (c0)) COLLATE RTRIM; sqlite> INSERT OR IGNORE INTO t2 VALUES (x''); sqlite> UPDATE t2 SET (c0, c0)=(NULL, -705652845); sqlite> ANALYZE t0; sqlite> ANALYZE; sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('YK#4('); sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('-940100587'); sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('嚚零%V-'); sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('-213986693'); sqlite> INSERT OR ROLLBACK INTO t1 VALUES (NULL); sqlite> CREATE INDEX i20 ON t2((((((c0)OR(0.48846542080589095)))OR(c0)) IN ())); sqlite> ANALYZE t1; sqlite> SELECT ALL t1.c0 FROM t0 LEFT OUTER JOIN t1 LEFT OUTER JOIN t2 ON (t1.c0 NOTNULL) == (t2.c0 IN ()) WHERE t2.c0; -- actual:{}, expected:{null} sqlite> select (SELECT ALL t1.c0 FROM t0 LEFT OUTER JOIN t1 LEFT OUTER JOIN t2 ON (t1.c0 NOTNULL) == (t2.c0 IN ()) WHERE t2.c0) is null; 1

Fixed on trunk and on branch-3.41

Sorry for the incomplete information. I found this bug on SQLite version 3.41.1, compiled from the source file. I try the above case in SQLite CLI, and the bug still exists. I put the results in the followings. The difference is that case 1 does not have ANALYZE statements and returns values. Case 2 with ANALYZE statements returns nothing. You can see my posted CLI process, where the last prompt is different.

case 1

SQLite version 3.41.1 2023-03-10 12:13:52
Enter ".help" for usage hints.
sqlite> CREATE TEMPORARY TABLE IF NOT EXISTS t0 (c0 TEXT );
sqlite> CREATE TABLE IF NOT EXISTS t1 (c0 INT );
sqlite> CREATE TABLE IF NOT EXISTS t2 (c0 REAL );
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES ('DM');
sqlite> CREATE INDEX i54 ON t0(((((c0)&(c0))) NOT BETWEEN ('?~') AND (x'')) DESC) WHERE ((NULL) BETWEEN (c0) AND (c0)) COLLATE RTRIM;
sqlite> INSERT OR IGNORE INTO t2 VALUES (x'');
sqlite> UPDATE t2 SET (c0, c0)=(NULL, -705652845);
sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('YK#4(');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('-940100587');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('嚚零%V-');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES  ('-213986693');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES  (NULL);
sqlite> CREATE INDEX i20 ON t2((((((c0)OR(0.48846542080589095)))OR(c0)) IN ()));
sqlite> SELECT ALL t1.c0 FROM t0 LEFT OUTER JOIN t1 LEFT OUTER JOIN t2 ON (t1.c0 NOTNULL) == (t2.c0 IN ()) WHERE t2.c0;

sqlite>

case 2

SQLite version 3.41.1 2023-03-10 12:13:52
Enter ".help" for usage hints.
sqlite> CREATE TEMPORARY TABLE IF NOT EXISTS t0 (c0 TEXT );
sqlite> CREATE TABLE IF NOT EXISTS t1 (c0 INT );
sqlite> CREATE TABLE IF NOT EXISTS t2 (c0 REAL );
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES ('DM');
sqlite> CREATE INDEX i54 ON t0(((((c0)&(c0))) NOT BETWEEN ('?~') AND (x'')) DESC) WHERE ((NULL) BETWEEN (c0) AND (c0)) COLLATE RTRIM;
sqlite> INSERT OR IGNORE INTO t2 VALUES (x'');
sqlite> UPDATE t2 SET (c0, c0)=(NULL, -705652845);
sqlite> ANALYZE t0;
sqlite> ANALYZE;
sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('YK#4(');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('-940100587');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES ('嚚零%V-');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES  ('-213986693');
sqlite> INSERT OR ROLLBACK INTO t1 VALUES  (NULL);
sqlite> CREATE INDEX i20 ON t2((((((c0)OR(0.48846542080589095)))OR(c0)) IN ()));
sqlite> ANALYZE t1;
sqlite> SELECT ALL t1.c0 FROM t0 LEFT OUTER JOIN t1 LEFT OUTER JOIN t2 ON (t1.c0 NOTNULL) == (t2.c0 IN ()) WHERE t2.c0;
sqlite>

No.

If you were using the fixed version it would say on the first line:

SQLite version 3.42.0 2023-03-14 20:08:39

The examples you show are from 2023-03-10 - 4 days ago.

Thanks for your reply, from the https://sqlite.org/download.html, I can only find 3.41.1. Would you please update this information?

Richard has assumed you would get the latest code and use it to build the library or CLI. The download page often does not contain executables other than for the latest release.

You can get a Zip archive of the fixed 3.41 branch here.

Thanks. I'll keep my application with the latest code.