Repository: lens Updated Branches: refs/heads/master 6e11d780f -> 3b3f42da5
LENS-730 : Fix issues with column aliases in multifact query Project: http://git-wip-us.apache.org/repos/asf/lens/repo Commit: http://git-wip-us.apache.org/repos/asf/lens/commit/3b3f42da Tree: http://git-wip-us.apache.org/repos/asf/lens/tree/3b3f42da Diff: http://git-wip-us.apache.org/repos/asf/lens/diff/3b3f42da Branch: refs/heads/master Commit: 3b3f42da51808e1bb0d23357e1bfaecaca978896 Parents: 6e11d78 Author: Amareshwari Sriramadasu <amareshw...@apache.org> Authored: Wed Apr 27 09:52:06 2016 +0530 Committer: Amareshwari Sriramadasu <amareshw...@apache.org> Committed: Wed Apr 27 09:52:06 2016 +0530 ---------------------------------------------------------------------- .../lens/cube/parse/AggregateResolver.java | 4 +- .../apache/lens/cube/parse/ColumnResolver.java | 44 +++-- .../lens/cube/parse/ExpressionResolver.java | 2 +- .../apache/lens/cube/parse/CubeTestSetup.java | 5 + .../lens/cube/parse/TestBaseCubeQueries.java | 165 ++++++++++++++++--- .../lens/cube/parse/TestBridgeTableQueries.java | 46 +++--- .../lens/cube/parse/TestCubeRewriter.java | 10 +- .../lens/cube/parse/TestExpressionResolver.java | 12 +- 8 files changed, 210 insertions(+), 78 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/lens-cube/src/main/java/org/apache/lens/cube/parse/AggregateResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/AggregateResolver.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/AggregateResolver.java index fd7036a..1593a54 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/AggregateResolver.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/AggregateResolver.java @@ -170,8 +170,8 @@ class AggregateResolver implements ContextRewriter { ASTNode tabident = HQLParser.findNodeByPath(node, TOK_TABLE_OR_COL, Identifier); ASTNode colIdent = (ASTNode) node.getChild(1); - colname = colIdent.getText(); - tabname = tabident.getText(); + colname = colIdent.getText().toLowerCase(); + tabname = tabident.getText().toLowerCase(); } String msrname = StringUtils.isBlank(tabname) ? colname : tabname + "." + colname; http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/lens-cube/src/main/java/org/apache/lens/cube/parse/ColumnResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/ColumnResolver.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/ColumnResolver.java index 75aa3f4..58a1d4d 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/ColumnResolver.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/ColumnResolver.java @@ -61,10 +61,10 @@ class ColumnResolver implements ContextRewriter { } getColsForSelectTree(cubeql); getColsForWhereTree(cubeql); - getColsForTree(cubeql, cubeql.getJoinAST(), cubeql); - getColsForTree(cubeql, cubeql.getGroupByAST(), cubeql); - getColsForTree(cubeql, cubeql.getHavingAST(), cubeql); - getColsForTree(cubeql, cubeql.getOrderByAST(), cubeql); + getColsForTree(cubeql, cubeql.getJoinAST(), cubeql, true); + getColsForTree(cubeql, cubeql.getGroupByAST(), cubeql, true); + getColsForTree(cubeql, cubeql.getHavingAST(), cubeql, true); + getColsForTree(cubeql, cubeql.getOrderByAST(), cubeql, true); // Update join dimension tables for (String table : cubeql.getTblAliasToColumns().keySet()) { @@ -77,7 +77,8 @@ class ColumnResolver implements ContextRewriter { } // finds columns in AST passed. - static void getColsForTree(final CubeQueryContext cubeql, ASTNode tree, final TrackQueriedColumns tqc) + static void getColsForTree(final CubeQueryContext cubeql, ASTNode tree, final TrackQueriedColumns tqc, + final boolean skipAliases) throws LensException { if (tree == null) { return; @@ -98,7 +99,7 @@ class ColumnResolver implements ContextRewriter { // Take child ident.totext ASTNode ident = (ASTNode) node.getChild(0); String column = ident.getText().toLowerCase(); - if (cubeql.getExprToAliasMap().values().contains(column)) { + if (skipAliases && cubeql.getExprToAliasMap().values().contains(column)) { // column is an existing alias return; } @@ -137,18 +138,12 @@ class ColumnResolver implements ContextRewriter { // Updates alias for each selected expression. // Alias is updated as follows: // Case 1: If select expression does not have an alias - // ** And the expression has only one column queried, the column name is put - // as - // select alias. - // ** If the expression has more than one column queried, the alias is - // constructed as - // 'expr' + index of the expression. + // ** And the expression is the column queried, the column name is put as select alias. + // ** If the expression is not just simple column queried, the alias is + // constructed as 'expr' + index of the expression. // Case 2: If select expression already has alias // ** Adds it to exprToAlias map - // ** and the alias does not have spaces, the select alias and final alias is - // same. - // ** If alias has spaces, select alias is constructed as 'expr' + index of - // the expression + // ** and the alias is constructed as 'expr' + index of the expression. // and user given alias is the final alias of the expression. private static final String SELECT_ALIAS_PREFIX = "expr"; @@ -156,24 +151,23 @@ class ColumnResolver implements ContextRewriter { int exprInd = 1; for (int i = 0; i < cubeql.getSelectAST().getChildCount(); i++) { ASTNode selectExpr = (ASTNode) cubeql.getSelectAST().getChild(i); - Set<String> cols = new HashSet<String>(); + ASTNode selectExprChild = (ASTNode)selectExpr.getChild(0); + Set<String> cols = new HashSet<>(); addColumnsForSelectExpr(cubeql, selectExpr, cubeql.getSelectAST(), cols); ASTNode alias = HQLParser.findNodeByPath(selectExpr, Identifier); String selectAlias; String selectFinalAlias = null; if (alias != null) { cubeql.addExprToAlias(selectExpr, alias); - if (HQLParser.hasSpaces(alias.getText())) { - selectFinalAlias = alias.getText(); - selectAlias = SELECT_ALIAS_PREFIX + exprInd; - } else { - selectAlias = alias.getText().trim(); - } - } else if (cols.size() == 1) { - // add the column name as alias + selectFinalAlias = alias.getText(); + selectAlias = SELECT_ALIAS_PREFIX + exprInd; + } else if (cols.size() == 1 && (selectExprChild.getToken().getType() == TOK_TABLE_OR_COL + || selectExprChild.getToken().getType() == DOT)) { + // select expression is same as the column selectAlias = cols.iterator().next().toLowerCase(); } else { selectAlias = SELECT_ALIAS_PREFIX + exprInd; + selectFinalAlias = HQLParser.getString(selectExprChild); } exprInd++; cubeql.addSelectAlias(selectAlias, selectFinalAlias); http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/lens-cube/src/main/java/org/apache/lens/cube/parse/ExpressionResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/ExpressionResolver.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/ExpressionResolver.java index 04c3c45..c3d15d0 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/ExpressionResolver.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/ExpressionResolver.java @@ -238,7 +238,7 @@ class ExpressionResolver implements ContextRewriter { void resolveColumns(CubeQueryContext cubeql) throws LensException { // finds all columns and table aliases in the expression - ColumnResolver.getColsForTree(cubeql, finalAST, this); + ColumnResolver.getColsForTree(cubeql, finalAST, this, false); } Date getStartTime() { http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java index 379e873..73e6221 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java @@ -948,6 +948,8 @@ public class CubeTestSetup { null)); cubeMeasures2.add(new ColumnMeasure(new FieldSchema("msr14", "bigint", "fourth measure"), "Measure4", null, "COUNT", null)); + cubeMeasures2.add(new ColumnMeasure(new FieldSchema("directMsr", "bigint", "fifth measure"), "Direct Measure", + null, "SUM", null)); cubeDimensions2.add(new BaseDimAttribute(new FieldSchema("dim11", "string", "basedim"))); cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("dim12", "int", "ref dim"), "Dim2 refer", @@ -1149,6 +1151,8 @@ public class CubeTestSetup { "user sports", "substr(sports, 3)")); baseExprs.add(new ExprColumn(new FieldSchema("sportids_abbr", "array<string>", ""), "user sports", "case when sportids == 1 then 'CKT' when sportids == 2 then 'FTB' else 'NON' end")); + baseExprs.add(new ExprColumn(new FieldSchema("directMsrExpr", "bigint", ""), + "Direct Measure", new ExprSpec("directMsr + 0", null, null), new ExprSpec("msr13 + msr14", null, null))); client.createCube(BASE_CUBE_NAME, cubeMeasures2, cubeDimensions2, baseExprs, joinChains, cubeProperties); Map<String, String> derivedProperties = new HashMap<>(); @@ -1167,6 +1171,7 @@ public class CubeTestSetup { measures.add("msr12"); measures.add("msr13"); measures.add("msr14"); + measures.add("directmsr"); dimensions = new HashSet<>(); dimensions.add("cityid"); dimensions.add("stateid"); http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/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 fec5d51..fcf975e 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 @@ -384,19 +384,137 @@ 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 as `d1`, sum(basecube.msr12) as `expr2` FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, sum(basecube.msr12) as `expr2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = - 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")); + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, round(sum(basecube.msr2)/1000) as `expr3` FROM ", + null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + compareContains(expected1, hqlQuery); + compareContains(expected2, hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq2.expr2 `my msr12`, mq1.expr3 `m2` from ") + || hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq1.expr2 `my msr12`, mq2.expr3 `m2` from "), hqlQuery); + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), + hqlQuery); + } + + @Test + public void testMultiFactQueryWithColumnAliasesAsFunctions() throws Exception { + String hqlQuery = + rewrite("select dim1 d1, msr12 `sum(msr12)`, roundedmsr2 as `round(sum(msr2)/1000)` from basecube where " + + TWO_DAYS_RANGE, conf); + String expected1 = + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, sum(basecube.msr12) as `expr2` FROM ", null, + " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + String expected2 = + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, round(sum(basecube.msr2)/1000) as `expr3` FROM ", + null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + compareContains(expected1, hqlQuery); + compareContains(expected2, hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq2.expr2 `sum(msr12)`, mq1.expr3 `round(sum(msr2)/1000)` from ") + || hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq1.expr2 `sum(msr12)`, mq2.expr3 `round(sum(msr2)/1000)` from "), + hqlQuery); + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), + hqlQuery); + } + + @Test + public void testMultiFactQueryWithAliasAsColumnName() throws Exception { + String hqlQuery = + rewrite("select dim1 d1, msr12 `my msr12`, roundedmsr2 as `msr2` from basecube where " + TWO_DAYS_RANGE, conf); + + String expected1 = + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, sum(basecube.msr12) as `expr2` FROM ", null, + " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + String expected2 = + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, round(sum(basecube.msr2)/1000) as `expr3` FROM ", + null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.d1, mq2.d1) d1, mq2.expr2 `my msr12`, mq1.m2 m2 from ") - || - hqlQuery.toLowerCase().startsWith("select coalesce(mq1.d1, mq2.d1) d1, mq1.expr2 `my msr12`, mq2.m2 m2 from "), + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq2.expr2 `my msr12`, mq1.expr3 `msr2` from ") + || hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq1.expr2 `my msr12`, mq2.expr3 `msr2` from "), + hqlQuery); + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), + hqlQuery); + } + + @Test + public void testMultiFactQueryWithAliasAsExpressionName() throws Exception { + String hqlQuery = + rewrite("select dim1 d1, msr12 `my msr12`, roundedmsr2 as `roundedmsr2` from basecube where " + TWO_DAYS_RANGE, + conf); + + String expected1 = + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, sum(basecube.msr12) as `expr2` FROM ", null, + " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + String expected2 = + getExpectedQuery(cubeName, "select basecube.dim1 as `expr1`, round(sum(basecube.msr2)/1000) as `expr3` FROM ", + null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + compareContains(expected1, hqlQuery); + compareContains(expected2, hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq2.expr2 `my msr12`, mq1.expr3 `roundedmsr2` from ") + || hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `d1`, mq1.expr2 `my msr12`, mq2.expr3 `roundedmsr2` from "), + hqlQuery); + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), + hqlQuery); + } + + @Test + public void testMultiFactQueryWithExprOnDimsWithoutAliases() throws Exception { + String hqlQuery = + rewrite("select func1(dim1), func2(dim1), msr12, roundedmsr2 from basecube where " + TWO_DAYS_RANGE, conf); + String expected1 = + getExpectedQuery(cubeName, "select func1(basecube.dim1) as `expr1`, func2(basecube.dim1) as `expr2`," + + " sum(basecube.msr12) as `msr12` FROM ", null, + " group by func1(basecube.dim1), func2(basecube.dim1)", + getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + String expected2 = + getExpectedQuery(cubeName, "select func1(basecube.dim1) as `expr1`, func2(basecube.dim1) as `expr2`," + + " round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, + " group by func1(basecube.dim1), func2(basecube.dim1)", + getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + compareContains(expected1, hqlQuery); + compareContains(expected2, hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select coalesce(mq1.expr1, mq2.expr1) `func1( dim1 )`," + + " coalesce(mq1.expr2, mq2.expr2) `func2( dim1 )`, mq2.msr12 msr12, mq1.roundedmsr2 roundedmsr2 from ") + || hqlQuery.toLowerCase().startsWith("select coalesce(mq1.expr1, mq2.expr1) `func1( dim1 )`," + + " coalesce(mq1.expr2, mq2.expr2) `func2( dim1 )`, mq1.msr12 msr12, mq2.roundedmsr2 roundedmsr2 from "), + hqlQuery); + assertTrue(hqlQuery.contains("mq1 full outer join ") + && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1 AND mq1.expr2 <=> mq2.expr2"), hqlQuery); + } + + @Test + public void testMultiFactQueryWithDirectMsr() throws Exception { + String hqlQuery = + rewrite("select func1(dim1), directMsrExpr as directMsr, roundedmsr2 from basecube where " + TWO_DAYS_RANGE, + conf); + String expected1 = + getExpectedQuery(cubeName, "select func1(basecube.dim1) as `expr1`, max(basecube.msr13) + count(basecube . msr14)" + + " as `expr2` FROM ", null, + " group by func1(basecube.dim1)", getWhereForDailyAndHourly2days(cubeName, "C1_testFact3_BASE")); + String expected2 = + getExpectedQuery(cubeName, "select func1(basecube.dim1) as expr1, round(sum(basecube.msr2)/1000) as `roundedmsr2`" + + " FROM ", null, " group by func1(basecube.dim1)", + getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + compareContains(expected1, hqlQuery); + compareContains(expected2, hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `func1( dim1 )`, mq2.expr2 `directmsr`, mq1.roundedmsr2 roundedmsr2 " + + "from ") + || hqlQuery.toLowerCase().startsWith( + "select coalesce(mq1.expr1, mq2.expr1) `func1( dim1 )`, mq1.expr2 `directmsr`, mq2.roundedmsr2 roundedmsr2 " + + "from "), + hqlQuery.toLowerCase()); + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.d1 <=> mq2.d1"), hqlQuery); } @Test @@ -404,17 +522,17 @@ 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 as `dim1`, avg(basecube.msr12) as `msr12` FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, avg(basecube.msr12) as `expr2` FROM ", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact2_raw_base")); String expected2 = - getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, avg(basecube.msr2)) as `msr2` FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, avg(basecube.msr2)) as `expr3` FROM ", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1) dim1, mq2.msr12 msr12, mq1.msr2 msr2 from ") + "select coalesce(mq1.dim1, mq2.dim1) dim1, mq2.expr2 `avg( msr12 )`, mq1.expr3 `avg( msr2 )` from ") || hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1) dim1, mq1.msr12 msr12, mq2.msr2 msr2 from "), hqlQuery); + "select coalesce(mq1.dim1, mq2.dim1) dim1, mq1.expr2 `avg( msr12 )`, mq2.expr3 `avg( msr2 )` from "), hqlQuery); assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.dim1 <=> mq2.dim1"), hqlQuery); @@ -500,17 +618,15 @@ public class TestBaseCubeQueries extends TestQueryRewrite { getWhereForHourly2days(cubeName, "C1_testfact3_raw_base")); String expected2 = getExpectedQuery(cubeName, "select basecube.dim1 != 'x' AND basecube.dim2 != 10 as `booleancut`," - + " round(sum(basecube.msr2)/1000) as `msr2` FROM ", null, + + " round(sum(basecube.msr2)/1000) as `expr2` FROM ", null, " group by basecube.dim1 != 'x' AND basecube.dim2 != 10", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); - assertTrue(hqlQuery.toLowerCase() - .startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, mq2.msr2 msr2," - + " mq1.expr3 expr3 from ") - || hqlQuery.toLowerCase() - .startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, mq1.msr2 msr2," - + " mq2.expr3 expr3 from "), hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, " + + "mq2.expr2 `round((sum( msr2 ) / 1000 ))`, mq1.expr3 `avg(( msr13 + msr14 ))` from ") + || hqlQuery.toLowerCase().startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, " + + "mq1.expr2 `round((sum( msr2 ) / 1000 ))`, mq2.expr3 `avg(( msr13 + msr14 ))` from "), hqlQuery); assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.booleancut <=> mq2.booleancut"), hqlQuery); @@ -533,17 +649,16 @@ public class TestBaseCubeQueries extends TestQueryRewrite { getWhereForHourly2days(cubeName, "C1_testfact3_raw_base")); String expected2 = getExpectedQuery(cubeName, "select basecube.dim1 != 'x' AND basecube.dim2 != 10 as `booleancut`," - + " round(sum(basecube.msr2)/1000) as `msr2` FROM ", "(basecube.dim1 != 'x' AND basecube.dim2 != 10) == true", + + " round(sum(basecube.msr2)/1000) as `expr2` FROM ", + "(basecube.dim1 != 'x' AND basecube.dim2 != 10) == true", " group by basecube.dim1 != 'x' AND basecube.dim2 != 10", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); - assertTrue(hqlQuery.toLowerCase() - .startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, mq2.msr2 msr2," - + " mq1.expr3 expr3 from ") - || hqlQuery.toLowerCase() - .startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, mq1.msr2 msr2," - + " mq2.expr3 expr3 from "), hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, " + + "mq2.expr2 `round((sum( msr2 ) / 1000 ))`, mq1.expr3 `avg(( msr13 + msr14 ))` from ") + || hqlQuery.toLowerCase().startsWith("select coalesce(mq1.booleancut, mq2.booleancut) booleancut, " + + "mq1.expr2 `round((sum( msr2 ) / 1000 ))`, mq2.expr3 `avg(( msr13 + msr14 ))` from "), hqlQuery); assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.booleancut <=> mq2.booleancut"), hqlQuery); http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java index e8905c1..b89ed2c 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java @@ -150,7 +150,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { @Test public void testBridgeTablesWithMultipleFacts() throws Exception { - String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; + String query = "select usersports.name, msr2, msr12 from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); String expected1 = getExpectedQuery("basecube", "select usersports.balias0 as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() @@ -180,7 +180,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { hqlQuery); // run with chain ref column - query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; + query = "select sports, msr2, msr12 from basecube where " + TWO_DAYS_RANGE; hqlQuery = rewrite(query, hConf); expected1 = getExpectedQuery("basecube", "select usersports.balias0 as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() @@ -433,7 +433,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { @Test public void testBridgeTablesWithMultipleFactsWithFilterBeforeFlattening() throws Exception { - String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + String query = "select usersports.name, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET'"; String hqlQuery = rewrite(query, hConf); String expected1 = getExpectedQuery("basecube", @@ -463,7 +463,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), hqlQuery); // run with chain ref column - query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET'"; + query = "select sports, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET'"; hqlQuery = rewrite(query, hConf); expected1 = getExpectedQuery("basecube", "select usersports.balias0 as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() @@ -498,7 +498,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesWithMultipleFactsWithFilterAfterFlattening() throws Exception { Configuration conf = new Configuration(hConf); conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); - String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + String query = "select usersports.name, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); String expected1 = getExpectedQuery("basecube", @@ -528,7 +528,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), hqlQuery); // run with chain ref column - query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + query = "select sports, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET,FOOTBALL'"; hqlQuery = rewrite(query, conf); expected1 = getExpectedQuery("basecube", @@ -658,11 +658,11 @@ public class TestBridgeTableQueries extends TestQueryRewrite { } @Test public void testBridgeTablesWithMultipleFactsWithExprBeforeFlattening() throws Exception { - String query = "select substr(usersports.name, 3), sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + String query = "select substr(usersports.name, 3), msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and usersports.name in ('CRICKET', 'FOOTBALL')"; String hqlQuery = rewrite(query, hConf); String expected1 = getExpectedQuery("basecube", - "select usersports.balias0 as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "select usersports.balias0 as `expr1`, 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(substr(usersports.name, 3)) as balias0, " + " collect_set(usersports.name) as balias1 from" @@ -673,7 +673,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); String expected2 = getExpectedQuery("basecube", - "select usersports.balias0 as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "select usersports.balias0 as `expr1`, 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(substr(usersports.name, 3)) as balias0, " + " collect_set(usersports.name) as balias1 from" @@ -686,13 +686,16 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + assertTrue(lower.startsWith("select coalesce(mq1.expr1, mq2.expr1) `substr(( usersports . name ), 3 )`," + + " mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.expr1, mq2.expr1) `substr(( usersports . name ), 3 )`, mq1.msr2 msr2, " + + "mq2.msr12 msr12 from "), + hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), hqlQuery); // run with chain ref column - query = "select sports_abbr, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + " and sports in " + query = "select sports_abbr, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports in " + "('CRICKET', 'FOOTBALL')"; hqlQuery = rewrite(query, hConf); expected1 = getExpectedQuery("basecube", @@ -735,11 +738,11 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesWithMultipleFactsWithExprAfterFlattening() throws Exception { Configuration conf = new Configuration(hConf); conf.setBoolean(CubeQueryConfUtil.DO_FLATTENING_OF_BRIDGE_TABLE_EARLY, true); - String query = "select substr(usersports.name, 3), sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + String query = "select substr(usersports.name, 3), msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); String expected1 = getExpectedQuery("basecube", - "select substr(usersports.name, 3) as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "select substr(usersports.name, 3) as `expr1`, 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() @@ -748,7 +751,7 @@ public class TestBridgeTableQueries extends TestQueryRewrite { " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); String expected2 = getExpectedQuery("basecube", - "select substr(usersports.name, 3) as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "select substr(usersports.name, 3) as `expr1`, 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() @@ -759,13 +762,16 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + assertTrue(lower.startsWith("select coalesce(mq1.expr1, mq2.expr1) `substr(( usersports . name ), 3 )`," + + " mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.expr1, mq2.expr1) `substr(( usersports . name ), 3 )`, mq1.msr2 msr2," + + " mq2.msr12 msr12 from "), + hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), hqlQuery); // run with chain ref column - query = "select sports_abbr, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE + " and sports = " + query = "select sports_abbr, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports = " + "'CRICKET,FOOTBALL'"; hqlQuery = rewrite(query, conf); expected1 = getExpectedQuery("basecube", http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/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 6ee45d8..a223449 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 @@ -553,13 +553,15 @@ public class TestCubeRewriter extends TestQueryRewrite { String whereCond = "zipcode = 'a' and cityid = 'b' and (" + TWO_DAYS_RANGE_SPLIT_OVER_UPDATE_PERIODS + ")"; String hqlQuery = rewrite("select zipcode, count(msr4), sum(msr15) from testCube where " + whereCond, conf); System.out.println(hqlQuery); - String possibleStart1 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq1.msr4 msr4, mq2.msr15 msr15 FROM "; - String possibleStart2 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq2.msr4 msr4, mq1.msr15 msr15 FROM "; + String possibleStart1 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq1.expr2 `count( msr4 )`," + + " mq2.expr3 `sum( msr15 )` FROM "; + String possibleStart2 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq2.expr2 `count( msr4 )`," + + " mq1.expr3 `sum( msr15 )` FROM "; assertTrue(hqlQuery.startsWith(possibleStart1) || hqlQuery.startsWith(possibleStart2)); - compareContains(rewrite("select zipcode as `zipcode`, sum(msr15) as `msr15` from testcube where " + whereCond, + compareContains(rewrite("select zipcode as `zipcode`, sum(msr15) as `expr3` from testcube where " + whereCond, conf), hqlQuery); - compareContains(rewrite("select zipcode as `zipcode`, count(msr4) as `msr4` from testcube where " + whereCond, + compareContains(rewrite("select zipcode as `zipcode`, count(msr4) as `expr2` from testcube where " + whereCond, conf), hqlQuery); assertTrue(hqlQuery.endsWith("on mq1.zipcode <=> mq2.zipcode")); // No time_range_in should be remaining http://git-wip-us.apache.org/repos/asf/lens/blob/3b3f42da/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 fa07b67..d54e8b9 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 @@ -145,8 +145,18 @@ public class TestExpressionResolver extends TestQueryRewrite { getExpectedQuery("tc", "select substr(tc.dim1, 3) as `subdim1`, avg(tc.msr1 + tc.msr2) FROM ", null, " and subdim1 != 'XYZ' group by substr(tc.dim1, 3)", getWhereForHourly2days("tc", "C1_testfact2_raw")); TestCubeRewriter.compareQueries(hqlQuery, expected); + } + @Test + public void testCubeQueryExpressionWithAliasAsColumnName() throws Exception { + String hqlQuery = rewrite("select dim1 as d1, roundedmsr2 as msr2 from testCube" + " where " + TWO_DAYS_RANGE, + conf); + String expected = + getExpectedQuery(cubeName, "select testcube.dim1 as `d1`, round(sum(testcube.msr2)/1000) as `msr2` FROM ", null, + " group by testcube.dim1", getWhereForDailyAndHourly2days(cubeName, "c1_summary1")); + TestCubeRewriter.compareQueries(hqlQuery, expected); } + @Test public void testExpressionInGroupbyToSelect() throws Exception { // expression with groupby @@ -158,8 +168,8 @@ public class TestExpressionResolver extends TestQueryRewrite { + " avg(testCube.msr1 + testCube.msr2) FROM ", null, " and substr(testCube.dim1, 3) != 'XYZ'" + " group by testCube.dim1 != 'x' AND testCube.dim2 != 10", getWhereForHourly2days("C1_testfact2_raw")); TestCubeRewriter.compareQueries(hqlQuery, expected); - } + @Test public void testExpressionInSelectToGroupby() throws Exception { String hqlQuery =
