Re: [sqlite] Bug: WINDOW clause within a CTE
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
[sqlite] Bug: WINDOW clause within a CTE
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