Hibernate version: Hibernate 3.0.3

Db: MySql 4.1.7

When trying to use Criteria.add(Subqueries.exists(DetachedCriteria)), I have the following problem:

Working with the DetachedCriteria only by itself works, but when adding this to Criteria.add(Subqueries.exists(...)), it generates invalid sql.

-------------------------------------------------------------------------
Working example Example with using only the DetachedCriteria:

/** article version has collection of chapters, chapter has collection of textBlocks **/
DetachedCriteria dt=
DetachedCriteria.forClass(ArticleVersion.class)
.setProjection(Property.forName("id"))
.createAlias("chapters","chapters")
.createAlias("chapters.textBlocks","textBlocks")
.add(Restrictions.eq("textBlocks.mediaObject.id","M050516ANOND000003"))
;

Criteria criteria=dt.getExecutableCriteria(session);

List<MediaObject> result=criteria.list();
System.out.println(result);

The generated sql is:
Hibernate: select this_.info_object_version as y0_ from wub_article_versions this_ inner join wub_info_object_versions this_1_ on this_.info_object_version=this_1_.id inner join wub_article_chapters chapters1_ on this_.info_object_version=chapters1_.article_version inner join wub_article_textblocks textblocks2_ on chapters1_.id=textblocks2_.parent_chapter where textblocks2_.media_object=?


-------------------------------------------------------------------------
Not working example - probably bug; just use the previously defined criteria in Criteria.add(Subqueries.exists(...))


Criteria criteria=session.createCriteria(MediaObject.class);

DetachedCriteria dt=
DetachedCriteria.forClass(ArticleVersion.class)
.setProjection(Property.forName("id"))
.createAlias("chapters","chapters")
.createAlias("chapters.textBlocks","textBlocks")
.add(Restrictions.eq("textBlocks.mediaObject.id","M050516ANOND000003"))
;

criteria.add(
Subqueries.exists(dt)
);

List<MediaObject> result=criteria.list();
System.out.println(result);

The generated sql is (textblocks2_ alias is not defined, why ?):
Hibernate: select this_.id as id0_, this_.created as created16_0_, this_.credit as credit16_0_, this_.caption as caption16_0_, this_.id_sequence_val as id5_16_0_, this_.display_text as display6_16_0_, this_.expires as expires16_0_, this_.fee_duty as fee8_16_0_, this_.keywords as keywords16_0_, this_.modified as modified16_0_, this_.rubric as rubric16_0_, this_.media_group as media12_16_0_, this_.created_by as created13_16_0_, this_.modified_by as modified14_16_0_, this_.perview_height as perview15_16_0_, this_.preview_height_original as preview16_16_0_, this_.preview_path as preview17_16_0_, this_.preview_width as preview18_16_0_, this_.preview_width_original as preview19_16_0_, this_.deleted as deleted16_0_, this_.deleted_by as deleted21_16_0_, this_.deleted_time as deleted22_16_0_ from wub_media_objects this_ where exists (select this0__.info_object_version as y0_ from wub_article_versions this0__ inner join wub_info_object_versions this0__1_ on this0__.info_object_version=this0__1_.id where textblocks2_.media_object=?)

And the exception:
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1565)
at org.hibernate.loader.Loader.list(Loader.java:1545)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1316)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at Test.main(Test.java:218)
Caused by: java.sql.SQLException: Unknown column 'textblocks2_.media_object' in 'where clause'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1239)
at org.hibernate.loader.Loader.doQuery(Loader.java:374)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1562)
... 5 more

Could anyone please help me or giv me a hint ? thanks

this is a well-known bug

Do you know if it is gonna be repaired and when ?

Thanks.

What is the JIRA ticket id for this issue ? I submitted bug HHH478 which was closed as duplicate by Gavin. Unfortunately I cannot find the original ticket, so if someone is monitoring this bug also, please post the ticket id here.

Anyone know the status of this one? I can't find anything regarding this in JIRA. My problem is somewhat different, but the setup seems the same. I have a detached query that generates sql fine. However, when I use Suqueries.exists to add it as an exists on my main query, the generated sql does not have the joins for the subquery.

Alone:
select id from foo f join bar b on f.barId = b.id where b.prop = 'z'

As exists subquery:
select id from foo f where b.prop = 'z'

The latter fails with unknown table or alias b. Is this problem related, or separate? In SubqueryExpression, where the sql is generated, Select.setOuterJoins is not called like it is elsewhere (AbstractEntityJoinWalker for one).

This is 3.1beta1, but SubqueryExpression has not changed in HEAD.

In CriteriaQueryTest.java, it appears that this is known to be an issue. Is there any implementation timeline?

For reference: This appears as issue HHH-952: http://opensource2.atlassian.com/projec ... se/HHH-952 .