org.hibernate.exception.SQLGrammarException: could not execute query

Hibernate JIRA | Adam Dyga | 9 years ago
  1. 0

    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)

    Hibernate JIRA | 9 years ago | Adam Dyga
    org.hibernate.exception.SQLGrammarException: could not execute query
  2. 0

    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)

    Hibernate JIRA | 9 years ago | Adam Dyga
    org.hibernate.exception.SQLGrammarException: could not execute query
  3. 0

    Help for to use onecmdb

    onecmdb | 10 years ago | raimundoas
    org.hibernate.exception.GenericJDBCException: could not load an entity: \[org.onecmdb.core.internal.ccb.CmdbTransaction\#0\]
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    jBPM开发入门指南(3) - 子在川上曰 - BlogJava

    blogjava.net | 1 week ago
    org.hibernate.exception.SQLGrammarException: could not execute query

    2 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. com.mysql.jdbc.exceptions.MySQLSyntaxErrorException

      Unknown column 'wallpapers1_1_.description' in 'where clause'

      at com.mysql.jdbc.SQLError.createSQLException()
    2. MySQL jdbc
      PreparedStatement.executeQuery
      1. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
      2. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
      3. com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
      4. com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
      5. com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
      6. com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
      7. com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
      7 frames