java.sql.SQLException: Unknown column 'departmen4_1_.VALID_SECTOR' in 'where clause'

Hibernate JIRA | user1 | 1 decade ago
  1. 0

    The following query works fine with a Classic query, however, doesn't work with ASTQueryTranslatorFactory. Note : Company has a collection of Departments. And, Department is a sub-class of Sectors. Company, Sectors, Department - all three are persited classes. The property validSector is on the parent class Sectors. For some reason, with ASTQueryTranslatorFactory, the select count clause's where with the parent class (SECTORS) property (validSector) doesn't generate the join on the table, but still has a where for that class correctly in the generated sql (departmen4_1_.VALID_SECTOR) --------------------------- error logs ------------- 2005-11-11 10:03:20,832 [http8080-Processor2] DEBUG HQLQueryPlan:132 - find: select distinct company from com.product..dao.transferobj.company as company left outer join company.departments as department where 1 = 1 AND ( company.cmpname LIKE :P0 ) AND ( size(company.departments) = 0 OR (select count(i) from company.departments i where i.validSector = :validSector) = 0 ) 2005-11-11 10:03:20,852 [http8080-Processor2] DEBUG QueryParameters:261 - named parameters: {P0=%, validSector=true} 2005-11-11 10:03:20,852 [http8080-Processor2] DEBUG Loader:1739 - bindNamedParameters() % -> P0 [1] 2005-11-11 10:03:20,852 [http8080-Processor2] DEBUG Loader:1739 - bindNamedParameters() true -> validSector [2] 2005-11-11 10:03:20,902 [http8080-Processor2] DEBUG JDBCExceptionReporter:63 - could not execute query [select distinct company0_.ID, company0_.CMP_NAME as OBJ1_61_ from COMPANY company0_ left outer join DEPARTMENT departmen1_ on company0_.ID=departmen1_.ID left outer join SECTORS sectors1_1_ on departmen1_.obid=sectors1_1_.ID where 1=1 and (company0_.CMP_NAME like ?) and ((select count(departmen3_.ID) from DEPARTMENT departmen3_ inner join SECTORS sectors3_1_ on departmen3_.ID=sectors3_1_.ID where company0_.ID=departmen3_.SPP_ID)=0 or (select count(departmen4_.obid) from DEPARTMENT departmen4_ where company0_.ID=departmen4_.SPP_ID and departmen4_1_.VALID_SECTOR=?)=0)] java.sql.SQLException: Unknown column 'departmen4_1_.VALID_SECTOR' in 'where clause' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622) at com.mysql.jdbc.Connection.execSQL(Connection.java:2376) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137) at org.hibernate.loader.Loader.getResultSet(Loader.java:1676) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223) at org.hibernate.loader.Loader.doList(Loader.java:2147) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026) at org.hibernate.loader.Loader.list(Loader.java:2021) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:298) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:137) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1014) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

    Hibernate JIRA | 1 decade ago | user1
    java.sql.SQLException: Unknown column 'departmen4_1_.VALID_SECTOR' in 'where clause'
  2. 0

    The following query works fine with a Classic query, however, doesn't work with ASTQueryTranslatorFactory. Note : Company has a collection of Departments. And, Department is a sub-class of Sectors. Company, Sectors, Department - all three are persited classes. The property validSector is on the parent class Sectors. For some reason, with ASTQueryTranslatorFactory, the select count clause's where with the parent class (SECTORS) property (validSector) doesn't generate the join on the table, but still has a where for that class correctly in the generated sql (departmen4_1_.VALID_SECTOR) --------------------------- error logs ------------- 2005-11-11 10:03:20,832 [http8080-Processor2] DEBUG HQLQueryPlan:132 - find: select distinct company from com.product..dao.transferobj.company as company left outer join company.departments as department where 1 = 1 AND ( company.cmpname LIKE :P0 ) AND ( size(company.departments) = 0 OR (select count(i) from company.departments i where i.validSector = :validSector) = 0 ) 2005-11-11 10:03:20,852 [http8080-Processor2] DEBUG QueryParameters:261 - named parameters: {P0=%, validSector=true} 2005-11-11 10:03:20,852 [http8080-Processor2] DEBUG Loader:1739 - bindNamedParameters() % -> P0 [1] 2005-11-11 10:03:20,852 [http8080-Processor2] DEBUG Loader:1739 - bindNamedParameters() true -> validSector [2] 2005-11-11 10:03:20,902 [http8080-Processor2] DEBUG JDBCExceptionReporter:63 - could not execute query [select distinct company0_.ID, company0_.CMP_NAME as OBJ1_61_ from COMPANY company0_ left outer join DEPARTMENT departmen1_ on company0_.ID=departmen1_.ID left outer join SECTORS sectors1_1_ on departmen1_.obid=sectors1_1_.ID where 1=1 and (company0_.CMP_NAME like ?) and ((select count(departmen3_.ID) from DEPARTMENT departmen3_ inner join SECTORS sectors3_1_ on departmen3_.ID=sectors3_1_.ID where company0_.ID=departmen3_.SPP_ID)=0 or (select count(departmen4_.obid) from DEPARTMENT departmen4_ where company0_.ID=departmen4_.SPP_ID and departmen4_1_.VALID_SECTOR=?)=0)] java.sql.SQLException: Unknown column 'departmen4_1_.VALID_SECTOR' in 'where clause' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622) at com.mysql.jdbc.Connection.execSQL(Connection.java:2376) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137) at org.hibernate.loader.Loader.getResultSet(Loader.java:1676) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223) at org.hibernate.loader.Loader.doList(Loader.java:2147) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026) at org.hibernate.loader.Loader.list(Loader.java:2021) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:298) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:137) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1014) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

    Hibernate JIRA | 1 decade ago | user1
    java.sql.SQLException: Unknown column 'departmen4_1_.VALID_SECTOR' in 'where clause'
  3. 0

    What is causing a Hibernate SQL query exception?

    Stack Overflow | 6 years ago | Dark Star1
    java.lang.Exception: org.hibernate.exception.SQLGrammarException: could not execute query
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    i am getting Hibernate error

    Stack Overflow | 2 years ago | Raji
    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. java.sql.SQLException

    Unknown column 'departmen4_1_.VALID_SECTOR' in 'where clause'

    at com.mysql.jdbc.MysqlIO.checkErrorPacket()
  2. MySQL jdbc
    PreparedStatement.executeQuery
    1. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
    2. com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
    3. com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
    4. com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
    5. com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
    6. com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
    7. com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705)
    7 frames
  3. c3p0:JDBC DataSources/Resource Pools
    NewProxyPreparedStatement.executeQuery
    1. com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    1 frame
  4. Hibernate
    QueryLoader.list
    1. org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
    2. org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
    3. org.hibernate.loader.Loader.doQuery(Loader.java:662)
    4. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
    5. org.hibernate.loader.Loader.doList(Loader.java:2147)
    6. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
    7. org.hibernate.loader.Loader.list(Loader.java:2021)
    8. org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
    8 frames
  5. Hibernate HQL/JP-QL Parser
    QueryTranslatorImpl.list
    1. org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:298)
    1 frame
  6. Hibernate
    QueryImpl.list
    1. org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:137)
    2. org.hibernate.impl.SessionImpl.list(SessionImpl.java:1014)
    3. org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    3 frames