kylin-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "willow (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (KYLIN-3012) Error when using UNION ALL with CASE WHEN to query
Date Thu, 09 Nov 2017 15:33:00 GMT

     [ https://issues.apache.org/jira/browse/KYLIN-3012?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

willow updated KYLIN-3012:
--------------------------
    Description: 
sql like following:
SELECT 
  case when c.login_cnt is null then 0 else c.login_cnt end as login_cnt, 
  case when f.work_cnt  is null then 0 else f.work_cnt  end as work_cnt, 
  case when d.received_cnt is null then 0 else d.received_cnt end as received_cnt, 
  case when e.download_cnt is null then 0 else e.download_cnt end as download_cnt, 
  case when a.ent_view_cnt is null then 0 else a.ent_view_cnt end as ent_view_cnt, 
  case when b.work_view_cnt is null then 0 else b.work_view_cnt end as work_view_cnt
FROM( 
  SELECT count(*) as ent_view_cnt 
  FROM adl.ent_backstage_entview 
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2018-02-06'}e
AND create_dt <= {d'2019-09-01'}
)a, (
  SELECT count(*) as work_view_cnt 
  FROM adl.ent_backstage_workview
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2018-02-06'}
AND create_dt <= {d'2019-09-01'}
)b, (
  SELECT count(*) as login_cnt
  FROM adl.ent_backstage_opration
  WHERE  1=1   AND ent_id = 2352083 
AND opration_create_dt >= {d'2018-02-06'}
AND opration_create_dt <= {d'2019-09-01'}
  AND oprationtype = 1
)c,(
  SELECT  count(*) as received_cnt
  FROM adl.ent_backstage_apply 
  WHERE  1=1   AND ent_id = 2352083 
AND send_dt >= {d'2018-02-06'}
AND send_dt <= {d'2019-09-01'}
)d,(
  SELECT  count(*) as download_cnt
  FROM adl.ent_backstage_resume_download
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2018-02-06'}
AND create_dt <= {d'2019-09-01'}
)e,(
  SELECT  count(*) as work_cnt
  FROM adl.ent_backstage_work
  WHERE  1=1   AND ent_id = 2352083 
AND work_create_dt >= {d'2018-02-06'}
AND work_create_dt <= {d'2019-09-01'}
  AND isdisplay = 1
)f 
 UNION ALL SELECT 
  case when c.login_cnt is null then 0 else c.login_cnt end as login_cnt, 
  case when f.work_cnt  is null then 0 else f.work_cnt  end as work_cnt, 
  case when d.received_cnt is null then 0 else d.received_cnt end as received_cnt, 
  case when e.download_cnt is null then 0 else e.download_cnt end as download_cnt, 
  case when a.ent_view_cnt is null then 0 else a.ent_view_cnt end as ent_view_cnt, 
  case when b.work_view_cnt is null then 0 else b.work_view_cnt end as work_view_cnt
FROM( 
  SELECT count(*) as ent_view_cnt 
  FROM adl.ent_backstage_entview 
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2017-02-06'}
AND create_dt <= {d'2018-02-05'}
)a, (
  SELECT count(*) as work_view_cnt 
  FROM adl.ent_backstage_workview
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2017-02-06'}
AND create_dt <= {d'2018-02-05'}
)b, (
  SELECT count(*) as login_cnt
  FROM adl.ent_backstage_opration
  WHERE  1=1   AND ent_id = 2352083 
AND opration_create_dt >= {d'2017-02-06'}
AND opration_create_dt <= {d'2018-02-05'}
  AND oprationtype = 1
)c,(
  SELECT  count(*) as received_cnt
  FROM adl.ent_backstage_apply 
  WHERE  1=1   AND ent_id = 2352083 
AND send_dt >= {d'2017-02-06'}
AND send_dt <= {d'2018-02-05'}
)d,(
  SELECT  count(*) as download_cnt
  FROM adl.ent_backstage_resume_download
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2017-02-06'}
AND create_dt <= {d'2018-02-05'}
)e,(
  SELECT  count(*) as work_cnt
  FROM adl.ent_backstage_work
  WHERE  1=1   AND ent_id = 2352083 
AND work_create_dt >= {d'2017-02-06'}
AND work_create_dt <= {d'2018-02-05'}
  AND isdisplay = 1
)f 

  was:
sql like following:
SELECT 
  case when c.login_cnt is null then 0 else c.login_cnt end as login_cnt, 
  case when f.work_cnt  is null then 0 else f.work_cnt  end as work_cnt, 
  case when d.received_cnt is null then 0 else d.received_cnt end as received_cnt, 
  case when e.download_cnt is null then 0 else e.download_cnt end as download_cnt, 
  case when a.ent_view_cnt is null then 0 else a.ent_view_cnt end as ent_view_cnt, 
  case when b.work_view_cnt is null then 0 else b.work_view_cnt end as work_view_cnt
FROM( 
  SELECT count(*) as ent_view_cnt 
  FROM adl.ent_backstage_entview 
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2018-02-06'}
AND create_dt <= {d'2019-09-01'}
)a, (
  SELECT count(*) as work_view_cnt 
  FROM adl.ent_backstage_workview
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2018-02-06'}
AND create_dt <= {d'2019-09-01'}
)b, (
  SELECT count(*) as login_cnt
  FROM adl.ent_backstage_opration
  WHERE  1=1   AND ent_id = 2352083 
AND opration_create_dt >= {d'2018-02-06'}
AND opration_create_dt <= {d'2019-09-01'}
  AND oprationtype = 1
)c,(
  SELECT  count(*) as received_cnt
  FROM adl.ent_backstage_apply 
  WHERE  1=1   AND ent_id = 2352083 
AND send_dt >= {d'2018-02-06'}
AND send_dt <= {d'2019-09-01'}
)d,(
  SELECT  count(*) as download_cnt
  FROM adl.ent_backstage_resume_download
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2018-02-06'}
AND create_dt <= {d'2019-09-01'}
)e,(
  SELECT  count(*) as work_cnt
  FROM adl.ent_backstage_work
  WHERE  1=1   AND ent_id = 2352083 
AND work_create_dt >= {d'2018-02-06'}
AND work_create_dt <= {d'2019-09-01'}
  AND isdisplay = 1
)f 
 UNION ALL SELECT 
  case when c.login_cnt is null then 0 else c.login_cnt end as login_cnt, 
  case when f.work_cnt  is null then 0 else f.work_cnt  end as work_cnt, 
  case when d.received_cnt is null then 0 else d.received_cnt end as received_cnt, 
  case when e.download_cnt is null then 0 else e.download_cnt end as download_cnt, 
  case when a.ent_view_cnt is null then 0 else a.ent_view_cnt end as ent_view_cnt, 
  case when b.work_view_cnt is null then 0 else b.work_view_cnt end as work_view_cnt
FROM( 
  SELECT count(*) as ent_view_cnt 
  FROM adl.ent_backstage_entview 
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2017-02-06'}
AND create_dt <= {d'2018-02-05'}
)a, (
  SELECT count(*) as work_view_cnt 
  FROM adl.ent_backstage_workview
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2017-02-06'}
AND create_dt <= {d'2018-02-05'}
)b, (
  SELECT count(*) as login_cnt
  FROM adl.ent_backstage_opration
  WHERE  1=1   AND ent_id = 2352083 
AND opration_create_dt >= {d'2017-02-06'}
AND opration_create_dt <= {d'2018-02-05'}
  AND oprationtype = 1
)c,(
  SELECT  count(*) as received_cnt
  FROM adl.ent_backstage_apply 
  WHERE  1=1   AND ent_id = 2352083 
AND send_dt >= {d'2017-02-06'}
AND send_dt <= {d'2018-02-05'}
)d,(
  SELECT  count(*) as download_cnt
  FROM adl.ent_backstage_resume_download
  WHERE  1=1   AND ent_id = 2352083 
AND create_dt >= {d'2017-02-06'}
AND create_dt <= {d'2018-02-05'}
)e,(
  SELECT  count(*) as work_cnt
  FROM adl.ent_backstage_work
  WHERE  1=1   AND ent_id = 2352083 
AND work_create_dt >= {d'2017-02-06'}
AND work_create_dt <= {d'2018-02-05'}
  AND isdisplay = 1
)f 


> Error when using UNION ALL with CASE WHEN to query
> --------------------------------------------------
>
>                 Key: KYLIN-3012
>                 URL: https://issues.apache.org/jira/browse/KYLIN-3012
>             Project: Kylin
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: v2.1.0
>            Reporter: willow
>            Assignee: liyang
>            Priority: Minor
>
> sql like following:
> SELECT 
>   case when c.login_cnt is null then 0 else c.login_cnt end as login_cnt, 
>   case when f.work_cnt  is null then 0 else f.work_cnt  end as work_cnt, 
>   case when d.received_cnt is null then 0 else d.received_cnt end as received_cnt, 
>   case when e.download_cnt is null then 0 else e.download_cnt end as download_cnt, 
>   case when a.ent_view_cnt is null then 0 else a.ent_view_cnt end as ent_view_cnt, 
>   case when b.work_view_cnt is null then 0 else b.work_view_cnt end as work_view_cnt
> FROM( 
>   SELECT count(*) as ent_view_cnt 
>   FROM adl.ent_backstage_entview 
>   WHERE  1=1   AND ent_id = 2352083 
> AND create_dt >= {d'2018-02-06'}e
> AND create_dt <= {d'2019-09-01'}
> )a, (
>   SELECT count(*) as work_view_cnt 
>   FROM adl.ent_backstage_workview
>   WHERE  1=1   AND ent_id = 2352083 
> AND create_dt >= {d'2018-02-06'}
> AND create_dt <= {d'2019-09-01'}
> )b, (
>   SELECT count(*) as login_cnt
>   FROM adl.ent_backstage_opration
>   WHERE  1=1   AND ent_id = 2352083 
> AND opration_create_dt >= {d'2018-02-06'}
> AND opration_create_dt <= {d'2019-09-01'}
>   AND oprationtype = 1
> )c,(
>   SELECT  count(*) as received_cnt
>   FROM adl.ent_backstage_apply 
>   WHERE  1=1   AND ent_id = 2352083 
> AND send_dt >= {d'2018-02-06'}
> AND send_dt <= {d'2019-09-01'}
> )d,(
>   SELECT  count(*) as download_cnt
>   FROM adl.ent_backstage_resume_download
>   WHERE  1=1   AND ent_id = 2352083 
> AND create_dt >= {d'2018-02-06'}
> AND create_dt <= {d'2019-09-01'}
> )e,(
>   SELECT  count(*) as work_cnt
>   FROM adl.ent_backstage_work
>   WHERE  1=1   AND ent_id = 2352083 
> AND work_create_dt >= {d'2018-02-06'}
> AND work_create_dt <= {d'2019-09-01'}
>   AND isdisplay = 1
> )f 
>  UNION ALL SELECT 
>   case when c.login_cnt is null then 0 else c.login_cnt end as login_cnt, 
>   case when f.work_cnt  is null then 0 else f.work_cnt  end as work_cnt, 
>   case when d.received_cnt is null then 0 else d.received_cnt end as received_cnt, 
>   case when e.download_cnt is null then 0 else e.download_cnt end as download_cnt, 
>   case when a.ent_view_cnt is null then 0 else a.ent_view_cnt end as ent_view_cnt, 
>   case when b.work_view_cnt is null then 0 else b.work_view_cnt end as work_view_cnt
> FROM( 
>   SELECT count(*) as ent_view_cnt 
>   FROM adl.ent_backstage_entview 
>   WHERE  1=1   AND ent_id = 2352083 
> AND create_dt >= {d'2017-02-06'}
> AND create_dt <= {d'2018-02-05'}
> )a, (
>   SELECT count(*) as work_view_cnt 
>   FROM adl.ent_backstage_workview
>   WHERE  1=1   AND ent_id = 2352083 
> AND create_dt >= {d'2017-02-06'}
> AND create_dt <= {d'2018-02-05'}
> )b, (
>   SELECT count(*) as login_cnt
>   FROM adl.ent_backstage_opration
>   WHERE  1=1   AND ent_id = 2352083 
> AND opration_create_dt >= {d'2017-02-06'}
> AND opration_create_dt <= {d'2018-02-05'}
>   AND oprationtype = 1
> )c,(
>   SELECT  count(*) as received_cnt
>   FROM adl.ent_backstage_apply 
>   WHERE  1=1   AND ent_id = 2352083 
> AND send_dt >= {d'2017-02-06'}
> AND send_dt <= {d'2018-02-05'}
> )d,(
>   SELECT  count(*) as download_cnt
>   FROM adl.ent_backstage_resume_download
>   WHERE  1=1   AND ent_id = 2352083 
> AND create_dt >= {d'2017-02-06'}
> AND create_dt <= {d'2018-02-05'}
> )e,(
>   SELECT  count(*) as work_cnt
>   FROM adl.ent_backstage_work
>   WHERE  1=1   AND ent_id = 2352083 
> AND work_create_dt >= {d'2017-02-06'}
> AND work_create_dt <= {d'2018-02-05'}
>   AND isdisplay = 1
> )f 



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message