/ View Ticket
Login
Ticket Hash: 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: (text/x-fossil-wiki)
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:

<verbatim>
    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;
</verbatim>

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: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
OSSFuzz found the following counter-example to the fix described above:

<blockquote><verbatim>
SELECT(WITH c AS(VALUES(1))SELECT '' FROM c,c) x WHERE x+x;
</verbatim></blockquote>

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

drh added on 2019-01-01 18:03:21: (text/x-fossil-wiki)
Check-in [5c188361a9140780] resolves the secondary issue that was discovered
by OSSFuzz