> On Nov. 17, 2015, 5:27 p.m., Amareshwari Sriramadasu wrote: > > lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java, > > line 441 > > <https://reviews.apache.org/r/39895/diff/4/?file=1120739#file1120739line441> > > > > Can you share cube query and expected union query in a comment, cannot > > make much from looking at testcase. > > Rajat Khandelwal wrote: > Signature should be intuitive to understand: > > ``` > public static String getExpectedUnionQuery(String cubeName, List<String> > storages, StoragePartitionProvider provider, > String outerSelectPart, String outerWhere, String outerPostWhere, > String innerQuerySelectPart, > String innerWhere, String innerPostWhere) { > ``` > > Wherever used, the query is supposed to be like: `outerselectpart from > (innerselectpart from table1 where partitions and innerWhere innerPostWhere > union all innerselectpart from table2 where partitions and innerWhere > innerPostWhere) cubeName where outerWhere outerPostWhere` > > postwhere is things like `group by`, `order by`, `having`, `limit` etc. > > Mentioning some complex queries here: > > ``` > SELECT ( testcube . alias0 ) as `City ID` , (sum(( testcube . alias1 )) + > max(( testcube . alias2 ))), case when (sum(( testcube . alias1 )) = 0 ) > then 0 else (sum(( testcube . alias3 )) / sum(( testcube . alias1 ))) end > as `Third measure` FROM (SELECT ( testcube . cityid ) as `alias0` , sum(( > testcube . msr2 )) as `alias1` , max(( testcube . msr3 )) as `alias2` , sum( > case when (( testcube . cityid ) = 'x' ) then ( testcube . msr21 ) else ( > testcube . msr22 ) end ) as `alias3` FROM TestQueryRewrite.c1_testfact > testcube WHERE (((((((((((((((((((((((((((((((((((((((((((((((((((((((( > testcube . dt ) = '2015-09-17-19' ) or (( testcube . dt ) = '2015-09-17-20' > )) or (( testcube . dt ) = '2015-09-17-21' )) or (( testcube . dt ) = > '2015-09-17-22' )) or (( testcube . dt ) = '2015-09-17-23' )) or (( testcube > . dt ) = '2015-09-18' )) or (( testcube . dt ) = '2015-09-19' )) or (( > testcube . dt ) = '2015-09-20' )) or (( testcube . dt ) = '2015-09-21' )) > or (( testcube . dt ) = '2015-09-22' )) or (( testcube . dt ) = '2015-09-23' )) or (( testcube . dt ) = '2015-09-24' )) or (( testcube . dt ) = '2015-09-25' )) or (( testcube . dt ) = '2015-09-26' )) or (( testcube . dt ) = '2015-09-27' )) or (( testcube . dt ) = '2015-09-28' )) or (( testcube . dt ) = '2015-09-29' )) or (( testcube . dt ) = '2015-09-30' )) or (( testcube . dt ) = '2015-11-01' )) or (( testcube . dt ) = '2015-11-02' )) or (( testcube . dt ) = '2015-11-03' )) or (( testcube . dt ) = '2015-11-04' )) or (( testcube . dt ) = '2015-11-05' )) or (( testcube . dt ) = '2015-11-06' )) or (( testcube . dt ) = '2015-11-07' )) or (( testcube . dt ) = '2015-11-08' )) or (( testcube . dt ) = '2015-11-09' )) or (( testcube . dt ) = '2015-11-10' )) or (( testcube . dt ) = '2015-11-11' )) or (( testcube . dt ) = '2015-11-12' )) or (( testcube . dt ) = '2015-11-13' )) or (( testcube . dt ) = '2015-11-14' )) or (( testcube . dt ) = '2015-11-15' )) or (( testcube . dt ) = '201 5-11-16' )) or (( testcube . dt ) = '2015-11-17-00' )) or (( testcube . dt ) = '2015-11-17-01' )) or (( testcube . dt ) = '2015-11-17-02' )) or (( testcube . dt ) = '2015-11-17-03' )) or (( testcube . dt ) = '2015-11-17-04' )) or (( testcube . dt ) = '2015-11-17-05' )) or (( testcube . dt ) = '2015-11-17-06' )) or (( testcube . dt ) = '2015-11-17-07' )) or (( testcube . dt ) = '2015-11-17-08' )) or (( testcube . dt ) = '2015-11-17-09' )) or (( testcube . dt ) = '2015-11-17-10' )) or (( testcube . dt ) = '2015-11-17-11' )) or (( testcube . dt ) = '2015-11-17-12' )) or (( testcube . dt ) = '2015-11-17-13' )) or (( testcube . dt ) = '2015-11-17-14' )) or (( testcube . dt ) = '2015-11-17-15' )) or (( testcube . dt ) = '2015-11-17-16' )) or (( testcube . dt ) = '2015-11-17-17' )) or (( testcube . dt ) = '2015-11-17-18' )))) GROUP BY ( testcube . cityid ) UNION ALL SELECT ( testcube . cityid ) as `alias0` , sum(( testcube . msr2 )) as `alias1` , max(( testcube . msr3 )) as `alias2` , sum( case when (( testcube . cityid ) = 'x' ) then ( testcube . msr21 ) else ( testcube . msr22 ) end ) as `alias3` FROM TestQueryRewrite.c2_testfact testcube WHERE (((( testcube . dt ) = '2015-10' ))) GROUP BY ( testcube . cityid )) testcube GROUP BY ( testcube . alias0 ) > ``` > > ``` > SELECT ( testcube . alias0 ) as `City ID` , max(( testcube . alias1 )) as > `Third measure` FROM (SELECT ( testcube . cityid ) as `alias0` , max(( > testcube . msr3 )) as `alias1` , sum(( testcube . msr2 )) as `alias2` , sum( > case when (( testcube . cityid ) = 'x' ) then ( testcube . msr21 ) else ( > testcube . msr22 ) end ) as `alias3` FROM TestQueryRewrite.c1_testfact > testcube WHERE (((((((((((((((((((((((((((((((((((((((((((((((((((((((( > testcube . dt ) = '2015-09-17-19' ) or (( testcube . dt ) = '2015-09-17-20' > )) or (( testcube . dt ) = '2015-09-17-21' )) or (( testcube . dt ) = > '2015-09-17-22' )) or (( testcube . dt ) = '2015-09-17-23' )) or (( testcube > . dt ) = '2015-09-18' )) or (( testcube . dt ) = '2015-09-19' )) or (( > testcube . dt ) = '2015-09-20' )) or (( testcube . dt ) = '2015-09-21' )) > or (( testcube . dt ) = '2015-09-22' )) or (( testcube . dt ) = > '2015-09-23' )) or (( testcube . dt ) = '2015-09-24' )) or (( testcube . dt > ) = '2015-09-25' )) or (( te stcube . dt ) = '2015-09-26' )) or (( testcube . dt ) = '2015-09-27' )) or (( testcube . dt ) = '2015-09-28' )) or (( testcube . dt ) = '2015-09-29' )) or (( testcube . dt ) = '2015-09-30' )) or (( testcube . dt ) = '2015-11-01' )) or (( testcube . dt ) = '2015-11-02' )) or (( testcube . dt ) = '2015-11-03' )) or (( testcube . dt ) = '2015-11-04' )) or (( testcube . dt ) = '2015-11-05' )) or (( testcube . dt ) = '2015-11-06' )) or (( testcube . dt ) = '2015-11-07' )) or (( testcube . dt ) = '2015-11-08' )) or (( testcube . dt ) = '2015-11-09' )) or (( testcube . dt ) = '2015-11-10' )) or (( testcube . dt ) = '2015-11-11' )) or (( testcube . dt ) = '2015-11-12' )) or (( testcube . dt ) = '2015-11-13' )) or (( testcube . dt ) = '2015-11-14' )) or (( testcube . dt ) = '2015-11-15' )) or (( testcube . dt ) = '2015-11-16' )) or (( testcube . dt ) = '2015-11-17-00' )) or (( testcube . dt ) = '2015-11-17-01' )) or (( testcube . dt ) = '2015-11-17-02' )) or (( testcu be . dt ) = '2015-11-17-03' )) or (( testcube . dt ) = '2015-11-17-04' )) or (( testcube . dt ) = '2015-11-17-05' )) or (( testcube . dt ) = '2015-11-17-06' )) or (( testcube . dt ) = '2015-11-17-07' )) or (( testcube . dt ) = '2015-11-17-08' )) or (( testcube . dt ) = '2015-11-17-09' )) or (( testcube . dt ) = '2015-11-17-10' )) or (( testcube . dt ) = '2015-11-17-11' )) or (( testcube . dt ) = '2015-11-17-12' )) or (( testcube . dt ) = '2015-11-17-13' )) or (( testcube . dt ) = '2015-11-17-14' )) or (( testcube . dt ) = '2015-11-17-15' )) or (( testcube . dt ) = '2015-11-17-16' )) or (( testcube . dt ) = '2015-11-17-17' )) or (( testcube . dt ) = '2015-11-17-18' )))) GROUP BY ( testcube . cityid ) UNION ALL SELECT ( testcube . cityid ) as `alias0` , max(( testcube . msr3 )) as `alias1` , sum(( testcube . msr2 )) as `alias2` , sum( case when (( testcube . cityid ) = 'x' ) then ( testcube . msr21 ) else ( testcube . msr22 ) end ) as `alias3` FROM TestQueryRewrite. c2_testfact testcube WHERE (((( testcube . dt ) = '2015-10' ))) GROUP BY ( testcube . cityid )) testcube GROUP BY ( testcube . alias0 ) HAVING ( case when (sum(( testcube . alias2 )) = 0 ) then 0 else (sum(( testcube . alias3 )) / sum(( testcube . alias2 ))) end > 10 ) > ``` > > ``` > SELECT ( testcube . alias0 ), ( testcube . alias1 ) as `City ID` , max(( > testcube . alias2 )) as `Measure 3` , count(( testcube . alias3 )), sum(( > testcube . alias4 )) as `Measure 2` FROM (SELECT ( testcube . zipcode ) as > `alias0` , ( testcube . cityid ) as `alias1` , max(( testcube . msr3 )) as > `alias2` , count(( testcube . msr4 )) as `alias3` , sum(( testcube . msr2 )) > as `alias4` FROM TestQueryRewrite.c1_testfact testcube WHERE > (((((((((((((((((((((((((((((((((((((((((((((((((((((((( testcube . dt ) = > '2015-09-17-19' ) or (( testcube . dt ) = '2015-09-17-20' )) or (( testcube > . dt ) = '2015-09-17-21' )) or (( testcube . dt ) = '2015-09-17-22' )) or > (( testcube . dt ) = '2015-09-17-23' )) or (( testcube . dt ) = > '2015-09-18' )) or (( testcube . dt ) = '2015-09-19' )) or (( testcube . dt > ) = '2015-09-20' )) or (( testcube . dt ) = '2015-09-21' )) or (( testcube > . dt ) = '2015-09-22' )) or (( testcube . dt ) = '2015-09-23' )) or (( > testcube . dt ) = '2015-09-24' ) ) or (( testcube . dt ) = '2015-09-25' )) or (( testcube . dt ) = '2015-09-26' )) or (( testcube . dt ) = '2015-09-27' )) or (( testcube . dt ) = '2015-09-28' )) or (( testcube . dt ) = '2015-09-29' )) or (( testcube . dt ) = '2015-09-30' )) or (( testcube . dt ) = '2015-11-01' )) or (( testcube . dt ) = '2015-11-02' )) or (( testcube . dt ) = '2015-11-03' )) or (( testcube . dt ) = '2015-11-04' )) or (( testcube . dt ) = '2015-11-05' )) or (( testcube . dt ) = '2015-11-06' )) or (( testcube . dt ) = '2015-11-07' )) or (( testcube . dt ) = '2015-11-08' )) or (( testcube . dt ) = '2015-11-09' )) or (( testcube . dt ) = '2015-11-10' )) or (( testcube . dt ) = '2015-11-11' )) or (( testcube . dt ) = '2015-11-12' )) or (( testcube . dt ) = '2015-11-13' )) or (( testcube . dt ) = '2015-11-14' )) or (( testcube . dt ) = '2015-11-15' )) or (( testcube . dt ) = '2015-11-16' )) or (( testcube . dt ) = '2015-11-17-00' )) or (( testcube . dt ) = '2015-11-17-01' )) or (( testcube . dt ) = '2015-11-17-02' )) or (( testcube . dt ) = '2015-11-17-03' )) or (( testcube . dt ) = '2015-11-17-04' )) or (( testcube . dt ) = '2015-11-17-05' )) or (( testcube . dt ) = '2015-11-17-06' )) or (( testcube . dt ) = '2015-11-17-07' )) or (( testcube . dt ) = '2015-11-17-08' )) or (( testcube . dt ) = '2015-11-17-09' )) or (( testcube . dt ) = '2015-11-17-10' )) or (( testcube . dt ) = '2015-11-17-11' )) or (( testcube . dt ) = '2015-11-17-12' )) or (( testcube . dt ) = '2015-11-17-13' )) or (( testcube . dt ) = '2015-11-17-14' )) or (( testcube . dt ) = '2015-11-17-15' )) or (( testcube . dt ) = '2015-11-17-16' )) or (( testcube . dt ) = '2015-11-17-17' )) or (( testcube . dt ) = '2015-11-17-18' )))) GROUP BY ( testcube . zipcode ), ( testcube . cityid ) ORDER BY testcube . cityid desc LIMIT 5 UNION ALL SELECT ( testcube . zipcode ) as `alias0` , ( testcube . cityid ) as `alias1` , max(( testcube . msr3 )) as `alias2` , count(( testcube . msr4 )) as `alias3` , sum(( testcube . msr2 )) as `alias4` FROM TestQueryRewrite.c2_testfact testcube WHERE (((( testcube . dt ) = '2015-10' ))) GROUP BY ( testcube . zipcode ), ( testcube . cityid ) ORDER BY testcube . cityid desc LIMIT 5) testcube GROUP BY ( testcube . alias0 ), ( testcube . alias1 ) HAVING (count(( testcube . alias3 )) > 10 ) ORDER BY testcube . alias1 desc LIMIT 5 > ``` > > Amareshwari Sriramadasu wrote: > For query number-3 put above, i think we should not be doing ORDER BY and > LIMIT on inner queries. If they are done inside and outer having can filter > all those rows.
Removing limit and order by too from inner queries. - Rajat ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/39895/#review106822 ----------------------------------------------------------- On Nov. 18, 2015, 1 p.m., Rajat Khandelwal wrote: > > ----------------------------------------------------------- > This is an automatically generated e-mail. To reply, visit: > https://reviews.apache.org/r/39895/ > ----------------------------------------------------------- > > (Updated Nov. 18, 2015, 1 p.m.) > > > Review request for lens. > > > Bugs: LENS-851 > https://issues.apache.org/jira/browse/LENS-851 > > > Repository: lens > > > Description > ------- > > Will do further cleanup and refactoring. Putting this up for an early review. > > > Diffs > ----- > > lens-api/src/main/resources/lens-errors.conf > a582dc266ae98c240489d2fe78fde0887601545e > lens-cube/src/main/java/org/apache/lens/cube/error/LensCubeErrorCode.java > 73a584f507444ceceb265e8439fab45d0de7a1b3 > lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryConfUtil.java > 87972c87126210a10fcb55c136409fb18deca8c8 > lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryContext.java > 450d17202d7a06214ecd997d4bab68fe8351ab3e > lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java > 16e1aa3f3288426e1bc9b823ac8c3d805d54c42f > lens-cube/src/main/java/org/apache/lens/cube/parse/SimpleHQLContext.java > 067a37a29b536a9ff03aae12a396e7bb99f9ba0f > > lens-cube/src/main/java/org/apache/lens/cube/parse/SingleFactMultiStorageHQLContext.java > 15a98dd941bd7b4cb68369fb553d6722956430d6 > lens-cube/src/main/java/org/apache/lens/cube/parse/UnionHQLContext.java > 90058260b36879bbc149c3c33198d3daf1179b36 > lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java > 826f6b63cb4b2c3c3be7846bfbd8d0744d1069ad > > lens-cube/src/test/java/org/apache/lens/cube/parse/TestAggregateResolver.java > 8da52635c72dfc5f669002f1c8dd1da1b9712b40 > lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java > 4acd063f4746044afa0e0345faf81adbdda18e0c > > lens-cube/src/test/java/org/apache/lens/cube/parse/TestDenormalizationResolver.java > 64b1ac66566239f3a16e55fb5174467940fa0818 > > lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java > 9dcced06b533461c0f2e087709546fe7b66e89c2 > lens-cube/src/test/java/org/apache/lens/cube/parse/TestQuery.java > 8e35ea9bd9cd727cc25ff011a03d6a38a6f0229f > > Diff: https://reviews.apache.org/r/39895/diff/ > > > Testing > ------- > > [INFO] > ------------------------------------------------------------------------ > [INFO] Reactor Summary: > [INFO] > [INFO] Lens Checkstyle Rules ............................. SUCCESS [2.062s] > [INFO] Lens .............................................. SUCCESS [3.128s] > [INFO] Lens API .......................................... SUCCESS [29.618s] > [INFO] Lens API for server and extensions ................ SUCCESS [23.581s] > [INFO] Lens Cube ......................................... SUCCESS [5:44.708s] > [INFO] Lens DB storage ................................... SUCCESS [20.474s] > [INFO] Lens Query Library ................................ SUCCESS [16.973s] > [INFO] Lens Hive Driver .................................. SUCCESS [2:53.401s] > [INFO] Lens Driver for JDBC .............................. SUCCESS [37.230s] > [INFO] Lens Elastic Search Driver ........................ SUCCESS [17.677s] > [INFO] Lens Server ....................................... SUCCESS [8:16.106s] > [INFO] Lens client ....................................... SUCCESS [38.470s] > [INFO] Lens CLI .......................................... SUCCESS [51.862s] > [INFO] Lens Examples ..................................... SUCCESS [9.145s] > [INFO] Lens Ship Jars to Distributed Cache ............... SUCCESS [1.612s] > [INFO] Lens Distribution ................................. SUCCESS [9.499s] > [INFO] Lens ML Lib ....................................... SUCCESS [1:20.974s] > [INFO] Lens ML Ext Distribution .......................... SUCCESS [2.216s] > [INFO] Lens Regression ................................... SUCCESS [14.834s] > [INFO] Lens UI ........................................... SUCCESS [27.308s] > [INFO] > ------------------------------------------------------------------------ > [INFO] BUILD SUCCESS > [INFO] > ------------------------------------------------------------------------ > [INFO] Total time: 23:21.839s > [INFO] Finished at: Tue Nov 17 14:25:05 UTC 2015 > [INFO] Final Memory: 202M/2273M > [INFO] > ------------------------------------------------------------------------ > > > Thanks, > > Rajat Khandelwal > >
