SQLite User Forum

ANALYZE causes incorrect query results.
Login
Hierarchical

ANALYZE causes incorrect query results.

(1) By John (John-Jove) on 2023-03-14 07:16:12 [source]

SQLite version: 3.41.0

The same select statement in the following two cases outputs different results. The only difference between case1 and case2 is that case2 does not have ANALYZE statements. While I try my best to reduce these two cases, the two cases still have many statements.

case 1 

CREATE TEMP TABLE t0 (c0 TEXT );
CREATE TABLE IF NOT EXISTS t1 (c0 TEXT , c1 INT , c2 TEXT );
INSERT OR IGNORE INTO t1(c0, c2) VALUES (0X4958eb99, x'');
INSERT OR IGNORE INTO t1(c0, c2) VALUES (x'', '1230564249');
INSERT OR IGNORE INTO t1(c0, c2) VALUES (NULL, '');
INSERT OR IGNORE INTO t0 VALUES (0.24332684784085767);
INSERT OR IGNORE INTO t0 VALUES ('1230564249');
INSERT OR IGNORE INTO t0 VALUES ('');
INSERT OR ROLLBACK INTO t0 VALUES (1230564249);
INSERT OR IGNORE INTO t1(c1) VALUES (0X61093f2c);
INSERT OR IGNORE INTO t1(c1) VALUES (x'7421');
INSERT OR IGNORE INTO t1(c1) VALUES ('0.24332684784085767');
INSERT OR ROLLBACK INTO t0 VALUES (0.24332684784085767);
INSERT OR ROLLBACK INTO t1(c0, c2, c1) VALUES (x'', 'u#2kS+q', 0.8751527781096999);
INSERT OR FAIL INTO t1(c1) VALUES (x'');
INSERT OR FAIL INTO t0 VALUES (''), (0.959394934855813), (x'');
INSERT OR IGNORE INTO t0(c0) VALUES ('j|y1'), (NULL), (5.55043453E8);
CREATE INDEX IF NOT EXISTS i43 ON t1(((x'')LIKE(1.627995948E9)),c2 DESC,((c2) NOTNULL));
INSERT OR IGNORE INTO t0(c0) VALUES (0.0074064953695271996);
INSERT OR IGNORE INTO t1(c1, c2) VALUES (NULL, x'');
INSERT OR ROLLBACK INTO t0(c0) VALUES ('R2q{|AFx');
INSERT OR IGNORE INTO t0 VALUES ('Oy');
INSERT OR IGNORE INTO t0 VALUES (x'4b56');
INSERT OR IGNORE INTO t0 VALUES ('2032525456');
INSERT OR ABORT INTO t0(c0) VALUES ('555043453');
INSERT OR IGNORE INTO t1 VALUES ('-2082820776', x'', '1');
INSERT OR IGNORE INTO t1 VALUES (5.55043453E8, NULL, 'j|y1');
INSERT OR IGNORE INTO t1 VALUES ('', 'u', '');
INSERT OR IGNORE INTO t1 VALUES ('0.0074064953695271996', x'', NULL);
INSERT OR IGNORE INTO t1 VALUES (x'', '', 'u#2kS+q');
INSERT OR REPLACE INTO t0(c0) VALUES (0xffffffffce47994c);
INSERT OR REPLACE INTO t0(c0) VALUES (0.367375135807375);
INSERT OR REPLACE INTO t0(c0) VALUES (0.2609727027806802);
INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.7278005219375546, NULL, x'088e');
INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (-1122097386, 0Xf64f451, x'');
INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.8666746987919098, x'c8b4', -1.122097386E9);
INSERT OR IGNORE INTO t1(c2, c1, c0) VALUES ('-491160470', x'', NULL);
ANALYZE;
ANALYZE t0;
SELECT ALL t1.c2 FROM t0 NATURAL JOIN t1 WHERE t1.c2=t1.c0; -- {}
When removing the two ANALYZE statements in case 1, case 2 outputs different results.

