Hello,

i was looking at optimizations what all optimizations sqlite has and this gives a different result than what i expect with default optimizations. again, correct me if i am wrong or i misunderstood any semantics.

SQLite docs state:

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once.

So replacing x BETWEEN y AND z with x>=y AND x<=z should not change query result semantics.

CREATE TABLE t(v INT);
INSERT INTO t VALUES(1),(2);
begin;
UPDATE t SET v=100
 WHERE v=1 OR (1 BETWEEN 0 AND ((SELECT min(v) FROM t) >= v));
SELECT 'original', group_concat(v,'|') FROM (SELECT v FROM t ORDER BY v);
rollback;
UPDATE t SET v=100
 WHERE v=1 OR ((1 >= 0) AND (1 <= ((SELECT min(v) FROM t) >= v)));
SELECT 'equivalent..?', group_concat(v,'|') FROM (SELECT v FROM t ORDER BY v);

disabling optimization

.testctrl optimizations 0x08000000

git bisect

• bad: d192b4441de853d23fd377244dfcfbaa5b246ba7

First bad commit message:

• "only disable one-pass if the WHERE clause contains a subquery". (2023-08-01).

additional repro - i think maybe same root cause if this is a bug.?

CREATE TABLE t1 (vkey INTEGER, c5 INTEGER);
INSERT INTO t1 VALUES(3,NULL),(6,-54);

BEGIN;
UPDATE t1 SET vkey = 100
 WHERE c5 is null
    OR ((1, NOT(-10*(select min(vkey) from t1) >= c5))=(1,1));
SELECT group_concat(vkey||':'||ifnull(c5,'NULL'),'|')
FROM (SELECT * FROM t1 ORDER BY vkey,c5);
ROLLBACK;