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.
>
> --
> st...@hibernate.org
> http://hibernate.org
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
