Skip to content
Advertisement

Internal error when lateral unnesting collection derived table in Informix

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

Advertisement

Answer

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)

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement