Thanks for confirming kylin 2.1 does not have this problem.
On Sun, Aug 20, 2017 at 9:12 PM, apache_...@163.com <apache_...@163.com>
wrote:
> Thinks .
>
> This issue not found in kylin v2.1.
>
>
>
> apache_...@163.com
>
> 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, apache_...@163.com <apache_...@163.com>
> 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]].
> >
> >
> >
> >
> >
> >
> >
> > apache_...@163.com
> >
>
