Bug: WINDOW clause within a CTE

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug: WINDOW clause within a CTE

Jake
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: WINDOW clause within a CTE

Dan Kennedy-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users