SQLite Forum

Possible Bug
Login

Possible Bug

(1) By Cong Ding (tson1111) on 2021-01-11 06:46:05 [link] [source]

The query below acted weirdly.

The last row returns zero result but the query on the second to last row returns one row.

However, the last SELECT should include the results of the second to last RESULT. I guess that's a bug.

Query attached:

--java.lang.AssertionError: 1 0
--	at sqlancer.sqlite3.oracle.SQLite3NoRECOracle.check(SQLite3NoRECOracle.java:77)
--	at sqlancer.ProviderAdapter.generateAndTestDatabase(ProviderAdapter.java:49)
--	at sqlancer.Main$DBMSExecutor.run(Main.java:323)
--	at sqlancer.Main$2.run(Main.java:507)
--	at sqlancer.Main$2.runThread(Main.java:485)
--	at sqlancer.Main$2.run(Main.java:475)
--	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
--	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
--	at java.base/java.lang.Thread.run(Thread.java:834)
---- Time: 2021/01/04 13:39:59
-- Database: database25
-- Database version: 3.34.0
-- seed value: 1609696302781
PRAGMA cache_size = 50000;
PRAGMA temp_store=MEMORY;
PRAGMA synchronous=off;
PRAGMA case_sensitive_like=ON;
PRAGMA encoding = 'UTF-16';
CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2, c3, c4, c5, c6);
CREATE VIRTUAL TABLE vt1 USING fts4(c0, prefix=59, prefix=866);
BEGIN DEFERRED TRANSACTION;
REINDEX;
PRAGMA temp.ignore_check_constraints;
INSERT OR IGNORE INTO vt1(c0) VALUES ('ni-W!i'), (NULL), (x'');
INSERT OR ABORT INTO vt1(c0) VALUES ('录'), ('t2');
PRAGMA optimize;
INSERT OR IGNORE INTO rt0(c1, c6, c2, c5, c0, c3, c4) VALUES (0.05526816862614525, 0.5921540296644346, 0.5921540296644346, '-2054725803', '0.5921540296644346', 0.7321085659273071, 0.6571062542676721), (NULL, NULL, '\흧', NULL, 0.9871854350051078, NULL, x'0c2830'), (x'54d6a3f1', 0.5621377067259727, NULL, NULL, 0x4dbb4335, x'44', 0X7fffffffffffffff);
INSERT OR IGNORE INTO rt0(c2, c6, c0, c3) VALUES (0xffffffffce133acc, '0.5921540296644346', NULL, x''), (0.6928509780759669, '-837600564', 599706316, x'a6'), (NULL, 0.7270912793185681, x'fa39', '1039970164');
PRAGMA locking_mode = NORMAL;
INSERT INTO vt1(vt1, rank) VALUES('usermerge', 10);
INSERT INTO vt1(vt1, rank) VALUES('automerge', 12);
INSERT INTO vt1(vt1, rank) VALUES('crisismerge', 1930987790);
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1) VALUES('integrity-check');
INSERT OR REPLACE INTO rt0(c4, c5, c6, c1) VALUES (-2.054725803E9, '-70362705', -6.10347161E8, 1039970164), (x'', NULL, (((- (0.7079948192078538)))==(0.37505738423520174)), 1.039970164E9);
INSERT OR REPLACE INTO vt1 VALUES (NULL), (''), (0X7fffffffffffffff);
END;
UPDATE OR IGNORE rt0 SET c3 = '1432078254', c1 = 0.7957413910627326, c3 = x'';
ANALYZE;
BEGIN EXCLUSIVE TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;
INSERT INTO vt1(vt1) VALUES('integrity-check');
BEGIN  TRANSACTION;
INSERT OR IGNORE INTO rt0(c0, c5, c3) VALUES ('T1', '-1258501503', '7ck0V'), (NULL, 0.05526816862614525, ''), ('-986584328', '-986584328', '1065137157'), (x'1576b2', '-236330005', 0.25511262190689943);
PRAGMA temp.short_column_names;
INSERT OR FAIL INTO vt1 VALUES ('{K +tm''Kl'), (-642819673);
INSERT INTO vt1(vt1) VALUES('integrity-check');
INSERT OR IGNORE INTO vt1(c0) VALUES (0.01894897775656712), (0.37505738423520174), (x'eefaba'), (0.05526816862614525);
INSERT OR IGNORE INTO rt0(c5, c4, c6) VALUES (NULL, 987116023, 0xffffffffdb9ed767), (0xfffffffff1e9e3eb, x'', x'c9f42c'), (x'', 0.7079948192078538, NULL);
INSERT OR IGNORE INTO rt0(c2, c6, c1) VALUES ('wUN', x'eac7', x'9b'), (0.6485954187303292, 'I<eU5,#', '1065137157');
PRAGMA mmap_size = -1319546109154881709;
REINDEX;
INSERT OR ROLLBACK INTO rt0(c3, c2, c4, c5, c1, c6, c0) VALUES (0xffffffffcc5d3044, 0.6485954187303292, 'T1', '1302571972', 0xadf9201, NULL, '8쪁r');
INSERT OR IGNORE INTO vt1(c0) VALUES (-6.42819673E8), ('Gd}ڑ0eC0');
INSERT OR ABORT INTO rt0(c4, c1, c3, c5, c0) VALUES ('', x'ef08', '1302571972', '-1183606739', '');
INSERT OR FAIL INTO rt0(c4) VALUES (0.5694497889849068), (0.9157406037267074), (-1855805818);
INSERT OR REPLACE INTO vt1 VALUES (0.4747910386125175);
INSERT OR FAIL INTO rt0(c3, c4, c6, c5) VALUES (981090749, 0.5503055744956081, 0.15023159544912956, NULL), (NULL, '1344536687', NULL, NULL);
INSERT OR IGNORE INTO rt0(c2, c5, c0, c3, c1, c6) VALUES (0.878401658251491, x'b6', x'', '0.7079948192078538', '.>_#e ZL', '1065137157'), (0.610202699887959, 'rwAyl', '_)dB', '&m宫8', x'', 0.907269098947168), (0.6928612347980843, x'', 0X295b4656, x'1b9add', 0.21079940727971913, NULL), (1021764061, '-328562757', '', 0xfffffffff1e9e3eb, 0.8796307467733379, x'');
INSERT OR IGNORE INTO rt0(c0, c5, c3, c6) VALUES ('T', x'67e6c353eb', 'X	#
b~Af', NULL);
INSERT OR FAIL INTO vt1 VALUES (0Xa2621e3), ('0.5921540296644346'), ('CL*{');
PRAGMA reverse_unordered_selects;
INSERT INTO vt1(vt1) VALUES('rebuild');
PRAGMA recursive_triggers;
INSERT OR IGNORE INTO rt0 VALUES ('-642819673', '-415372284', '/ZEA<￿E4䷺', x'49', NULL, 0.7309753377931609, 0.14259382846088975), ('1578690952', '1473661179', '1779257277', x'2e9a', x'', x'048170', NULL);
INSERT OR REPLACE INTO vt1(c0) VALUES (x'ffeb'), (0.14259382846088975), (x'af9b');
INSERT OR ROLLBACK INTO rt0(c5, c3, c4, c1, c0) VALUES (NULL, 0.5739492764981124, x'255082', '0.14259382846088975', '1432078254');
INSERT OR IGNORE INTO rt0(c0, c6, c2, c3, c5, c4) VALUES (0.5036656586955645, NULL, x'f2', NULL, x'', 0.9157406037267074), (NULL, NULL, x'c7', x'91', 0.7235110416950825, NULL), ('n..*&N', '230650597', x'', 1243139870, 0.6605223517136947, 'M');
ROLLBACK TRANSACTION;
INSERT INTO vt1(vt1) VALUES('merge=9223372036854775807,13');
;
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1, rank) VALUES('pgsz', '60694');
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1) VALUES('optimize');
ROLLBACK TRANSACTION;
INSERT INTO vt1(vt1) VALUES('optimize');
PRAGMA wal_autocheckpoint;
COMMIT TRANSACTION;
INSERT INTO vt1(vt1, rank) VALUES('merge', -518829013);
;
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1) VALUES('integrity-check');
COMMIT TRANSACTION;
INSERT OR IGNORE INTO rt0 VALUES (0.8205240588181247, NULL, NULL, 'fcsJTh}', ((((NULL) NOTNULL)) BETWEEN ('2041770427') AND (3.47819514E8 COLLATE NOCASE)), NULL, '1065137157'), (1021764061, x'e0', 0.45046235544789603, 'Q뿄뒟m妯', NULL, x'', '+C샔P烪i&');
PRAGMA foreign_keys;
INSERT OR FAIL INTO rt0(c1, c2, c3, c5, c0) VALUES (x'', 0.10488486080909665, 0.3141504787013635, -9223372036854775808, '1'), (x'bb39a5', NULL, 0.8724759585300668, 0.641561632938418, 0.9501172449318448);
INSERT OR IGNORE INTO vt1 VALUES (''), (0x5024006f);
INSERT OR FAIL INTO rt0(c1) VALUES (NULL);
SELECT rtreecheck('rt0');
INSERT OR IGNORE INTO rt0(c3, c5, c0) VALUES (x'', '0.8724759585300668', 0.955735548833668), (NULL, x'27', 1.473661179E9);
INSERT INTO vt1(vt1, rank) VALUES('merge', 450118506);
;
INSERT INTO vt1(vt1) VALUES('rebuild');
INSERT OR IGNORE INTO sqlite_stat1 VALUES('rt0', 'vt1', '0 sz=1624606889');
UPDATE OR IGNORE vt1 SET (c0, c0)=(NULL, 6.93847638E8) WHERE CAST(((vt1.c0)>(vt1.c0)) AS BLOB);
UPDATE OR FAIL rt0 SET (c3)=('-518829013') WHERE HEX((('') NOTNULL));
END TRANSACTION;
INSERT OR IGNORE INTO rt0(c0, c1, c3, c6) VALUES (x'94', 0.024775806053485727, -320110491, ''), (x'', 1203766495, x'95', 0x16aac0d4), ('-610347161', x'', x'0b', '-642819673'), (0.012656754196046505, '1203766495', '-642819673', 'b9_G'), (NULL, '', '1779257277', NULL);
INSERT OR IGNORE INTO vt1 VALUES ('0.5921540296644346'), ('-562315324');
INSERT INTO vt1(vt1) VALUES('rebuild');
PRAGMA temp.recursive_triggers = true;
PRAGMA main.journal_size_limit;
INSERT INTO vt1(vt1) VALUES('rebuild');
COMMIT;
ROLLBACK TRANSACTION;
VACUUM temp;
INSERT OR ABORT INTO rt0(c5) VALUES ('-986584328'), (0.5036656586955645), ('50794249');
INSERT OR IGNORE INTO rt0(c6, c2) VALUES ('327410658', -139814963);
SELECT rtreecheck('rt0');
COMMIT;
INSERT OR REPLACE INTO vt1(c0) VALUES ('0.37505738423520174'), ('');
INSERT OR IGNORE INTO rt0(c1, c4, c2, c0, c5, c3, c6) VALUES (NULL, '1926454187', 0xffffffffb2d7f2cb, 0.7235110416950825, NULL, 0.3283508406410687, 0.4747910386125175), (x'', '', '0.01894897775656712', 0.7677703135539137, json_array_length((((0.02749715070069081, x'e9'))>=(('', x'1d')))), 0.3549445859797826, 'y'), ('3', x'e255', 0.6395510234336461, x'', '2041770427', NULL, '');
ROLLBACK TRANSACTION;
UPDATE OR IGNORE rt0 SET (c0)=(0xffffffffffffffff);
COMMIT TRANSACTION;
REINDEX;
PRAGMA journal_mode = PERSIST;
INSERT INTO vt1(vt1, rank) VALUES('merge', -252516511);
;
INSERT INTO vt1(vt1) VALUES('automerge=10');
INSERT INTO vt1(vt1) VALUES('rebuild');
INSERT INTO vt1(vt1, rank) VALUES('automerge', 9);
INSERT INTO vt1(vt1, rank) VALUES('automerge', 5);
INSERT INTO vt1(vt1, rank) VALUES('pgsz', '60281');
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1) VALUES('optimize');
ANALYZE;
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1, rank) VALUES('automerge', 0);
INSERT INTO vt1(vt1, rank) VALUES('automerge', 2);
INSERT INTO vt1(vt1) VALUES('optimize');
END;
VACUUM main;
VACUUM temp;
PRAGMA defer_foreign_keys = false;
PRAGMA temp.foreign_keys = false;
INSERT INTO vt1(vt1) VALUES('rebuild');
INSERT INTO vt1(vt1, rank) VALUES('crisismerge', 2059305225);
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1, rank) VALUES('usermerge', 7);
INSERT INTO vt1(vt1) VALUES('merge=-1426374726,6');
;
INSERT INTO vt1(vt1, rank) VALUES('crisismerge', 1787184809);
INSERT INTO vt1(vt1) VALUES('integrity-check');
INSERT INTO vt1(vt1, rank) VALUES('crisismerge', 1407512012);
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1) VALUES('rebuild');
INSERT INTO vt1(vt1, rank) VALUES('usermerge', 7);
INSERT INTO vt1(vt1, rank) VALUES('pgsz', '34890');
INSERT INTO vt1(vt1, rank) VALUES('rank', 'bm25(10.0, 5.0)');
INSERT INTO vt1(vt1) VALUES('rebuild');
BEGIN  TRANSACTION;
PRAGMA wal_autocheckpoint;
END;
ROLLBACK TRANSACTION;
SELECT * FROM rt0, vt1 WHERE rt0.c4 COLLATE NOCASE IN (vt1.c0);
SELECT * FROM rt0, vt1 WHERE (rt0.c4 COLLATE NOCASE IN (vt1.c0))OR(rt0.c5);

