[jira] [Created] (PHOENIX-6205) use count with 'case when' is not correct

["Future.Zhang (Jira)" <ji...@apache.org>]

Future.Zhang created PHOENIX-6205:
-------------------------------------

             Summary: 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
         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)