http://git-wip-us.apache.org/repos/asf/lens/blob/975fa2c2/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 b367214..8a559e2 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 @@ -31,8 +31,6 @@ import static org.apache.hadoop.hive.ql.parse.HiveParser.KW_AND; import static org.testng.Assert.*; import java.util.*; -import java.util.regex.Matcher; -import java.util.regex.Pattern; import org.apache.lens.api.error.ErrorCollectionFactory; import org.apache.lens.cube.error.LensCubeErrorCode; @@ -52,7 +50,6 @@ import org.testng.annotations.BeforeTest; import org.testng.annotations.Test; import com.google.common.base.Splitter; -import com.google.common.collect.Sets; import lombok.Getter; public class TestBaseCubeQueries extends TestQueryRewrite { @@ -72,73 +69,74 @@ public class TestBaseCubeQueries extends TestQueryRewrite { @Test public void testNoCandidateFactAvailableExceptionCompareTo() throws Exception { - //maxCause : COLUMN_NOT_FOUND, Ordinal : 9 + //maxCause : COLUMN_NOT_FOUND NoCandidateFactAvailableException ne1 =(NoCandidateFactAvailableException) - getLensExceptionInRewrite("select dim1, test_time_dim, msr3, msr13 from basecube where " - + TWO_DAYS_RANGE, conf); - //maxCause : FACT_NOT_AVAILABLE_IN_RANGE, Ordinal : 1 + getLensExceptionInRewrite("select dim1, test_time_dim, msr3, msr13 from basecube where " + + TWO_DAYS_RANGE, conf); + //maxCause : COLUMN_NOT_FOUND NoCandidateFactAvailableException ne2 = (NoCandidateFactAvailableException) - getLensExceptionInRewrite("select dim1 from " + cubeName + " where " + LAST_YEAR_RANGE, getConf()); - assertEquals(ne1.compareTo(ne2), 8); + getLensExceptionInRewrite("select dim1 from " + cubeName + " where " + LAST_YEAR_RANGE, getConf()); + assertEquals(ne1.compareTo(ne2), 0); } @Test public void testColumnErrors() throws Exception { LensException e; - -// e = getLensExceptionInRewrite("select msr11 + msr2 from basecube" + " where " + TWO_DAYS_RANGE, conf); -// e.buildLensErrorResponse(new ErrorCollectionFactory().createErrorCollection(), null, "testid"); -// assertEquals(e.getErrorCode(), -// LensCubeErrorCode.NO_FACT_HAS_COLUMN.getLensErrorInfo().getErrorCode()); -// assertTrue(e.getMessage().contains("msr11"), e.getMessage()); -// assertTrue(e.getMessage().contains("msr2"), e.getMessage()); + e = getLensExceptionInRewrite("select msr11 + msr2 from basecube" + " where " + TWO_DAYS_RANGE, conf); + e.buildLensErrorResponse(new ErrorCollectionFactory().createErrorCollection(), null, "testid"); + assertEquals(e.getErrorCode(), + LensCubeErrorCode.NO_FACT_HAS_COLUMN.getLensErrorInfo().getErrorCode()); + assertTrue(e.getMessage().contains("msr11"), e.getMessage()); + assertTrue(e.getMessage().contains("msr2"), e.getMessage()); // no fact has the all the dimensions queried e = getLensExceptionInRewrite("select dim1, test_time_dim, msr3, msr13 from basecube where " + TWO_DAYS_RANGE, conf); assertEquals(e.getErrorCode(), LensCubeErrorCode.NO_CANDIDATE_FACT_AVAILABLE.getLensErrorInfo().getErrorCode()); - // TODO union : Commented below line. With the new changes We are keeping only one - // TODO union : datastrucucture for candidates. Hence pruning candidateSet using Candidate is not happening. - // TODO union : Exception is thrown in later part of rewrite. NoCandidateFactAvailableException ne = (NoCandidateFactAvailableException) e; - PruneCauses.BriefAndDetailedError pruneCauses = ne.getJsonMessage(); - String regexp = String.format(CandidateTablePruneCause.CandidateTablePruneCode.COLUMN_NOT_FOUND.errorFormat, - "Column Sets: (.*?)", "queriable together"); - Matcher matcher = Pattern.compile(regexp).matcher(pruneCauses.getBrief()); - assertTrue(matcher.matches(), pruneCauses.getBrief()); - assertEquals(matcher.groupCount(), 1); - String columnSetsStr = matcher.group(1); - assertNotEquals(columnSetsStr.indexOf("test_time_dim"), -1, columnSetsStr); - assertNotEquals(columnSetsStr.indexOf("msr3, msr13"), -1); - - /** - * Verifying the BriefAndDetailedError: - * 1. Check for missing columns(COLUMN_NOT_FOUND) - * and check the respective tables for each COLUMN_NOT_FOUND - * 2. check for ELEMENT_IN_SET_PRUNED - * - */ - boolean columnNotFound = false; - List<String> testTimeDimFactTables = Arrays.asList("c1_testfact3_raw_base", - "c1_testfact5_base", "c1_testfact6_base", "c1_testfact1_raw_base", - "c1_testfact4_raw_base", "c1_testfact3_base"); - List<String> factTablesForMeasures = Arrays.asList( - "c2_testfact2_base","c2_testfact_deprecated","c1_union_join_ctx_fact1","c1_union_join_ctx_fact2", - "c1_union_join_ctx_fact3","c1_union_join_ctx_fact5","c1_testfact2_base", - "c1_union_join_ctx_fact6","c1_testfact2_raw_base","c1_testfact5_raw_base", - "c3_testfact_deprecated","c1_testfact_deprecated","c4_testfact_deprecated", - "c3_testfact2_base","c4_testfact2_base"); - for (Map.Entry<String, List<CandidateTablePruneCause>> entry : pruneCauses.getDetails().entrySet()) { - if (entry.getValue().contains(CandidateTablePruneCause.columnNotFound("test_time_dim"))) { - columnNotFound = true; - compareStrings(testTimeDimFactTables, entry); - } - if (entry.getValue().contains(CandidateTablePruneCause.columnNotFound("msr3", "msr13"))) { - columnNotFound = true; - compareStrings(factTablesForMeasures, entry); - } - } - Assert.assertTrue(columnNotFound); + //ne.briefAndDetailedError.getBriefCause() + //ne.getJsonMessage().brief + assertTrue(CandidateTablePruneCode.UNSUPPORTED_STORAGE.errorFormat.equals(ne.getJsonMessage().getBrief())); +// PruneCauses.BriefAndDetailedError pruneCauses = ne.getJsonMessage(); +// String regexp = String.format(CandidateTablePruneCode.UNSUPPORTED_STORAGE.errorFormat, +// "Column Sets: (.*?)", "queriable together"); +// Matcher matcher = Pattern.compile(regexp).matcher(pruneCauses.getBrief()); +// assertTrue(matcher.matches(), pruneCauses.getBrief()); +// assertEquals(matcher.groupCount(), 1); +// String columnSetsStr = matcher.group(1); +// assertNotEquals(columnSetsStr.indexOf("test_time_dim"), -1, columnSetsStr); +// assertNotEquals(columnSetsStr.indexOf("msr3, msr13"), -1); +// +// /** +// * Verifying the BriefAndDetailedError: +// * 1. Check for missing columns(COLUMN_NOT_FOUND) +// * and check the respective tables for each COLUMN_NOT_FOUND +// * 2. check for ELEMENT_IN_SET_PRUNED +// * +// */ +// boolean columnNotFound = false; +// List<String> testTimeDimFactTables = Arrays.asList("c1_testfact3_raw_base", +// "c1_testfact5_base", "c1_testfact6_base", "c1_testfact1_raw_base", +// "c1_testfact4_raw_base", "c1_testfact3_base"); +// List<String> factTablesForMeasures = Arrays.asList( +// "c2_testfact2_base","c2_testfact_deprecated","c1_union_join_ctx_fact1","c1_union_join_ctx_fact2", +// "c1_union_join_ctx_fact3","c1_union_join_ctx_fact5","c1_testfact2_base", +// "c1_union_join_ctx_fact6","c1_testfact2_raw_base","c1_testfact5_raw_base", +// "c3_testfact_deprecated","c1_testfact_deprecated","c4_testfact_deprecated", +// "c3_testfact2_base","c4_testfact2_base"); +// for (Map.Entry<String, List<CandidateTablePruneCause>> entry : pruneCauses.getDetails().entrySet()) { +// if (entry.getValue().contains(CandidateTablePruneCause.columnNotFound( +// CandidateTablePruneCode.COLUMN_NOT_FOUND, "test_time_dim"))) { +// columnNotFound = true; +// compareStrings(testTimeDimFactTables, entry); +// } +// if (entry.getValue().contains(CandidateTablePruneCause.columnNotFound( +// CandidateTablePruneCode.COLUMN_NOT_FOUND, "msr3", "msr13"))) { +// columnNotFound = true; +// compareStrings(factTablesForMeasures, entry); +// } +// } +// Assert.assertTrue(columnNotFound); // assertEquals(pruneCauses.getDetails().get("testfact1_base"), // Arrays.asList(new CandidateTablePruneCause(CandidateTablePruneCode.ELEMENT_IN_SET_PRUNED))); } @@ -190,72 +188,64 @@ public class TestBaseCubeQueries extends TestQueryRewrite { " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); compareQueries(hqlQuery, expected); } - // TODO union : Fix after CandidateFact deleted - /* + @Test public void testMultiFactQueryWithNoDimensionsSelected() throws Exception { CubeQueryContext ctx = rewriteCtx("select roundedmsr2, msr12 from basecube" + " where " + TWO_DAYS_RANGE, conf); - Set<String> candidateFacts = new HashSet<String>(); - for (CandidateFact cfact : ctx.getCandidateFacts()) { - candidateFacts.add(cfact.getName().toLowerCase()); + Set<String> storageCandidates = new HashSet<String>(); + Set<StorageCandidate> scSet = CandidateUtil.getStorageCandidates(ctx.getCandidates()); + for (StorageCandidate sc : scSet) { + storageCandidates.add(sc.getName()); } - Assert.assertTrue(candidateFacts.contains("testfact1_base")); - Assert.assertTrue(candidateFacts.contains("testfact2_base")); + Assert.assertTrue(storageCandidates.contains("c1_testfact1_base")); + Assert.assertTrue(storageCandidates.contains("c1_testfact2_base")); String hqlQuery = ctx.toHQL(); String expected1 = - getExpectedQuery(cubeName, "select sum(basecube.msr12) as `msr12` FROM ", null, + getExpectedQuery(cubeName, "SELECT sum(0.0) as `alias0`, sum((basecube.msr12)) as `alias1` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = - getExpectedQuery(cubeName, "select round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, + getExpectedQuery(cubeName, "SELECT sum((basecube.msr2)) as `alias0`, sum(0.0) as `alias1` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select mq2.roundedmsr2 roundedmsr2, mq1.msr12 msr12 from ") - || lower.startsWith("select mq1.roundedmsr2 roundedmsr2, mq2.msr12 msr12 from "), hqlQuery); - assertTrue(lower.contains("mq1 full outer join") && lower.endsWith("mq2"), hqlQuery); - assertFalse(lower.contains("mq2 on"), hqlQuery); - assertFalse(lower.contains("<=>"), hqlQuery); + assertTrue(lower.startsWith("select round((sum((basecube.alias0)) / 1000)) as `roundedmsr2`, " + + "sum((basecube.alias1)) as `msr12` from "), hqlQuery); + assertFalse(lower.contains("UNION ALL"), hqlQuery); } -*/ - // TODO union : Fix after CandidateFact deleted - /* @Test public void testMoreThanTwoFactQueryWithNoDimensionsSelected() throws Exception { CubeQueryContext ctx = rewriteCtx("select roundedmsr2, msr14, msr12 from basecube" + " where " + TWO_DAYS_RANGE, conf); - Set<String> candidateFacts = new HashSet<String>(); - for (CandidateFact cfact : ctx.getCandidateFacts()) { - candidateFacts.add(cfact.getName().toLowerCase()); + Set<String> storageCandidates = new HashSet<String>(); + Set<StorageCandidate> scSet = CandidateUtil.getStorageCandidates(ctx.getCandidates()); + for (StorageCandidate sc : scSet) { + storageCandidates.add(sc.getName()); } - Assert.assertEquals(candidateFacts.size(), 3); - Assert.assertTrue(candidateFacts.contains("testfact1_base")); - Assert.assertTrue(candidateFacts.contains("testfact2_base")); - Assert.assertTrue(candidateFacts.contains("testfact3_base")); + Assert.assertEquals(storageCandidates.size(), 3); + Assert.assertTrue(storageCandidates.contains("c1_testfact1_base")); + Assert.assertTrue(storageCandidates.contains("c1_testfact2_base")); + Assert.assertTrue(storageCandidates.contains("c1_testfact3_base")); String hqlQuery = ctx.toHQL(); - String expected1 = getExpectedQuery(cubeName, "select sum(basecube.msr12) as `msr12` FROM ", null, null, + String expected1 = getExpectedQuery(cubeName, "SELECT sum(0.0) as `alias0`, count(0.0) as `alias1`, " + + "sum((basecube.msr12)) as `alias2` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); - String expected2 = getExpectedQuery(cubeName, "select round(sum(basecube.msr2)/1000) as `roundedmsr2` FROM ", null, + String expected2 = getExpectedQuery(cubeName, "SELECT sum((basecube.msr2)) as `alias0`, count(0.0) as `alias1`, " + + "sum(0.0) as `alias2` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); - String expected3 = getExpectedQuery(cubeName, "select count((basecube.msr14)) as `msr14` FROM ", null, null, + String expected3 = getExpectedQuery(cubeName, "SELECT sum(0.0) as `alias0`, count((basecube.msr14)) as `alias1`, " + + "sum(0.0) as `alias2` FROM ", null, null, getWhereForDailyAndHourly2days(cubeName, "C1_testFact3_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); compareContains(expected3, hqlQuery); String lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select mq1.roundedmsr2 roundedmsr2, mq3.msr14 msr14, mq2.msr12 msr12 from ") || lower - .startsWith("select mq3.roundedmsr2 roundedmsr2, mq1.msr14 msr14, mq2.msr12 msr12 from ") || lower - .startsWith("select mq2.roundedmsr2 roundedmsr2, mq3.msr14 msr14, mq1.msr12 msr12 from ") || lower - .startsWith("select mq3.roundedmsr2 roundedmsr2, mq2.msr14 msr14, mq1.msr12 msr12 from ") || lower - .startsWith("select mq1.roundedmsr2 roundedmsr2, mq2.msr14 msr14, mq3.msr12 msr12 from ") || lower - .startsWith("select mq2.roundedmsr2 roundedmsr2, mq1.msr14 msr14, mq3.msr12 msr12 from "), hqlQuery); - assertTrue(lower.contains("mq1 full outer join") && lower.endsWith("mq3")); - assertFalse(lower.contains("mq3 on"), hqlQuery); - assertFalse(lower.contains("mq2 on"), hqlQuery); - assertFalse(lower.contains("<=>"), hqlQuery); + assertTrue(lower.startsWith("select round((sum((basecube.alias0)) / 1000)) as `roundedmsr2`, " + + "count((basecube.alias1)) as `msr14`, sum((basecube.alias2)) as `msr12` from"), hqlQuery); + assertTrue(lower.contains("union all")); } -*/ + @Test public void testMultiFactQueryWithSingleCommonDimension() throws Exception { String hqlQuery = rewrite("select dim1, roundedmsr2, msr12 from basecube" + " where " + TWO_DAYS_RANGE, conf); @@ -281,8 +271,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { Configuration tConf = new Configuration(conf); 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) as `alias0`, sum(0.0) as `alias1`, " + - "sum((basecube.msr12)) as `alias2` FROM ", null, " group by basecube.dim1", + String expected1 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, " + + "sum((basecube.msr12)) as `alias2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum((basecube.msr2)) " + "as `alias1`, sum(0.0) as `alias2` FROM ", null, " group by basecube.dim1", @@ -290,8 +280,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select (basecube.alias0) as `dim1`, round((sum((basecube.alias1)) / 1000)) " + - "as `roundedmsr2`, sum((basecube.alias2)) as `msr12` from"), hqlQuery); + assertTrue(lower.startsWith("select (basecube.alias0) as `dim1`, round((sum((basecube.alias1)) / 1000)) " + + "as `roundedmsr2`, sum((basecube.alias2)) as `msr12` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); } @@ -354,8 +344,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + "sum((basecube.msr2)) as `alias3`, max(0.0) as `alias4`, max((basecube.msr3)) as `alias5` FROM ", null, " group by basecube.dim1, (basecube.d_time)", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); String expected3 = - getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, (basecube.d_time) as `alias1`, sum(0.0) " + - "as `alias2`, sum(0.0) as `alias3`, max((basecube.msr13)) as `alias4`, max(0.0) as `alias5` FROM ", null, + getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, (basecube.d_time) as `alias1`, sum(0.0) " + + "as `alias2`, sum(0.0) as `alias3`, max((basecube.msr13)) as `alias4`, max(0.0) as `alias5` FROM ", null, " group by basecube.dim1, (basecube.d_time)", getWhereForDailyAndHourly2days(cubeName, "c1_testfact3_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); @@ -403,8 +393,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( - "select (basecube.alias0) as `dim1`, (basecube.alias1) as `msr11`, " + - "(basecube.alias2) as `roundedmsr2` from"), hqlQuery); + "select (basecube.alias0) as `dim1`, (basecube.alias1) as `msr11`, " + + "(basecube.alias2) as `roundedmsr2` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("as basecube"), hqlQuery); } @@ -463,8 +453,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + "sum(0.0) as `alias2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = - getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, sum((basecube.msr2)) " + - "as `alias2` FROM ", null, " group by basecube.dim1", + getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, sum((basecube.msr2)) " + + "as `alias2` FROM ", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); @@ -486,14 +476,14 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + "sum(0.0) as `alias2` FROM", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); String expected2 = - getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, sum((basecube.msr2)) " + - "as `alias2` FROM", null, + getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, sum((basecube.msr2)) " + + "as `alias2` FROM", null, " group by basecube.dim1", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( - "select (basecube.alias0) as `d1`, sum((basecube.alias1)) as `my msr12`, round((sum((basecube.alias2)) / 1000)) " + - "as `roundedmsr2` from"), hqlQuery); + "select (basecube.alias0) as `d1`, sum((basecube.alias1)) as `my msr12`, " + + "round((sum((basecube.alias2)) / 1000)) as `roundedmsr2` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); } @@ -515,7 +505,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `reverse(dim1)`, (basecube.alias1) " - + "as `ltrim(dim1)`, sum((basecube.alias2)) as `msr12`, round((sum((basecube.alias3)) / 1000)) as `roundedmsr2` from"), + + "as `ltrim(dim1)`, sum((basecube.alias2)) as `msr12`, round((sum((basecube.alias3)) / 1000)) " + + "as `roundedmsr2` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0), (basecube.alias1)"), hqlQuery); @@ -553,8 +544,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + " avg(0.0) as `alias2` FROM ", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact2_raw_base")); String expected2 = - getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, avg(0.0) as `alias1`, avg((basecube.msr2)) " + - "as `alias2` FROM ", null, " group by basecube.dim1", + getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, avg(0.0) as `alias1`, avg((basecube.msr2)) " + + "as `alias2` FROM ", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); @@ -615,8 +606,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { // query with denorm variable String hqlQuery = rewrite("select dim2, msr13, roundedmsr2 from basecube where dim2 == 10 and " + TWO_DAYS_RANGE, conf); - String expected1 = getExpectedQuery(cubeName, "SELECT (dim2chain.id) as `alias0`, max((basecube.msr13)) " + - "as `alias1`, sum(0.0) as `alias2` FROM ", " JOIN " + getDbName() + String expected1 = getExpectedQuery(cubeName, "SELECT (dim2chain.id) as `alias0`, max((basecube.msr13)) " + + "as `alias1`, sum(0.0) as `alias2` FROM ", " JOIN " + getDbName() + "c1_testdim2tbl dim2chain ON basecube.dim12 = " + " dim2chain.id and (dim2chain.dt = 'latest') ", "dim2chain.id == 10", " group by dim2chain.id", null, getWhereForHourly2days(cubeName, "C1_testFact3_RAW_BASE")); @@ -627,12 +618,12 @@ public class TestBaseCubeQueries extends TestQueryRewrite { compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( - "select (basecube.alias0) as `dim2`, max((basecube.alias1)) as `msr13`, " + - "round((sum((basecube.alias2)) / 1000)) as `roundedmsr2` from"), hqlQuery); + "select (basecube.alias0) as `dim2`, max((basecube.alias1)) as `msr13`, " + + "round((sum((basecube.alias2)) / 1000)) as `roundedmsr2` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); } - //TODO union : Wrong fact picked + @Test public void testMultiFactQueryWithExpressionInvolvingDenormVariable() throws Exception { // query with expression @@ -642,8 +633,8 @@ 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 ((dim2chain.id) != 10)) as `alias0`, " + - "sum(0.0) as `alias1`, avg(((basecube.msr13) + (basecube.msr14))) as `alias2` FROM ", " JOIN " + getExpectedQuery(cubeName, "SELECT (((basecube.dim1) != 'x') and ((dim2chain.id) != 10)) as `alias0`, " + + "sum(0.0) as `alias1`, avg(((basecube.msr13) + (basecube.msr14))) as `alias2` FROM ", " JOIN " + getDbName() + "c1_testdim2tbl dim2chain ON basecube.dim12 = " + " dim2chain.id and (dim2chain.dt = 'latest') ", null, " group by basecube.dim1 != 'x' AND dim2chain.id != 10", null, @@ -672,8 +663,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { "select booleancut, round(sum(msr2)/1000), avg(msr13 + msr14) from basecube where booleancut == 'true' and " + TWO_DAYS_RANGE, conf); String expected1 = - getExpectedQuery(cubeName, "SELECT (((basecube.dim1) != 'x') and ((dim2chain.id) != 10)) as `alias0`, " + - "sum(0.0) as `alias1`, avg(((basecube.msr13) + (basecube.msr14))) as `alias2` FROM ", " JOIN " + getDbName() + getExpectedQuery(cubeName, "SELECT (((basecube.dim1) != 'x') and ((dim2chain.id) != 10)) as `alias0`, " + + "sum(0.0) as `alias1`, avg(((basecube.msr13) + (basecube.msr14))) as `alias2` FROM ", " JOIN " + getDbName() + "c1_testdim2tbl dim2chain ON basecube.dim12 = " + " dim2chain.id and (dim2chain.dt = 'latest') ", "(basecube.dim1 != 'x' AND dim2chain.id != 10) == true", " group by basecube.dim1 != 'x' AND dim2chain.id != 10", null, @@ -686,9 +677,9 @@ public class TestBaseCubeQueries extends TestQueryRewrite { getWhereForHourly2days(cubeName, "C1_testfact1_raw_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); - assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `booleancut`, " + - "round((sum((basecube.alias1)) / 1000)) as `round((sum(msr2) / 1000))`, " + - "avg((basecube.alias2)) as `avg((msr13 + msr14))` from"), hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `booleancut`, " + + "round((sum((basecube.alias1)) / 1000)) as `round((sum(msr2) / 1000))`, " + + "avg((basecube.alias2)) as `avg((msr13 + msr14))` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); @@ -702,11 +693,11 @@ public class TestBaseCubeQueries extends TestQueryRewrite { rewrite("select booleancut, round(sum(msr2)/1000), msr13 from basecube where " + TWO_DAYS_RANGE, tconf); String expected1 = getExpectedQuery(cubeName, "SELECT (basecube.booleancut) as `alias0`, sum(0.0) as `alias1`, " - + "max((basecube.msr13)) as `alias2` FROM", null, " " + - "group by basecube.booleancut", getWhereForDailyAndHourly2days(cubeName, "C1_testfact6_base")); + + "max((basecube.msr13)) as `alias2` FROM", null, " " + + "group by basecube.booleancut", getWhereForDailyAndHourly2days(cubeName, "C1_testfact6_base")); String expected2 = - getExpectedQuery(cubeName, "SELECT (basecube.booleancut) as `alias0`, sum((basecube.msr2)) as `alias1`, " + - "max(0.0) as `alias2` FROM ", null, " group by basecube.booleancut", + getExpectedQuery(cubeName, "SELECT (basecube.booleancut) as `alias0`, sum((basecube.msr2)) as `alias1`, " + + "max(0.0) as `alias2` FROM ", null, " group by basecube.booleancut", getWhereForDailyAndHourly2days(cubeName, "C1_testfact5_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); @@ -733,8 +724,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { getWhereForHourly2days(cubeName, "c1_testfact1_raw_base")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); - assertTrue(hqlQuery.toLowerCase().startsWith("select sum((basecube.alias0)) as `case_expr`, sum((basecube.alias1)) " - + "as `sum(msr1)` from "), hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select sum((basecube.alias0)) as `case_expr`, " + + "sum((basecube.alias1)) as `sum(msr1)` from "), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("basecube"), hqlQuery); } @@ -766,7 +757,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + "where " + TWO_DAYS_RANGE, tconf); String expected1 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(case when ((basecube.dim13) = 'x') " - + "then (basecube.msr12) else 0 end) as `alias1`, sum(0.0) as `alias2` FROM ", null, " group by basecube.dim1 ", + + "then (basecube.msr12) else 0 end) as `alias1`, sum(0.0) as `alias2` FROM ", null, + " group by basecube.dim1 ", getWhereForHourly2days(cubeName, "C1_testfact2_raw_base")); String expected2 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, sum((basecube.msr1)) " @@ -775,8 +767,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith( - "select (basecube.alias0) as `dim1`, sum((basecube.alias1)) as `case_expr`, " + - "sum((basecube.alias2)) as `sum(msr1)` from"), hqlQuery); + "select (basecube.alias0) as `dim1`, sum((basecube.alias1)) as `case_expr`, " + + "sum((basecube.alias2)) as `sum(msr1)` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); } @@ -787,7 +779,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { String hqlQuery = rewrite("select sum(case when dim13 = 'x' then msr12 else 0 end) as case_expr, sum(msr1) from basecube " + "where " - + TWO_DAYS_RANGE + " having sum(case when dim13 = 'x' then msr12 else 0 end) > 100 and sum(msr1) > 500", tconf); + + TWO_DAYS_RANGE + " having sum(case when dim13 = 'x' then msr12 else 0 end) > 100 " + + "and sum(msr1) > 500", tconf); String expected1 = getExpectedQuery(cubeName, "SELECT sum(case when ((basecube.dim13) = 'x') then (basecube.msr12) else 0 end) " + "as `alias0`, sum(0.0) as `alias1` FROM ", null, "", @@ -808,11 +801,11 @@ public class TestBaseCubeQueries extends TestQueryRewrite { Configuration tconf = new Configuration(conf); String hqlQuery = rewrite("select dim1, sum(case when dim13 = 'x' then msr12 else 0 end) as case_expr, sum(msr1) from basecube " - + "where " - + TWO_DAYS_RANGE + " having sum(case when dim13 = 'x' then msr12 else 0 end) > 100 and sum(msr1) > 500", tconf); + + "where " + TWO_DAYS_RANGE + " having sum(case when dim13 = 'x' then msr12 else 0 end) > 100 " + + "and sum(msr1) > 500", tconf); String expected1 = - getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(case when ((basecube.dim13) = 'x') then " + - "(basecube.msr12) else 0 end) as `alias1`, sum(0.0) as `alias2` FROM", null, " group by basecube.dim1", + getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(case when ((basecube.dim13) = 'x') then " + + "(basecube.msr12) else 0 end) as `alias1`, sum(0.0) as `alias2` FROM", null, " group by basecube.dim1", getWhereForHourly2days(cubeName, "C1_testfact2_raw_base")); String expected2 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, sum(0.0) as `alias1`, sum((basecube.msr1)) " @@ -827,8 +820,6 @@ public class TestBaseCubeQueries extends TestQueryRewrite { && hqlQuery.endsWith("HAVING ((sum((basecube.alias1)) > 100) and (sum((basecube.alias2)) > 500))"), hqlQuery); } - // TODO union : Fix after MaxCoveringSet resolver - /* @Test public void testFallbackPartCol() throws Exception { Configuration conf = getConfWithStorages("C1"); @@ -836,7 +827,7 @@ public class TestBaseCubeQueries extends TestQueryRewrite { String hql, expected; // Prefer fact that has a storage with part col on queried time dim hql = rewrite("select msr12 from basecube where " + TWO_DAYS_RANGE, conf); - expected = getExpectedQuery(BASE_CUBE_NAME, "select sum(basecube.msr12) FROM ", null, null, + expected = getExpectedQuery(BASE_CUBE_NAME, "select sum(basecube.msr12) as `msr12` FROM ", null, null, getWhereForDailyAndHourly2days(BASE_CUBE_NAME, "c1_testfact2_base")); compareQueries(hql, expected); @@ -848,10 +839,10 @@ public class TestBaseCubeQueries extends TestQueryRewrite { NoCandidateFactAvailableException ne = (NoCandidateFactAvailableException) exc; PruneCauses.BriefAndDetailedError pruneCause = ne.getJsonMessage(); assertTrue(pruneCause.getBrief().contains("Missing partitions")); - assertEquals(pruneCause.getDetails().get("testfact2_base").iterator().next().getCause(), MISSING_PARTITIONS); - assertEquals(pruneCause.getDetails().get("testfact2_base").iterator().next().getMissingPartitions().size(), 1); + assertEquals(pruneCause.getDetails().get("c4_testfact2_base").iterator().next().getCause(), MISSING_PARTITIONS); + assertEquals(pruneCause.getDetails().get("c4_testfact2_base").iterator().next().getMissingPartitions().size(), 1); assertEquals( - pruneCause.getDetails().get("testfact2_base").iterator().next().getMissingPartitions().iterator().next(), + pruneCause.getDetails().get("c4_testfact2_base").iterator().next().getMissingPartitions().iterator().next(), "ttd:[" + UpdatePeriod.SECONDLY.format(DateUtils.addDays(DateUtils.truncate(TWODAYS_BACK, Calendar.HOUR), -10)) + ", " + UpdatePeriod.SECONDLY.format(DateUtils.addDays(DateUtils.truncate(NOW, Calendar.HOUR), 10)) @@ -871,7 +862,7 @@ public class TestBaseCubeQueries extends TestQueryRewrite { DateUtils.addDays(TWODAYS_BACK, -5))))) + "' and " + "basecube.processing_time < '" + HIVE_QUERY_DATE_PARSER.get().format(ABSDATE_PARSER.get().parse( getAbsDateFormatString(getDateUptoHours(DateUtils.addDays(NOW, 5))))); - expected = getExpectedQuery(BASE_CUBE_NAME, "select sum(basecube.msr12) FROM ", null, + expected = getExpectedQuery(BASE_CUBE_NAME, "select sum(basecube.msr12) as `msr12` FROM ", null, " and " + dTimeWhereClause + " and " + pTimeWhereClause, getWhereForDailyAndHourly2daysWithTimeDim(BASE_CUBE_NAME, "ttd", DateUtils.addDays(TWODAYS_BACK, -10), DateUtils.addDays(NOW, 10), "c4_testfact2_base")); @@ -880,33 +871,26 @@ public class TestBaseCubeQueries extends TestQueryRewrite { // Multiple timedims in single query. test that CubeQueryContext ctx = rewriteCtx("select msr12 from basecube where " + TWO_DAYS_RANGE + " and " + TWO_DAYS_RANGE_TTD, conf); - assertEquals(ctx.getCandidateFactSets().size(), 1); - assertEquals(ctx.getCandidateFactSets().iterator().next().size(), 1); - CandidateFact cfact = ctx.getCandidateFactSets().iterator().next().iterator().next(); - - assertEquals(cfact.getRangeToStoragePartMap().size(), 2); - Set<String> storages = Sets.newHashSet(); - for(Map<String, String> entry: cfact.getRangeToStorageWhereMap().values()) { - storages.addAll(entry.keySet()); - } - assertEquals(storages.size(), 1); - String storage = storages.iterator().next(); - for(Map.Entry<TimeRange, Map<String, String>> entry: cfact.getRangeToStorageWhereMap().entrySet()) { + assertEquals(ctx.getCandidates().size(), 1); + assertEquals(CandidateUtil.getStorageCandidates(ctx.getCandidates().iterator().next()).size(), 1); + StorageCandidate sc = CandidateUtil.getStorageCandidates(ctx.getCandidates().iterator().next()).iterator().next(); + assertEquals(sc.getRangeToWhere().size(), 2); + for(Map.Entry<TimeRange, String> entry: sc.getRangeToWhere().entrySet()) { if (entry.getKey().getPartitionColumn().equals("dt")) { - ASTNode parsed = HQLParser.parseExpr(entry.getValue().get(storage)); + ASTNode parsed = HQLParser.parseExpr(entry.getValue()); assertEquals(parsed.getToken().getType(), KW_AND); - assertTrue(entry.getValue().get(storage).substring(((CommonToken) parsed.getToken()).getStopIndex() + 1) + assertTrue(entry.getValue().substring(((CommonToken) parsed.getToken()).getStopIndex() + 1) .toLowerCase().contains(dTimeWhereClause)); - assertFalse(entry.getValue().get(storage).substring(0, ((CommonToken) parsed.getToken()).getStartIndex()) + assertFalse(entry.getValue().substring(0, ((CommonToken) parsed.getToken()).getStartIndex()) .toLowerCase().contains("and")); } else if (entry.getKey().getPartitionColumn().equals("ttd")) { - assertFalse(entry.getValue().get(storage).toLowerCase().contains("and")); + assertFalse(entry.getValue().toLowerCase().contains("and")); } else { throw new LensException("Unexpected"); } } } - */ + @Test public void testMultiFactQueryWithHaving() throws Exception { @@ -918,8 +902,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { hqlQuery = rewrite("select dim1, dim11, msr12 from basecube where " + TWO_DAYS_RANGE + "having roundedmsr2 > 0", conf); expected1 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) " + - "as `alias2`, sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) " + + "as `alias2`, sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " @@ -941,8 +925,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + "sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + - "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + + "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); @@ -953,59 +937,12 @@ public class TestBaseCubeQueries extends TestQueryRewrite { assertTrue(hqlQuery.endsWith("HAVING ((sum((basecube.alias2)) > 2) " + "and (round((sum((basecube.alias3)) / 1000)) > 0))")); - // Two having clauses and one complex expression in having which needs to be split over the two facts - // And added as where clause outside - //TODO union : floor is not a valid function. - /* - (((tok_function(sum((basecube.msr12))) + round((sum((basecube.alias3)) / 1000))) <= 1000) - and (sum((basecube.alias2)) > 2) and (round((sum((basecube.alias3)) / 1000)) > 0)) - <= [LESSTHANOREQUALTO] (l3c1p145) { - + [PLUS] (l4c1p132) { - TOK_FUNCTION [TOK_FUNCTION] (l5c1p0) { - TOK_FUNCTION [TOK_FUNCTION] (l6c1p0) { - SUM [Identifier] (l7c1p0)$ - . [DOT] (l7c2p0) { - TOK_TABLE_OR_COL [TOK_TABLE_OR_COL] (l8c1p0) { - basecube [Identifier] (l9c1p0)$ - } - alias2 [Identifier] (l8c2p0)$ - } - } - TOK_FUNCTION [TOK_FUNCTION] (l6c2p0) { - SUM [Identifier] (l7c1p0)$ - . [DOT] (l7c2p0) { - TOK_TABLE_OR_COL [TOK_TABLE_OR_COL] (l8c1p0) { - basecube [Identifier] (l9c1p0)$ - } - msr12 [Identifier] (l8c2p0)$ - } - } - } - TOK_FUNCTION [TOK_FUNCTION] (l5c2p0) { - round [Identifier] (l6c1p0)$ - / [DIVIDE] (l6c2p10) { - TOK_FUNCTION [TOK_FUNCTION] (l7c1p0) { - SUM [Identifier] (l8c1p0)$ - . [DOT] (l8c2p0) { - TOK_TABLE_OR_COL [TOK_TABLE_OR_COL] (l9c1p0) { - basecube [Identifier] (l10c1p0)$ - } - alias3 [Identifier] (l9c2p0)$ - } - } - 1000 [Number] (l7c2p11)$ - } - } - } - 1000 [Number] (l4c2p148)$ - } - */ hqlQuery = rewrite("select dim1, dim11, msr12, roundedmsr2 from basecube where " + TWO_DAYS_RANGE - + "having msr12+roundedmsr2 <= 1000 and msr12 > 2 and roundedmsr2 > 0", conf); - expected1 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " - + "sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", - getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); + + "having msr12+roundedmsr2 <= 1000 and msr12 > 2 and roundedmsr2 > 0", conf); + expected1 = getExpectedQuery(cubeName, + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + + "sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", + getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " @@ -1019,17 +956,16 @@ public class TestBaseCubeQueries extends TestQueryRewrite { assertTrue(hqlQuery.endsWith("(((sum((basecube.alias2)) + round((sum((basecube.alias3)) / 1000))) <= 1000) " + "and (sum((basecube.alias2)) > 2) and (round((sum((basecube.alias3)) / 1000)) > 0))"), hqlQuery); - // TODO union : why?, columns are projected can't be part of having! // No push-down-able having clauses. hqlQuery = rewrite("select dim1, dim11, msr12, roundedmsr2 from basecube where " + TWO_DAYS_RANGE + "having msr12+roundedmsr2 <= 1000", conf); expected1 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + - "sum(0.0) as `alias3` FROM", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + + "sum(0.0) as `alias3` FROM", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, sum((basecube.msr2)) " + - "as `alias3` FROM", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, sum((basecube.msr2)) " + + "as `alias3` FROM", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); @@ -1037,28 +973,29 @@ public class TestBaseCubeQueries extends TestQueryRewrite { assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `dim1`, (basecube.alias1) as `dim11`, " + "sum((basecube.alias2)) as `msr12`, round((sum((basecube.alias3)) / 1000)) as `roundedmsr2` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") - && hqlQuery.endsWith("HAVING ((sum((basecube.alias2)) + " + - "round((sum((basecube.alias3)) / 1000))) <= 1000)"), hqlQuery); + && hqlQuery.endsWith("HAVING ((sum((basecube.alias2)) + " + + "round((sum((basecube.alias3)) / 1000))) <= 1000)"), hqlQuery); // function over expression of two functions over measures hqlQuery = rewrite("select dim1, dim11, msr12, roundedmsr2 from basecube where " + TWO_DAYS_RANGE + "having round(msr12+roundedmsr2) <= 1000", conf); expected1 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + - "sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + + "sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, - " SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + - "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", + " SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + + "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `dim1`, (basecube.alias1) as `dim11`, " - + "sum((basecube.alias2)) as `msr12`, round((sum((basecube.alias3)) / 1000)) as `roundedmsr2` from"), hqlQuery); + + "sum((basecube.alias2)) as `msr12`, round((sum((basecube.alias3)) / 1000)) " + + "as `roundedmsr2` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") - && hqlQuery.endsWith(" HAVING (round((sum((basecube.alias2)) + " + - "round((sum((basecube.alias3)) / 1000)))) <= 1000)"), hqlQuery); + && hqlQuery.endsWith(" HAVING (round((sum((basecube.alias2)) + " + + "round((sum((basecube.alias3)) / 1000)))) <= 1000)"), hqlQuery); // Following test cases only select dimensions, and all the measures are in having. @@ -1072,8 +1009,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { + "sum(0.0) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + - "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + + "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); String begin = "select (basecube.alias0) as `dim1`, (basecube.alias1) as `dim11` from"; compareContains(expected1, hqlQuery); @@ -1086,21 +1023,21 @@ public class TestBaseCubeQueries extends TestQueryRewrite { hqlQuery = rewrite("select dim1, dim11 from basecube where " + TWO_DAYS_RANGE + "having msr12 > 2 and roundedmsr2 > 0 and msr2 > 100", conf); expected1 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + - "sum(0.0) as `alias3`, sum(0.0) as `alias4` FROM ", null, " group by basecube.dim1, basecube.dim11", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + + "sum(0.0) as `alias3`, sum(0.0) as `alias4` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + - "sum((basecube.msr2)) as `alias3`, sum((basecube.msr2)) as `alias4` FROM ", null, + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + + "sum((basecube.msr2)) as `alias3`, sum((basecube.msr2)) as `alias4` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); - assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `dim1`, " + - "(basecube.alias1) as `dim11` from"), hqlQuery); - assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("HAVING ((sum((basecube.alias2)) > 2) and " + - "(round((sum((basecube.alias4)) / 1000)) > 0) and (sum((basecube.alias4)) > 100))"), hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `dim1`, " + + "(basecube.alias1) as `dim11` from"), hqlQuery); + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("HAVING ((sum((basecube.alias2)) > 2) and " + + "(round((sum((basecube.alias4)) / 1000)) > 0) and (sum((basecube.alias4)) > 100))"), hqlQuery); hqlQuery = rewrite("select dim1, dim11 from basecube where " + TWO_DAYS_RANGE + "having msr12+roundedmsr2 <= 1000", conf); @@ -1110,15 +1047,15 @@ public class TestBaseCubeQueries extends TestQueryRewrite { null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + - "sum((basecube.msr2)) as `alias3` FROM ", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum(0.0) as `alias2`, " + + "sum((basecube.msr2)) as `alias3` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact1_BASE")); compareContains(expected1, hqlQuery); compareContains(expected2, hqlQuery); - assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `dim1`, (basecube.alias1) " + - "as `dim11` from"), hqlQuery); + assertTrue(hqlQuery.toLowerCase().startsWith("select (basecube.alias0) as `dim1`, (basecube.alias1) " + + "as `dim11` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("HAVING ((sum((basecube.alias2)) + round((sum((basecube.alias3)) / 1000))) <= 1000)"), hqlQuery); @@ -1148,8 +1085,8 @@ public class TestBaseCubeQueries extends TestQueryRewrite { hqlQuery = rewrite("select dim1, dim11 from basecube where " + TWO_DAYS_RANGE + "having msr12 > 2 or roundedmsr2 > 0 or msr12+roundedmsr2 <= 1000", conf); expected1 = getExpectedQuery(cubeName, - "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + - "sum(0.0) as `alias3`, sum((basecube.msr12)) as `alias4`, sum(0.0) as `alias5` FROM ", + "SELECT (basecube.dim1) as `alias0`, (basecube.dim11) as `alias1`, sum((basecube.msr12)) as `alias2`, " + + "sum(0.0) as `alias3`, sum((basecube.msr12)) as `alias4`, sum(0.0) as `alias5` FROM ", null, " group by basecube.dim1, basecube.dim11", getWhereForDailyAndHourly2days(cubeName, "C1_testFact2_BASE")); expected2 = getExpectedQuery(cubeName,
http://git-wip-us.apache.org/repos/asf/lens/blob/975fa2c2/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 76618a7..2bf1ef8 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 @@ -295,18 +295,22 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + "as `xsports`, (yusersports.balias0) as `ysports`, sum((basecube.msr2)) as `sum(msr2)` FROM ", " join " + getDbName() + "c1_usertable userdim_1 on basecube.userid = userdim_1.id " + " join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as balias0 from " - + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + "c1_sports_tbl usersports on " + + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + + "c1_sports_tbl usersports on " + "user_interests_1.sport_id = usersports.id group by user_interests_1.user_id) " + "usersports on userdim_1.id = usersports.user_id" + " join " + getDbName() + "c1_usertable userdim_0 on basecube.yuserid = userdim_0.id " + " join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as balias0 from " - + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + "c1_sports_tbl yusersports on " - + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) yusersports on userdim_0.id =" + + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + + "c1_sports_tbl yusersports on user_interests_0.sport_id = yusersports.id group by " + + "user_interests_0.user_id) yusersports on userdim_0.id =" + " yusersports.user_id join " + getDbName() + "c1_usertable userdim on basecube.xuserid = userdim.id" + " join (select user_interests.user_id as user_id,collect_set(xusersports.name) as balias0 from " - + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" - + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) xusersports on userdim.id = " - + " xusersports.user_id", null, "group by usersports.balias0, xusersports.balias0, yusersports.balias0", null, + + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + + "c1_sports_tbl xusersports on user_interests.sport_id = xusersports.id " + + "group by user_interests.user_id) xusersports on userdim.id = " + + " xusersports.user_id", + null, "group by usersports.balias0, xusersports.balias0, yusersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -343,20 +347,26 @@ public class TestBridgeTableQueries extends TestQueryRewrite { expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, (xusersports.balias0) " + "as `xsports`, (yusersports.balias0) as `ysports`, sum((basecube.msr2)) as `sum(msr2)` FROM ", " left outer join " + getDbName() + "c1_usertable userdim_1 on basecube.userid = userdim_1.id " - + " left outer join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as balias0 from " - + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + "c1_sports_tbl usersports on " + + " left outer join (select user_interests_1.user_id as user_id, " + + "collect_set(usersports.name) as balias0 from " + + getDbName() + "c1_user_interests_tbl user_interests_1 join " + + getDbName() + "c1_sports_tbl usersports on " + "user_interests_1.sport_id = usersports.id group by user_interests_1.user_id) " + "usersports on userdim_1.id = usersports.user_id" + " left outer join " + getDbName() + "c1_usertable userdim_0 on basecube.yuserid = userdim_0.id " - + " left outer join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as balias0 from " - + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + "c1_sports_tbl yusersports on " - + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) yusersports on userdim_0.id =" - + " yusersports.user_id left outer join " + getDbName() + + " left outer join (select user_interests_0.user_id as user_id," + + "collect_set(yusersports.name) as balias0 from " + + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + + "c1_sports_tbl yusersports on " + + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) " + + "yusersports on userdim_0.id = yusersports.user_id left outer join " + getDbName() + "c1_usertable userdim on basecube.xuserid = userdim.id" - + " left outer join (select user_interests.user_id as user_id,collect_set(xusersports.name) as balias0 from " - + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" - + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) xusersports on userdim.id = " - + " xusersports.user_id", null, "group by usersports.balias0, xusersports.balias0, yusersports.balias0", null, + + " left outer join (select user_interests.user_id as user_id," + + "collect_set(xusersports.name) as balias0 from " + getDbName() + + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" + + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) " + + "xusersports on userdim.id = xusersports.user_id", null, + "group by usersports.balias0, xusersports.balias0, yusersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -368,8 +378,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, sum((basecube.msr2)) " + - "as `sum(msr2)` FROM ", " join " + getDbName() + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, sum((basecube.msr2)) " + + "as `sum(msr2)` FROM ", " join " + getDbName() + "c2_usertable userdim ON basecube.userid = userdim.id and userdim.dt='latest' " + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c2_user_interests_tbl user_interests" @@ -382,8 +392,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; - expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, sum((basecube.msr2)) " + - "as `sum(msr2)` FROM ", " join " + getDbName() + expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, sum((basecube.msr2)) " + + "as `sum(msr2)` FROM ", " join " + getDbName() + "c2_usertable userdim ON basecube.userid = userdim.id and userdim.dt='latest' " + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as balias0" + " from " + getDbName() + "c2_user_interests_tbl user_interests" @@ -415,7 +425,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + "c1_statetable statedim_0 on citydim.stateid=statedim_0.id and statedim_0.dt='latest'" + " join " + getDbName() + "c1_countrytable cubecitystatecountry on statedim_0.countryid=cubecitystatecountry.id" - + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id and (statedim.dt = 'latest')" + + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id " + + "and (statedim.dt = 'latest')" + " join " + getDbName() + "c1_countrytable cubestatecountry on statedim.countryid=cubestatecountry.id ", null, "group by usersports.balias0, cubestatecountry.name, cubecitystatecountry.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); @@ -431,12 +442,13 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " 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 " - + " join " + getDbName() + "c1_citytable citydim on basecube.cityid = citydim.id and (citydim.dt = 'latest')" - + " join " + getDbName() + + " join " + getDbName() + "c1_citytable citydim on basecube.cityid = citydim.id " + + "and (citydim.dt = 'latest') join " + getDbName() + "c1_statetable statedim_0 on citydim.stateid=statedim_0.id and statedim_0.dt='latest'" + " join " + getDbName() + "c1_countrytable cubecitystatecountry on statedim_0.countryid=cubecitystatecountry.id" - + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id and (statedim.dt = 'latest')" + + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id " + + "and (statedim.dt = 'latest')" + " join " + getDbName() + "c1_countrytable cubestatecountry on statedim.countryid=cubestatecountry.id ", null, "group by usersports.balias0, cubestatecountry.name, cubecitystatecountry.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); @@ -503,25 +515,26 @@ public class TestBridgeTableQueries extends TestQueryRewrite { @Test public void testBridgeTablesWithFilterAndOrderby() throws Exception { String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE - + " and usersports.name = 'CRICKET' order by usersports.name"; + + " and usersports.name = 'CRICKET' order by usersports.name"; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, sum((basecube.msr2)) " + - "as `sum(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 balias0" - + " 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, - " and array_contains(usersports.balias0, 'CRICKET') group by usersports.balias0 order by usersports.balias0 asc", - null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, sum((basecube.msr2)) " + + "as `sum(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 balias0" + + " 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, + " and array_contains(usersports.balias0, 'CRICKET') group by usersports.balias0 " + + "order by name asc", + null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET' order by " - + "sports"; - expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, sum((basecube.msr2)) " + - "as `sum(msr2)` FROM ", " join " + + "sports"; + expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, sum((basecube.msr2)) " + + "as `sum(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 balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" @@ -529,7 +542,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - " and array_contains(usersports.balias0, 'CRICKET') group by usersports.balias0 order by usersports.balias0 asc", + " and array_contains(usersports.balias0, 'CRICKET') group by usersports.balias0 " + + "order by sports asc", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -664,8 +678,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select (basecube.alias0) as `sports`, sum((basecube.alias1)) as `msr2`, " + - "sum((basecube.alias2)) as `msr12` from"), hqlQuery); + assertTrue(lower.startsWith("select (basecube.alias0) as `sports`, sum((basecube.alias1)) as `msr2`, " + + "sum((basecube.alias2)) as `msr12` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); @@ -760,7 +774,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + "sum((basecube.msr2)) as `sum(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 " + getDbName() + "c1_user_interests_tbl user_interests" + + " collect_set(( usersports . name )) as balias1 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 ", @@ -789,7 +804,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column - query = "select sports_abbr, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET,FOOTBALL'"; + query = "select sports_abbr, sum(msr2) from basecube where " + TWO_DAYS_RANGE + + " and sports = 'CRICKET,FOOTBALL'"; expected = getExpectedQuery("basecube", "SELECT substr((usersports.name), 3) as " + "`sports_abbr`, sum((basecube.msr2)) as `sum(msr2)` FROM ", " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " @@ -963,8 +979,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select (basecube.alias0) as `substr((usersports.name), 3)`, " + - "sum((basecube.alias1)) as `msr2`, sum((basecube.alias2)) as `msr12` from"), + assertTrue(lower.startsWith("select (basecube.alias0) as `substr((usersports.name), 3)`, " + + "sum((basecube.alias1)) as `msr2`, sum((basecube.alias2)) as `msr12` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), @@ -997,8 +1013,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); assertTrue(lower.startsWith( - "select (basecube.alias0) as `sports_abbr`, sum((basecube.alias1)) as `msr2`, " + - "sum((basecube.alias2)) as `msr12` from"), hqlQuery); + "select (basecube.alias0) as `sports_abbr`, sum((basecube.alias1)) as `msr2`, " + + "sum((basecube.alias2)) as `msr12` from"), hqlQuery); assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); @@ -1046,7 +1062,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { " join " + getDbName() + "c1_usertable userchain ON basecube.userid = userchain.id " + " join ( select userinterestids.user_id as user_id, collect_set(userinterestids.sport_id) as balias0," + " collect_set(userinterestids.user_id) as balias1 from " + getDbName() + "c1_user_interests_tbl " - + " userinterestids group by userinterestids.user_id) userinterestids on userchain.id = userinterestids.user_id" + + " userinterestids group by userinterestids.user_id) userinterestids " + + "on userchain.id = userinterestids.user_id" + " join (select userinterestids.user_id as user_id, collect_set(usersports . id) as balias0 from" + getDbName() + " c1_user_interests_tbl userinterestids join " + getDbName() + "c1_sports_tbl" + " usersports on userinterestids.sport_id = usersports.id group by userinterestids.user_id) usersports"