(2) By TripeHound on 2021-01-11 10:17:43 in reply to 1 [link] [source]

I've no idea how relevant it is to whether there really is a bug in SQLite (as opposed to SQLite being misused), but in skimming your "wall of SQL"1, I noticed:

...
BEGIN EXCLUSIVE TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;
INSERT INTO vt1(vt1) VALUES('integrity-check');
BEGIN  TRANSACTION;
...

However, according to SQLite's Transactions documentation, you cannot nest BEGIN statements (attempting to do so returns an error). You start your post with "The query below", but in reality you have a sequence of queries: I don't know how you are executing them, but are you correctly checking the return-codes of each query to ensure they are not in error?


1 I don't know whether this is a "real" query or a "constructed test case" (I strongly suspect the latter), but on StackOverflow, such a wall of almost impenetrable SQL would likely be met with a request for an MCVE – a minimal, complete, verifiable example. In other words, for you to cut your query down to the absolute minimum capable of demonstrating the problem.

(3) By Richard Hipp (drh) on 2021-01-11 13:12:56 in reply to 2 [source]

The OP is using a fuzzer. A simplified test case is as follows:

CREATE VIRTUAL TABLE t0 USING rtree(c0,c1,c2);
INSERT INTO t0(c0,c1,c2) VALUES(0, 0, 9223372036854775807.8);
CREATE TABLE t1(c0);
INSERT INTO t1(c0) VALUES(9223372036854775807);
SELECT * FROM t0, t1 WHERE t0.c2=t1.c0;
SELECT * FROM t0, t1 WHERE t0.c2=t1.c0 OR t0.c1;