case 2 

CREATE TEMP TABLE t0 (c0 TEXT );
CREATE TABLE IF NOT EXISTS t1 (c0 TEXT , c1 INT , c2 TEXT );
INSERT OR IGNORE INTO t1(c0, c2) VALUES (0X4958eb99, x'');
INSERT OR IGNORE INTO t1(c0, c2) VALUES (x'', '1230564249');
INSERT OR IGNORE INTO t1(c0, c2) VALUES (NULL, '');
INSERT OR IGNORE INTO t0 VALUES (0.24332684784085767);
INSERT OR IGNORE INTO t0 VALUES ('1230564249');
INSERT OR IGNORE INTO t0 VALUES ('');
INSERT OR ROLLBACK INTO t0 VALUES (1230564249);
INSERT OR IGNORE INTO t1(c1) VALUES (0X61093f2c);
INSERT OR IGNORE INTO t1(c1) VALUES (x'7421');
INSERT OR IGNORE INTO t1(c1) VALUES ('0.24332684784085767');
INSERT OR ROLLBACK INTO t0 VALUES (0.24332684784085767);
INSERT OR ROLLBACK INTO t1(c0, c2, c1) VALUES (x'', 'u#2kS+q', 0.8751527781096999);
INSERT OR FAIL INTO t1(c1) VALUES (x'');
INSERT OR FAIL INTO t0 VALUES (''), (0.959394934855813), (x'');
INSERT OR IGNORE INTO t0(c0) VALUES ('j|y1'), (NULL), (5.55043453E8);
CREATE INDEX IF NOT EXISTS i43 ON t1(((x'')LIKE(1.627995948E9)),c2 DESC,((c2) NOTNULL));
INSERT OR IGNORE INTO t0(c0) VALUES (0.0074064953695271996);
INSERT OR IGNORE INTO t1(c1, c2) VALUES (NULL, x'');
INSERT OR ROLLBACK INTO t0(c0) VALUES ('R2q{|AFx');
INSERT OR IGNORE INTO t0 VALUES ('Oy');
INSERT OR IGNORE INTO t0 VALUES (x'4b56');
INSERT OR IGNORE INTO t0 VALUES ('2032525456');
INSERT OR ABORT INTO t0(c0) VALUES ('555043453');
INSERT OR IGNORE INTO t1 VALUES ('-2082820776', x'', '1');
INSERT OR IGNORE INTO t1 VALUES (5.55043453E8, NULL, 'j|y1');
INSERT OR IGNORE INTO t1 VALUES ('', 'u', '');
INSERT OR IGNORE INTO t1 VALUES ('0.0074064953695271996', x'', NULL);
INSERT OR IGNORE INTO t1 VALUES (x'', '', 'u#2kS+q');
INSERT OR REPLACE INTO t0(c0) VALUES (0xffffffffce47994c);
INSERT OR REPLACE INTO t0(c0) VALUES (0.367375135807375);
INSERT OR REPLACE INTO t0(c0) VALUES (0.2609727027806802);
INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.7278005219375546, NULL, x'088e');
INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (-1122097386, 0Xf64f451, x'');
INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.8666746987919098, x'c8b4', -1.122097386E9);
INSERT OR IGNORE INTO t1(c2, c1, c0) VALUES ('-491160470', x'', NULL);
SELECT ALL t1.c2 FROM t0 NATURAL JOIN t1 WHERE t1.c2=t1.c0; --{(),()}

(2) By Richard Hipp (drh) on 2023-03-14 20:17:41 in reply to 1 [link] [source]

Fixed on trunk and on branch-3.41.

(3) By John (John-Jove) on 2023-03-15 01:57:03 in reply to 2 [link] [source]

I put the CLI prompts for the above too cases. The bug still exists, because SQLite CLI returns different results.

case 1

