Prev: Computing sales taxes
Next: Java Adapter Pattern Wiki Redux
From: Tom Anderson on 9 May 2010 14:14
[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.

tom

--
When the facts change, I change my mind. What do you do, sir? -- John
Maynard Keynes
From: Arne Vajhøj on 9 May 2010 22:30
On 09-05-2010 14:14, Tom Anderson wrote:
> [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.

I believe that UNNEST requires SQL99.

Lots of database only fully support SQL92.

Arne
 | 
Pages: 1
Prev: Computing sales taxes
Next: Java Adapter Pattern Wiki Redux