SQLite Forum

Unexpected results of where optimization
Login

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]

(8) By anonymous on 2024-03-14 16:23:00 in reply to 5 [link] [source]

Hello,

I'm sorry to bother you. I already know that this bug has been fixed. Here I just want to confirm if my tool is working properly. The version of the sqlite is 3.44.0.

Below is the simplified query result that still triggers the bug. The aim of this simplification is to assist developers in identifying the root cause of the issue.

CREATE TABLE vt0(c0 integer);

CREATE TABLE t1 (c0 INT );

CREATE VIEW  IF NOT EXISTS v0(c0, c1, c2) AS SELECT  t1.c0,t1.c0  , CAST(((t1.c0) IS TRUE) AS TEXT) FROM t1, vt0 WHERE (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( v0.c2));  --0

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

  • We've distilled the steps and SQL statements to the bare essentials needed to reproduce the issue. This ensures clarity and focuses on the core problem.
  • The logic behind this simplification process was to eliminate any elements of the query that did not directly contribute to the observed bug, streamlining the diagnostic process.

Could you please confirm whether this simplification process has been beneficial for developers in diagnosing and addressing the issue?

(9) By Stephan Beal (stephan) on 2024-03-14 16:26:44 in reply to 8 [link] [source]

The version of the sqlite is 3.44.0.

See the link in the response you replied to: that fix was applied almost three weeks after 3.44.0 was released.

(10) By anonymous on 2024-03-14 19:19:25 in reply to 9 [link] [source]

Yes, I know it has already been fixed. I just want to confirm if my tool is helpful for developers to diagnose bugs.

I would greatly appreciate it if you could confirm this for me.

(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.