SQLite

View Ticket
Login
2019-05-19
17:01 Ticket [787fa716] Assertion fault when multi-use subquery implemented by co-routine status still Fixed with 3 other changes (artifact: 929aa109 user: mrigger)
2019-05-18
21:22
Fix an assert() failure that could occur in a join query if the RHS of an IN() operator is a list containing correlated expressions. This problem was introduced by checkin [e130319317e7611938] which was part of the fix for ticket [787fa716be3a7f650c] - so this commit is part of that ticket's fix too. (check-in: 778b1224 user: dan tags: trunk)
21:22 Ticket [787fa716] Assertion fault when multi-use subquery implemented by co-routine status still Fixed with 3 other changes (artifact: fe9414e3 user: mrigger)
2018-12-31
20:39
Additional steps to help ensure that scalar subqueries are only evaluated once even if they are used in multiple places within the query. This fixes a performance regression reported on the mailing list and caused by check-in [531eca6104e41e43] which was a fix for ticket [787fa716be3a7f650c]. Think of this check-in as an improved fix for that ticket. (check-in: e1303193 user: drh tags: trunk)
2018-11-09
14:18 Fixed ticket [787fa716]: Assertion fault when multi-use subquery implemented by co-routine plus 3 other changes (artifact: f1d68076 user: drh)
14:17
Fix for ticket [787fa716be3a7f650cac] (check-in: 531eca61 user: drh tags: trunk)
13:39 Ticket [787fa716] Assertion fault when multi-use subquery implemented by co-routine status still Open with 4 other changes (artifact: 23c14e4a user: drh)
00:02
Disable the use of coroutines for subqueries within a query that is the RHS of an IN operator, as the IN operator might be evaluated more than once. Possible fix for [787fa716be3a7f65], unless we can come up with something better. Later: Counter-example found. (Closed-Leaf check-in: 8d663bfa user: drh tags: tkt787fa716-deadend)
2018-11-08
22:03 Ticket [787fa716] Assertion fault due to IN operator status still Open with 3 other changes (artifact: 0d3537a7 user: drh)
20:52 Ticket [787fa716]: 3 changes (artifact: 29699fe9 user: drh)
20:49 Ticket [787fa716]: 3 changes (artifact: 838f5f42 user: drh)
19:27 Ticket [787fa716]: 6 changes (artifact: 7da230dd user: drh)
15:06 New ticket [787fa716]. (artifact: 03d10343 user: drh)

Ticket Hash: 787fa716be3a7f650cac1b1413f12f95f5e7639d
Title: Assertion fault when multi-use subquery implemented by co-routine
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-19 17:01:13
Version Found In: 3.25.3
User Comments:
drh added on 2018-11-08 15:06:12:

The following SQL gives an assertion fault:

CREATE TABLE artists (
  id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  name varchar(255)
);
CREATE TABLE albums (
  id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  name varchar(255),
  artist_id integer REFERENCES artists
);
INSERT INTO artists (name) VALUES ('Ar');
INSERT INTO albums (name, artist_id) VALUES ('Al', 1);
SELECT artists.*
FROM artists
INNER JOIN artists AS 'b' ON (b.id = artists.id)
WHERE (artists.id IN (
  SELECT albums.artist_id
  FROM albums
  WHERE ((name = 'Al')
    AND (albums.artist_id IS NOT NULL)
    AND (albums.id IN (
      SELECT id
      FROM (
        SELECT albums.id,
               row_number() OVER (
                 PARTITION BY albums.artist_id
                 ORDER BY name
               ) AS 'x'
        FROM albums
        WHERE (name = 'Al')
      ) AS 't1'
      WHERE (x = 1)
    ))
    AND (albums.id IN (1, 2)))
));

This problem was reported on the mailing list by Jeremy Evans.


drh added on 2018-11-08 19:27:38:

This appears to have nothing to do with window functions. The use of window functions in the example above merely disables the query flattener. The problem can also be seen in the following query:

CREATE TABLE t1 (a);
CREATE TABLE t2 (b);
CREATE TABLE t3 (c);
CREATE TABLE t4 (d);
INSERT INTO t1 (a) VALUES (104);
INSERT INTO t2 (b) VALUES (104);
INSERT INTO t3 (c) VALUES (104);
INSERT INTO t4 (d) VALUES (104);

-- Disable the query-flattener
.testctrl optimizations 0x01

SELECT *
FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN (
  SELECT t3.c
  FROM t3
  WHERE t3.c IN (
    SELECT d FROM (SELECT d FROM t4) AS innermost WHERE innermost.d=104
  )
);

The query above fails in all versions of SQLite from 3.8.11 and later. The check-in that causes the failure is [020b8b106fc8f840f6b506e1c9c3b] on 2015-05-29


drh added on 2018-11-08 20:49:18:

Here is another test case that does not require manually disabling the query flattener:

CREATE TABLE t5(a, b, c, d);
CREATE INDEX t5a ON t5(a);
CREATE INDEX t5b ON t5(b);
CREATE TABLE t6(e);
INSERT INTO t6 VALUES(1);
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN (
  SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1
);

The failure in this test case also goes back to check-in [020b8b106fc8f840f6b506e1c9c3bc75676daec3] on 2015-05-29.


drh added on 2018-11-08 22:03:39:

Here is a variant of the second test case that does not require the manual disabling of the query flattener:

CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (104);
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
SELECT *
FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN (
  SELECT t3.c
  FROM t3
  WHERE t3.c IN (
    SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104
  )
);


drh added on 2018-11-09 13:39:45:

Here is another test case that does not make use of the IN operator. The common factor is now the use of co-routines to implement a subquery and then evaluating that subquery more than once.

CREATE TABLE t1(a1, a2, a3);
CREATE INDEX t1a2 ON t1(a2, a1);
CREATE INDEX t1a3 ON t1(a3, a1);
CREATE TABLE t2(d);
INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2);
INSERT INTO t2 VALUES(22);
SELECT * FROM t1 WHERE (a2=1 OR a3=2) AND a1 = (
  SELECT d FROM (SELECT DISTINCT d FROM t2) WHERE d=22
);

This new test case demonstrates that checkin [8d663bfaaa4656c4f4ff1] is not a solution to the problem.


mrigger added on 2019-05-18 21:22:10:

In the latest version of SQLite (3.28.0), I could use the following statement sequence to cause a segfault:

CREATE TABLE t0 (c0, c1, PRIMARY KEY (c0, c1)); CREATE TABLE t1 (c0); INSERT INTO t1 VALUES (2); SELECT * FROM t0, t1 WHERE (t0.c1 >= 1 OR t0.c1 < 1) AND t0.c0 IN (1, t1.c0) ORDER BY 1;


mrigger added on 2019-05-19 17:01:13:

Note that I sent the test case that triggers the segfault via a personal email to the SQLite developers. The bug was fixed by check-in [778b1224a318d013] shortly after and before I attached the bug report as part of this ticket.