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

> On 14. 1. 2016, at 0:50, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>
> 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.
>
> 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.

I understand why it happens - with normal group by, the predicate *can* be pushed down
unless it is on generated column. But with cube, every column is a geenrated column because
of the way cube works.

It works correctly unless cube is involved:

create table test (a int, b int);
create view v_test as select a, count(*) from test group by a;
create view v_test2 as select a, count(*) from test group by cube (a);
insert into test values (1, 1), (2, 2);

this doesn't get pushed down either as CTE or from view:

explain with t as (select a, count(*) from test group by a) select * from t where count = 1;
QUERY PLAN
----------------------------------------------------------------------
CTE Scan on t (cost=45.90..50.40 rows=1 width=12)
Filter: (count = 1)
CTE t
-> HashAggregate (cost=43.90..45.90 rows=200 width=4)
Group Key: test.a
-> Seq Scan on test (cost=0.00..32.60 rows=2260 width=4)
(6 rows)

explain select * from v_test where count = 1;
QUERY PLAN
--------------------------------------------------------------
HashAggregate (cost=49.55..52.05 rows=200 width=4)
Group Key: test.a
Filter: (count(*) = 1)
-> Seq Scan on test (cost=0.00..32.60 rows=2260 width=4)
(4 rows)

This does get pushed down correctly:

explain select * from v_test where a = 1;
QUERY PLAN
------------------------------------------------------------
GroupAggregate (cost=0.00..38.32 rows=1 width=4)
Group Key: test.a
-> Seq Scan on test (cost=0.00..38.25 rows=11 width=4)
Filter: (a = 1)
(4 rows)

This doesn't get pushed down, but it's a known issue with CTEs being optimization boundary in postgres:

explain with t as (select a, count(*) from test group by a) select * from t where a = 1;
QUERY PLAN
----------------------------------------------------------------------
CTE Scan on t (cost=45.90..50.40 rows=1 width=12)
Filter: (a = 1)
CTE t
-> HashAggregate (cost=43.90..45.90 rows=200 width=4)
Group Key: test.a
-> Seq Scan on test (cost=0.00..32.60 rows=2260 width=4)
(6 rows)

But when cube gets involved:

with t as (select a, count(*) from test group by cube (a)) select * from t where a is null;
a | count
---+-------
| 2

CTE still works, because it doesn't get pushed down:

explain with t as (select a, count(*) from test group by cube (a)) select * from t where a is null;
QUERY PLAN
----------------------------------------------------------------------------
CTE Scan on t (cost=177.47..181.49 rows=1 width=12)
Filter: (a IS NULL)
CTE t
-> GroupAggregate (cost=158.51..177.47 rows=201 width=4)
Group Key: test.a
Group Key: ()
-> Sort (cost=158.51..164.16 rows=2260 width=4)
Sort Key: test.a
-> Seq Scan on test (cost=0.00..32.60 rows=2260 width=4)
(9 rows)

but with a view, it gets pushed down to the table, even though it doesn't make any sense for cube,
and gives incorrect results:

select * from v_test2 where a is null;
a | count
---+-------
| 0

explain select * from v_test2 where a is null;
QUERY PLAN
------------------------------------------------------------------
GroupAggregate (cost=32.79..32.89 rows=2 width=4)
Group Key: test.a
Group Key: ()
Filter: (test.a IS NULL)
-> Sort (cost=32.79..32.82 rows=11 width=4)
Sort Key: test.a
-> Seq Scan on test (cost=0.00..32.60 rows=11 width=4)
Filter: (a IS NULL)

No, I'm pretty sure this is incorrect result, because views are supposed to provide logical data independence and running the same query through nested select, CTE and view should give the same result, right? If not, I'm pretty sure this is the first case where it doesn't work like this.

W

>
> This is just an usage problem.
>
> Regards,
> Hari Babu
> Fujitsu Australia