catchsql {
UPDATE t1 INDEXED BY t1b SET a=a+100
WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
OR (b NOT NULL AND c IS NULL AND d NOT NULL)
OR (b NOT NULL AND c NOT NULL AND d IS NULL)
}
} {1 {no query solution}}
ifcapable stat4 {
# When STAT3 is enabled, the "b NOT NULL" terms get translated
# into b>NULL, which can be satified by the index t1b. It is a very
# expensive way to do the query, but it works, and so a solution is possible.
do_test where9-6.8.3-stat4 {
catchsql {
UPDATE t1 INDEXED BY t1b SET a=a+100
WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
................................................................................
CREATE INDEX t5yd ON t5(y, d);
CREATE INDEX t5ye ON t5(y, e);
CREATE INDEX t5yf ON t5(y, f);
CREATE INDEX t5yg ON t5(y, g);
CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
INSERT INTO t6 SELECT * FROM t5;
ANALYZE t5;
}
} {}
do_test where9-7.1.1 {
count_steps {
SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
}
} {79 81 83 scan 0 sort 1}