The semantics of NOT IN are not intuitive when NULL values are involved. If H_CODE is null, then
H_CODE NOT IN (‘other’) is equivalent to NOT (null = ‘other’) which evaluates to UNKNOWN. WHERE only returns rows whose condition evaluates to TRUE, so it skips that row. And there are even worse problems with NOT IN when the IN contains a NULL value. Bottom line: It’s best to avoid NOT IN; Kylin may well be giving you the correct answer here. Julian > On Jan 6, 2016, at 5:44 PM, 曾经 <[email protected]> wrote: > > The version I use is 1.0. > > Recently I updated to 1.2. The problent is like following: > > Case1:******************************************************************* > > When I use “not in” in my sql, the resultset is less than my correct > data. Like following: > > When the sql I use not including a “not in” is : > > SELECT "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" AS "H_CODE", > > COUNT(1) AS "sum_Number_of_Records_ok", > > SUM("KYLIN_VIEW_TVAD_SUMMARY"."OCCU") AS "sum_OCCU_ok" > > FROM "BD_WAREHOUSE"."KYLIN_VIEW_TVAD_SUMMARY" "KYLIN_VIEW_TVAD_SUMMARY" > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_CITY_V_KYLIN" > "KYLIN_TV_DIM_CITY_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."CITY" = > > "KYLIN_TV_DIM_CITY_V_KYLIN"."CITY") > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."DT" = > > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN"."DT1") > > GROUP BY "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" > > The result is like following: > > (null) 206735 436180 > > other 169103517 480104320 > > 无 2597081 3161884 > > A 42464098 148884448 > > B 31945514 121964156 > > O 2787895 7265948 > > K 37694048 127243245 > > Whiel when I use a sql having “not in”, like following: > > SELECT "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" AS "H_CODE", > > COUNT(1) AS "sum_Number_of_Records_ok", > > SUM("KYLIN_VIEW_TVAD_SUMMARY"."OCCU") AS "sum_OCCU_ok" > > FROM "BD_WAREHOUSE"."KYLIN_VIEW_TVAD_SUMMARY" "KYLIN_VIEW_TVAD_SUMMARY" > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_CITY_V_KYLIN" > "KYLIN_TV_DIM_CITY_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."CITY" = > > "KYLIN_TV_DIM_CITY_V_KYLIN"."CITY") > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."DT" = > > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN"."DT1") > > WHERE ("KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" NOT IN ('other')) > > GROUP BY "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" > > The result I got is, there are only 3 lines, and the results are all less > than the true value: > > 无 323911 646111 > > A 7463670 21674399 > > K 15248274 54972183 > > Case2:******************************************************** > > Sql1: select channel_id, sum(occu) from kylin_view_tvad_summary where > channel_id not in (2, 3) group by channel_id. > > Here, 2 is a member of channel_id in kylin_view_tvad_summary, while 3 > is not a member of kylin_view_tvad_summary, there will be a null pointer > exception like following: > > java.sql.SQLException: error while executing SQL "select channel_id, > sum(occu) from kylin_view_tvad_summary where channel_id not in (2, 3) group > by channel_id > > LIMIT 50000": null > > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112) > > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130) > > at > org.apache.kylin.rest.service.QueryService.execute(QueryService.java:354) > > at > org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:268) > > at > org.apache.kylin.rest.service.QueryService.query(QueryService.java:114) > > 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$$3a29d57a.query(<generated>) > > at > org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:178) > > at > org.apache.kylin.rest.controller.QueryController.query(QueryController.java:85) > > at sun.reflect.GeneratedMethodAccessor153.invoke(Unknown Source) > > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > at java.lang.reflect.Method.invoke(Method.java:606) > > 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(DefaultLoginPageGeneratingFilter.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.SecurityContextPersistenceFilter.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 > org.apache.kylin.rest.filter.KylinApiFilter.doFilterInternal(KylinApiFilter.java:66) > > at > org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) > > 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:1145) > > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > > at > org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) > > at java.lang.Thread.run(Thread.java:744) > > Caused by: java.lang.NullPointerException > > at Baz$1$1.moveNext(Unknown Source) > > at > org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:737) > > at > org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:677) > > at > org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:301) > > at Baz.bind(Unknown Source) > > at > org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:281) > > at > org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:235) > > at > org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:533) > > at > org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184) > > at > org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:63) > > at > org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:42) > > at > org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:473) > > at > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:566) > > at > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477) > > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:109) > > ... 83 more > > [http-bio-7070-exec-3]:[2015-12-25 > 09:45:26,486][INFO][org.apache.kylin.rest.service.QueryService.logQuery(QueryService.java:242)] > - > > ==========================[QUERY]=============================== > > SQL: select channel_id, sum(occu) from kylin_view_tvad_summary where > channel_id not in (2, 3) group by channel_id > > User: ADMIN > > Success: false > > Duration: 0.0 > > Project: test_tv_album > > Realization Names: [cube_test_01] > > Cuboid Ids: [2] > > Total scan count: 0 > > Result row count: 0 > > Accept Partial: true > > Is Partial Result: false > > Hit Cache: false > > Message: null > > while executing SQL: "select channel_id, sum(occu) from > kylin_view_tvad_summary where channel_id not in (2, 3) group by channel_id > LIMIT 50000" > > ==========================[QUERY]=============================== > > > > [http-bio-7070-exec-3]:[2015-12-25 > 09:45:26,487][ERROR][org.apache.kylin.rest.controller.BasicController.handleError(BasicController.java:46)] > - > > org.apache.kylin.rest.exception.InternalErrorException: null > > while executing SQL: "select channel_id, sum(occu) from > kylin_view_tvad_summary where channel_id not in (2, 3) group by channel_id > LIMIT 50000" > > at > org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:205) > > at > org.apache.kylin.rest.controller.QueryController.query(QueryController.java:85) > > at sun.reflect.GeneratedMethodAccessor153.invoke(Unknown Source) > > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > at java.lang.reflect.Method.invoke(Method.java:606) > > 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(DefaultLoginPageGeneratingFilter.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.SecurityContextPersistenceFilter.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 > org.apache.kylin.rest.filter.KylinApiFilter.doFilterInternal(KylinApiFilter.java:66) > > at > org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) > > 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:1145) > > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > > at > org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) > > at java.lang.Thread.run(Thread.java:744) > > Case2: > > When I use “not in” in my sql, the resultset is less than my correct > data. Like following: > > When the sql I use not including a “not in” is : > > SELECT "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" AS "H_CODE", > > COUNT(1) AS "sum_Number_of_Records_ok", > > SUM("KYLIN_VIEW_TVAD_SUMMARY"."OCCU") AS "sum_OCCU_ok" > > FROM "BD_WAREHOUSE"."KYLIN_VIEW_TVAD_SUMMARY" "KYLIN_VIEW_TVAD_SUMMARY" > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_CITY_V_KYLIN" > "KYLIN_TV_DIM_CITY_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."CITY" = > > "KYLIN_TV_DIM_CITY_V_KYLIN"."CITY") > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."DT" = > > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN"."DT1") > > GROUP BY "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" > > The result is like following: > > (null) 206735 436180 > > other 169103517 480104320 > > 无 2597081 3161884 > > A 42464098 148884448 > > B 31945514 121964156 > > O 2787895 7265948 > > K 37694048 127243245 > > Whiel when I use a sql having “not in”, like following: > > SELECT "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" AS "H_CODE", > > COUNT(1) AS "sum_Number_of_Records_ok", > > SUM("KYLIN_VIEW_TVAD_SUMMARY"."OCCU") AS "sum_OCCU_ok" > > FROM "BD_WAREHOUSE"."KYLIN_VIEW_TVAD_SUMMARY" "KYLIN_VIEW_TVAD_SUMMARY" > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_CITY_V_KYLIN" > "KYLIN_TV_DIM_CITY_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."CITY" = > > "KYLIN_TV_DIM_CITY_V_KYLIN"."CITY") > > LEFT JOIN "BD_WAREHOUSE"."KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN" > > ON ("KYLIN_VIEW_TVAD_SUMMARY"."DT" = > > "KYLIN_TV_DIM_DATE_CUBE_V_KYLIN"."DT1") > > WHERE ("KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" NOT IN ('other')) > > GROUP BY "KYLIN_TV_DIM_CITY_V_KYLIN"."H_CODE" > > The result I got is: > > 无 323911 646111 > > A 7463670 21674399 > > K 15248274 54972183
