[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3224) Invalid generated SQL for nested queries

[Adam Dyga (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3224?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29958 ] 

Adam Dyga commented on HHH-3224:
--------------------------------

May be related to this one, because I've experienced this bug also in 3.2.5:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2609

> Invalid generated SQL for nested queries
> ----------------------------------------
>
>                 Key: HHH-3224
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3224
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.2.6
>         Environment: MySQL
>            Reporter: Adam Dyga
>
> I'm facing a problem with invalid generated SQL for nested queries.
> I've the following class hierarchy (left only the most important fields for clarity):
> //base class
> class Asset {
>    String description; // <- field in base class
> }
> class ImageSet {
>    List<Wallpaper> wallpapers;
> }
> class Wallpaper extends Asset { // <- inherits from Asset
> }
> The HBM mappings:
> <class name="com.company.Asset" table="Asset" abstract="true">
>   <property name="description" column="description" type="string" />
>   <id>...</id>
> </class>
> <class name="ImageSet" table="ImageSets">
>     <bag name="wallpapers" cascade="delete-orphan" inverse="true"
>       table="Wallpapers">
>       <key column="imageSetId" />
>       <one-to-many class="com.company.Wallpaper" />
>     </bag>
> </class>
> <joined-subclass name="com.company.Wallpaper"
>     extends="com.company.Asset" table="Wallpapers">
>     <key column="id" />
>     <many-to-one name="imageSet" column="imageSetId"
>       class="com.company.ImageSet" />
> </joined-subclass>
> When I try to execute the following HQL query:
> select distinct imageSet from ImageSet imageSet where (select count(*) from imageSet.wallpapers w where w.description is not null) = size(imageSet.wallpapers)
> I get the following SQL query:
> select distinct imageset0_.id as id14_ from ImageSets imageset0_ where (select count(*) from Wallpapers wallpapers1_ where imageset0_.id=wallpapers1_.imageSetId and (wallpapers1_1_.description is not null)])=(select count(wallpapers2_.imageSetId) from Wallpapers wallpapers2_ inner join Asset wallpapers2_1_ on wallpapers2_.id=wallpapers2_1_.id where imageset0_.id=wallpapers2_.imageSetId)
> The error message is 
> Unknown column 'wallpapers1_1_.description' in 'where clause'
> which is right - note the missing wallpapers1_1_ alias which should be defined during inner join of the Wallpapers table with the base Asset table in the first nested query - just as it is done in the second nested query.
> Full stack trace of any exception that occurs:
> org.hibernate.exception.SQLGrammarException: could not execute query
> 	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
> 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> 	at org.hibernate.loader.Loader.doList(Loader.java:2216)
> 	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
> 	at org.hibernate.loader.Loader.list(Loader.java:2099)
> 	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
> 	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
> 	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
> 	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
> 	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)	
> Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'wallpapers1_1_.description' in 'where clause'
> 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
> 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
> 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
> 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
> 	at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
> 	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
> 	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira