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,

That is probably a bug since »The SQLite developers used the PostgreSQL window function documentation as their primary reference for how window functions ought to behave. Many test cases have been run against PostgreSQL to ensure that window functions operate the same way in both SQLite and PostgreSQL.« - https://www.sqlite.org/windowfunctions.html

% 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