BUG #8396: Window function results differ when selecting from table and view, with where clause - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8396
Logged by:          Paul M.
Email address:      paul@weotta.com
PostgreSQL version: 9.3rc1
Operating system:   Ubuntu Linux
Description:

When I select from a view, the where clause in my select statement does not
restrict the rows processed by window functions referenced inside the view
definition. Thus, if window functions are involved, using a where clause
when selecting from a view and using a where clause when selecting directly
from the underlying table produces different results.


Without wanting to speculate on the cause of the differing results, I will
say that this seems to be a case of an issue noted a year ago on Stack
Overflow: "Will Postgres push down a WHERE clause into a VIEW with a Window
Function (Aggregate)?"


http://stackoverflow.com/questions/7533877/


At that time, responder Evan Carroll noted, "I can't think of anyway an
un-referenced Window function can change the result if the WHERE was pushed
down." This seems to be just such a case.


Thanks in advance for looking into this. I've written a test case, which I
hope will be helpful...


create table plant (
  plant_id character varying( 6 )
, city character varying( 25 )
, constraint p_pk_pid primary key ( plant_id )
);


insert into plant
  ( plant_id, city )
values
  ( '14 ST', 'San Francisco' )
, ( 'FOLSOM', 'San Francisco' )
, ( 'CHAVEZ', 'San Francisco' )
, ( 'HEINZ', 'Berkeley' )
;


create view plant_extend as
select
  plant_id as plant_id
, row_number() over ( partition by city order by plant_id ) as plant_number
, count(*) over ( partition by city ) as of_plants_in_city
, city as city
from plant;


-- Despite the where clause, the window functions see all 3 San Francisco
plants:


select
  *
from plant_extend
where
  plant_id = 'FOLSOM'
;


-- But when the query is expressed this way, the window functions see only
the Folsom Street plant:


select
  plant_id as plant_id
, row_number() over ( partition by city order by plant_id ) as plant_number
, count(*) over ( partition by city ) as of_plants_in_city
, city as city
from plant
where
  plant_id = 'FOLSOM'
;