Bug report: Window functions in VIEWs broken in 3.25.1

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

Bug report: Window functions in VIEWs broken in 3.25.1

Bjoern Hoehrmann
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.

Thanks,
--
https://bjoern.hoehrmann.de/
_______________________________________________
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 report: Window functions in VIEWs broken in 3.25.1

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