Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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,
@hoehrmann

This comment has been minimized.

Copy link
Owner Author

hoehrmann commented Sep 22, 2018

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

@hoehrmann

This comment has been minimized.

Copy link
Owner Author

hoehrmann commented Sep 22, 2018

% 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         
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.