Repository: lens Updated Branches: refs/heads/master ccf71332f -> 6c4dba51b
LENS-850: Queries with column name alias like "year" fails on some DBs Project: http://git-wip-us.apache.org/repos/asf/lens/repo Commit: http://git-wip-us.apache.org/repos/asf/lens/commit/6c4dba51 Tree: http://git-wip-us.apache.org/repos/asf/lens/tree/6c4dba51 Diff: http://git-wip-us.apache.org/repos/asf/lens/diff/6c4dba51 Branch: refs/heads/master Commit: 6c4dba51bfebba2bd019508d2e44850ef964d150 Parents: ccf7133 Author: Puneet Gupta <[email protected]> Authored: Tue Oct 27 20:39:49 2015 +0530 Committer: Rajat Khandelwal <[email protected]> Committed: Tue Oct 27 20:39:49 2015 +0530 ---------------------------------------------------------------------- .../org/apache/lens/cube/parse/HQLParser.java | 6 +- .../lens/cube/parse/TestBaseCubeQueries.java | 97 ++++++++++---------- .../lens/cube/parse/TestCubeRewriter.java | 28 +++--- .../lens/cube/parse/TestExpressionResolver.java | 12 +-- .../apache/lens/cube/parse/TestHQLParser.java | 26 +++++- .../lens/cube/parse/TestJoinResolver.java | 33 ++++--- .../driver/jdbc/TestColumnarSQLRewriter.java | 12 +-- .../apache/lens/driver/jdbc/TestJDBCFinal.java | 12 +-- 8 files changed, 120 insertions(+), 106 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java index 1964e32..ea9badd 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java @@ -345,9 +345,9 @@ public final class HQLParser { buf.append(" true "); } else if (KW_FALSE == rootType) { buf.append(" false "); - } else if (Identifier == rootType && TOK_SELEXPR == ((ASTNode) root.getParent()).getToken().getType() - && hasSpaces(rootText)) { - // If column alias contains spaces, enclose in back quotes + } else if (Identifier == rootType && TOK_SELEXPR == ((ASTNode) root.getParent()).getToken().getType()) { + // back quote column alias in all cases. This is required since some alias values can match DB keywords + // (example : year as alias) and in such case queries can fail on certain DBs if the alias in not back quoted buf.append(" as `").append(rootText).append("` "); } else if (Identifier == rootType && TOK_FUNCTIONSTAR == ((ASTNode) root.getParent()).getToken().getType()) { // count(*) or count(someTab.*): Don't append space after the identifier http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBaseCubeQueries.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBaseCubeQueries.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBaseCubeQueries.java index e6c3be0..548bf5c 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBaseCubeQueries.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBaseCubeQueries.java @@ -167,10 +167,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { Assert.assertTrue(candidateFacts.contains("testfact2_base")); String hqlQuery = ctx.toHQL(); String expected1 = - getExpectedQuery(cubeName, "select sum(basecube.msr12) msr12 FROM ", null, + getExpectedQuery(cubeName, "select sum(basecube.msr12) as `msr12` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = - getExpectedQuery(cubeName, "select round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", null, + getExpectedQuery(cubeName, "select round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -185,10 +185,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { public void testMultiFactQueryWithSingleCommonDimension() throws Exception { String hqlQuery = rewrite("select dim1, roundedmsr2, msr12 from basecube" + " where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, sum(basecube.msr12) msr12 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, sum(basecube.msr12) as `msr12` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", null, + String expected2 = getExpectedQuery(cubeName, + "select basecube.dim1 as `dim1`, round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -208,10 +208,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { tConf.setBoolean(CubeQueryConfUtil.LIGHTEST_FACT_FIRST, true); String hqlQuery = rewrite("select dim1, roundedmsr2, msr12 from basecube" + " where " + TWO_DAYS_RANGE, tConf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, sum(basecube.msr12) msr12 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, sum(basecube.msr12) as `msr12` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", null, + String expected2 = getExpectedQuery(cubeName, + "select basecube.dim1 as `dim1`, round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -230,10 +230,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { // columns in select interchanged String hqlQuery = rewrite("select dim1, msr12, roundedmsr2 from basecube" + " where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, sum(basecube.msr12) msr12 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, sum(basecube.msr12) as `msr12` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", null, + String expected2 = getExpectedQuery(cubeName, + "select basecube.dim1 as `dim1`, round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -253,14 +253,14 @@ public class TestBaseCubeQueries extends TestQueryRewrite { String hqlQuery = rewrite("select dim1, msr12, roundedmsr2, msr13, msr3 from basecube where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, sum(basecube.msr12) msr12 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, sum(basecube.msr12) as `msr12` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = - getExpectedQuery(cubeName, - "select basecube.dim1 dim1, round(sum(basecube.msr2)/1000) roundedmsr2, max(basecube.msr3) msr3 FROM ", null, - " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + String expected2 = getExpectedQuery( + cubeName, + "select basecube.dim1 as `dim1`, round(sum(basecube.msr2)/1000) as `roundedmsr2`, max(basecube.msr3) as `msr3` " + + "FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); String expected3 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, max(basecube.msr13) msr13 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, max(basecube.msr13) as `msr13` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "c1_testfact3_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -292,13 +292,13 @@ public class TestBaseCubeQueries extends TestQueryRewrite { public void testMultiFactQueryWithTwoCommonDimensions() throws Exception { // query two dim attributes String hqlQuery = rewrite("select dim1, dim11, msr12, roundedmsr2 from basecube where " + TWO_DAYS_RANGE, conf); - String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, basecube.dim11 dim11, sum(basecube.msr12) msr12 FROM ", - null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = - getExpectedQuery(cubeName, - "select basecube.dim1 dim1, basecube.dim11 dim11, round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", null, - " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + String expected1 = getExpectedQuery(cubeName, + "select basecube.dim1 as `dim1`, basecube.dim11 as `dim11`, sum(basecube.msr12) as `msr12` FROM ", null, + " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + String expected2 = getExpectedQuery( + cubeName, + "select basecube.dim1 as `dim1`, basecube.dim11 as `dim11`, round(sum(basecube.msr2)/1000) as `roundedmsr2` " + + "FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( @@ -317,10 +317,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { // no aggregates in the query String hqlQuery = rewrite("select dim1, msr11, roundedmsr2 from basecube" + " where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, basecube.msr11 msr11 FROM ", null, null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, basecube.msr11 as `msr11` FROM ", null, null, getWhereForHourly2days(cubeName, "C1_testfact2_raw_base")); - String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, round(basecube.msr2/1000) roundedmsr2 FROM ", null, null, + String expected2 = getExpectedQuery(cubeName, + "select basecube.dim1 as `dim1`, round(basecube.msr2/1000) as `roundedmsr2` FROM ", null, null, getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -339,10 +339,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { String hqlQuery = rewrite("select dim1 d1, msr12 `my msr12`, roundedmsr2 m2 from basecube" + " where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 d1, sum(basecube.msr12) expr2 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `d1`, sum(basecube.msr12) as `expr2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 d1, round(sum(basecube.msr2)/1000) m2 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `d1`, round(sum(basecube.msr2)/1000) as `m2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -359,10 +359,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { // query with non default aggregate String hqlQuery = rewrite("select dim1, avg(msr12), avg(msr2) from basecube" + " where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, avg(basecube.msr12) msr12 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, avg(basecube.msr12) as `msr12` FROM ", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact2_raw_base")); String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 dim1, avg(basecube.msr2)) msr2 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, avg(basecube.msr2)) as `msr2` FROM ", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -379,15 +379,14 @@ public class TestBaseCubeQueries extends TestQueryRewrite { public void testMultiFactQueryWithJoins() throws Exception { // query with join String hqlQuery = rewrite("select testdim2.name, msr12, roundedmsr2 from basecube where " + TWO_DAYS_RANGE, conf); - String expected1 = - getExpectedQuery(cubeName, "select testdim2.name name, sum(basecube.msr12) msr12 FROM ", " JOIN " + getDbName() - + "c1_testdim2tbl testdim2 ON basecube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, + String expected1 = getExpectedQuery(cubeName, + "select testdim2.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " JOIN " + getDbName() + + "c1_testdim2tbl testdim2 ON basecube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, " group by testdim2.name", null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = - getExpectedQuery(cubeName, "select testdim2.name name, round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", " JOIN " - + getDbName() + "c1_testdim2tbl testdim2 ON basecube.dim2 = " - + " testdim2.id and (testdim2.dt = 'latest') ", null, " group by testdim2.name", null, - getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); + String expected2 = getExpectedQuery(cubeName, + "select testdim2.name as `name`, round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", " JOIN " + getDbName() + + "c1_testdim2tbl testdim2 ON basecube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, + " group by testdim2.name", null, getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( @@ -402,12 +401,12 @@ public class TestBaseCubeQueries extends TestQueryRewrite { public void testMultiFactQueryWithDenormColumn() throws Exception { // query with denorm variable String hqlQuery = rewrite("select dim2, msr13, roundedmsr2 from basecube" + " where " + TWO_DAYS_RANGE, conf); - String expected1 = - getExpectedQuery(cubeName, "select testdim2.id dim2, max(basecube.msr13) msr13 FROM ", " JOIN " + getDbName() - + "c1_testdim2tbl testdim2 ON basecube.dim12 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, - " group by testdim2.id", null, getWhereForHourly2days(cubeName, "C1_testFact3_RAW_BASE")); - String expected2 = - getExpectedQuery(cubeName, "select basecube.dim2 dim2, round(sum(basecube.msr2)/1000) roundedmsr2 FROM ", null, + String expected1 = getExpectedQuery(cubeName, "select testdim2.id as `dim2`, max(basecube.msr13) as `msr13` FROM ", + " JOIN " + getDbName() + "c1_testdim2tbl testdim2 ON basecube.dim12 = " + + " testdim2.id and (testdim2.dt = 'latest') ", null, " group by testdim2.id", null, + getWhereForHourly2days(cubeName, "C1_testFact3_RAW_BASE")); + String expected2 = getExpectedQuery(cubeName, + "select basecube.dim2 as `dim2`, round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, " group by basecube.dim2", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); @@ -428,14 +427,14 @@ public class TestBaseCubeQueries extends TestQueryRewrite { "select booleancut, round(sum(msr2)/1000), avg(msr13 + msr14) from basecube" + " where " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 != 'x' AND testdim2.id != 10 booleancut," - + " avg(basecube.msr13 + basecube.msr14) expr3 FROM ", " JOIN " + getDbName() + getExpectedQuery(cubeName, "select basecube.dim1 != 'x' AND testdim2.id != 10 as `booleancut`," + + " avg(basecube.msr13 + basecube.msr14) as `expr3` FROM ", " JOIN " + getDbName() + "c1_testdim2tbl testdim2 ON basecube.dim12 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, " group by basecube.dim1 != 'x' AND testdim2.id != 10", null, getWhereForHourly2days(cubeName, "C1_testfact3_raw_base")); String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 != 'x' AND basecube.dim2 != 10 booleancut," - + " round(sum(basecube.msr2)/1000) msr2 FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 != 'x' AND basecube.dim2 != 10 as `booleancut`," + + " round(sum(basecube.msr2)/1000) as `msr2` FROM ", null, " group by basecube.dim1 != 'x' AND basecube.dim2 != 10", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java index f5ff49a..efe0980 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java @@ -779,10 +779,10 @@ public class TestCubeRewriter extends TestQueryRewrite { expected = getExpectedQuery( cubeName, - "SELECT ( citydim.name ) g1 ," + "SELECT ( citydim.name ) as `g1` ," + " case when (( citydim.name ) == 'NULL' ) then 'NULL' when (( citydim.name ) == 'X' )" + " then 'X-NAME' when (( citydim.name ) == 'Y' ) then 'Y-NAME'" - + " else 'DEFAULT' end g2 , ( statedim.name ) g3 , ( statedim.id ) g4 ," + + " else 'DEFAULT' end as `g2` , ( statedim.name ) as `g3` , ( statedim.id ) as `g4` ," + " ((( zipdim.code ) != 1 ) and ((((( zipdim.f1 ) == \"xyz\" )" + " and (((( zipdim.f2 ) >= \"3\" ) and (( zipdim.f2 ) != \"NULL\" ))" + " and (( zipdim.f2 ) != \"uk\" ))) or (((( zipdim.f2 ) == \"adc\" )" @@ -791,7 +791,7 @@ public class TestCubeRewriter extends TestQueryRewrite { + " or ((((( zipdim.f1 ) == \"api\" )" + " or (( zipdim.f1 ) == \"uk\" )) or ((( zipdim.f1 ) == \"adc\" )" + " and (( zipdim.f1 ) != \"js\" )))" - + " and (( citydim.id ) == 12 )))) g5 , ((( zipdim.code ) == 1 )" + + " and (( citydim.id ) == 12 )))) as `g5` , ((( zipdim.code ) == 1 )" + " and ((((( zipdim.f1 ) == \"xyz\" ) and (((( zipdim.f2 ) >= \"3\" )" + " and (( zipdim.f2 ) != \"NULL\" ))" + " and (( zipdim.f2 ) != \"uk\" ))) or (((( zipdim.f2 ) == \"adc\" )" @@ -800,14 +800,14 @@ public class TestCubeRewriter extends TestQueryRewrite { + " or ((((( zipdim.f1 ) == \"api\" )" + " or (( zipdim.f1 ) == \"uk\" )) or ((( zipdim.f1 ) == \"adc\" )" + " and (( zipdim.f1 ) != \"js\" )))" - + " and (( citydim.id ) == 12 )))) g6 , ( zipdim.f1 ) g7 ," - + " format_number(sum(( testcube.msr1 )), \"##################.###\" ) a1 ," - + " format_number(sum(( testcube.msr2 )), \"##################.###\" ) a2 ," - + " format_number(sum(( testcube.msr3 )), \"##################.###\" ) a3, " - + " format_number((sum(( testcube.msr1 )) + sum(( testcube.msr2 ))), \"##################.###\" ) a4 ," - + " format_number((sum(( testcube.msr1 )) + sum(( testcube.msr3 ))), \"##################.###\" ) a5 ," + + " and (( citydim.id ) == 12 )))) as `g6` , ( zipdim.f1 ) as `g7` ," + + " format_number(sum(( testcube.msr1 )), \"##################.###\" ) as `a1` ," + + " format_number(sum(( testcube.msr2 )), \"##################.###\" ) as `a2` ," + + " format_number(sum(( testcube.msr3 )), \"##################.###\" ) as `a3`, " + + " format_number((sum(( testcube.msr1 )) + sum(( testcube.msr2 ))), \"##################.###\" ) as `a4` ," + + " format_number((sum(( testcube.msr1 )) + sum(( testcube.msr3 ))), \"##################.###\" ) as `a5` ," + " format_number((sum(( testcube.msr1 )) - (sum(( testcube.msr2 )) + sum(( testcube.msr3 )))), " - + " \"##################.###\" ) a6" + + " \"##################.###\" ) as `a6`" + " FROM ", actualExpr, null, @@ -885,8 +885,8 @@ public class TestCubeRewriter extends TestQueryRewrite { + " on testCube.cityid = citydim.id where " + LAST_HOUR_TIME_RANGE; String expectedRewrittenQuery = "SELECT ( citydim . name ) as `Alias With Spaces` , sum(( testcube . msr2 )) " - + "testmeasure FROM TestQueryRewrite.c2_testfact testcube inner JOIN TestQueryRewrite.c2_citytable citydim ON " - + "(( testcube . cityid ) = ( citydim . id )) WHERE (((( testcube . dt ) = '" + + "as `TestMeasure` FROM TestQueryRewrite.c2_testfact testcube inner JOIN TestQueryRewrite.c2_citytable citydim " + + "ON (( testcube . cityid ) = ( citydim . id )) WHERE (((( testcube . dt ) = '" + CubeTestSetup.getDateUptoHours(LAST_HOUR) + "' ))) GROUP BY ( citydim . name )"; String actualRewrittenQuery = rewrite(inputQuery, getConfWithStorages("C2")); @@ -902,8 +902,8 @@ public class TestCubeRewriter extends TestQueryRewrite { + " on testCube.cityid = citydim.id where " + LAST_HOUR_TIME_RANGE; String expectedRewrittenQuery = "SELECT ( citydim . name ) as `Alias With Spaces` , sum(( testcube . msr2 )) " - + "testmeasure FROM TestQueryRewrite.c2_testfact testcube inner JOIN TestQueryRewrite.c2_citytable citydim ON " - + "(( testcube . cityid ) = ( citydim . id )) WHERE (((( testcube . dt ) = '" + + "as `TestMeasure` FROM TestQueryRewrite.c2_testfact testcube inner JOIN TestQueryRewrite.c2_citytable citydim " + + "ON (( testcube . cityid ) = ( citydim . id )) WHERE (((( testcube . dt ) = '" + CubeTestSetup.getDateUptoHours(LAST_HOUR) + "' ))) GROUP BY ( citydim . name )"; String actualRewrittenQuery = rewrite(inputQuery, getConfWithStorages("C2")); http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java index b2a15a0..1cb22eb 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java @@ -394,9 +394,9 @@ public class TestExpressionResolver extends TestQueryRewrite { + "c1_countrytable countrydim on" + " statedim.countryid = countrydim.id"; joinExpr = join2 + join3 + join1; String expected = - getExpectedQuery("citydim", "SELECT citydim.name cname, concat((citydim.name), \":\", (statedim.name )," - + " \":\",(countrydim.name), \":\" , ( zipdim . code )) caddr FROM ", joinExpr, null, null, "c1_citytable", - true); + getExpectedQuery("citydim", "SELECT citydim.name as `cname`, concat((citydim.name), \":\", (statedim.name )," + + " \":\",(countrydim.name), \":\" , ( zipdim . code )) as `caddr` FROM ", joinExpr, null, null, + "c1_citytable", true); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -438,9 +438,9 @@ public class TestExpressionResolver extends TestQueryRewrite { + ""; String expected = - getExpectedQuery("ct", "SELECT ct.name cname, concat((ct.name), \":\", (statedim.name )," - + " \":\",(countrydim.name), \":\" , ( zipdim . code )) caddr FROM ", joinExpr, null, null, "c1_citytable", - true); + getExpectedQuery("ct", "SELECT ct.name as `cname`, concat((ct.name), \":\", (statedim.name )," + + " \":\",(countrydim.name), \":\" , ( zipdim . code )) as `caddr` FROM ", joinExpr, null, null, + "c1_citytable", true); TestCubeRewriter.compareQueries(hqlQuery, expected); } http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java index b0b2e88..4afd403 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestHQLParser.java @@ -60,7 +60,7 @@ public class TestHQLParser { ASTNode select = HQLParser.findNodeByPath(tree, TOK_INSERT, TOK_SELECT); String selectStr = HQLParser.getString(select).trim(); - String expectedSelect = "'abc' col1 , 'DEF' col2"; + String expectedSelect = "'abc' as `col1` , 'DEF' as `col2`"; Assert.assertEquals(expectedSelect, selectStr); ASTNode where = HQLParser.findNodeByPath(tree, TOK_INSERT, TOK_WHERE); @@ -80,7 +80,7 @@ public class TestHQLParser { System.out.println("reconstructed clause "); System.out.println(selectStr); Assert.assertEquals("case ((( col1 * 100 ) / 200 ) + 5 ) " - + "when 'ABC' then 'def' when 'EFG' then 'hij' " + "else 'XyZ' end complexcasestatement", + + "when 'ABC' then 'def' when 'EFG' then 'hij' " + "else 'XyZ' end as `ComplexCaseStatement`", selectStr.trim()); String q2 = "SELECT " + "CASE WHEN col1 = 'abc' then 'def' " + "when col1 = 'ghi' then 'jkl' " @@ -92,7 +92,7 @@ public class TestHQLParser { System.out.println("reconstructed clause 2"); System.out.println(selectStr); Assert.assertEquals("case when ( col1 = 'abc' ) then 'def' " + "when ( col1 = 'ghi' ) then 'jkl' " - + "else 'none' end complex_case_statement_2", selectStr.trim()); + + "else 'none' end as `Complex_Case_Statement_2`", selectStr.trim()); String q3 = "SELECT " + "CASE (col1 * 100)/200 + 5 " + "WHEN 'ABC' THEN 'def' " + "WHEN 'EFG' THEN 'hij' " + "END AS ComplexCaseStatement FROM FOO"; @@ -103,7 +103,7 @@ public class TestHQLParser { System.out.println("reconstructed clause "); System.out.println(selectStr); Assert.assertEquals("case ((( col1 * 100 ) / 200 ) + 5 ) " + "when 'ABC' then 'def' when 'EFG' " - + "then 'hij' end complexcasestatement", selectStr.trim()); + + "then 'hij' end as `ComplexCaseStatement`", selectStr.trim()); String q4 = "SELECT " + "CASE WHEN col1 = 'abc' then 'def' " + "when col1 = 'ghi' then 'jkl' " + "END AS Complex_Case_Statement_2" + " from FOO"; @@ -114,7 +114,7 @@ public class TestHQLParser { System.out.println("reconstructed clause 2"); System.out.println(selectStr); Assert.assertEquals("case when ( col1 = 'abc' ) then " + "'def' when ( col1 = 'ghi' ) then 'jkl' " - + "end complex_case_statement_2", selectStr.trim()); + + "end as `Complex_Case_Statement_2`", selectStr.trim()); } @@ -248,6 +248,22 @@ public class TestHQLParser { } @Test + public void testAliasShouldBeQuoted() throws Exception { + Assert.assertEquals(getSelectStrForQuery("select id as identity from sample_dim"), "id as `identity`"); + Assert.assertEquals(getSelectStrForQuery("select id as `column identity` from sample_dim"), + "id as `column identity`"); + Assert.assertEquals(getSelectStrForQuery("select id identity from sample_dim"), "id as `identity`"); + Assert.assertEquals(getSelectStrForQuery("select id `column identity` from sample_dim"), + "id as `column identity`"); + } + + private String getSelectStrForQuery(String query) throws Exception { + ASTNode tree = HQLParser.parseHQL(query, conf); + ASTNode select = HQLParser.findNodeByPath(tree, TOK_INSERT, TOK_SELECT); + return HQLParser.getString(select).trim(); + } + + @Test public void testAllColumns() throws Exception { String query = "select * from tab"; ASTNode select = HQLParser.findNodeByPath(HQLParser.parseHQL(query, conf), TOK_INSERT, TOK_SELECT); http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java index 7580541..ea561b6 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java @@ -835,23 +835,22 @@ public class TestJoinResolver extends TestQueryRewrite { conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected1 = getExpectedQuery("basecube", "select usersports.name name, sum(basecube.msr2) msr2 FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl user_interests" - + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" - + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - String expected2 = getExpectedQuery("basecube", "select usersports.name name, sum(basecube.msr12) msr12 FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl user_interests" - + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" - + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, + String expected1 = getExpectedQuery("basecube", + "select usersports.name as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected2 = getExpectedQuery("basecube", + "select usersports.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java ---------------------------------------------------------------------- diff --git a/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java b/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java index 3a00510..149c450 100644 --- a/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java +++ b/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java @@ -437,7 +437,7 @@ public class TestColumnarSQLRewriter { String expected = "select ( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "week((time_dim__time_dim . day )), " + "date(( time_dim___time_dim . day )), ( item_dim___item_dim . item_key ), " - + "case when (sum(alias2) = 0 ) then 0.0 else sum(alias2) end dollars_sold , " + + "case when (sum(alias2) = 0 ) then 0.0 else sum(alias2) end as `dollars_sold` , " + "format(sum(alias3), 4 ), format(avg(alias4), '##################.###' ), " + "min(alias5), max(alias6) from (select sales_fact___fact.time_key, " + "sales_fact___fact.location_key, sales_fact___fact.item_key,sum(( sales_fact___fact . " @@ -505,7 +505,7 @@ public class TestColumnarSQLRewriter { String actual = qtest.rewrite(query, conf, hconf); String expected = "select ( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ), case when (sum(alias2) = 0 ) then 0.0 " - + "else sum(alias2) end dollars_sold from (select sales_fact___fact.time_key, " + + "else sum(alias2) end as `dollars_sold` from (select sales_fact___fact.time_key, " + "sales_fact___fact.location_key,sum(( sales_fact___fact . dollars_sold )) as alias1, " + "sum(( sales_fact___fact . dollars_sold )) as alias2 from sales_fact sales_fact___fact " + "where sales_fact___fact.time_key in ( select time_dim .time_key from time_dim where " @@ -523,7 +523,7 @@ public class TestColumnarSQLRewriter { + "( time_dim___time_dim . day_of_week ), ( time_dim___time_dim . day ) " + "order by dollars_sold asc union all select ( sales_fact___fact . time_key ), " + "( time_dim___time_dim . day_of_week ), ( time_dim___time_dim . day ), " - + "case when (sum(alias2) = 0 ) then 0.0 else sum(alias2) end dollars_sold " + + "case when (sum(alias2) = 0 ) then 0.0 else sum(alias2) end as `dollars_sold` " + "from (select sales_fact___fact.time_key, sales_fact___fact.location_key," + "sum(( sales_fact___fact . dollars_sold )) as alias1, sum(( sales_fact___fact . dollars_sold )) " + "as alias2 from sales_fact sales_fact___fact where sales_fact___fact.time_key in " @@ -541,7 +541,7 @@ public class TestColumnarSQLRewriter { + "( time_dim___time_dim . day ) order by dollars_sold asc union all select " + "( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ), case when (sum(alias2) = 0 ) then 0.0 " - + "else sum(alias2) end dollars_sold from (select sales_fact___fact.time_key, " + + "else sum(alias2) end as `dollars_sold` from (select sales_fact___fact.time_key, " + "sales_fact___fact.location_key,sum(( sales_fact___fact . dollars_sold )) as alias1, " + "sum(( sales_fact___fact . dollars_sold )) as alias2 from sales_fact sales_fact___fact " + "where sales_fact___fact.time_key in ( select time_dim .time_key from time_dim where " @@ -1022,13 +1022,13 @@ public class TestColumnarSQLRewriter { String actual = qtest.rewrite(query, conf, hconf); String expected = "select ( sales_fact__db_sales_fact_fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ), ((sum(( sales_fact__db_sales_fact_fact . item_count )) + 0 ) + " - + "(sum(( sales_fact__db_sales_fact_fact . product_count )) + 0 )) expr1 , " + + "(sum(( sales_fact__db_sales_fact_fact . product_count )) + 0 )) as `expr1` , " + "((sum( case when ( location_dim__db_location_dim_ld . status_id ) is null then 0 " + "when (( location_dim__db_location_dim_ld . status_id ) = 1 ) then " + "( sales_fact__db_sales_fact_fact . item_sold ) else 0 end ) * 1000 ) / sum( case " + "when ( location_dim__db_location_dim_ld . status_id ) is null then 0 " + "when (( location_dim__db_location_dim_ld . status_id ) = 2 ) then " - + "( sales_fact__db_sales_fact_fact . dollars_sold ) else 0 end )) expr2 " + + "( sales_fact__db_sales_fact_fact . dollars_sold ) else 0 end )) as `expr2` " + "from db.sales_fact sales_fact__db_sales_fact_fact inner join " + "(select time_key,day_of_week,day from time_dim) time_dim___time_dim on " + "(( sales_fact__db_sales_fact_fact . time_key ) = ( time_dim___time_dim . time_key )) " http://git-wip-us.apache.org/repos/asf/lens/blob/6c4dba51/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestJDBCFinal.java ---------------------------------------------------------------------- diff --git a/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestJDBCFinal.java b/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestJDBCFinal.java index d8c2f63..053e20d 100644 --- a/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestJDBCFinal.java +++ b/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestJDBCFinal.java @@ -181,10 +181,10 @@ public class TestJDBCFinal { testCreateJdbcDriver(); final String query = - "select fact.time_key,time_dim.day_of_week,time_dim.day," + "sum(fact.dollars_sold) dollars_sold " + "select fact.time_key,time_dim.day_of_week,time_dim.day," + "sum(fact.dollars_sold) " + "from sales_fact fact " + "inner join time_dim time_dim on fact.time_key = time_dim.time_key " + "where time_dim.day between '1900-01-01' and '1900-01-03' " - + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold desc"; + + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by fact.time_key desc"; QueryContext context = new QueryContext(query, "SA", new LensConf(), baseConf, drivers); @@ -210,7 +210,7 @@ public class TestJDBCFinal { ColumnDescriptor col4 = rsMeta.getColumns().get(3); assertEquals(col4.getTypeName().toLowerCase(), "double"); - assertEquals(col4.getName(), "dollars_sold".toUpperCase()); + assertEquals(col4.getName(), "c4".toUpperCase()); while (rs.hasNext()) { ResultRow row = rs.next(); @@ -234,14 +234,14 @@ public class TestJDBCFinal { testCreateJdbcDriver(); final String query = - "select fact.time_key,time_dim.day_of_week,time_dim.day, " + "sum(fact.dollars_sold) dollars_sold " + "select fact.time_key,time_dim.day_of_week,time_dim.day, " + "sum(fact.dollars_sold) " + "from sales_fact fact " + "inner join time_dim time_dim on fact.time_key = time_dim.time_key " + "inner join item_dim item_dim on fact.item_key = item_dim.item_key and item_dim.item_name = 'item2' " + "inner join branch_dim branch_dim on fact.branch_key = branch_dim.branch_key " + "and branch_dim.branch_name = 'branch2' " + "inner join location_dim location_dim on fact.location_key = location_dim.location_key " + "where time_dim.day between '1900-01-01' and '1900-01-04' " + "and location_dim.location_name = 'loc2' " - + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold desc "; + + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by fact.time_key desc "; QueryContext context = new QueryContext(query, "SA", new LensConf(), baseConf, drivers); LensResultSet resultSet = driver.execute(context); @@ -266,7 +266,7 @@ public class TestJDBCFinal { ColumnDescriptor col4 = rsMeta.getColumns().get(3); assertEquals(col4.getTypeName().toLowerCase(), "double"); - assertEquals(col4.getName(), "dollars_sold".toUpperCase()); + assertEquals(col4.getName(), "c4".toUpperCase()); while (rs.hasNext()) { ResultRow row = rs.next();
