sqlinformixmultiset

Informix doubly nested MULTISET remains empty


Given this schema:

create table a (id int);
create table b (id int, a_id int, c_id int);
create table c (id int);

insert into a values (1);
insert into a values (2);
insert into b values (1, 1, 1);
insert into b values (2, 1, 1);
insert into b values (3, 2, 4);
insert into b values (4, 2, 2);
insert into c values (1);
insert into c values (2);
insert into c values (3);
insert into c values (4);

I tried running a query like this:

select
  a.id,
  multiset(
    select
      b.id,
      multiset(
        select c.id
        from c
        where c.id = b.c_id
      )
    from b
    where b.a_id = a.id
  ) m
from a
order by a.id

But the output suggests that the inner most nested MULTISET query doesn't work. The output is:

id  1
m   MULTISET{ROW(1,MULTISET{}),ROW(2,MULTISET{})}

id  2
m   MULTISET{ROW(3,MULTISET{}),ROW(4,MULTISET{})}

Is this a known limitation or a bug? How can I work around this limitation? I'm using IBM Informix Dynamic Server Version 14.10.FC5DE


Solution

  • Just like a similar problem I've discovered recently, there seems to be an ORDER BY related bug. Removing the ORDER BY clause:

    select
      a.id,
      multiset(
        select
          b.id,
          multiset(
            select c.id
            from c
            where c.id = b.c_id
          )
        from b
        where b.a_id = a.id
      ) m
    from a
    

    And the output is now the expected one:

    id  1
    m   MULTISET{ROW(1,MULTISET{ROW(1)}),ROW(2,MULTISET{ROW(1)})}
    
    id  2
    m   MULTISET{ROW(3,MULTISET{ROW(4)}),ROW(4,MULTISET{ROW(2)})}
    

    Definitely seems to be a bug in Informix.