You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Viraj Jasani (Jira)" <ji...@apache.org> on 2021/05/21 16:26:00 UTC
[jira] [Updated] (PHOENIX-6205) use count with 'case when' is not
correct
[ https://issues.apache.org/jira/browse/PHOENIX-6205?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Viraj Jasani updated PHOENIX-6205:
----------------------------------
Fix Version/s: (was: 4.16.1)
4.16.2
> 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.17.0, 4.16.2
>
> Attachments: bigdata.sql, 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)