SQLite version 3.41.1 2023-03-10 12:13:52
Enter ".help" for usage hints.
sqlite> CREATE TEMP TABLE t0 (c0 TEXT );
sqlite> CREATE TABLE IF NOT EXISTS t1 (c0 TEXT , c1 INT , c2 TEXT );
sqlite> INSERT OR IGNORE INTO t1(c0, c2) VALUES (0X4958eb99, x'');
sqlite> INSERT OR IGNORE INTO t1(c0, c2) VALUES (x'', '1230564249');
sqlite> INSERT OR IGNORE INTO t1(c0, c2) VALUES (NULL, '');
sqlite> INSERT OR IGNORE INTO t0 VALUES (0.24332684784085767);
sqlite> INSERT OR IGNORE INTO t0 VALUES ('1230564249');
sqlite> INSERT OR IGNORE INTO t0 VALUES ('');
sqlite> INSERT OR ROLLBACK INTO t0 VALUES (1230564249);
sqlite> INSERT OR IGNORE INTO t1(c1) VALUES (0X61093f2c);
sqlite> INSERT OR IGNORE INTO t1(c1) VALUES (x'7421');
sqlite> INSERT OR IGNORE INTO t1(c1) VALUES ('0.24332684784085767');
sqlite> INSERT OR ROLLBACK INTO t0 VALUES (0.24332684784085767);
sqlite> INSERT OR ROLLBACK INTO t1(c0, c2, c1) VALUES (x'', 'u#2kS+q', 0.8751527781096999);
sqlite> INSERT OR FAIL INTO t1(c1) VALUES (x'');
sqlite> INSERT OR FAIL INTO t0 VALUES (''), (0.959394934855813), (x'');
sqlite> INSERT OR IGNORE INTO t0(c0) VALUES ('j|y1'), (NULL), (5.55043453E8);
sqlite> CREATE INDEX IF NOT EXISTS i43 ON t1(((x'')LIKE(1.627995948E9)),c2 DESC,((c2) NOTNULL));
sqlite> INSERT OR IGNORE INTO t0(c0) VALUES (0.0074064953695271996);
sqlite> INSERT OR IGNORE INTO t1(c1, c2) VALUES (NULL, x'');
sqlite> INSERT OR ROLLBACK INTO t0(c0) VALUES ('R2q{|AFx');
sqlite> INSERT OR IGNORE INTO t0 VALUES ('Oy');
sqlite> INSERT OR IGNORE INTO t0 VALUES (x'4b56');
sqlite> INSERT OR IGNORE INTO t0 VALUES ('2032525456');
sqlite> INSERT OR ABORT INTO t0(c0) VALUES ('555043453');
sqlite> INSERT OR IGNORE INTO t1 VALUES ('-2082820776', x'', '1');
sqlite> INSERT OR IGNORE INTO t1 VALUES (5.55043453E8, NULL, 'j|y1');
sqlite> INSERT OR IGNORE INTO t1 VALUES ('', 'u', '');
sqlite> INSERT OR IGNORE INTO t1 VALUES ('0.0074064953695271996', x'', NULL);
sqlite> INSERT OR IGNORE INTO t1 VALUES (x'', '', 'u#2kS+q');
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES (0xffffffffce47994c);
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES (0.367375135807375);
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES (0.2609727027806802);
sqlite> INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.7278005219375546, NULL, x'088e');
sqlite> INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (-1122097386, 0Xf64f451, x'');
sqlite> INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.8666746987919098, x'c8b4', -1.122097386E9);
sqlite> INSERT OR IGNORE INTO t1(c2, c1, c0) VALUES ('-491160470', x'', NULL);
sqlite> ANALYZE;
sqlite> ANALYZE t0;
sqlite> SELECT ALL t1.c2 FROM t0 NATURAL JOIN t1 WHERE t1.c2=t1.c0;
sqlite>

case 2

