3:19 p.m.
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? Or shall I add another method to Dialect class like
supportsRowValueConstructorSyntaxInSelectList()? Please advise. I
just do not want the Dialect class to grow and grow.
Dialect#supportsRowValueConstructorSyntaxInInList() is overridden only by
Oracle dialect, but PostgreSQL supports this feature as well.
Regards,
Lukasz Antoniak
7:53 a.m.
One additional note:
Where clause | Oracle | PostgreSQL | MySQL
------------------------------------------------------------------------------
WHERE (a1, b1) = ( 1, 1 ) | Fail | Pass | Pass
------------------------------------------------------------------------------
WHERE (a1, b1) IN ( ( 1, 1 ), ( 2, 2 ) ) | Pass | Pass | Pass
------------------------------------------------------------------------------
WHERE (a1, b1) = ( SELECT 1, 1 FROM dual ) | Pass | Pass (not DUAL) | Pass
------------------------------------------------------------------------------
WHERE (a1, b1) IN ( SELECT 1, 1 FROM dual ) | Pass | Pass (not DUAL) | Pass
Currently Dialect#supportsRowValueConstructorSyntaxInInList() is overridden only by Oracle
dialect and
Dialect#supportsRowValueConstructorSyntax() by MySQL and PostgreSQL.
Should we assume that Dialect#supportsRowValueConstructorSyntax() corresponds to first row
in the table above and
Dialect#supportsRowValueConstructorSyntaxInInList() to the rest? If so, MySQL and
PostgreSQL dialects would need to be updated.
Regards,
Lukasz
6:29 p.m.
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(a)hibernate.org
http://hibernate.org
3:52 a.m.
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-suppor....
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(a)hibernate.org
http://hibernate.org
10:48 a.m.
On Sun 27 May 2012 02:52:23 AM CDT, Łukasz Antoniak wrote:
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)
Ok. And this form comes about because of the multi-table HQL splitting
to use temp tables right?
As an alternative suggestion, why don't we just have envers directly
perform an SQL UPDATE? Its really just the ParentEntity_AUD table you
want to update right? And this is really just to lock the table right?
> 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-suppor....
Maybe the name "supportsRowValueConstructorSyntaxInSubquery" would be
more accurate.
Ok, then the issue is naming. See the issue is not the subquery. The
subquery does not even have a row value constructor; "select c1, d1" is
just a simple select list. The problem is that the dialect does not
support the use of a subquery as the predicate of a logical expression
where the other predicate is a tuple. Basically they are doing too
simplistic of type checking on the 2 predicates. So maybe
supportsRowValueConstructorPredicateAndSubqueryPredicateInLogicalExpression().
I assume it is the same problem with all forms of logical expressions
(=, !=, >, etc), not just IN? Could you verify that?
We might want to consider consolidating these together as well into a
composite class that reports the dialects level of tuple support. That
unfortunately would have to wait for the next major rev.
> 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.
My point is just that the third query does not involve an IN
expression. So why would it fall under
supportsRowValueConstructorSyntax*InInList*?
--
steve(a)hibernate.org
http://hibernate.org
4349
days inactive
4353
days old
4 comments
2 participants
participants (2)
-
Steve Ebersole -
Łukasz Antoniak