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

Hibernate JIRA | Bob Harrod | 8 years ago
  1. 0

    The following annotations are being used to simulate a soft delete strategy: @Entity @Table(name = "login_unit_user") @SQLDelete( sql="UPDATE login_unit_user SET _is_active = 0 WHERE id = ?") @Where(clause="_is_active <> 0") public class LoginUnitUser extends DomainObject implements Serializable{...} and @Entity @Table(name = "login_unit") @SQLDelete( sql="UPDATE login_unit SET _is_active = 0 WHERE id = ?") @Where(clause="_is_active <> 0") public class LoginUnit extends DomainObject implements Serializable{...} In the attached code, both LoginUnit and LoginUnitUser contain a system column - "_is_active". This column represents a flag which indicates whether or not the row is "active" or "deleted". When these two hibernate model objects are queried, hibernate does not properly append the table alias to this additional where clause that it uses during it's database query. The error raised is: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2231) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88) ... 35 more Caused by: org.postgresql.util.PSQLException: ERROR: column reference "_is_active" is ambiguous at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) ... 43 more An example query with the hibernate generated aliases (loginuser0_, loginunitu1_, loginunit2_) is below: Hibernate: select loginuser0_.id as id21_, loginuser0_._audit_login_user_id as column7_21_, loginuser0_._created as column2_21_, loginuser0_.first_name as first3_21_, loginuser0_.last_name as last4_21_, loginuser0_._updated as column5_21_, loginuser0_.user_name as user6_21_ from login_user loginuser0_, login_unit_user loginunitu1_, login_unit loginunit2_ where ( _is_active <> 0 ) and ( _is_active <> 0 ) and loginuser0_.id=loginunitu1_.login_user_id and loginunitu1_.login_unit_id=loginunit2_.id and loginuser0_.user_name=? and loginunit2_.id=? limit ?

    Hibernate JIRA | 8 years ago | Bob Harrod
    org.hibernate.exception.SQLGrammarException: could not execute query
  2. 0

    The following annotations are being used to simulate a soft delete strategy: @Entity @Table(name = "login_unit_user") @SQLDelete( sql="UPDATE login_unit_user SET _is_active = 0 WHERE id = ?") @Where(clause="_is_active <> 0") public class LoginUnitUser extends DomainObject implements Serializable{...} and @Entity @Table(name = "login_unit") @SQLDelete( sql="UPDATE login_unit SET _is_active = 0 WHERE id = ?") @Where(clause="_is_active <> 0") public class LoginUnit extends DomainObject implements Serializable{...} In the attached code, both LoginUnit and LoginUnitUser contain a system column - "_is_active". This column represents a flag which indicates whether or not the row is "active" or "deleted". When these two hibernate model objects are queried, hibernate does not properly append the table alias to this additional where clause that it uses during it's database query. The error raised is: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2231) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88) ... 35 more Caused by: org.postgresql.util.PSQLException: ERROR: column reference "_is_active" is ambiguous at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) ... 43 more An example query with the hibernate generated aliases (loginuser0_, loginunitu1_, loginunit2_) is below: Hibernate: select loginuser0_.id as id21_, loginuser0_._audit_login_user_id as column7_21_, loginuser0_._created as column2_21_, loginuser0_.first_name as first3_21_, loginuser0_.last_name as last4_21_, loginuser0_._updated as column5_21_, loginuser0_.user_name as user6_21_ from login_user loginuser0_, login_unit_user loginunitu1_, login_unit loginunit2_ where ( _is_active <> 0 ) and ( _is_active <> 0 ) and loginuser0_.id=loginunitu1_.login_user_id and loginunitu1_.login_unit_id=loginunit2_.id and loginuser0_.user_name=? and loginunit2_.id=? limit ?

    Hibernate JIRA | 8 years ago | Bob Harrod
    org.hibernate.exception.SQLGrammarException: could not execute query
  3. 0

    [javasf]Obtendo valor unico

    Google Groups | 5 years ago | rtoaldo
    org.hibernate.exception.SQLGrammarException: could not execute query
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Hibernate: PSQLException: Error: Column "column_name" does not exist

    Stack Overflow | 3 years ago | GammaG
    org.hibernate.exception.SQLGrammarException: could not execute query
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. org.postgresql.util.PSQLException

    ERROR: column reference "_is_active" is ambiguous

    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse()
  2. PostgreSQL JDBC Driver
    AbstractJdbc2Statement.executeQuery
    1. org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
    2. org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
    3. org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
    4. org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    5. org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    6. org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    6 frames
  3. Hibernate
    QueryLoader.list
    1. org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    2. org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
    3. org.hibernate.loader.Loader.doQuery(Loader.java:697)
    4. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    5. org.hibernate.loader.Loader.doList(Loader.java:2228)
    6. org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
    7. org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    8. org.hibernate.loader.Loader.doList(Loader.java:2231)
    9. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    10. org.hibernate.loader.Loader.list(Loader.java:2120)
    11. org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
    11 frames
  4. Hibernate HQL/JP-QL Parser
    QueryTranslatorImpl.list
    1. org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
    1 frame
  5. Hibernate
    QueryImpl.list
    1. org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    2. org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
    3. org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    3 frames
  6. Hibernate EJB
    QueryImpl.getSingleResult
    1. org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88)
    1 frame