Possible bug in window function queries.

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Possible bug in window function queries.

Shawn Wagner
Been playing around with the new window functions in 3.25, and ran into
something that looks like an infinite loop.

Working with the t1 table many of the examples in the documentation use,
I've come up with a minimal test case:

sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id >
1 order by b;
id          b           x
----------  ----------  ----------
2           B           two
3           C           three
4           D           one
5           E
6           F           two
7           G           three

is all well and good. However, after adding a LIMIT to the query:

sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id >
1 order by b limit 1;
(time goes by)
^CError: interrupted

A LIMIT of just a few rows causes the query to hang and never produce any
output while sqlite3 uses 100% CPU.

LIMIT 4 and up works, but change the comparison to >= and LIMIT 4 also
freezes, but LIMIT 5 works. Take out the ORDER BY b and no freeze.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users