Unexpected results of where optimization

(1.1) By syang (ysy111) on 2023-11-21 03:27:06 edited from 1.0 [source]

Dear developer,

I encountered an issue while using the SQLancer tool. In the last two SELECT statements, the expected output should be the same. However, the current output is 0 and 1. I observed this problem in version 3.40, and it was reproducible in version 3.45.0 as well.

Additionally, I am curious about the reason behind the difference in results when adding IS TRUE after WHERE. Is this issue caused by a specific optimization step?

CREATE TABLE vt0(c0 integer);

CREATE TABLE t1 (c0 INT );

CREATE VIEW  IF NOT EXISTS v0(c0, c1, c2) AS SELECT DISTINCT ((((((((((t1.c0)OR(t1.c0)))OR(NULL)))AND(t1.c0)))AND(t1.c0)))<<((t1.c0 IN ()))), CASE ((((((((vt0.c0)AND(vt0.c0)))OR(t1.c0)))AND(t1.c0)))OR(t1.c0))  WHEN (vt0.c0 IN ()) THEN CAST(vt0.c0 AS TEXT) ELSE CAST(vt0.c0 AS BLOB) END, CAST(((t1.c0) IS TRUE) AS TEXT) FROM t1, vt0 WHERE (((((((0.705)IS('Lf')))OR(((0.322)<>('sM')))))OR(((x'') NOTNULL))));

INSERT OR ROLLBACK INTO vt0 VALUES ('	x');

CREATE INDEX IF NOT EXISTS i46 ON t1(CAST(((c0) IS TRUE) AS TEXT));

INSERT OR IGNORE INTO t1(c0) VALUES (NULL);

SELECT count(*) FROM t1, v0 WHERE (((((t1.c0 IN ())))<((LIKELY(DISTINCT v0.c2))))); -- reslut:0

SELECT count(*) FROM t1, v0 WHERE (((((t1.c0 IN ())))<((LIKELY(DISTINCT v0.c2))))) IS TRUE; -- reslut:1

(2) By jose isaias cabrera (jicman) on 2023-11-21 21:04:16 in reply to 1.1 [link] [source]

This is fixed in the latest trunk:

16:00:16.86>sqlite3
-- Loading resources from C:\Users\jcabrera/.sqliterc
SQLite version 3.45.0 2023-11-21 18:26:06 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

[clip]

sqlite> SELECT count(*) FROM t1, v0 WHERE (((((t1.c0 IN ())))<((LIKELY(DISTINCT v0.c2))))); -- reslut:0
┌──────────┐
│ count(*) │
├──────────┤
│ 1        │
└──────────┘
VM-steps: 68
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>
sqlite> SELECT count(*) FROM t1, v0 WHERE (((((t1.c0 IN ())))<((LIKELY(DISTINCT v0.c2))))) IS TRUE; -- reslut:1
┌──────────┐
│ count(*) │
├──────────┤
│ 1        │
└──────────┘
VM-steps: 68
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>

(3) By syang (ysy111) on 2023-11-22 08:10:11 in reply to 2 [link] [source]

Thanks！btw,I would appreciate that if you could tell me why does adding 'IS TRUE' after 'WHERE' cause such an error.I've been testing databases recently, and your response might be helpful to me.

(4) By Stephan Beal (stephan) on 2023-11-22 08:15:57 in reply to 3 [link] [source]

I would appreciate that if you could tell me why does adding 'IS TRUE' after 'WHERE' cause such an error.I

i'm not 100% certain, but i vaguely (and possibly incorrectly) remember the problem having to do with LIKELY() incorrectly influencing data type affinity of the result. This was corrected sometime in the past week, IIRC.

(5) By Richard Hipp (drh) on 2023-11-22 11:34:12 in reply to 4 [link] [source]

The fix was here: https://sqlite.org/src/timeline?c=f5b3eb0fc8936ba2

(6) By syang (ysy111) on 2023-11-23 08:33:13 in reply to 4 [link] [source]

I see,but i am still curious why adding 'IS TRUE' after 'WHERE' would trigger that bug.Theoretically,'IS TRUE' does not affect the result of the where predicate,so it should fetch the same results.My question is whether adding 'IS TRUE' after 'WHERE' would affect the plan to execute the sql statements or not.if so,and why it would?

(7.1) By jose isaias cabrera (jicman) on 2023-11-27 14:08:31 edited from 7.0 in reply to 6 [link] [source]

I see,but i am still curious why adding 'IS TRUE' after 'WHERE' would trigger that bug.

Because the code had a bug that caused the wrong output. Thus 'the fix' that now produces the correct result. You could look at the code, if you want and see the change that was done.

SQLite Forum