Hi,

I am playing around with Hibernate 3.5 as a JPA provider to access MySQL. Everything looked rather impressive until I stumbled upon a problem with sub queries and EXISTS predicate. I am not sure about all sub queries but JPA-QL query with EXISTS predicate does not seem to work with MySQL 5.1 RDBMS setup. I used a query similar to the one from JPA spec:

SELECT DISTINCT emp FROM Employee emp WHERE EXISTS (
SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp = emp.spouse)

The exception I was getting was java.sql.SQLException. The relevant stacktrace is at the bottom of this post. When I turned on SQL debugging I noticed that Hibernate translated above JPA-QL query into SQL query where SELECT clause in subquery looked like this:
.... (exists (select (employe1_.name....)

and MySQL does not seem to accept SELECT format where sql rows are surrounded with brackets. I tried the same query as Hibernate constructed it in MySQL management tool and I got a response: Operand should contain 1 column(s). As soon as I removed these brackets the query executed flawlessly!

Anyone else seen this? Can you try to reproduce it on your own MySQL setup?

Regards,
Vladimir










Caused by: org.hibernate.exception.DataException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2297)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
at org.hibernate.loader.Loader.list(Loader.java:2167)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:448)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:236)
... 97 more
Caused by: java.sql.SQLException: Operand should contain 1 column(s)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264)
at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:848)
at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:641)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
at org.hibernate.loader.Loader.doQuery(Loader.java:718)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2294)