[
https://issues.apache.org/jira/browse/PHOENIX-6205?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17227261#comment-17227261
]
Chao Wang commented on PHOENIX-6205:
------------------------------------
hello [~weilai]， could you provide your create sql? I want reproduct in my
local environment. if this is exist, I will solved it.
> use count with 'case when' is not correct
> ------------------------------------------
>
> Key: PHOENIX-6205
> URL: https://issues.apache.org/jira/browse/PHOENIX-6205
> Project: Phoenix
> Issue Type: Bug
> Components: core
> Affects Versions: 5.0.0
> Environment: OS: CentOS 7.3
> HBase: 2.0.0
> Reporter: Future.Zhang
> Priority: Critical
> Fix For: 4.16.1, 4.17.0
>
> Attachments: image-2020-10-23-14-24-35-085.png,
> image-2020-10-23-14-26-58-256.png
>
>
> *Query1 and result:*
>
> {code:java}
> SELECT REPAIR_NODE_CODE, THEME_ID , DATABASE_ID, OBJECT_NAME,
> count(1) AS aaa,
> count(CASE WHEN EXPORT_SQL_BATCH_NO is NULL THEN 1 ELSE null end) AS
> notexport,
> count(CASE WHEN EXPORT_SQL_BATCH_NO IS NOT NULL THEN 1 ELSE null END) AS
> export
> FROM QM_CK_RESULT.QM_CK_RESULT
> WHERE REPAIR_NODE_CODE IS NOT NULL
> AND STATUS='7'
> AND REPAIR_TYPE IN ('1','2')
> GROUP BY REPAIR_NODE_CODE, THEME_ID , DATABASE_ID, OBJECT_NAME
> {code}
> the EXPORT column result is：
> !image-2020-10-23-14-24-35-085.png|width=671,height=103!
>
> *Query2 and result:*
> {code:java}
> SELECT REPAIR_NODE_CODE, THEME_ID , DATABASE_ID, OBJECT_NAME,
> count(1) AS aaa,
> count(CASE WHEN EXPORT_SQL_BATCH_NO is NULL THEN 1 ELSE null end) AS
> notexport,
> count(CASE WHEN EXPORT_SQL_BATCH_NO IS NOT NULL THEN 2 ELSE null END) AS
> export
> FROM QM_CK_RESULT.QM_CK_RESULT
> WHERE REPAIR_NODE_CODE IS NOT NULL
> AND STATUS='7'
> AND REPAIR_TYPE IN ('1','2')
> GROUP BY REPAIR_NODE_CODE, THEME_ID , DATABASE_ID, OBJECT_NAME
> {code}
> the EXPORT column result is：
> *!image-2020-10-23-14-26-58-256.png|width=642,height=94!*
>
> *The* distinction between query1 and query2 is
> I change query1 aggregation‘s column ‘export’ conditon
> {code:java}
> count(CASE WHEN EXPORT_SQL_BATCH_NO IS NOT NULL THEN 2 ELSE null END) AS
> export
> {code}
> ‘1’ to ‘2’ .
>
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
