I just have a try on Kylin's sample data: "select count(*) from KYLIN_SALES where ((EXTRACT(MONTH FROM "PART_DT")-1)/3 +1 =2)", the query works and result is 2462. So the extract works well. What's the data type of "DADD"? In my test, it's date.
2017-01-18 2:07 GMT+08:00 Павел Тарасов <[email protected]>: > Thank you for fast reply, > > May be it’s possible to fix it? > > 2017-01-17 20:58:07,835 ERROR [http-bio-7070-exec-6] > controller.QueryController:209 : Exception when execute sql > java.sql.SQLException: Error while executing SQL "SELECT manager gopagopa > FROM "PTARASOV"."TEST_ORDERFACT" "TEST_ORDERFACT" > WHERE (( EXTRACT(MONTH FROM "TEST_ORDERFACT"."DADD") - 1) / 3 + 1 = 2) > LIMIT 50000": + > 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:143) > at org.apache.calcite.avatica.AvaticaStatement.executeQuery( > AvaticaStatement.java:186) > at org.apache.kylin.rest.service.QueryService.execute( > QueryService.java:361) > at org.apache.kylin.rest.service.QueryService.queryWithSqlMassage( > QueryService.java:273) > at org.apache.kylin.rest.service.QueryService.query(QueryService.java:121) > at org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f. > invoke(<generated>) > at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) > at org.springframework.aop.framework.Cglib2AopProxy$ > DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) > at org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$ > $d3a0f31d.query(<generated>) > at org.apache.kylin.rest.controller.QueryController.doQueryWithCache( > QueryController.java:192) > at org.apache.kylin.rest.controller.QueryController. > query(QueryController.java:94) > at sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source) > at sun.reflect.DelegatingMethodAccessorImpl.invoke( > DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:497) > at org.springframework.web.method.support.InvocableHandlerMethod.invoke( > InvocableHandlerMethod.java:213) > at org.springframework.web.method.support.InvocableHandlerMethod. > invokeForRequest(InvocableHandlerMethod.java:126) > at org.springframework.web.servlet.mvc.method.annotation. > ServletInvocableHandlerMethod.invokeAndHandle( > ServletInvocableHandlerMethod.java:96) > at org.springframework.web.servlet.mvc.method.annotation. > RequestMappingHandlerAdapter.invokeHandlerMethod( > RequestMappingHandlerAdapter.java:617) > at org.springframework.web.servlet.mvc.method.annotation. > RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter. > java:578) > at org.springframework.web.servlet.mvc.method. > AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) > at org.springframework.web.servlet.DispatcherServlet. > doDispatch(DispatcherServlet.java:923) > at org.springframework.web.servlet.DispatcherServlet. > doService(DispatcherServlet.java:852) > at org.springframework.web.servlet.FrameworkServlet.processRequest( > FrameworkServlet.java:882) > at org.springframework.web.servlet.FrameworkServlet. > doPost(FrameworkServlet.java:789) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:646) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) > 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 org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:330) > at org.springframework.security.web.access.intercept. > FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) > at org.springframework.security.web.access.intercept. > FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.access.ExceptionTranslationFilter. > doFilter(ExceptionTranslationFilter.java:113) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.session.SessionManagementFilter. > doFilter(SessionManagementFilter.java:103) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.authentication. > AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter. > java:113) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.servletapi. > SecurityContextHolderAwareRequestFilter.doFilter( > SecurityContextHolderAwareRequestFilter.java:54) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.savedrequest.RequestCacheAwareFilter. > doFilter(RequestCacheAwareFilter.java:45) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.authentication.www. > BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.authentication.ui. > DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilt > er.java:91) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.authentication. > AbstractAuthenticationProcessingFilter.doFilter( > AbstractAuthenticationProcessingFilter.java:183) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.authentication.logout. > LogoutFilter.doFilter(LogoutFilter.java:105) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.context.SecurityContextPersistenceFilt > er.doFilter(SecurityContextPersistenceFilter.java:87) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.FilterChainProxy.doFilterInternal( > FilterChainProxy.java:192) > at org.springframework.security.web.FilterChainProxy.doFilter( > FilterChainProxy.java:160) > at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate( > DelegatingFilterProxy.java:346) > at org.springframework.web.filter.DelegatingFilterProxy.doFilter( > DelegatingFilterProxy.java:259) > 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:195) > at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266) > 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:220) > at org.apache.catalina.core.StandardContextValve.invoke( > StandardContextValve.java:122) > at org.apache.catalina.authenticator.AuthenticatorBase.invoke( > AuthenticatorBase.java:504) > at org.apache.catalina.core.StandardHostValve.invoke( > StandardHostValve.java:170) > at org.apache.catalina.valves.ErrorReportValve.invoke( > ErrorReportValve.java:103) > at org.apache.catalina.valves.AccessLogValve.invoke( > AccessLogValve.java:950) > at org.apache.catalina.core.StandardEngineValve.invoke( > StandardEngineValve.java:116) > at org.apache.catalina.connector.CoyoteAdapter.service( > CoyoteAdapter.java:421) > at org.apache.coyote.http11.AbstractHttp11Processor.process( > AbstractHttp11Processor.java:1074) > at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler. > process(AbstractProtocol.java:611) > at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor. > run(JIoEndpoint.java:316) > at java.util.concurrent.ThreadPoolExecutor.runWorker( > ThreadPoolExecutor.java:1142) > at java.util.concurrent.ThreadPoolExecutor$Worker.run( > ThreadPoolExecutor.java:617) > at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run( > TaskThread.java:61) > at java.lang.Thread.run(Thread.java:745) > Caused by: java.lang.UnsupportedOperationException: + > at org.apache.kylin.query.relnode.OLAPFilterRel$ > TupleFilterVisitor.visitCall(OLAPFilterRel.java:141) > at org.apache.kylin.query.relnode.OLAPFilterRel$ > TupleFilterVisitor.visitCall(OLAPFilterRel.java:73) > at org.apache.calcite.rex.RexCall.accept(RexCall.java:108) > at org.apache.kylin.query.relnode.OLAPFilterRel$ > TupleFilterVisitor.visitCall(OLAPFilterRel.java:145) > at org.apache.kylin.query.relnode.OLAPFilterRel$ > TupleFilterVisitor.visitCall(OLAPFilterRel.java:73) > at org.apache.calcite.rex.RexCall.accept(RexCall.java:108) > at org.apache.kylin.query.relnode.OLAPFilterRel. > translateFilter(OLAPFilterRel.java:292) > at org.apache.kylin.query.relnode.OLAPFilterRel. > implementOLAP(OLAPFilterRel.java:274) > at org.apache.kylin.query.relnode.OLAPRel$OLAPImplementor.visitChild( > OLAPRel.java:81) > at org.apache.kylin.query.relnode.OLAPProjectRel. > implementOLAP(OLAPProjectRel.java:101) > at org.apache.kylin.query.relnode.OLAPRel$OLAPImplementor.visitChild( > OLAPRel.java:81) > at org.apache.kylin.query.relnode.OLAPLimitRel.implementOLAP(OLAPLimitRel. > java:74) > at org.apache.kylin.query.relnode.OLAPRel$OLAPImplementor.visitChild( > OLAPRel.java:81) > at org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement( > OLAPToEnumerableConverter.java:69) > at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor. > implementRoot(EnumerableRelImplementor.java:102) > at org.apache.calcite.adapter.enumerable.EnumerableInterpretable. > toBindable(EnumerableInterpretable.java:92) > at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt. > implement(CalcitePrepareImpl.java:1171) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196) > at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_( > CalcitePrepareImpl.java:721) > at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_( > CalcitePrepareImpl.java:588) > at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql( > CalcitePrepareImpl.java:558) > at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery( > CalciteConnectionImpl.java:214) > at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute( > CalciteMetaImpl.java:573) > at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal( > AvaticaConnection.java:571) > at org.apache.calcite.avatica.AvaticaStatement.executeInternal( > AvaticaStatement.java:135) > ... 79 more > 2017-01-17 20:58:07,837 INFO [http-bio-7070-exec-6] > service.QueryService:250 : > ==========================[QUERY]=============================== > SQL: SELECT manager gopagopa > FROM "PTARASOV"."TEST_ORDERFACT" "TEST_ORDERFACT" > WHERE (({fn EXTRACT(MONTH FROM "TEST_ORDERFACT"."DADD")} - 1) / 3 + 1 = 2) > User: ADMIN > Success: false > Duration: 0.0 > Project: test > Realization Names: [] > Cuboid Ids: [] > Total scan count: 0 > Result row count: 0 > Accept Partial: true > Is Partial Result: false > Hit Exception Cache: false > Storage cache used: false > Message: Error while executing SQL "SELECT manager gopagopa FROM > "PTARASOV"."TEST_ORDERFACT" "TEST_ORDERFACT" WHERE (( EXTRACT(MONTH FROM > "TEST_ORDERFACT"."DADD") - 1) / 3 + 1 = 2) LIMIT 50000": + > ==========================[QUERY]=============================== > > 2017-01-17 20:58:07,838 ERROR [http-bio-7070-exec-6] > controller.BasicController:44 : > org.apache.kylin.rest.exception.InternalErrorException: Error while > executing SQL "SELECT manager gopagopa FROM "PTARASOV"."TEST_ORDERFACT" > "TEST_ORDERFACT" WHERE (( EXTRACT(MONTH FROM "TEST_ORDERFACT"."DADD") - 1) > / 3 + 1 = 2) LIMIT 50000": + > at org.apache.kylin.rest.controller.QueryController.doQueryWithCache( > QueryController.java:224) > at org.apache.kylin.rest.controller.QueryController. > query(QueryController.java:94) > at sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source) > at sun.reflect.DelegatingMethodAccessorImpl.invoke( > DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:497) > at org.springframework.web.method.support.InvocableHandlerMethod.invoke( > InvocableHandlerMethod.java:213) > at org.springframework.web.method.support.InvocableHandlerMethod. > invokeForRequest(InvocableHandlerMethod.java:126) > at org.springframework.web.servlet.mvc.method.annotation. > ServletInvocableHandlerMethod.invokeAndHandle( > ServletInvocableHandlerMethod.java:96) > at org.springframework.web.servlet.mvc.method.annotation. > RequestMappingHandlerAdapter.invokeHandlerMethod( > RequestMappingHandlerAdapter.java:617) > at org.springframework.web.servlet.mvc.method.annotation. > RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter. > java:578) > at org.springframework.web.servlet.mvc.method. > AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) > at org.springframework.web.servlet.DispatcherServlet. > doDispatch(DispatcherServlet.java:923) > at org.springframework.web.servlet.DispatcherServlet. > doService(DispatcherServlet.java:852) > at org.springframework.web.servlet.FrameworkServlet.processRequest( > FrameworkServlet.java:882) > at org.springframework.web.servlet.FrameworkServlet. > doPost(FrameworkServlet.java:789) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:646) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) > 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) > > On 17 Jan 2017, at 15:34, Billy Liu <[email protected]> wrote: > > One workaround is to define a new column with "EXTRACT(MONTH FROM > "TEST_ORDERFACT"."DADD")" by Hive view. > > 2017-01-17 20:22 GMT+08:00 Тарасов Павел <[email protected]>: > >> Hi, >> >> I’ve problem with tableau & kylin connect. When creating filter on month >> in tableau it generate query with filter >> >> WHERE (({fn EXTRACT(MONTH FROM "TEST_ORDERFACT"."DADD")} - 1) / 3 + 1 = >> 2) >> >> and kylin fails on such queries. Is there way to solve this problem? >> >> Best regards, >> Pavel Tarasov > > > >
