You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@lens.apache.org by "Sushil Mohanty (JIRA)" <ji...@apache.org> on 2017/03/30 09:51:41 UTC
[jira] [Created] (LENS-1406) Rewritten union query is wrong when
group by and order by columns are promoted
Sushil Mohanty created LENS-1406:
------------------------------------
Summary: Rewritten union query is wrong when group by and order by columns are promoted
Key: LENS-1406
URL: https://issues.apache.org/jira/browse/LENS-1406
Project: Apache Lens
Issue Type: Bug
Components: cube
Reporter: Sushil Mohanty
Test case : TestUnionQueries#testNonAggregateOverAggregateFunction
Input query :
{CODE}
select cityid as `City ID`, msr3 as `Measure 3`, round(SUM(msr2)) as `Measure 2` from testCube where time_range_in(d_time,'2017-01-30-12','2017-03-30-12') group by zipcode having msr4 > 10 order by cityid desc, stateid asc, zipcode asc limit 5
{CODE}
Rewritten Query :
{CODE}
SELECT (testcube.alias0) AS `City ID`,
max((testcube.alias1)) AS `Measure 3`,
round(sum((testcube.alias2))) AS `Measure 2`
FROM
(SELECT (testcube.cityid) AS `alias0`,
max((testcube.msr3)) AS `alias1`,
sum((testcube.msr2)) AS `alias2`,
(testcube.zipcode) AS `alias3`,
count((testcube.msr4)) AS `alias4`,
(testcube.stateid) AS `alias5`
FROM TestQueryRewrite.c1_testfact testcube
WHERE ((((testcube.dt) = '2017-01-30-12')
OR ((testcube.dt) = '2017-01-30-13')
OR ((testcube.dt) = '2017-01-30-14')
OR ((testcube.dt) = '2017-01-30-15')
OR ((testcube.dt) = '2017-01-30-16')
OR ((testcube.dt) = '2017-01-30-17')
OR ((testcube.dt) = '2017-01-30-18')
OR ((testcube.dt) = '2017-01-30-19')
OR ((testcube.dt) = '2017-01-30-20')
OR ((testcube.dt) = '2017-01-30-21')
OR ((testcube.dt) = '2017-01-30-22')
OR ((testcube.dt) = '2017-01-30-23')
OR ((testcube.dt) = '2017-01-31')
OR ((testcube.dt) = '2017-03-01')
OR ((testcube.dt) = '2017-03-02')
OR ((testcube.dt) = '2017-03-03')
OR ((testcube.dt) = '2017-03-04')
OR ((testcube.dt) = '2017-03-05')
OR ((testcube.dt) = '2017-03-06')
OR ((testcube.dt) = '2017-03-07')
OR ((testcube.dt) = '2017-03-08')
OR ((testcube.dt) = '2017-03-09')
OR ((testcube.dt) = '2017-03-10')
OR ((testcube.dt) = '2017-03-11')
OR ((testcube.dt) = '2017-03-12')
OR ((testcube.dt) = '2017-03-13')
OR ((testcube.dt) = '2017-03-14')
OR ((testcube.dt) = '2017-03-15')
OR ((testcube.dt) = '2017-03-16')
OR ((testcube.dt) = '2017-03-17')
OR ((testcube.dt) = '2017-03-18')
OR ((testcube.dt) = '2017-03-19')
OR ((testcube.dt) = '2017-03-20')
OR ((testcube.dt) = '2017-03-21')
OR ((testcube.dt) = '2017-03-22')
OR ((testcube.dt) = '2017-03-23')
OR ((testcube.dt) = '2017-03-24')
OR ((testcube.dt) = '2017-03-25')
OR ((testcube.dt) = '2017-03-26')
OR ((testcube.dt) = '2017-03-27')
OR ((testcube.dt) = '2017-03-28')
OR ((testcube.dt) = '2017-03-29')
OR ((testcube.dt) = '2017-03-30-00')
OR ((testcube.dt) = '2017-03-30-01')
OR ((testcube.dt) = '2017-03-30-02')
OR ((testcube.dt) = '2017-03-30-03')
OR ((testcube.dt) = '2017-03-30-04')
OR ((testcube.dt) = '2017-03-30-05')
OR ((testcube.dt) = '2017-03-30-06')
OR ((testcube.dt) = '2017-03-30-07')
OR ((testcube.dt) = '2017-03-30-08')
OR ((testcube.dt) = '2017-03-30-09')
OR ((testcube.dt) = '2017-03-30-10')
OR ((testcube.dt) = '2017-03-30-11')))
GROUP BY (testcube.zipcode)
UNION ALL SELECT (testcube.cityid) AS `alias0`,
max((testcube.msr3)) AS `alias1`,
sum((testcube.msr2)) AS `alias2`,
(testcube.zipcode) AS `alias3`,
count((testcube.msr4)) AS `alias4`,
(testcube.stateid) AS `alias5`
FROM TestQueryRewrite.c2_testfact testcube
WHERE ((((testcube.dt) = '2017-02')))
GROUP BY (testcube.zipcode)) testcube
GROUP BY (testcube.alias3)
HAVING (count((testcube.alias4)) > 10)
ORDER BY testcube.alias0 DESC,
testcube.alias5 ASC,
testcube.alias3 ASC
LIMIT 5
{CODE}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)