/ View Ticket
Login
Ticket UUID: d0866b26f83e9c55e30de0821f5deb2a0bf6a7e6
Title: Window function in correlated subquery causes assertion fault
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-01-01 18:03:21
Version Found In: 3.26.0
User Comments:
dan added on 2018-12-31 09:14:12:

Using a window function in a correlated subquery with a "ROWS" window that is not "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" (the default) causes an assert to fail. In non-debug builds valgrind also shows problems. Example:

    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES('a'), ('b'), ('c');

    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3);

    SELECT x, (
      SELECT sum(b)
        OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
      FROM t2 WHERE b<x
    ) FROM t1;

This problem was discovered and reported to the mailing list by Nicolas Roy-Renaud here:

http://sqlite.1065341.n5.nabble.com/Bug-in-3-25-2-RECURSIVE-CTE-window-function-tp103958p105246.html


drh added on 2018-12-31 18:09:33:

The ephemeral table used for the partition table was being reopened with each iteration of the correlated subquery. However, there were OP_OpenDup cursors on that table that were not being reopened. The solution was to enhance the OP_OpenEphemeral opcode so that on second and subsequent invocations it merely deletes any content from the table and reuses it rather than opening a new table.


drh added on 2019-01-01 13:02:56:

OSSFuzz found the following counter-example to the fix described above:

SELECT(WITH c AS(VALUES(1))SELECT '' FROM c,c) x WHERE x+x;

The query above fails with check-in [4678cb1044f0b4dc8] but works before then.


drh added on 2019-01-01 18:03:21:

Check-in [5c188361a9140780] resolves the secondary issue that was discovered by OSSFuzz