sqlinformixmultiset

How to nest MULTISET in unnamed ROW in Informix?


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


Solution

  • 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;