|Error 90052, Subquery is not a single column query||Karsten Krieg||11/30/11 6:55 AM|
We have a working query (working on oracle that is) which throws the
The query is:
Is this designed as such or are we facing a bug?
|Re: Error 90052, Subquery is not a single column query||Noel Grandin||11/30/11 7:40 AM|
H2 does not currently support that style of IN query.
You're welcome to log a feature request.
|Re: Error 90052, Subquery is not a single column query||Markus Waltl||9/20/13 9:38 AM|
is there already a solution for this planned? As the current version still does not support this kind of queries.
I am using H2 with Hibernate and have the issue that Hibernate generates a query with multiple columns in the subquery which I cannot modify.
Do you have a solution for this, e.g., modifying the H2Dialect of Hibernate or telling Hibernate to not generate this thing?
|Re: [h2] Re: Error 90052, Subquery is not a single column query||Thomas Mueller Graf||9/24/13 11:27 AM|
No, I'm sorry, I don't have a solution for this. Patches are welcome!
According to my test, MySQL, PostgreSQL, and HSQLDB support this. Apache Derby and H2 do not. I think the typical solution would be to use a join.
|Re: [h2] Re: Error 90052, Subquery is not a single column query||Lukas Eder||9/24/13 10:29 PM|
The odd background-info from me ;-)
This is called an IN predicate on row value expressions. I have brought this up before on this user group, as I think that true row value expression support would be a good feature addition for H2:
The problem with H2 right now is that the row value constructor syntax (a, b) is "misused" by H2 as array syntax. A possible workaround for H2 is to implement the SQL standard "ROW" constructor keyword (also supported by PostgreSQL) when implementing support for row value constructors:
In the mean time, IN predicates performed on row value expressions can be emulated using exists, if you are willing to perform such emulation on your side. I've blogged about this here:
jOOQ natively supports such emulation, transparently. From what I recall, another way to emulate this with H2 directly is by using arrays, if you write:
Notice the additional parentheses around the projection in the nested select. I don't know about performance, though, as I imagine that indexes will not be applicable when comparing arrays.
Hope this helps
|Re: [h2] Re: Error 90052, Subquery is not a single column query||Lukas Eder||9/24/13 10:31 PM|
... I'm sorry, I was missing the fact that you were using Hibernate. I guess Hibernate will have to be fixed according to what I mentioned below...
|Re: [h2] Re: Error 90052, Subquery is not a single column query||Markus Waltl||9/24/13 10:56 PM|
thank you for your elaboration on the issue.
I already posted in the Hibernate forum about this issue but it seems they are not reacting act the moment (did not receive any comment on it).
I saw the same issue happening with the H2Dialect "on commit drop" which was indicated by others but Hibernate developers had not responded on it.
I will check if there is a possibility to modify the code (which I am not sure) but as indicated by Thomas and Lukas, it would be a good feature to add as it makes switching databases much easier and it would increases the compatibility of the databases.