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