Unexpected Execution Result

(1.1) By Zhaokun Xiang (silva9988) on 2025-10-29 18:30:41 edited from 1.0 [source]

Dear SQLite Developers,

I meet a wrong execution result. Please see the below cases. I can reproduce it on the latest Check-in [e2c20aa592] of trunk. I would like to express my gratitude to your efforts in bug fixing and detailed analysis!

CREATE TABLE t0(c0);
INSERT INTO t0 VALUES (1);

SELECT * FROM (SELECT 0 AS col_0) LEFT JOIN (SELECT 1 AS col_1, (0 OR 2) AS col_2 FROM t0) as subQuery ON (subQuery.col_1 % subQuery.col_2);
-- Wrong Execution Result: 0,NULL,NULL
-- Expected Result: 0,1,2

(2) By Richard Hipp (drh) on 2025-10-29 20:32:07 in reply to 1.1 [link] [source]

Are you sure that 0,1,2 is the correct result? Because the equivalent query for PostgreSQL is:

CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES (1);
SELECT *
  FROM (SELECT 0 AS col_0) AS sq1
  LEFT JOIN (SELECT 1 AS col_1, CASE WHEN (false OR true) THEN 1 ELSE 0 END AS col_2 FROM t0) as subQuery
            ON (subQuery.col_1 % subQuery.col_2)<>0;

And that query also returns 0,NULL,NULL.

(3) By Richard Hipp (drh) on 2025-10-29 20:48:10 in reply to 1.1 [link] [source]

I think the real problem is that new short-circuit optimizations that are part of the pending release introduced a new bug in that:

SELECT 0 OR 2;

Began returning 2 instead of 1. That problem should now be fixed, as of a few moments ago.

(4) By Zhaokun Xiang (silva9988) on 2025-10-30 04:31:38 in reply to 3 [link] [source]

Thanks for your fixing!