[x-posted to comp.lang.java.programmer, since this is about JDBC and a
java embedded database]
For the benefit of cljp readers, UNNEST is an SQL-standard function
which takes an array value and returns a table-like value, which you can
use in join and IN conditions; i want it because i can use it to write
queries that are like "select everything with an ID in this set".
On Sun, 9 May 2010, Tom Anderson wrote:
FWIW, H2 doesn't support UNNEST. It does have something similar in the
shape of a TABLE pseudo-function, which lets you create temporary
tables inline, and into which you can substitute array parameters. In
JDBC syntax:
SELECT * FROM TABLE(x INTEGER = ?);
You can put an array in as the parameter. Although H2 doesn't support
the java.sql.Array type; you have to use a normal java array instead.
And you can't use TABLE with IN; this:
SELECT * FROM thing WHERE thing_id IN TABLE(selected_id INTEGER = ?);
doesn't work. You have to rewrite it as a join:
Correction - you can also whip up a closer approximation of UNNEST,
which can be used in an IN clause, by hand:
SELECT * FROM thing WHERE thing_id IN (SELECT ARRAY_GET(?1, X) AS
selected_id FROM SYSTEM_RANGE(1, ARRAY_LENGTH(?1)));
(the AS is unnecessary but informative)
That makes a 100% genuine subquery, so i'd be optimistic about the query
planner doing something sensible here.