mysql - SQL异常: Column not found when there is no such column in the query

Question

我正在尝试在我的 java 应用程序中运行查询，但它引发了一个异常，我将很快粘贴该异常的堆栈跟踪。当我在 mysql 终端上运行相同的查询时，它运行得很好。但当 javax 持久性出现时，它就会爆炸。

Query query = entityManager.createNativeQuery(myQuery);
List<String> resultList = query.getResultList();

这是我遇到的异常。

java.sql.SQLException: Column 'id' not found.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
    at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2813)
    at org.hibernate.type.IntegerType.get(IntegerType.java:28)
    at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
    at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189)
    at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:474)
    at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:420)
    at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:317)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:606)
    at org.hibernate.loader.Loader.doQuery(Loader.java:701)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2213)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:65)

这是查询:

select uqd.id as requestId,count(*) as dailyRunCount,sum(finalTab.diffInMinutes) as totalRunningTimeInMinutes,config_level,user_email_id,team_name, description,cast(sum(finalTab.diffInMinutes) * 3.35 as decimal(10,2)) as cost  from user_query_details uqd join (select a.request_id,(b.state_change_time_stamp - a.state_change_time_stamp)/60000 as diffInMinutes from (select request_id,job_id,state_change_time_stamp from user_request_history where state = 'RUNNING' and state_change_time_stamp >= (UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)*1000)) a join (select job_id,state_change_time_stamp from user_request_history where state ='COMPLETED') b on a.job_id = b.job_id) finalTab on uqd.id=finalTab.request_id where uqd.discriminator='S' and query is not null and config_level='LEVEL_TWO' group by uqd.id order by totalRunningTimeInMinutes desc limit 3

任何帮助将不胜感激。谢谢。

Answer 1

更改查询

SELECT 
      uqd.id as requestID,count(*) as dailyRunCount,
      sum(finalTab.diffInMinutes) as totalRunningTimeInMinutes,
      config_level,user_email_id,team_name, 
      description,
      cast(sum(finalTab.diffInMinutes) * 3.35 as decimal(10,2)) as cost  
    FROM 
      user_query_details uqd join 
       (SELECT 
         a.request_id,
        (b.state_change_time_stamp - a.state_change_time_stamp)/60000 as diffInMinutes 
        FROM 
         (SELECT 
             request_id,job_id,state_change_time_stamp 
          FROM 
             user_request_history 
          WHERE 
            state = 'RUNNING' and 
            state_change_time_stamp >= (UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)*1000)) a join 
(select job_id,state_change_time_stamp from user_request_history where state ='COMPLETED') b on a.job_id = b.job_id) finalTab on uqd.id=finalTab.request_id where uqd.discriminator='S' and query is not null and config_level='LEVEL_TWO' group by requestID order by totalRunningTimeInMinutes desc limit 3