I think the issue comes down the question of whether 9223372036854775807.8 is or is not equal to 9223372036854775807.0, and that is a question of precision. I am not sure this is a bug. We are continuing to investigate.

(4) By Richard Hipp (drh) on 2021-01-11 13:32:56 in reply to 3 [link] [source]

The RTREE virtual table does its internal computation as 32-bit floating point values. The values 9223372036854775807.8 and 9223372036854775807 are the same number when they are represented as an IEEE754 32-bit float. Hence, the RTREE virtual table things those two values are equal.

The WHERE clause of the first SELECT statement (the one without the OR term) is processed by the RTREE virtual table. And so RTREE is doing the comparison, and it determines that the two numbers are the same. Hence, the row is returned.

In the second SELECT, the presence of the OR term means that the RTREE lookup is not possible, and therefore comparison is done by SQLite itself using 64-bit floating point values, and for 64-bit comparisons the two values are not equal. Hence, the WHERE condition is false in the second query.

This comes down to a limitation in RTREE that it is precision limited to 32-bit IEEE floats. This is also an example of why you should never do equality comparisons on floating-point numbers → because floating point numbers are apprximations. See the discussion of this issue in the SQLite documentation.

I'm going to claim that, while surprising, this is not a bug.

(5) By ddevienne on 2021-01-11 13:56:58 in reply to 4 [link] [source]

Not sure how to word it, but perhaps an additional sentence or paragraph in rtree roundoff could be added?

What you describe makes complete sense Richard, but the fact that depending on how a query is planned
leads to differences in results, based on who evaluates a given clause, is subtle and surprising.

At worse just a link to the forum? Although I'm not sure there are precedents for such links in the doc.

(7) By Ryan Smith (cuz) on 2021-01-11 14:35:56 in reply to 5 [link] [source]

At worse just a link to the forum? Although I'm not sure there are precedents for such links in the doc.

That is true, but then the forum is new. Before it was just a mailing list, but now that it is a proper forum, perhaps a precedent is not needed, and this is the opportune premier/inaugural antecedent. Links to posts are static and that intends to remain so, I think - they are definitely used in bug tickets.

(6) By Cong Ding (tson1111) on 2021-01-11 14:12:05 in reply to 4 [link] [source]

Thanks for your reduction and explanation, Richard! It's very helpful.