Bjoern Hoehrmann wrote: > 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
The EXPLAIN output shows that the optimizer ended up generating a program for "SELECT 1 - 1 FROM example". > while PostgreSQL 9.6 gives 0, 1, 2. And the same query outside a view gives the correct ouput. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
