select 질의시 오류가 납니다. 도움 부탁드립니다.

by hajun posted Oct 21, 2021


* 질문 등록 시 다음의 내용을 꼭 기입하여 주세요.

OS
Window 64
CUBRID Ver.
11.0.2.0291
CUBRID TOOL Ver.
[도움말]-[버전정보] 확인
응용 환경(API)
java, jsp, mybatis, spring


* CUBRID 응용 오류, SQL 오류 또는 SQL 튜닝 관련된 문의는 반드시 다음의 내용을 추가해 주세요. 비밀글이나 비밀 댓글도 가능합니다.
* 저희가 상황을 이해하고, 재현이 가능해야 알 수 있는 문제들이 많습니다. 가능한 정보/정황들을 부탁합니다.

 

에러 내용 및 재현 방법 재현 가능한 Source와 SQL
관련 테이블(인덱스, 키정보 포함) 정보 CUBRID 홈 디렉토리 아래 log 디렉토리 압축


-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
 

 

컬럼들 타입

 

CREATE TABLE "statistics_member" (
    "insertdate" bigint NOT NULL,
    "domainUid" bigint NOT NULL,
    "totalcount" bigint DEFAULT 0 NOT NULL,
    "usecount" bigint DEFAULT 0 NOT NULL,
    "sleepcount" bigint DEFAULT 0 NOT NULL,
    "stopcount" bigint DEFAULT 0 NOT NULL,
    "newcount" bigint DEFAULT 0 NOT NULL,
    
    CONSTRAINT PRIMARY KEY("insertdate","domainuid")
    
);

CREATE INDEX idx_statistics_member_domainuid ON statistics_member("domainUid");

 

 

 

--------------------------------------------------------------------------------------------------------------------

 

 

* (#{item} 에 들어가는 수=> 20211020, 20211019 )

 

쿼리문 

<select id="getMemberStatistics" parameterType="StatisticsForm" resultType="MemberStatistics">
        SELECT     dt.insertDate, 
                sm.totalCount, sm.usecount, sm.sleepcount, sm.stopcount
        FROM (
            <foreach collection="dates" item="item" separator="UNION" >
                SELECT #{item} AS insertDate 
            </foreach> 
        ) dt
        LEFT JOIN (
            SELECT (useCount+ sleepCount+stopCount) AS totalCount, TT.* FROM (
                SELECT     insertDate, 
                        SUM(usecount)             AS useCount, 
                        SUM(sleepcount)         AS sleepCount, 
                        SUM(stopcount)             AS stopCount
                FROM     statistics_member
                WHERE     domainUid = 1
                GROUP BY insertDate
            ) TT
        )sm ON (dt.insertDate = sm.insertDate)
        ORDER BY dt.insertDate DESC
    </select>

 

 

-------------------------------------------------------------------------------------------------------------------

 

오류 메시지

 

SQL: SELECT  dt.insertDate,      sm.totalCount, sm.usecount, sm.sleepcount, sm.stopcount   FROM (           SELECT ? AS insertDate      UNION      SELECT ? AS insertDate          ) dt   LEFT JOIN (    SELECT (useCount+ sleepCount+stopCount) AS totalCount, TT.* FROM (     SELECT  insertDate,        SUM(usecount)    AS useCount,        SUM(sleepcount)   AS sleepCount,        SUM(stopcount)    AS stopCount     FROM  statistics_member           WHERE  domainUid = 1           GROUP BY insertDate    ) TT   )sm ON (dt.insertDate = sm.insertDate)   ORDER BY dt.insertDate DESC   LIMIT ?, ?
### Cause: cubrid.jdbc.driver.CUBRIDException: Cannot coerce value of domain "bigint" to domain "*variable*".[CAS INFO-localhost:30000,1,16148],[SESSION-90990],[URL-jdbc:cubrid:localhost:30000:cmTest:cmTest:********:?charset=UTF-8].
; uncategorized SQLException for SQL []; SQL state [null]; error code [-181]; Cannot coerce value of domain "bigint" to domain "*variable*".[CAS INFO-localhost:30000,1,16148],[SESSION-90990],[URL-jdbc:cubrid:localhost:30000:cmTest:cmTest:********:?charset=UTF-8].; nested exception is cubrid.jdbc.driver.CUBRIDException: Cannot coerce value of domain "bigint" to domain "*variable*".[CAS INFO-localhost:30000,1,16148],[SESSION-90990],[URL-jdbc:cubrid:localhost:30000:cmTest:cmTest:********:?charset=UTF-8].
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy19.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
    at com.crinity.webapps.statistics.dao.StatisticsDAO.getMemberStatistics(StatisticsDAO.java:253)
    at com.crinity.webapps.statistics.dao.StatisticsDAO$$FastClassBySpringCGLIB$$eef7d971.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
    at com.crinity.webapps.statistics.dao.StatisticsDAO$$EnhancerBySpringCGLIB$$264888fc.getMemberStatistics(<generated>)
    at com.crinity.webapps.admin._.statistics.service.StatisticsMemberStatusManager.getMemberStatus(StatisticsMemberStatusManager.java:107)
    at com.crinity.webapps.admin._.statistics.StatisticsManage.memberStatus(StatisticsManage.java:162)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:854)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:765)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at com.crinity.webapps.servlet.filter.SecurityHeaderFilter.doFilter(SecurityHeaderFilter.java:38)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:958)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1087)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: cubrid.jdbc.driver.CUBRIDException: Cannot coerce value of domain "bigint" to domain "*variable*".[CAS INFO-localhost:30000,1,16148],[SESSION-90990],[URL-jdbc:cubrid:localhost:30000:cmTest:cmTest:********:?charset=UTF-8].
    at cubrid.jdbc.jci.UConnection.createJciException(UConnection.java:2178)
    at cubrid.jdbc.jci.UInputBuffer.<init>(UInputBuffer.java:112)
    at cubrid.jdbc.jci.UConnection.send_recv_msg(UConnection.java:1751)
    at cubrid.jdbc.jci.UConnection.send_recv_msg(UConnection.java:1763)
    at cubrid.jdbc.jci.UStatement.executeInternal(UStatement.java:780)
    at cubrid.jdbc.jci.UStatement.execute(UStatement.java:847)
    at cubrid.jdbc.driver.CUBRIDStatement.executeCoreInternal(CUBRIDStatement.java:906)
    at cubrid.jdbc.driver.CUBRIDStatement.executeCore(CUBRIDStatement.java:871)
    at cubrid.jdbc.driver.CUBRIDPreparedStatement.execute(CUBRIDPreparedStatement.java:486)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:60)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 59 common frames omitted\

 

 

위의 전제조건하에 쿼리 실해잇 오류가 나는데 어떤이유에서 발생하는지 모르겠습니다,

답변 부탁드립니다,