Thanks for confirming kylin 2.1 does not have this problem. On Sun, Aug 20, 2017 at 9:12 PM, [email protected] <[email protected]> wrote:
> Thinks . > > This issue not found in kylin v2.1. > > > > [email protected] > > From: Li Yang > Date: 2017-08-20 14:31 > To: dev > Subject: Re: Subquery cann't work > Seems a bug to me. Please log a JIRA. > > On Thu, Aug 17, 2017 at 10:17 AM, [email protected] <[email protected]> > wrote: > > > Hi, > > > > There seems to be a problem with sql parsing engine? please confirm > > > > This Sql can work. > > > > SELECT T8_0.*, T8_0.Ieceb82a4498247aeb911ba59f82148f0 - T8_1. > > Ieceb82a4498247aeb911ba59f82148f0 AS D81e0286e9b9c404cb7cb04c0b92cd7c2, > > T8_2.Ieceb82a4498247aeb911ba59f82148f0 AS Dcc3b3deff3c043e8834b70b6f41f8 > > 009 > > FROM (SELECT KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID, > > SUM(KYLIN_SALES.PRICE) AS Ieceb82a4498247aeb911ba59f82148f0 > > FROM KYLIN_SALES > > INNER JOIN KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT > > WHERE PART_DT IN ('2012-01-01', '2012-01-02', '2012-01-03') > > GROUP BY KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID > > ) T8_0 > > JOIN KYLIN_CAL_DT ON T8_0.PART_DT = KYLIN_CAL_DT.CAL_DT > > LEFT JOIN (SELECT KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID, > > SUM(KYLIN_SALES.PRICE) AS Ieceb82a4498247aeb911ba59f82148f0 > > FROM KYLIN_SALES > > INNER JOIN KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT > > WHERE PART_DT IN ('2012-01-01', '2012-01-02', '2012-01-03') > > GROUP BY KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID > > ) T8_1 ON T8_1.PART_DT = KYLIN_CAL_DT.YEAR_BEG_DT > > AND T8_0.SELLER_ID = T8_1.SELLER_ID > > LEFT JOIN (SELECT KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID, > > SUM(KYLIN_SALES.PRICE) AS Ieceb82a4498247aeb911ba59f82148f0 > > FROM KYLIN_SALES > > INNER JOIN KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT > > WHERE PART_DT IN ('2012-01-01', '2012-01-02', '2012-01-03') > > GROUP BY KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID > > ) T8_2 ON T8_2.PART_DT = KYLIN_CAL_DT.MONTH_BEG_DT > > AND T8_0.SELLER_ID = T8_2.SELLER_ID > > > > > > but after add a aggregation condition (boldface) ,it run error. > > > > > > SELECT PART_DT, SUM(D81e0286e9b9c404cb7cb04c0b92cd7c2) > > FROM (SELECT T8_0.*, T8_0.Ieceb82a4498247aeb911ba59f82148f0 - T8_1. > > Ieceb82a4498247aeb911ba59f82148f0 AS D81e0286e9b9c404cb7cb04c0b92cd7c2, > > T8_2.Ieceb82a4498247aeb911ba59f82148f0 AS Dcc3b3deff3c043e8834b70b6f41f8 > > 009 > > FROM (SELECT KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID, > > SUM(KYLIN_SALES.PRICE) AS Ieceb82a4498247aeb911ba59f82148f0 > > FROM KYLIN_SALES > > INNER JOIN KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT > > WHERE PART_DT IN ('2012-01-01', '2012-01-02', '2012-01-03') > > GROUP BY KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID > > ) T8_0 > > JOIN KYLIN_CAL_DT ON T8_0.PART_DT = KYLIN_CAL_DT.CAL_DT > > LEFT JOIN (SELECT KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID, > > SUM(KYLIN_SALES.PRICE) AS Ieceb82a4498247aeb911ba59f82148f0 > > FROM KYLIN_SALES > > INNER JOIN KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT > > WHERE PART_DT IN ('2012-01-01', '2012-01-02', '2012-01-03') > > GROUP BY KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID > > ) T8_1 ON T8_1.PART_DT = KYLIN_CAL_DT.YEAR_BEG_DT > > AND T8_0.SELLER_ID = T8_1.SELLER_ID > > LEFT JOIN (SELECT KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID, > > SUM(KYLIN_SALES.PRICE) AS Ieceb82a4498247aeb911ba59f82148f0 > > FROM KYLIN_SALES > > INNER JOIN KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT > > WHERE PART_DT IN ('2012-01-01', '2012-01-02', '2012-01-03') > > GROUP BY KYLIN_SALES.PART_DT, KYLIN_SALES.SELLER_ID > > ) T8_2 ON T8_2.PART_DT = KYLIN_CAL_DT.MONTH_BEG_DT > > AND T8_0.SELLER_ID = T8_2.SELLER_ID > > ) > > GROUP BY PART_DT > > > > > > error info: > > > > Error while executing SQL "SELECT PART_DT, SUM( > > D81e0286e9b9c404cb7cb04c0b92cd7c2) FROM (SELECT T8_0.*, T8_0. > > Ieceb82a4498247aeb911ba59f82148f0 - T8_1.Ieceb82a4498247aeb911ba59f8214 > 8f0 > > AS D81e0286e9b9c404cb7cb04c0b92cd7c2, T8_2. > Ieceb82a4498247aeb911ba59f82148f0 > > AS Dcc3b3deff3c043e8834b70b6f41f8009 FROM (SELECT KYLIN_SALES.PART_DT, > > KYLIN_SALES.SELLER_ID, SUM(KYLIN_SALES.PRICE) AS > > Ieceb82a4498247aeb911ba59f82148f0 FROM KYLIN_SALES INNER JOIN > > KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT WHERE PART_DT > IN > > ('2012-01-01', '2012-01-02', '2012-01-03') GROUP BY KYLIN_SALES.PART_DT, > > KYLIN_SALES.SELLER_ID ) T8_0 JOIN KYLIN_CAL_DT ON T8_0.PART_DT = > > KYLIN_CAL_DT.CAL_DT LEFT JOIN (SELECT KYLIN_SALES.PART_DT, > > KYLIN_SALES.SELLER_ID, SUM(KYLIN_SALES.PRICE) AS > > Ieceb82a4498247aeb911ba59f82148f0 FROM KYLIN_SALES INNER JOIN > > KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT WHERE PART_DT > IN > > ('2012-01-01', '2012-01-02', '2012-01-03') GROUP BY KYLIN_SALES.PART_DT, > > KYLIN_SALES.SELLER_ID ) T8_1 ON T8_1.PART_DT = KYLIN_CAL_DT.YEAR_BEG_DT > AND > > T8_0.SELLER_ID = T8_1.SELLER_ID LEFT JOIN (SELECT KYLIN_SALES.PART_DT, > > KYLIN_SALES.SELLER_ID, SUM(KYLIN_SALES.PRICE) AS > > Ieceb82a4498247aeb911ba59f82148f0 FROM KYLIN_SALES INNER JOIN > > KYLIN_CAL_DT ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT WHERE PART_DT > IN > > ('2012-01-01', '2012-01-02', '2012-01-03') GROUP BY KYLIN_SALES.PART_DT, > > KYLIN_SALES.SELLER_ID ) T8_2 ON T8_2.PART_DT = KYLIN_CAL_DT.MONTH_BEG_DT > > AND T8_0.SELLER_ID = T8_2.SELLER_ID ) GROUP BY PART_DT LIMIT 50000": > Can't > > find any realization. Please confirm with providers. SQL digest: fact > table > > DEFAULT.KYLIN_CAL_DT,group by [DEFAULT.KYLIN_SALES.PART_DT],filter on > > [],with aggregates[FunctionDesc [expression=SUM, parameter=-($2, $9), > > returnType=null]]. > > > > > > > > > > > > > > > > [email protected] > > >
