Given this schema:
create table t (i int);
create table u (i int, j int);
insert into t values (1);
insert into t values (2);
insert into u values (1, 10);
insert into u values (2, 20);
The following query looks correct to me:
select
t.i,
row(
multiset(
select * from u where u.i = t.i
)
) r
from t
order by t.i
But it produces some unspecified internal error:
SQL Error [IX000]: User Defined Routine (collectionsend) execution failed.
Is this a documented limitation? How can I work around this problem? I'm using IBM Informix Dynamic Server Version 14.10.FC5DE
One workaround might be to wrap the entire thing in a dummy MULTISET
like this, which seems to work:
select multiset(
select
t.i,
row(
multiset(
select * from u where u.i = t.i
)
) r
from t
order by t.i
);
Though, when unnesting the auxiliary multiset again, the old error appears. This doesn't work:
select * from table(multiset(
select
t.i,
row(
multiset(
select * from u where u.i = t.i
)
) r
from t
order by t.i
)) t;