Re: [Postgres-xc-developers] bug 3602898 status

[Koichi S. <koi...@gm...>]

I did something different for this bug, as follows;

----8<-----------8<-------------
koichi=# create table T1
koichi-# (
koichi(#     C1 varchar(128),
koichi(#     C2 numeric(9,2),
koichi(#     C3 int);
CREATE TABLE
koichi=#
koichi=# insert into T1 values
koichi-#     ('N1', 1.1, 20121001),
koichi-#     ('N1', 2.1, 20121002);
INSERT 0 2
koichi=#
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=#
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=#
koichi=# create table newt1 as
koichi-# select
koichi-# C1 "NewC1",
koichi-# C2::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231;
INSERT 0 2
koichi=#
koichi=# create table newt2 as
koichi-# select
koichi-# C1 "NewC1",
koichi-# sum(C2)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
INSERT 0 1
koichi=# create table newt4 as
koichi-# select
koichi-# C1 "NewC1",
koichi-# count(*)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
INSERT 0 1
koichi=#
---->8----------->8-------------

Interesting result is:

can use sum() and count() in CREATE TABLE AS SELECT ... but not avg().   I
suspect this issue occurs with aggregate where we have different functions
for two-phase and three-phase aggregation.

Hope this is a good hint to fix the problem.

Regards;

----------
Koichi Suzuki


2013/6/19 Koichi Suzuki <koi...@gm...>

> I've checked the current master status for the bug 3602898.
>  Unfortunately, the bug still exists.
>
> Attached is the script to reproduce it.
>
> Regards;
> ----------
> Koichi Suzuki
>