#387 CREATE TABLE AS ... with aggregate

[Koichi Suzuki]

Report from Venky Kandaswamy

------------8<-------------------------------8<-----------
Lets say we run this query in Postgres-XC (latest master):

select
msn_account_name "MsnAccountName",
avg(msnavgpos)::Numeric(9,2) "MsnAveragePosition"
from bi6121.dm_sem_performance_msn_32
where date_id between 20121001 and 20121231
group by msn_account_name;

We get the result:

"MSN_RTW_Adchemy";4.06

But if we try to create a table out of the result, we get an error:

create table temp1 as
select
msn_account_name "MsnAccountName",
avg(msnavgpos)::Numeric(9,2) "MsnAveragePosition"
from bi6121.dm_sem_performance_msn_32
where date_id between 20121001 and 20121231
group by msn_account_name;

ERROR: function pg_catalog.numeric_avg(numeric) does not exist
LINE 3: msn_account_name "MsnAccountName",
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function pg_catalog.numeric_avg(numeric) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 65
------------>8------------------------------->8-----------

I suspect the above error is related to the difference of aggregate function infrastructure in XC from PG, additional cfunc (collection function).

[Koichi Suzuki]

Ashutosh Bapat wrote:
Yes, CTAS is not handling the aggregation correctly. It's probably passing down the whole query to the datanode, which can not handle aggregation in this case. It needs to look at the query to see if it's shippable or not, in first place.

[Koichi Suzuki]

Nice to have in 1.1. May not be a beta blocker. Venky, could your try to provide a fix?

[Koichi Suzuki]

Do the test with the current master and determine how to handle this.

[Koichi Suzuki]

Test with the current master.

[Koichi Suzuki]

[Koichi Suzuki]

Did somewhat simpler test against the current master as of June 19th. We still have this problem. test.sql reproduces the problem.

koichi=# create table T1
koichi-# (
koichi(# C1 varchar(128),
koichi(# C2 numeric(9,2),
koichi(# C3 int);
CREATE TABLE
koichi=# insert into T1 values
koichi-# ('N1', 1.1,20121001),
koichi-# ('N1', 2.1,20121002);
INSERT 0 2
koichi=# select
koichi-# C1 "NewC1",
koichi-# avg(C2)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
NewC1 | NewC2
-------+-------
N1 | 1.60
(1 row)

koichi=# create table newt as
koichi-# select
koichi-# C1 "NewC1",
koichi-# avg(C2)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
ERROR: function pg_catalog.numeric_avg(numeric) does not exist
LINE 4: avg(C2)::Numeric(9,2) "NewC2"
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
koichi=# \q
PGXC$