I'm playing around with what's possible in Informix related to collection derived tables and nested collections. 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);
I tried the following query:
with x as (
select
t.i,
multiset(
select * from u where u.i = t.i
) m
from t
order by t.i
)
select *
from x, lateral(table(x.m));
Running it the first time from the Dbeaver SQL editor produces this error:
SQL Error [IX000]: The current transaction has been rolled back due to an internal error.
Running it the second time produces this error:
SQL Error [IX000]: System or internal error
It seems the connection has gone stale and I have to reconnect to run further queries.
This seems to be a bug in Informix, but how can I work around this problem? I'm using IBM Informix Dynamic Server Version 14.10.FC5DE
It seems the problem is related to that ORDER BY
clause. This query works as expected:
with x as (
select
t.i,
multiset(
select * from u where u.i = t.i
) m
from t
)
select *
from x, lateral(table(x.m));
Producing the following output:
|i |m |i |j |
|---|-----------------------------------------|---|---|
|1 |[IfxStruct. Type: row ( i int , j int ) ]|1 |10 |
|2 |[IfxStruct. Type: row ( i int , j int ) ]|2 |20 |
(Don't mind the IfxStruct
text. That's just Dbeaver, which can't print these types, yet)