sqlite-3.25.1-window-function-in-view-bug.txt

Hi,

  Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that
Window functions in VIEWS behave differently from PostgreSQL 9.6 and
from what I expect.

  DROP TABLE IF EXISTS example;
  CREATE TABLE example(t INT, total INT);
  INSERT INTO example VALUES(0,2);
  INSERT INTO example VALUES(5,1);
  INSERT INTO example VALUES(10,1);

  DROP VIEW IF EXISTS view_example;
  CREATE VIEW view_example AS
  SELECT
    NTILE(256) OVER (ORDER BY total) - 1 AS nt
  FROM
    example
  ;

  SELECT * FROM view_example;

In SQLite 3.25.1 I get 0, 0, 0, while PostgreSQL 9.6 gives 0, 1, 2.

(Executing the SELECT directly gives the correct result in SQLite.)

Thanks,

% sqlite-tools-linux-x86-3250100/sqlite3 
-- Loading resources from /home/bjoern/.sqliterc
SQLite version 3.25.1 2018-09-18 20:20:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>   DROP TABLE IF EXISTS example;
sqlite>   CREATE TABLE example(t INT, total INT);
sqlite>   INSERT INTO example VALUES(0,2);
sqlite>   INSERT INTO example VALUES(5,1);
sqlite>   INSERT INTO example VALUES(10,1);
sqlite> 
sqlite>   DROP VIEW IF EXISTS view_example;
sqlite>   CREATE VIEW view_example AS
   ...>   SELECT
   ...>     NTILE(256) OVER (ORDER BY total) - 1 AS nt
   ...>   FROM
   ...>     example 
   ...>   ;
sqlite> 
sqlite>   SELECT * FROM view_example;
nt        
----------
0         
0         
0         
sqlite> SELECT
   ...> NTILE(256) OVER (ORDER BY total) - 1 AS nt
   ...> FROM
   ...> example ;
nt        
----------
0         
1         
2