sqlinformixmultiset

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


Solution

  • 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)