The following bug has been logged on the website: Bug reference: 8396 Logged by: Paul M. Email address: p...@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' ; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
