|Title:||Window function in correlated subquery causes assertion fault|
|Last Modified:||2019-01-01 18:03:21|
|Version Found In:||3.26.0|
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:
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