SQLite Forum

Bug in Sqlite >3.35.0 when using WHERE(EXISTS)
Login

Bug in Sqlite >3.35.0 when using WHERE(EXISTS)

(1) By Tom Forbes (orf123) on 2021-04-06 23:35:30 [link] [source]

Hello, I'm currently tracking down a test failure in the Django test suite when using SQLite 3.35.3 on MacOS.

From our test suite I've extracted the definitions of two tables and a single query that returns different results on sqlite 3.35.3 and sqlite 3.34.0.

On 3.34.0 and below the query returns three rows:

1|Joe|Smith|10
2|Frank|Meyer|20
3|Max|Mustermann|30

However on 3.35.3 it returns only a single row:

1|Joe|Smith|10

I see in the changelog that there is a potentially relevant entry in 3.35.0:

Attempt to process EXISTS operators in the WHERE clause as if they were IN operators, in cases where this is a valid transformation and seems likely to improve performance.

Setup

CREATE TABLE "expressions_company"
(
    "id"                  integer          NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name"                varchar(100)     NOT NULL,
    "num_employees"       integer unsigned NOT NULL CHECK ("num_employees" >= 0),
    "num_chairs"          integer unsigned NOT NULL CHECK ("num_chairs" >= 0),
    "ceo_id"              integer          NOT NULL REFERENCES "expressions_employee" ("id") DEFERRABLE INITIALLY DEFERRED,
    "point_of_contact_id" integer          NULL REFERENCES "expressions_employee" ("id") DEFERRABLE INITIALLY DEFERRED,
    "based_in_eu"         bool             NOT NULL
);
INSERT INTO "expressions_company"
VALUES (1, 'Example Inc.', 2300, 5, 1, NULL, 0);
INSERT INTO "expressions_company"
VALUES (2, 'Foobar Ltd.', 3, 4, 2, NULL, 1);
INSERT INTO "expressions_company"
VALUES (3, 'Test GmbH', 32, 1, 3, NULL, 0);
CREATE TABLE "expressions_employee"
(
    "id"        integer     NOT NULL PRIMARY KEY AUTOINCREMENT,
    "firstname" varchar(50) NOT NULL,
    "lastname"  varchar(50) NOT NULL,
    "salary"    integer     NULL
);
INSERT INTO "expressions_employee"
VALUES (1, 'Joe', 'Smith', 10);
INSERT INTO "expressions_employee"
VALUES (2, 'Frank', 'Meyer', 20);
INSERT INTO "expressions_employee"
VALUES (3, 'Max', 'Mustermann', 30);

UPDATE "expressions_company"
SET "name"                = 'Test GmbH',
    "num_employees"       = 32,
    "num_chairs"          = 1,
    "ceo_id"              = 3,
    "point_of_contact_id" = 3,
    "based_in_eu"         = 0
WHERE "expressions_company"."id" = 3;

Failing query

SELECT "expressions_employee"."id",
       "expressions_employee"."firstname",
       "expressions_employee"."lastname",
       "expressions_employee"."salary"
FROM "expressions_employee"
WHERE (EXISTS(SELECT (1) AS "a"
              FROM "expressions_company" U0
              WHERE U0."ceo_id" = "expressions_employee"."id"
              LIMIT 1) OR EXISTS(SELECT (1) AS "a"
                                 FROM "expressions_company" U0
                                 WHERE U0."point_of_contact_id" = "expressions_employee"."id"
                                 LIMIT 1));

(2) By Tom Forbes (orf123) on 2021-04-06 23:45:54 in reply to 1 [link] [source]

FYI, removing the LIMIT 1 makes the query return the correct results in 3.35.3.

(3) By Richard Hipp (drh) on 2021-04-07 00:05:57 in reply to 1 [link] [source]

Fixed in 3.35.4. Item #26 in the release notes. The same problem was Previously reported by Mozilla it appears to have broken Firefox. Nevertheless, thanks for the report. Please feel free to report any issues you see on this forum.

In the future, if possible, it would be great if you could try out our betas prior to each release, just to make sure we don't mess up other optimization attempts.

(4) By Tom Forbes (orf123) on 2021-04-07 00:12:05 in reply to 3 [link] [source]

Thank you! I'll work on adding a fallable step to our CI build that runs on the latest sqlite beta. Is there a stable URL we can grab the latest amalgamation beta from, without hard-coding a specific version?

(5) By Richard Hipp (drh) on 2021-04-07 01:28:37 in reply to 4 [source]

Trunk is usually stable. You are welcomed to load the latest trunk version using a URL like this:

https://sqlite.org/src/tarball/trunk/sqlite.tar.gz

A more bandwidth and server-CPU friendly technique is to clone the Fossil repository and do "fossil update trunk" when you want the latest version. See the README.md file for details.

There is also a GitHub mirror of the canonical repository.

But these sources do not provide you with an amalgamation. To get an amalgamation you need to download the canonical source tree and do:

./configure && make sqlite3.c

Or on Windows:

nmake /f Makefile.msc sqlite3.c

You will need tclsh installed for the Makefiles to work. But I do not believe there are any other dependencies apart from the C compiler.

If you only want an amalgamation, we usually provide "Pre-release Snapshots" on the Download page. There is not one there as I write this, as we are too soon after an official release. But come back in another week or two and you might find it then. The pre-release snapshots are a tarball that contain pre-build "sqlite3.c" and "sqlite3.h" files together with Makefiles for building the command-line tool.

(6) By Tom Forbes (orf123) on 2021-04-07 01:50:16 in reply to 5 [link] [source]

Thank you! I'll try using the trunk for now.

I would suggest that a "sqlite-[product]-latest.zip" format for snapshots might be useful if you want to encourage CI testing against betas and pre-releases. In this scenario we don't really care what specific snapshot or beta we are testing against, only that it's the latest relevant one. If the tests fail with the snapshot/beta then it's worth looking into and potentially reporting an issue if one exists, but otherwise it's plain sailing.

The moment you introduce a step that requires watching pre-releases and updating a URL in a CI file you ensure it won't happen automatically or regularly.

(7) By ThanksRyan on 2021-04-07 14:10:27 in reply to 6 [link] [source]

Are you saying you want a zip version of the src from trunk? That's also available:

https://www.sqlite.org/src/zip/trunk/download.zip