SQLite version 3.41.1 2023-03-10 12:13:52
Enter ".help" for usage hints.
sqlite> CREATE TEMP TABLE t0 (c0 TEXT );
sqlite> CREATE TABLE IF NOT EXISTS t1 (c0 TEXT , c1 INT , c2 TEXT );
sqlite> INSERT OR IGNORE INTO t1(c0, c2) VALUES (0X4958eb99, x'');
sqlite> INSERT OR IGNORE INTO t1(c0, c2) VALUES (x'', '1230564249');
sqlite> INSERT OR IGNORE INTO t1(c0, c2) VALUES (NULL, '');
sqlite> INSERT OR IGNORE INTO t0 VALUES (0.24332684784085767);
sqlite> INSERT OR IGNORE INTO t0 VALUES ('1230564249');
sqlite> INSERT OR IGNORE INTO t0 VALUES ('');
sqlite> INSERT OR ROLLBACK INTO t0 VALUES (1230564249);
sqlite> INSERT OR IGNORE INTO t1(c1) VALUES (0X61093f2c);
sqlite> INSERT OR IGNORE INTO t1(c1) VALUES (x'7421');
sqlite> INSERT OR IGNORE INTO t1(c1) VALUES ('0.24332684784085767');
sqlite> INSERT OR ROLLBACK INTO t0 VALUES (0.24332684784085767);
sqlite> INSERT OR ROLLBACK INTO t1(c0, c2, c1) VALUES (x'', 'u#2kS+q', 0.8751527781096999);
sqlite> INSERT OR FAIL INTO t1(c1) VALUES (x'');
sqlite> INSERT OR FAIL INTO t0 VALUES (''), (0.959394934855813), (x'');
sqlite> INSERT OR IGNORE INTO t0(c0) VALUES ('j|y1'), (NULL), (5.55043453E8);
sqlite> CREATE INDEX IF NOT EXISTS i43 ON t1(((x'')LIKE(1.627995948E9)),c2 DESC,((c2) NOTNULL));
sqlite> INSERT OR IGNORE INTO t0(c0) VALUES (0.0074064953695271996);
sqlite> INSERT OR IGNORE INTO t1(c1, c2) VALUES (NULL, x'');
sqlite> INSERT OR ROLLBACK INTO t0(c0) VALUES ('R2q{|AFx');
sqlite> INSERT OR IGNORE INTO t0 VALUES ('Oy');
sqlite> INSERT OR IGNORE INTO t0 VALUES (x'4b56');
sqlite> INSERT OR IGNORE INTO t0 VALUES ('2032525456');
sqlite> INSERT OR ABORT INTO t0(c0) VALUES ('555043453');
sqlite> INSERT OR IGNORE INTO t1 VALUES ('-2082820776', x'', '1');
sqlite> INSERT OR IGNORE INTO t1 VALUES (5.55043453E8, NULL, 'j|y1');
sqlite> INSERT OR IGNORE INTO t1 VALUES ('', 'u', '');
sqlite> INSERT OR IGNORE INTO t1 VALUES ('0.0074064953695271996', x'', NULL);
sqlite> INSERT OR IGNORE INTO t1 VALUES (x'', '', 'u#2kS+q');
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES (0xffffffffce47994c);
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES (0.367375135807375);
sqlite> INSERT OR REPLACE INTO t0(c0) VALUES (0.2609727027806802);
sqlite> INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.7278005219375546, NULL, x'088e');
sqlite> INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (-1122097386, 0Xf64f451, x'');
sqlite> INSERT OR ROLLBACK INTO t1(c2, c0, c1) VALUES (0.8666746987919098, x'c8b4', -1.122097386E9);
sqlite> INSERT OR IGNORE INTO t1(c2, c1, c0) VALUES ('-491160470', x'', NULL);
sqlite> SELECT ALL t1.c2 FROM t0 NATURAL JOIN t1 WHERE t1.c2=t1.c0;


sqlite>

(4) By Richard Hipp (drh) on 2023-03-15 02:04:34 in reply to 3 [link] [source]

Incorrect. See forum comment eecefe6fcc.