You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "Billy Liu (JIRA)" <ji...@apache.org> on 2017/11/06 14:33:00 UTC
[jira] [Commented] (KYLIN-3012) Error when using UNION ALL with
CASE WHEN to query
[ https://issues.apache.org/jira/browse/KYLIN-3012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16240370#comment-16240370 ]
Billy Liu commented on KYLIN-3012:
----------------------------------
Thanks [~revolyw] for reporting this issue. Could you reproduce this issue by the sample cube? It will help others to identify the root cause.
> 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'}
> 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)