Re: BUG #13863: Select from views gives wrong results - Mailing list pgsql-bugs
From | Haribabu Kommi |
---|---|
Subject | Re: BUG #13863: Select from views gives wrong results |
Date | |
Msg-id | CAJrrPGdmBM9=ttRfT24FOzDySE9dSs1Uw0+ynD5FAo+pFR9Z8w@mail.gmail.com Whole thread Raw |
In response to | BUG #13863: Select from views gives wrong results (wrb@autistici.org) |
Responses |
Re: BUG #13863: Select from views gives wrong results
|
List | pgsql-bugs |
On Thu, Jan 14, 2016 at 5:35 AM, <wrb@autistici.org> wrote: > The following bug has been logged on the website: > > Bug reference: 13863 > Logged by: W > Email address: wrb@autistici.org > PostgreSQL version: 9.5.0 > Operating system: Mac OS X 10.11.2 (postgres is from homebrew) > Description: > > Hello, > > I noticed strange behavior while using views with the new grouping sets > functionality. > > Steps to reproduce are here: > https://gist.github.com/wrb/a73f45dcd335c8bc166c I feel, It is always better to place the simple sql file in the mail itself, instead of link. Following are the sql statements that i took from the link. -- table create table emp (emp_id serial, department text, position text, sex text, salary numeric); -- data begin; insert into emp (department, position, sex, salary) values ('marketing', 'junior', 'F', 20000); insert into emp (department, position, sex, salary) values ('marketing', 'junior', 'F', 25000); insert into emp (department, position, sex, salary) values ('marketing', 'junior', 'M', 25000); insert into emp (department, position, sex, salary) values ('it', 'senior', 'M', 125000); insert into emp (department, position, sex, salary) values ('sales', 'normal', 'M', 50000); insert into emp (department, position, sex, salary) values ('sales', 'junior', 'F', 30000); insert into emp (department, position, sex, salary) values ('sales', 'junior', 'F', 30000); commit; -- view create view v_emp_stats as select department, position, sex, avg(salary) from emp group by cube (department, position, sex); -- materialized view create materialized view mv_emp_stats as select department, position, sex, avg(salary) from emp group by cube (department, position, sex); -- those two selects should be identical, right? select * from v_emp_stats where department is not null and position is null; select * from mv_emp_stats where department is not null and position is null; --CTE with emp_data as ( select department, position, sex, avg(salary) from emp group by cube (department, position, sex) ) select * from emp_data where department is not null and position is null; > On my machine, selecting from view gives zero results while selecting from > materialize view (or using CTE) gives the correct result. There's nothing > unusual in log. > > Looking at explain analyze function, it seems that the filter is pushed too > deep and removes rows from the original table and not from the aggreage. It > works correctly when using CTE. As per my understanding of the result, this is correct. >-- those two selects should be identical, right? >select * from v_emp_stats where department is not null and position is null; View's don't have storage, so any query on the view is rewritten to the target relation. In the above scenario, the same happened and the where clause is pushed into the view select query. There is no row that is presented in the emp table satisfies the where clause that is provided. Because of this reason no data is selected. >select * from mv_emp_stats where department is not null and position is null; > >with emp_data as ( >select department, position, sex, avg(salary) from emp group by cube (department, position, sex) >) >select * from emp_data where department is not null and position is null; But where as with materialized view, it has the storage. The materialized view is populated with the query result during creation. So adding an where clause on materialized view satisfies with it and thus it returned the results. The number of records in the materialized view are 27 compared to the number of records in table are 7. The same with the CTE also, first the inner query is executed and on top of that result the outer query is executed. Because of this reason, the where clause is satisfied and the results are returned. This is just an usage problem. Regards, Hari Babu Fujitsu Australia
pgsql-bugs by date: