CODEWITHSUNDEEP
sqlinformixmultiset
Lukas Eder
Lukas Eder

Reputation: 221106

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

Upvotes: 0

Views: 705

Answers (2)

jlar310
jlar310

Reputation: 649

I have verified that this is fixed in Informix 14.10.FC9

https://www.ibm.com/support/pages/apar/IT40822

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 221106

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;

Upvotes: 0

Related Questions