Re: [sqlite] Bug: WINDOW clause within a CTE

2019-09-15 Thread Dan Kennedy 


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

2019-09-14 Thread Jake Thaw 
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