Repository: lens Updated Branches: refs/heads/master fa865bfb6 -> f1a959bdd
LENS-1334 : Fix full outer join condition for multi fact queries with more than two tables Project: http://git-wip-us.apache.org/repos/asf/lens/repo Commit: http://git-wip-us.apache.org/repos/asf/lens/commit/f1a959bd Tree: http://git-wip-us.apache.org/repos/asf/lens/tree/f1a959bd Diff: http://git-wip-us.apache.org/repos/asf/lens/diff/f1a959bd Branch: refs/heads/master Commit: f1a959bdd78e4161b7883734776e4dc4c464bb49 Parents: fa865bf Author: Sushil Mohanty <sushilmoha...@apache.org> Authored: Tue Sep 27 16:38:28 2016 +0530 Committer: Amareshwari Sriramadasu <amareshw...@apache.org> Committed: Tue Sep 27 16:38:28 2016 +0530 ---------------------------------------------------------------------- .../lens/cube/parse/MultiFactHQLContext.java | 36 ++++++++------- .../lens/cube/parse/TestBaseCubeQueries.java | 46 +++++++++++--------- 2 files changed, 45 insertions(+), 37 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/lens/blob/f1a959bd/lens-cube/src/main/java/org/apache/lens/cube/parse/MultiFactHQLContext.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/MultiFactHQLContext.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/MultiFactHQLContext.java index 7fbcd7e..b3547db 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/MultiFactHQLContext.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/MultiFactHQLContext.java @@ -129,6 +129,17 @@ class MultiFactHQLContext extends SimpleHQLContext { return select.toString(); } + private String getMultiFactJoinCondition(int i, String dim) { + StringBuilder joinCondition = new StringBuilder(); + if (i <= 1) { + return "".toString(); + } else { + joinCondition.append("mq").append(i - 2).append(".").append(dim).append(" <=> "). + append("mq").append(i - 1).append(".").append(dim); + } + return joinCondition.toString(); + } + private String getFromString() throws LensException { StringBuilder fromBuilder = new StringBuilder(); int aliasCount = 1; @@ -137,24 +148,17 @@ class MultiFactHQLContext extends SimpleHQLContext { SimpleHQLContext facthql = factHQLContextMap.get(fact); fromBuilder.append(sep).append("(").append(facthql.toHQL()).append(")").append(" mq").append(aliasCount++); sep = " full outer join "; - } - CandidateFact firstFact = facts.iterator().next(); - if (!firstFact.getDimFieldIndices().isEmpty()) { - fromBuilder.append(" on "); - } - for (int i = 2; i <= facts.size(); i++) { - Iterator<Integer> dimIter = firstFact.getDimFieldIndices().iterator(); - while (dimIter.hasNext()) { - String dim = query.getSelectAlias(dimIter.next()); - fromBuilder.append("mq1").append(".").append(dim).append(" <=> ").append("mq").append(i).append(".") - .append(dim); - if (dimIter.hasNext()) { - fromBuilder.append(" AND "); + if (!fact.getDimFieldIndices().isEmpty() && aliasCount > 2) { + fromBuilder.append(" on "); + Iterator<Integer> dimIter = fact.getDimFieldIndices().iterator(); + while (dimIter.hasNext()) { + String dim = query.getSelectAlias(dimIter.next()); + fromBuilder.append(getMultiFactJoinCondition(aliasCount, dim)); + if (dimIter.hasNext()) { + fromBuilder.append(" AND "); + } } } - if (i != facts.size() && firstFact.getDimFieldIndices().size() > 0) { - fromBuilder.append(" AND "); - } } return fromBuilder.toString(); } http://git-wip-us.apache.org/repos/asf/lens/blob/f1a959bd/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 6fb027a..053cad3 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 @@ -331,42 +331,46 @@ public class TestBaseCubeQueries extends TestQueryRewrite { @Test public void testMultiFactQueryInvolvingThreeFactTables() throws Exception { // query with 3 fact tables - String hqlQuery = rewrite("select dim1, msr12, roundedmsr2, msr13, msr3 from basecube where " + TWO_DAYS_RANGE, - conf); + String hqlQuery = rewrite("select dim1, d_time, msr12, roundedmsr2, msr13, msr3 from basecube where " + + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, sum(basecube.msr12) as `msr12` FROM ", null, - " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, basecube.d_time as `d_time`, " + + "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`, round(sum(basecube.msr2)/1000) as `roundedmsr2`, max(basecube.msr3) as `msr3` " - + "FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); + "select basecube.dim1 as `dim1`, basecube.d_time as `d_time`, 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 as `dim1`, max(basecube.msr13) as `msr13` FROM ", null, + getExpectedQuery(cubeName, "select basecube.dim1 as `dim1`, basecube.d_time as `d_time`, " + + "max(basecube.msr13) as `msr13` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "c1_testfact3_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); compareContains(expected3, hqlQuery); assertTrue( hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, mq1.msr12 msr12," - + " mq2.roundedmsr2 roundedmsr2, mq3.msr13 msr13, mq2.msr3 msr3 from ") + "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, coalesce(mq1.d_time, mq2.d_time, mq3.d_time) d_time, " + + "mq1.msr12 msr12, mq2.roundedmsr2 roundedmsr2, mq3.msr13 msr13, mq2.msr3 msr3 from ") || hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, mq1.msr12 msr12," - + " mq3.roundedmsr2 roundedmsr2, mq2.msr13 msr13, mq3.msr3 msr3 from ") + "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, coalesce(mq1.d_time, mq2.d_time, mq3.d_time) d_time," + + " mq1.msr12 msr12, mq3.roundedmsr2 roundedmsr2, mq2.msr13 msr13, mq3.msr3 msr3 from ") || hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, mq2.msr12 msr12," - + " mq1.roundedmsr2 roundedmsr2, mq3.msr13 msr13, mq1.msr3 msr3 from ") + "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, coalesce(mq1.d_time, mq2.d_time, mq3.d_time) d_time," + + " mq2.msr12 msr12, mq1.roundedmsr2 roundedmsr2, mq3.msr13 msr13, mq1.msr3 msr3 from ") || hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, mq2.msr12 msr12," - + " mq3.roundedmsr2 roundedmsr2, mq1.msr13 msr13, mq3.msr3 msr3 from ") + "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, coalesce(mq1.d_time, mq2.d_time, mq3.d_time) d_time, " + + "mq2.msr12 msr12, mq3.roundedmsr2 roundedmsr2, mq1.msr13 msr13, mq3.msr3 msr3 from ") || hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, mq3.msr12 msr12," - + " mq1.roundedmsr2 roundedmsr2, mq2.msr13 msr13, mq1.msr3 msr3 from ") + "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, coalesce(mq1.d_time, mq2.d_time, mq3.d_time) d_time," + + " mq3.msr12 msr12, mq1.roundedmsr2 roundedmsr2, mq2.msr13 msr13, mq1.msr3 msr3 from ") || hqlQuery.toLowerCase().startsWith( - "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, mq3.msr12 msr12," - + " mq2.roundedmsr2 roundedmsr2, mq1.msr13 msr13, mq2.msr3 msr3 from "), hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.contains("mq2 full outer join ") - && hqlQuery.endsWith("mq3 on mq1.dim1 <=> mq2.dim1 AND mq1.dim1 <=> mq3.dim1"), hqlQuery); + "select coalesce(mq1.dim1, mq2.dim1, mq3.dim1) dim1, coalesce(mq1.d_time, mq2.d_time, mq3.d_time) d_time, " + + "mq3.msr12 msr12, mq2.roundedmsr2 roundedmsr2, mq1.msr13 msr13, mq2.msr3 msr3 from "), hqlQuery); + assertTrue(hqlQuery.toLowerCase().contains("mq1 full outer join ") + && hqlQuery.toLowerCase().contains("mq2 on mq1.dim1 <=> mq2.dim1 and mq1.d_time <=> mq2.d_time") + && hqlQuery.toLowerCase().endsWith("mq3 on mq2.dim1 <=> mq3.dim1 and mq2.d_time <=> mq3.d_time"), hqlQuery); } @Test