Select count(*) in Sub query

[vigneshn]

Hi

I am trying to get the count(*) from a subquery which has a column count(*) . The result of outer query according to me should be 1, but it takes the count(*) from the inner query and results the inner query's count(*). Is this known/correct behaviour.

-- Inner Query Output.

dbadmin=> select 'No of rows' as description , count(*) from tables;
description | count
-------------+-------
No of rows | 20
(1 row)

-- Wrapping Outer Query

dbadmin=> select count(*) from (select 'No of rows' as description, count(*) as cnt from tables) as sub;
count
-------
20
(1 row)

-- I was expecting The output as 1 but I get 20 since the inner query count(*) value is 20

But when I change my query in the below format, I get the expected output. What is the reason , Could anyone explain please.


dbadmin=> select count(*) from (
dbadmin(> Select Description, cnt from (
dbadmin(> select 'No of Rows' as description , Count(*) as cnt from tables) as sub1
dbadmin(> group by Description, cnt) as sub2;
count
-------
1
(1 row)


Thanks in Advance.

[scutter]

Interesting. Looks like a bug. It was as expected without the ‘No of rows’ tacked on.

explain select count(*) from (select 'No of rows' as description, count(*) as cnt from tables) as sub;

Access Path:
+-GROUPBY NOTHING [Cost: 609, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: Query Initiator
| Execute on: Query Initiator
| +---> STORAGE ACCESS for t [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| | Projection: v_internal.vs_tables_view_p
| | Materialize: t.table_schema_id
| | Execute on: Query Initiator

explain select count(*) from (select count(*) as cnt from tables) as sub;

Access Path:
+-GROUPBY NOTHING [Cost: 608, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: Query Initiator
| Execute on: Query Initiator
| +---> GROUPBY NOTHING [Cost: 607, Rows: 1 (NO STATISTICS)] (PATH ID: 3)
| | Aggregates: count(*)
| | Execute on: Query Initiator
| | +---> STORAGE ACCESS for t [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: v_internal.vs_tables_view_p
| | | Materialize: t.table_schema_id
| | | Execute on: Query Initiator

[JimKnicely]

That is interesting... I agree with Sharon in that it is a bug.

If you add a GROUP BY on the description column, you get a row count of one

Code: Select all

dbadmin=> select count(*) from (select 'No of rows' as description, count(*) as cnt from tables group by description) as sub;
 count
-------
     1
(1 row)