Aggregate within a ROW in a SELECT projection list is not allowed
Hello Community,
I found a weird behaviour when I wanted to move a database from one server to another
from
Informix Dynamic Server 14.10.FC7W1AEE
to
Informix Dynamic Server 14.10.FC10AEE
(both RHEL 8, but different machines).
Everything works well except the following command for a view.
create view "dbo".dirdoc1 (
typv,predpona,eecyear,eecnum,popivaz
) as select
x0.typv ,
min(row(x1.eecyear ,
x1.eecnum ,
x1.predpona )::eec ).predpona ,
min(row(x1.eecyear ,
x1.eecnum ,
x1.predpona )::eec ).eecyear ,
min(row(x1.eecyear ,
x1.eecnum ,
x1.predpona )::eec ).eecnum ,
max(x0.popivaz )
from
"dbo".typv x0 ,
outer("dbo".dirdoc x1 ) where
(
x0.typv = x1.typv
)
group by
x0.typv ;
On FC7W1 it works OK, but on FC10 I have been receiving the following error:
Aggregate within a ROW in a SELECT projection list is not allowed
When I run the command with just one of the aggregates, e.g.:
select
x0.typv,
--min(row(x1.eecyear,x1.eecnum,x1.predpona)::eec).predpona,
--min(row(x1.eecyear,x1.eecnum,x1.predpona)::eec).eecyear,
min(row(x1.eecyear,x1.eecnum,x1.predpona)::eec).eecnum ,
max(x0.popivaz)
from
It works on
FC10
, but when I use 2 or more
min()
columns it shows me an error.
Here are commands to replicate the issue. These commands allow create the view on FC7W1 but do not allow the creation of the view on FC10 and return the error above.
CREATE TABLE dbo.typv (
typv char(8) NOT NULL,
popivaz varchar(254) NOT NULL,
poznvaz varchar,
nazevc varchar,
snp smallint DEFAULT 0 NOT NULL,
peca smallint DEFAULT 0 NOT NULL,
uveu smallint,
PRIMARY KEY (typv) CONSTRAINT typv_pk
);###
create table dbo.dirdoc
(
typv char(8) not null ,
eecyear datetime year to year not null ,
eecnum integer not null ,
oj char(14),
predpona varchar(7)
default 'EEC' not null ,
celex char(10),
check (((((oj MATCHES 'OJ [0-9][0-9]/[CL] [1-9][0-9/ ][0-9/ ][0-9/ ][0-9/ ][0-9/ ]'
) AND ((oj MATCHES '*[0-9]/[0-9][0-9]*' ) OR (oj MATCHES '????????[1-9][0-9 ][0-9 ] '
) ) ) AND (oj NOT MATCHES '*/*/*/*' ) ) AND (oj NOT MATCHES '*[0-9] [0-9]*'
) ) AND (oj NOT MATCHES '*/[0-9][0-9][0-9]*' ) ) constraint dbo.oj_dirdoc
) extent size 16 next size 16 lock mode page;###
create row type informix.eec
(
eecyear datetime year to year,
eecnum integer,
predpona varchar(7)
);
###
CREATE FUNCTION informix.lessthanorequal(eec1 eec, eec2 eec) RETURNING BOOLEAN WITH(NOT VARIANT,NEGATOR=greaterthan);IF eec1.eecyear IS NULL OR eec2.eecyear IS NULL THEN RETURN NULL::BOOLEAN;
ELIF eec1.eecyear < eec2.eecyear THEN RETURN 't';
ELIF eec1.eecyear > eec2.eecyear THEN RETURN 'f';
END IF
IF eec1.eecnum IS NULL OR eec2.eecnum IS NULL THEN RETURN NULL::BOOLEAN;
ELIF eec1.eecnum < eec2.eecnum THEN RETURN 't';
ELIF eec1.eecnum > eec2.eecnum THEN RETURN 'f';
END IF
IF eec1.predpona IS NULL AND eec2.predpona IS NULL THEN RETURN 't';
ELIF eec1.predpona < eec2.predpona THEN RETURN 't';
ELIF eec1.predpona > eec2.predpona THEN RETURN 'f';
ELIF eec2.predpona IS NULL THEN RETURN 't';
ELIF eec1.predpona IS NULL THEN RETURN 'f';
ELSE RETURN 't';
END IF
END FUNCTION;
###
create view dbo.dirdoc1 (typv,predpona,eecyear,eecnum,popivaz) asselect
x0.typv,
min(row(x1.eecyear,x1.eecnum,x1.predpona)::eec).predpona,
min(row(x1.eecyear,x1.eecnum,x1.predpona)::eec).eecyear,
min(row(x1.eecyear,x1.eecnum,x1.predpona)::eec).eecnum,
max(x0.popivaz)
from
dbo.typv x0,
outer(dbo.dirdoc x1) where
(x0.typv = x1.typv)
group by
x0.typv;
Based on the discussion at Stackoverflow, it may look like a bug.
Do you have any idea if there can be some missing configuration somewhere or anything else, that "blocks" the view creation?
Thank you
Ondrej
------------------------------
Ondřej Žižka
------------------------------
Hi Ondrej, if I'm getting you right, you're having trouble defining this view in a 14.10.FC10 database, correct? I just tried it, also in 14.10.FC10, an it worked, and even selecting from the view didn't return an error (and no data either, of course.) What might we be missing? Also, if really suspecting a defect, don't hesitate having this worked through a support ticket! BR, Andreas ------------------------------ Andreas Legner ------------------------------