hit-lacus edited a comment on pull request #1493: URL: https://github.com/apache/kylin/pull/1493#issuecomment-735928205
## Cube Set shardBy to true to `KYLIN_SALES.SELLER_ID` . ### Reproduce SQL ```sql SELECT SELLER_ID, CASE WHEN LSTG_SITE_ID > 1 then LSTG_SITE_ID + 0.3 else LSTG_SITE_ID END AS dyna_group, SUM(PRICE) FROM KYLIN_SALES GROUP BY SELLER_ID, CASE WHEN LSTG_SITE_ID > 1 then LSTG_SITE_ID + 0.3 else LSTG_SITE_ID END HAVING SUM(PRICE)>10 ``` ### Error Log ```java 2020-12-01 09:49:27,262 ERROR [Query c43e8817-e24d-6230-8857-cc0c2092ef5e-59] service.QueryService:576 : Exception while executing query java.sql.SQLException: Error while executing SQL "select * from (SELECT SELLER_ID, CASE WHEN LSTG_SITE_ID > 1 then LSTG_SITE_ID + 0.3 else LSTG_SITE_ID END AS dyna_group, SUM(PRICE) FROM KYLIN_SALES GROUP BY SELLER_ID, CASE WHEN LSTG_SITE_ID > 1 then LSTG_SITE_ID + 0.3 else LSTG_SITE_ID END HAVING SUM(PRICE)>10) limit 50000": Index: 4, Size: 1 at org.apache.calcite.avatica.Helper.createException(Helper.java:56) at org.apache.calcite.avatica.Helper.createException(Helper.java:41) at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163) at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) at org.apache.kylin.rest.service.QueryService.executeRequest(QueryService.java:1010) at org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:706) at org.apache.kylin.rest.service.QueryService.query(QueryService.java:225) at org.apache.kylin.rest.service.QueryService.queryAndUpdateCache(QueryService.java:514) at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:474) at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:402) at org.apache.kylin.rest.controller.QueryController.query(QueryController.java:93) 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:647) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209) at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244) 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:219) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:165) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104) 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:1195) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:654) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:319) 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: java.lang.IndexOutOfBoundsException: Index: 4, Size: 1 at java.util.ArrayList.rangeCheck(ArrayList.java:657) at java.util.ArrayList.get(ArrayList.java:433) at org.apache.kylin.storage.gtrecord.GTCubeStorageQueryBase.checkHavingCanPushDown(GTCubeStorageQueryBase.java:550) at org.apache.kylin.storage.gtrecord.GTCubeStorageQueryBase.getStorageQueryRequest(GTCubeStorageQueryBase.java:188) at org.apache.kylin.storage.gtrecord.GTCubeStorageQueryBase.search(GTCubeStorageQueryBase.java:89) at org.apache.kylin.query.enumerator.OLAPEnumerator.queryStorage(OLAPEnumerator.java:121) at org.apache.kylin.query.enumerator.OLAPEnumerator.moveNext(OLAPEnumerator.java:62) at Baz$1$1.moveNext(ANONYMOUS.java:55) at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:825) at org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:761) at org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:302) at Baz.bind(Baz.java:80) at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:365) at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:301) at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:559) at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:550) at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182) at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67) at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44) at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:667) at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:619) at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675) at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156) ... 80 more ``` ## Cuase The method in `buildGroups` in `OLAPAggregateRel.java` return duplicate column, thus cause `sqlDigest.havingFilter` be set to a wrong column. ## Step by step 1. Having Filter is wrong, should be `price` not `LSTG_SITE_ID` ! Let's see what happen ? <img width="1127" alt="image" src="https://user-images.githubusercontent.com/14030549/100642316-993b7b80-3373-11eb-88cf-b1a7db7044e8.png"> 2. Fetch duplicate column in TupleExpression, two `LSTG_SITE_ID`. <img width="842" alt="image" src="https://user-images.githubusercontent.com/14030549/100642470-c556fc80-3373-11eb-9601-0e2ac26f3c90.png"> 3. Add same column(`LSTG_SITE_ID`) to groupBy column twice. <img width="857" alt="image" src="https://user-images.githubusercontent.com/14030549/100642615-f1727d80-3373-11eb-977a-a530e2ab36f6.png"> 4. Start to create a having TupleFilter <img width="1042" alt="image" src="https://user-images.githubusercontent.com/14030549/100642787-31d1fb80-3374-11eb-9acc-babce32c535b.png"> 5. `$2` is wrong because <img width="1210" alt="image" src="https://user-images.githubusercontent.com/14030549/100642872-5201ba80-3374-11eb-872f-b44c46e85b80.png"> ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org