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
Advertisement
Answer
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;