[hibernate-dev] Various "row value construct" alike syntax and Dialect class

[Łukasz Antoniak]

The initial question here is how to distinguish dialects that support statements like:
update ParentEntity_AUD set REVEND=? where (id, REV) IN (select id, REV from HT_ChildEntity_AUD)

For example H2 fails:
Caused by: org.h2.jdbc.JdbcSQLException: Podzapytanie nie jest zapytaniem opartym o jedna kolumne
Subquery is not a single column query; SQL statement:
update ParentEntity_AUD set REVEND=? where (id, REV) IN (select id, REV from HT_ParentEntity_AUD) [90052-145]

SQL Server fails:
09:27:31,906 DEBUG SQL:104 - update ParentEntity_AUD set REVEND=? where (id, REV) IN (select id, REV from #ParentEntity_AUD)
09:27:31,953  WARN SqlExceptionHelper:143 - SQL Error: 4145, SQLState: S0001
09:27:31,953 ERROR SqlExceptionHelper:144 - An expression of non-boolean type specified in a context where a condition is 
expected, near ','.

 > I am not understanding the need for
 > supportsRowValueConstructorSyntaxInSelectList.  What exactly is the
 > purpose of grouping the values together in a select list?  And in fact
 > you are not even using tuple (row value constructor) syntax in any of
 > the selects.
I can see that people name this type of SQL queries ("where (a1, b1) IN (select c1, d1 from another_table)") as row value 
constructor, for example here: 
http://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors. Maybe the 
name "supportsRowValueConstructorSyntaxInSubquery" would be more accurate.

 > Why would the third query fall into
 > supportsRowValueConstructorSyntaxInInList?
Taken into consideration the grow of Dialect class and based on the table posted here: 
http://lists.jboss.org/pipermail/hibernate-dev/2012-May/008511.html.

Steve Ebersole pisze:
> 
> On Fri 25 May 2012 02:19:29 PM CDT, Łukasz Antoniak wrote:
>> Hello Community,
>>
>> Really quick question. There are quite few SQL syntax tricks similar 
>> to "row value construct".
>>
>> CREATE TABLE test ( a1 NUMBER, b1 NUMBER );
>> SELECT * FROM test WHERE (a1, b1) = ( 1, 1 ); -- Fails on Oracle
>> SELECT * FROM test WHERE (a1, b1) IN ( ( 1, 1 ), ( 2, 2 ) ); -- Passes 
>> on Oracle
>> SELECT * FROM test WHERE (a1, b1) = ( SELECT 1, 1 FROM dual ); -- 
>> Passes on Oracle
>> SELECT * FROM test WHERE (a1, b1) IN ( SELECT 1, 1 FROM dual ); -- 
>> Passes on Oracle
>>
>> Oracle does not support "row value syntax" as shown in the first query 
>> (Dialect#supportsRowValueConstructorSyntax() has to return
>> false).
>>
>> Should I assume that second, third and fourth query ought to pass when 
>> Dialect#supportsRowValueConstructorSyntaxInInList() returns
>> positive?
> 
> Why would the third query fall into 
> supportsRowValueConstructorSyntaxInInList?
> 
> 
>> Or shall I add another method to Dialect class like 
>> supportsRowValueConstructorSyntaxInSelectList()?
> 
> I am not understanding the need for 
> supportsRowValueConstructorSyntaxInSelectList.  What exactly is the 
> purpose of grouping the values together in a select list?  And in fact 
> you are not even using tuple (row value constructor) syntax in any of 
> the selects.
> 
>> Please advise. I
>> just do not want the Dialect class to grow and grow.
> 
> Nor do I :)  The problem is all the variation in support for tuple 
> syntax in different scenarios across the different databases.
> 
> -- 
> steve at hibernate.org
> http://hibernate.org
>