How to join an unnested array

[Lukas Eder]

I have this sample database:

CREATE TABLE t_arrays (
  id integer not null,
  string_array ARRAY,
  number_array ARRAY,
  date_array ARRAY,
 
  CONSTRAINT pk_t_arrays PRIMARY KEY (ID)
);

INSERT INTO t_arrays VALUES (1, null, null, null);
INSERT INTO t_arrays VALUES (2, (), (), ());
INSERT INTO t_arrays VALUES (3, ('a'), (1), ('1981-07-10'));
INSERT INTO t_arrays VALUES (4, ('a', 'b'), (1, 2), ('1981-07-10', '2000-01-01'));

Now I would like to use the TABLE function as documented on this section of the manual:
http://www.h2database.com/html/functions.html#table

The goal is to self-join the t_arrays table in order to unnest the string_array for instance. The desired output would then be:

+----+--------------+
| ID | COLUMN_VALUE |
+----+--------------+
|  2 | a            |
|  3 | a            |
|  3 | b            |
+----+--------------+

Intuitively, I'd expect this to work, but it fails. The self-join on id = t1.id is not recognised:

select t1.id, t2.column_value
from t_arrays t1
join table(column_value varchar = (select string_array from t_arrays where id = t1.id)) t2

This works, so my understanding of syntax seems to be correct:

select t1.id, t2.column_value
from t_arrays t1
join table(column_value varchar = (select string_array from t_arrays where id = 4)) t2

Is there a way to do this with H2? I think being able to reference tables / columns from the outer scope of the TABLE function is important, not only when joining new nested selects, but also when unnesting arrays from stored functions, e.g. something like this:

select t1.id, t2.column_value
from t_arrays t1
join table(column_value varchar = my_array_function(t1.id)) t2

[Rami Ojares]

Hmm, the arrays really seem to simplify things, don't they? ;-)

- rami

[Lukas Eder]

@rami: I know it's a rather academic and low-prio question ;-)

Nevertheless, once you start actually using arrays in an RDBMS, these kinds of features become vital. I'm looking at H2 from the perspective of an Oracle PL/SQL user. VARRAY and in-memory TABLE types are a very neat thing in Oracle...

[Rami Ojares]

I just think that everything you can achieve with arrays you can
achieve without them.
But in order to use arrays you have to introduce plenty of new methods,
which you pointed out.
Seems a bit redundant to me, that's all.
Needs more code, introduces more bugs, boundary conditions and
inconsistencies.

- rami

[Lukas Eder]

I think the decision whether or not H2 should support arrays is off topic in this thread, as it has been made a long time ago? Surely, arrays (and joining their unnested tables) aren't a first priority feature of a light-weight RDBMS. Postgres, for instance, raises an explicit error about this "unnested join" not being supported. HSQLDB and Oracle on the other hand, handle this quite well.

[Lukas Eder]

Any other opinions on this topic?

[Thomas Mueller]

Hi,

Support for arrays was mainly added for PostgreSQL compatibility (in
order to support the PostgreSQL ODBC driver).

If there are any real-world use cases then the support could be
extended, but I'm hesitant to add features just because it's possible
to do that.

Regards,
Thomas

[Lukas Eder]

OK, thanks for the feedback. As usual, I don't have real-world use cases :-)