On 15/9/62 11:57, Jake Thaw wrote:
The following examples demonstrate a possible bug when using a WINDOW clause within a CTE:SQLite version 3.30.0 2019-09-14 16:44:51 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE x AS SELECT 1 a UNION SELECT 2; sqlite> sqlite> -- Unexpected result - expect 1,1 sqlite> WITH y AS ( ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)) ...> SELECT * FROM y; 1 2 sqlite> -- Unexpected result - expected "Error: no such column: fake_column" sqlite> WITH y AS ( ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY fake_column)) ...> SELECT * FROM y; 1 2
Thanks for tracking down and reporting these. Now fixed here: https://sqlite.org/src/info/ca564d4b5b19fe56
sqlite> -- Possible unexpected result - expected "Error: no such column: fake_column" sqlite> SELECT 1 WINDOW win AS (PARTITION BY fake_column); 1
I think we'll leave this one as is. SQLite only resolves the references in the WINDOW clause if it is used, so this doesn't produce an error. There are few other scenarios SQLite does this too. The statement "SELECT (0 AND fake_column);", for example.
Dan. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
