Hi, 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. Best regards, Markus On Wednesday, September 25, 2013 7:31:12 AM UTC+2, Lukas Eder wrote: > > ... 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... > > Am Mittwoch, 25. September 2013 07:29:36 UTC+2 schrieb Lukas Eder: >> >> Hello >> >> 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: >> https://groups.google.com/d/msg/h2-database/XlRLHjytKiE/-7KHPPCludkJ >> >> 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: >> >> ROW (org.id, org.timestamp) IN (SELECT ...) >> >> >> 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: >> >> http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions/ >> >> jOOQ <http://www.jooq.org> natively supports such emulation, >> transparently. From what I recall, another way to emulate this with H2 >> directly is by using arrays, if you write: >> >> (org.id, org.timestamp) IN (SELECT (org1.id, max(org1.timestamp)) ...) >> >> >> 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 >> Lukas >> >> Am Dienstag, 24. September 2013 20:27:42 UTC+2 schrieb Thomas Mueller: >>> >>> Hi, >>> >>> 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. >>> >>> Regards, >>> Thomas >>> >>> >>> >>> On Fri, Sep 20, 2013 at 6:38 PM, Markus Waltl <[email protected]>wrote: >>> >>>> Hi, >>>> >>>> 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? >>>> >>>> Best regards, >>>> >>>> Markus >>>> >>>> >>>> On Wednesday, November 30, 2011 4:40:54 PM UTC+1, Noel Grandin wrote: >>>>> >>>>> >>>>> H2 does not currently support that style of IN query. >>>>> >>>>> You're welcome to log a feature request. >>>>> >>>>> Karsten Krieg wrote: >>>>> > Hi group! >>>>> > >>>>> > We have a working query (working on oracle that is) which throws the >>>>> > above SQL error in H2 v1.3.154. The query is designed to return two >>>>> > values from a subquery. This seems to be defunct in H2. >>>>> > >>>>> > The query is: >>>>> > >>>>> > select * from organization as org >>>>> > where (org.id, org.timestamp) in >>>>> > ( select org1.id, max(org1.timestamp) from organization as >>>>> ges1 >>>>> > group by org1.id) >>>>> > and org.state=1 >>>>> > order by org.name; >>>>> > >>>>> > >>>>> > >>>>> > Is this designed as such or are we facing a bug? >>>>> > >>>>> > Thanks >>>>> > Karsten >>>>> > >>>>> >>>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://groups.google.com/group/h2-database. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>> >>> -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
