|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: <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