Re: BUG #13863: Select from views gives wrong results

On Thu, Jan 14, 2016 at 5:35 AM, <wrb(at)autistici(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13863
> Logged by: W
> Email address: wrb(at)autistici(dot)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