Error 90052, Subquery is not a single column query

[Karsten Krieg]

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

[Noel Grandin]

H2 does not currently support that style of IN query.

You're welcome to log a feature request.

[Markus Waltl]

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

[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

 

--
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 h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

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

[Lukas Eder]

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

[Markus Waltl]

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