... 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.
