Are semi-joins using tuples supported?

[Lukas Eder]

I'm not sure how to interpret the manual regarding the use of tuples in semi-joins. The following syntax is valid SQL in some databases:

-- Oracle

select 1 from dual where (1, 2) in (select 1, 2 from dual)

-- Postgres

select 1 where (1, 2) in (select 1, 2)

H2 doesn't seem to support either statement. Does H2 support subqueries in IN-predicates, when the left-hand side is a tuple?

Cheers

Lukas

[Thomas Mueller]

Hi,

For H2, the expression (1, 2) is an array, so the queries would need to be:

select 1 from dual where (1, 2) in (select (1, 2) from dual);

select 1 where (1, 2) in (select (1, 2));

select 1 from dual where (1, 2) in ((1, 2));

Regards,

Thomas

[Lukas Eder]

Ah, yes, I forgot about H2's array notation, thanks for that hint.

Would it be difficult to extend H2 in a way that it would coerce tuples into arrays or arrays into tuples for these cases? This would probably help improve SQL standard conformance and compatibility with other dialects. I think the tuple use-case is more popular than the array use-case

What do you think?

[Rami Ojares]

Another would be to use [ ... ] as array constructor and ( ... ) as
tuple constructor.
That would be more in line with widespread conventions.
Just a thought.

- rami

[Lukas Eder]

I wasn't sure if general syntax criticism should be applied here. But since you suggested brackets, I'd like to counter-act and remind this group of the SQL standard. As of SQL:2008, the standard specifies the following two:

6.36 <array value constructor>

<array value constructor> ::=

    <array value constructor by enumeration>

  | <array value constructor by query> 

 

<array value constructor by enumeration> ::=

  ARRAY <left bracket or trigraph> <array element list> <right bracket or trigraph>

 

<array value constructor by query> ::=

  ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>

Whereas...

7.1 <row value constructor>

<row value constructor> ::=

    <common value expression>

  | <boolean value expression>

  | <explicit row value constructor>

 

<explicit row value constructor> ::=

    <left paren> <row value constructor element> <comma>

      <row value constructor element list> <right paren>

  | ROW <left paren> <row value constructor element list> <right paren>

  | <row subquery>

Hence, according to the standard, arrays must be expressed using the ARRAY constructor, whereas tuples/rows may be expressed using the ROW constructor, or just using a constructor-less, parenthesis-delimited argument list.

Following suit with the standard would probably impose a major (incompatible) change in H2, hence evaluating this carefully is important. Personally, I think letting the non-standard H2 ARRAY syntax live is a viable option, as long as it doesn't contradict the common understanding of what a tuple is and how it behaves in SQL...

[Rami Ojares]

So the easiest way would be to add reserved word ROW that would turn
the array constructor to a tuple constructor.
Further we could add reserved word ARRAY too to be more compatible with
standard.
After that the only deviation from standard would be the default case
when neither ARRAY nor ROW is mentioned.
And if desired that could be changed when a major release upgrade comes
along.

I'd suggest Lukas implement this ;-)

- Rami

[Lukas Eder]

> I'd suggest Lukas implement this ;-)  

Well, you can always try ;-)