[jira] [Commented] (KYLIN-1294) Filter of NOT IN (...) can cause incorrect result or NPE
[ https://issues.apache.org/jira/browse/KYLIN-1294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15093623#comment-15093623 ] hongbin ma commented on KYLIN-1294: --- the issue blocks at CALCITE-980 before we could even reproduce your problem > Filter of NOT IN (...) can cause incorrect result or NPE > > > Key: KYLIN-1294 > URL: https://issues.apache.org/jira/browse/KYLIN-1294 > Project: Kylin > Issue Type: Bug >Reporter: liyang >Assignee: hongbin ma > > 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 > other169103517 480104320 > 无 25970813161884 > A 42464098 148884448 > B 31945514 121964156 > O 27878957265948 > 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 5": 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() > 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() > 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 >
[jira] [Commented] (KYLIN-1294) Filter of NOT IN (...) can cause incorrect result or NPE
[ https://issues.apache.org/jira/browse/KYLIN-1294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15093604#comment-15093604 ] hongbin ma commented on KYLIN-1294: --- the issue is pending on https://issues.apache.org/jira/browse/CALCITE-980, which is fixed in calcite 1.6. We'll upgrade to calcite 1.6 as soon as they release it > Filter of NOT IN (...) can cause incorrect result or NPE > > > Key: KYLIN-1294 > URL: https://issues.apache.org/jira/browse/KYLIN-1294 > Project: Kylin > Issue Type: Bug >Reporter: liyang >Assignee: hongbin ma > > 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 > other169103517 480104320 > 无 25970813161884 > A 42464098 148884448 > B 31945514 121964156 > O 27878957265948 > 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 5": 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() > 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() > 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
[jira] [Commented] (KYLIN-1294) Filter of NOT IN (...) can cause incorrect result or NPE
[ https://issues.apache.org/jira/browse/KYLIN-1294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15093619#comment-15093619 ] huawang commented on KYLIN-1294: The question is even the sql doesn't throw a NPE, the result set of the sql is not correct as shown in the but description. > Filter of NOT IN (...) can cause incorrect result or NPE > > > Key: KYLIN-1294 > URL: https://issues.apache.org/jira/browse/KYLIN-1294 > Project: Kylin > Issue Type: Bug >Reporter: liyang >Assignee: hongbin ma > > 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 > other169103517 480104320 > 无 25970813161884 > A 42464098 148884448 > B 31945514 121964156 > O 27878957265948 > 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 5": 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() > 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() > 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 >
[jira] [Commented] (KYLIN-1294) Filter of NOT IN (...) can cause incorrect result or NPE
[ https://issues.apache.org/jira/browse/KYLIN-1294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15086737#comment-15086737 ] liyang commented on KYLIN-1294: --- Meanwhile worth note that this test query did pass with no problem, giving ('FP-GTC', 'ABIN') are valid values and lstg_format_name does not contain null. select lstg_format_name, sum(price) as GMV from test_kylin_fact where lstg_format_name not in ('FP-GTC', 'ABIN') group by lstg_format_name > Filter of NOT IN (...) can cause incorrect result or NPE > > > Key: KYLIN-1294 > URL: https://issues.apache.org/jira/browse/KYLIN-1294 > Project: Kylin > Issue Type: Bug >Reporter: liyang > > 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 > other169103517 480104320 > 无 25970813161884 > A 42464098 148884448 > B 31945514 121964156 > O 27878957265948 > 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 5": 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() > 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() > 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 >
[jira] [Commented] (KYLIN-1294) Filter of NOT IN (...) can cause incorrect result or NPE
[ https://issues.apache.org/jira/browse/KYLIN-1294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15086736#comment-15086736 ] liyang commented on KYLIN-1294: --- Julian pointed out a maybe related JIRA https://issues.apache.org/jira/browse/CALCITE-980 > Filter of NOT IN (...) can cause incorrect result or NPE > > > Key: KYLIN-1294 > URL: https://issues.apache.org/jira/browse/KYLIN-1294 > Project: Kylin > Issue Type: Bug >Reporter: liyang > > 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 > other169103517 480104320 > 无 25970813161884 > A 42464098 148884448 > B 31945514 121964156 > O 27878957265948 > 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 5": 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() > 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() > 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 >