Interesting finding; I guess it is a bug of Calcite. Kylin's calcite is a little old, we will upgrade it to a newer version, maybe it can be fixed with that.
Best regards, Shaofeng Shi 史少锋 Apache Kylin PMC Work email: shaofeng....@kyligence.io Kyligence Inc: https://kyligence.io/ Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html Join Kylin user mail group: user-subscr...@kylin.apache.org Join Kylin dev mail group: dev-subscr...@kylin.apache.org May Zhou <may.a.z...@foxmail.com> 于2018年11月21日周三 下午7:20写道： > Hi Lin, > Nice finding!!! Could you open a JIRA to track this issue? > Thank you. > > BRs, > May > > > ------------------ 原始邮件 ------------------ > *发件人:* "王林"<1059790...@qq.com>; > *发送时间:* 2018年11月21日(星期三) 下午4:50 > *收件人:* "dev"<dev@kylin.apache.org>;"shaofengshi"<shaofeng...@apache.org>; > *主题:* 回复： apache kylin left join 数据查询混乱咨询 > > SELECT c.DT, COALESCE (C.weekActivityCount, 0), COALESCE > (D.monthActivityCount, 0) FROM ( SELECT DT AS DT, count(*) AS > weekActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE 6 >= > LAST_ONLINE_DT_DIFF_DAY 改为：LAST_ONLINE_DT_DIFF_DAY<=6 AND > LAST_ONLINE_DT_DIFF_DAY >= 0 AND DT >= '2018-10-11' AND '2018-10-11' >= DT > AND IS_ACTIVATED = 1 GROUP BY dt ) C LEFT JOIN ( SELECT DT AS DT, count(*) > AS monthActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE 29 >= > LAST_ONLINE_DT_DIFF_DAY 改为：LAST_ONLINE_DT_DIFF_DAY<=29 AND > LAST_ONLINE_DT_DIFF_DAY >= 0 AND DT >= '2018-10-11' AND '2018-10-11' >= DT > AND IS_ACTIVATED = 1 GROUP BY dt ) D ON C.DT = D.DT > sql 按照上述修改，查询结果就是正确的，应该是kylin 的bug。 > > > ------------------ 原始邮件 ------------------ > 发件人: "shaofengshi"<shaofeng...@apache.org>; > 发送时间: 2018年11月20日(星期二) 下午4:40 > 收件人: "dev"<dev@kylin.apache.org>; > > 主题: Re: apache kylin left join 数据查询混乱咨询 > > > > Hi Ling, > Could you please provide the Kylin log when executing the first query? > Thanks! > > Best regards, > > Shaofeng Shi 史少锋 > Apache Kylin PMC > Work email: shaofeng....@kyligence.io > Kyligence Inc: https://kyligence.io/ > > Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html > Join Kylin user mail group: user-subscr...@kylin.apache.org > Join Kylin dev mail group: dev-subscr...@kylin.apache.org > > > > > dbaztp <dba...@gmail.com> 于2018年11月19日周一 下午6:52写道： > > > Hi Wang Lin, > > I hadencountered this problem as well, the E-mail I had sent to SQL > > developer is as below. Maybe it is helpful to you too. > > PS: You should adapt your SQL to recommended solution. > > > > > ---------------------------------------------------------------------------------------------------- > > Window functions are supported in Kylin, such as: lead(), lag(), first() > > Solution 1(deprecated)： > > SELECT "this_year"."all_count" * 1.0 /"last_year"."all_count" AS > > "new_rate" FROM (SELECT {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} > > AS "action_month", SUM("APP_NEW_MC"."ALLCOUNT") AS "all_count", {fn > > EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} AS "action_yr" FROM > > "ADL"."APP_NEW_MC" "APP_NEW_MC" WHERE {fn EXTRACT(MONTH FROM > > "APP_NEW_MC"."ACTION_DT")} = {fn EXTRACT(MONTH FROM TIMESTAMPADD(DAY, -1, > > CURRENT_DATE))} AND {fn EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} = > {fn > > EXTRACT(YEAR FROM CURRENT_DATE)} - 1 AND "APP_NEW_MC"."PRODUCT" = ‘xxxx' > > GROUP BY {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")}, {fn > > EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")}) "last_year" LEFT JOIN > (SELECT > > {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} AS "action_month", > > SUM("APP_NEW_MC"."ALLCOUNT") AS "all_count", {fn EXTRACT(YEAR FROM > > "APP_NEW_MC"."ACTION_DT")} AS "action_yr" FROM "ADL"."APP_NEW_MC" > > "APP_NEW_MC" WHERE {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} = > {fn > > EXTRACT(MONTH FROM TIMESTAMPADD(DAY, -1, CURRENT_DATE))} AND {fn > > EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} = {fn EXTRACT(YEAR FROM > > CURRENT_DATE)} AND "APP_NEW_MC"."PRODUCT" = ‘xxxx' GROUP BY {fn > > EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")}, {fn EXTRACT(YEAR FROM > > "APP_NEW_MC"."ACTION_DT")}) "this_year" ON "last_year"."action_month" = > > "this_year"."action_month"; > > Solution 2(recommended)： > > SELECT {fn EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} as "YEAR", {fn > > EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} as "MONTH", > > SUM("APP_NEW_MC"."ALLCOUNT") / cast(lead(SUM("APP_NEW_MC"."ALLCOUNT"), > 12) > > over () as double) as "new_rate" FROM "ADL"."APP_NEW_MC" "APP_NEW_MC" > WHERE > > "APP_NEW_MC"."PRODUCT" = ‘xxxx' GROUP BY {fn EXTRACT(YEAR FROM > > "APP_NEW_MC"."ACTION_DT")},{fn EXTRACT(MONTH FROM > > "APP_NEW_MC"."ACTION_DT")} order by "YEAR" desc, "MONTH" desc; > > > > > > Original Message > > Sender:王林1059790...@qq.com > > Recipient:dev...@kylin.apache.org > > Date:Monday, Nov 19, 2018 18:34 > > Subject:apache kylin left join 数据查询混乱咨询 > > > > > > 你好： 通过kylin 页面查询发现如下问题： 执行如下sql： SELECT c.DT, COALESCE > > (C.weekActivityCount, 0), COALESCE (D.monthActivityCount, 0) FROM ( > SELECT > > DT AS DT, count(*) AS weekActivityCount FROM ST_DEVICE_INFO_FACT_TABLE > > WHERE 6 = LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT > = > > '2018-10-11' AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt ) C > > LEFT JOIN ( SELECT DT AS DT, count(*) AS monthActivityCount FROM > > ST_DEVICE_INFO_FACT_TABLE WHERE 29 = LAST_ONLINE_DT_DIFF_DAY AND > > LAST_ONLINE_DT_DIFF_DAY = 0 AND DT = '2018-10-11' AND '2018-10-11' = DT > AND > > IS_ACTIVATED = 1 GROUP BY dt ) D ON C.DT = D.DT 查询使用cube： > > CUBE[name=st_device_info_cube_v140],CUBE[name=st_device_info_cube_v140] > > 查询结果为： 2018-10-11，996542，996542 但是单独执行C,D两个子查询： SELECT DT AS DT, count(*) > > AS weekActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE 6 = > > LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT = > > '2018-10-11' AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt > Cubes: > > CUBE[name=st_device_info_cube_v140] 查询结果为： 2018-10-11，996542 执行： SELECT > DT > > AS DT, count(*) AS monthActivityCount FROM ST_DEVICE_INFO_FACT_TABLE > WHERE > > 29 = LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT = > > '2018-10-11' AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt > Cubes: > > CUBE[name=st_device_info_cube_v140] 查询结果为： 2018-10-11，1119847 > 查询结果与第一个left > > join 查询结果不对应。请问是什么原因？ 这个问题需要怎么修复呢？ >
