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]<javascript:> > > 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] <javascript:>. >> >> To post to this group, send email to [email protected]<javascript:> >> . >> 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.
