SQLite

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

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.

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.

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