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 sqlite> -- Possible unexpected result - expected "Error: no such column: fake_column" sqlite> SELECT 1 WINDOW win AS (PARTITION BY fake_column); 1 sqlite> -- Expected result sqlite> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a); 1 1 sqlite> -- Expected result sqlite> WITH y AS (SELECT Row_Number() OVER (PARTITION BY a) FROM x) ...> SELECT * FROM y; 1 1 sqlite> -- Expected result sqlite> SELECT * FROM ( ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)); 1 1 sqlite> -Jake _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
