http://git-wip-us.apache.org/repos/asf/lens/blob/ae83caae/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBetweenTimeRangeWriter.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBetweenTimeRangeWriter.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBetweenTimeRangeWriter.java index cabb95e..5c57781 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBetweenTimeRangeWriter.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBetweenTimeRangeWriter.java @@ -19,6 +19,8 @@ package org.apache.lens.cube.parse; +import static java.util.Optional.*; + import static org.apache.lens.cube.metadata.DateFactory.*; import static org.apache.lens.cube.metadata.UpdatePeriod.DAILY; @@ -37,19 +39,19 @@ import org.testng.annotations.Test; public class TestBetweenTimeRangeWriter extends TestTimeRangeWriter { - public static final String CLOSED = "CLOSED"; - public static final String OPEN = "OPEN"; + private static final String CLOSED = "CLOSED"; + private static final String OPEN = "OPEN"; - public static final int START_DATE_OFFSET = 1; - public static final int END_DATE_OFFSET = 2; + private static final int START_DATE_OFFSET = 1; + private static final int END_DATE_OFFSET = 2; - public static final DateFormat DAY_DB_FORMAT = new SimpleDateFormat("yyyy-MM-dd"); + private static final DateFormat DAY_DB_FORMAT = new SimpleDateFormat("yyyy-MM-dd"); private static Set<FactPartition> answeringParts; private static Set<FactPartition> answeringPartsWithFormat; static { - answeringParts = new LinkedHashSet<FactPartition>(); + answeringParts = new LinkedHashSet<>(); answeringParts.add(new FactPartition("dt", getDateWithOffset(DAILY, START_DATE_OFFSET), DAILY, null, null)); answeringParts.add(new FactPartition("dt", getDateWithOffset(DAILY, END_DATE_OFFSET), DAILY, null, null)); @@ -108,7 +110,7 @@ public class TestBetweenTimeRangeWriter extends TestTimeRangeWriter { } - public void validateBetweenOnlySingle(String whereClause, DateFormat format) { + private void validateBetweenOnlySingle(String whereClause, DateFormat format) { String expected = null; if (format == null) { expected = @@ -121,22 +123,7 @@ public class TestBetweenTimeRangeWriter extends TestTimeRangeWriter { @DataProvider public Object[][] getBoundTypes() { - - Object[][] data = new Object[4][2]; - - data[0][0] = OPEN; - data[0][1] = OPEN; - - data[1][0] = OPEN; - data[1][1] = CLOSED; - - data[2][0] = CLOSED; - data[2][1] = OPEN; - - data[3][0] = CLOSED; - data[3][1] = CLOSED; - - return data; + return new Object[][]{{OPEN, OPEN}, {OPEN, CLOSED}, {CLOSED, OPEN}, {CLOSED, CLOSED}}; } @Test(dataProvider = "getBoundTypes") @@ -148,10 +135,10 @@ public class TestBetweenTimeRangeWriter extends TestTimeRangeWriter { int testStartOffset = START_DATE_OFFSET; int testEndOffset = END_DATE_OFFSET; - if (startBoundType.equals(OPEN)) { + if (startBoundType.equalsIgnoreCase(OPEN)) { testStartOffset = START_DATE_OFFSET - 1; } - if (endBoundType.equals(OPEN)) { + if (endBoundType.equalsIgnoreCase(OPEN)) { testEndOffset = END_DATE_OFFSET + 1; } validateBetweenBoundTypes(whereClause, null, testStartOffset, testEndOffset); @@ -163,18 +150,11 @@ public class TestBetweenTimeRangeWriter extends TestTimeRangeWriter { validateBetweenBoundTypes(whereClause, DAY_DB_FORMAT, testStartOffset, testEndOffset); } + private void validateBetweenBoundTypes(String whereClause, DateFormat format, - int testStartOffset, int testEndOffset) { - String expected = null; - if (format == null) { - expected = - getBetweenClause("test", "dt", getDateWithOffset(DAILY, testStartOffset), - getDateWithOffset(DAILY, testEndOffset), DAILY.format()); - } else { - expected = - getBetweenClause("test", "dt", getDateWithOffset(DAILY, testStartOffset), - getDateWithOffset(DAILY, testEndOffset), format); - } + int testStartOffset, int testEndOffset) { + String expected = getBetweenClause("test", "dt", getDateWithOffset(DAILY, testStartOffset), + getDateWithOffset(DAILY, testEndOffset), ofNullable(format).orElseGet(DAILY::format)); Assert.assertEquals(expected, whereClause); } }
http://git-wip-us.apache.org/repos/asf/lens/blob/ae83caae/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 2f00244..9fbeb41 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 @@ -48,7 +48,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesWithoutDimtablePartitioning() throws Exception { String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + 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" @@ -60,6 +61,16 @@ 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() + "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, "group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -68,8 +79,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesForExprFieldWithoutDimtablePartitioning() throws Exception { String query = "select substr(usersports.name, 10), sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `substr((usersports.name), 10)`, " + + "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, 10)) as balias0" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" @@ -81,6 +93,16 @@ public class TestBridgeTableQueries extends TestQueryRewrite { // run with chain ref column query = "select substrsprorts, sum(msr2) from basecube where " + TWO_DAYS_RANGE; hqlQuery = rewrite(query, hConf); + expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `substrsprorts`, " + + "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, 10)) 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, "group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -90,8 +112,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { conf.setBoolean(CubeQueryConfUtil.ENABLE_FLATTENING_FOR_BRIDGETABLES, false); String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + String expected = getExpectedQuery("basecube", "SELECT (usersports.name) as `name`, sum((basecube.msr2)) " + + "as `sum(msr2)` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + " join " + getDbName() + "c1_user_interests_tbl user_interests on userdim.id = user_interests.user_id" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id", null, "group by usersports.name", null, @@ -99,6 +122,13 @@ 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.name) as `sports`, sum((basecube.msr2)) " + + "as `sum(msr2)` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join " + getDbName() + "c1_user_interests_tbl user_interests on userdim.id = user_interests.user_id" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id", + null, "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -109,8 +139,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { conf.set(CubeQueryConfUtil.BRIDGE_TABLE_FIELD_AGGREGATOR, "custom_aggr"); 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, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + 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,custom_aggr(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" @@ -121,6 +152,16 @@ 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() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,custom_aggr(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, "group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -129,8 +170,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { public void testBridgeTablesWithMegringChains() throws Exception { String query = "select userInterestIds.sport_id, usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select userInterestIds.balias0, usersports.balias0," - + " sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "SELECT (userinterestids.balias0) as `sport_id`, " + + "(usersports.balias0) as `name`, sum((basecube.msr2)) as `sum(msr2)` FROM ", " join " + getDbName() + "c1_usertable userdim on basecube.userid = userdim.id join (select userinterestids" + ".user_id as user_id,collect_set(userinterestids.sport_id) as balias0 from " + getDbName() + "c1_user_interests_tbl userinterestids group by userinterestids.user_id) userinterestids on userdim.id = " @@ -144,6 +185,18 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sportids, sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + expected = getExpectedQuery("basecube", "SELECT (userinterestids.balias0) as `sportids`, " + + "(usersports.balias0) as `sports`, sum((basecube.msr2)) as `sum(msr2)` FROM ", + " join " + getDbName() + "c1_usertable userdim on basecube.userid = userdim.id join (select userinterestids" + + ".user_id as user_id,collect_set(userinterestids.sport_id) as balias0 from " + getDbName() + + "c1_user_interests_tbl userinterestids group by userinterestids.user_id) userinterestids on userdim.id = " + + "userinterestids.user_id " + + "join (select userinterestids.user_id as user_id,collect_set(usersports.name) 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 on userdim.id = usersports.user_id", + null, "group by userinterestids.balias0, usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -153,7 +206,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { 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() + "SELECT (usersports.balias0) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 as `alias2` 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() @@ -161,7 +215,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, "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 `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) as `alias2` 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() @@ -173,25 +228,26 @@ public class TestBridgeTableQueries extends TestQueryRewrite { 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); + lower.startsWith("select (basecube.alias0) as `name`, sum((basecube.alias1)) as `msr2`, " + + "sum((basecube.alias2)) as `msr12` from"), hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); // run with chain ref column 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() - + "c1_usertable userdim ON basecube.userid = userdim.id " + "SELECT (usersports.balias0) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 as `alias2` 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, "group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); expected2 = getExpectedQuery("basecube", - "select usersports.balias0 as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT (usersports.balias0) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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() @@ -203,11 +259,10 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); assertTrue( - lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq1.msr2 msr2, mq2.msr12 msr12 from "), - hqlQuery); + lower.startsWith("select (basecube.alias0) as `sports`, sum((basecube.alias1)) as `msr2`, " + + "sum((basecube.alias2)) as `msr12` from"), hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.sports <=> mq2.sports"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); } @@ -216,8 +271,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, xusersports.name, yusersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, xusersports.balias0, " - + "yusersports.balias0, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, (xusersports.balias0) " + + "as `name`, (yusersports.balias0) as `name`, 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 " @@ -236,6 +291,27 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sports, xsports, ysports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, (xusersports.balias0) " + + "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 " + + "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 =" + + " 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, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -247,8 +323,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, xusersports.name, yusersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, xusersports.balias0, " - + "yusersports.balias0, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, (xusersports.balias0) " + + "as `name`, (yusersports.balias0) as `name`, 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 " @@ -268,6 +344,30 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sports, xsports, ysports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + 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 " + + "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() + + "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, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -278,8 +378,9 @@ 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, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c2_usertable userdim ON basecube.userid = userdim.id and userdim.dt='latest' " + 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" + " join " + getDbName() + "c2_sports_tbl usersports on user_interests.sport_id = usersports.id" @@ -291,6 +392,17 @@ 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() + + "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" + + " join " + getDbName() + "c2_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " and usersports.dt='latest and user_interests.dt='latest'" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, "group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c2_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -300,8 +412,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, cubestatecountry.name, cubecitystatecountry.name," + " sum(msr2) from basecube where " + TWO_DAYS_RANGE; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, cubestatecountry.name, " - + "cubecitystatecountry.name, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `name`, (cubestatecountry.name) " + + "as `name`, (cubecitystatecountry.name) 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" @@ -313,13 +425,33 @@ 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")); TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sports, statecountry, citycountry, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports`, (cubestatecountry.name) " + + "as `statecountry`, (cubecitystatecountry.name) as `citycountry`, 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 " + + " 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_countrytable cubestatecountry on statedim.countryid=cubestatecountry.id ", + null, "group by usersports.balias0, cubestatecountry.name, cubecitystatecountry.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -333,8 +465,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " and not (some_filter(usersports.name, 'ASD') OR some_filter(usersports.name, 'ZXC'))" + " and myfunc(usersports.name) = 'CRT' and substr(usersports.name, 3) in ('CRI')"; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + 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," + " collect_set(myfunc(usersports.name)) as balias1, collect_set(substr(usersports.name, 3)) as balias2" + " from " + getDbName() + "c1_user_interests_tbl user_interests" @@ -357,6 +490,24 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " and (some_filter(sports, 'CRICKET') OR some_filter(sports, 'FOOTBALL'))" + " and not (some_filter(sports, 'ASD') OR some_filter(sports, 'ZXC'))" + " and myfunc(sports) = 'CRT' and sports_abbr in ('CRI')"; + 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," + + " collect_set(myfunc(usersports.name)) as balias1, collect_set(substr(usersports.name, 3)) as balias2" + + " 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') and (array_contains(usersports.balias0, 'BB')" + + " OR array_contains(usersports.balias0, 'FOOTBALL'))" + + " and not array_contains(usersports.balias0, 'RANDOM'))" + + " and not (array_contains(usersports.balias0, 'xyz') OR array_contains(usersports.balias0, 'ABC'))" + + " and (some_filter(usersports.name, 'CRICKET') OR some_filter(usersports.name, 'FOOTBALL'))" + + " and not (some_filter(usersports.name, 'ASD') OR some_filter(usersports.name, 'ZXC'))" + + " and (array_contains(usersports.balias1, 'CRT') AND array_contains(usersports.balias2, 'CRI'))" + + "group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -364,22 +515,36 @@ 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, sum(basecube.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"; + + "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" + + " 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 sports asc", + null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -391,7 +556,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and usersports.name in ('CRICKET','FOOTBALL')"; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + 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" @@ -404,6 +570,17 @@ 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 + " and sports in ('CRICKET','FOOTBALL')"; + 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" + + " 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 (custom_filter(usersports.balias0, 'CRICKET') OR custom_filter(usersports.balias0, 'FOOTBALL'))" + + "group by usersports.balias0", + null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -415,8 +592,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + String expected = getExpectedQuery("basecube", "SELECT (usersports.name) 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 name" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" @@ -427,6 +605,16 @@ 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 + " and sports = 'CRICKET,FOOTBALL'"; + expected = getExpectedQuery("basecube", "SELECT (usersports.name) 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 name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -437,7 +625,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " and usersports.name = 'CRICKET'"; 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 `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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() @@ -446,7 +635,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { " and array_contains(usersports.balias0,'CRICKET') 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 `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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() @@ -457,16 +647,17 @@ 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 (basecube.alias0) as `name`, sum((basecube.alias1)) as `msr2`, " + + "sum((basecube.alias2)) as `msr12` from"), hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); // run with chain ref column 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() + "SELECT (usersports.balias0) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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() @@ -475,7 +666,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { "and array_contains(usersports.balias0,'CRICKET') group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); expected2 = getExpectedQuery("basecube", - "select usersports.balias0 as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT (usersports.balias0) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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() @@ -486,11 +678,10 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq1.msr2 msr2, mq2.msr12 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("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.sports <=> mq2.sports"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); } @@ -502,8 +693,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); String expected1 = getExpectedQuery("basecube", - "select usersports.name as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() - + "c1_usertable userdim ON basecube.userid = userdim.id " + "SELECT (usersports.name) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 as `alias2` FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" @@ -511,7 +702,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); String expected2 = getExpectedQuery("basecube", - "select usersports.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT (usersports.name) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) as `alias2` 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() @@ -522,17 +714,18 @@ 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 (basecube.alias0) as `name`, sum((basecube.alias1)) as `msr2`, " + + "sum((basecube.alias2)) as `msr12` from"), hqlQuery); - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); // run with chain ref column query = "select sports, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET,FOOTBALL'"; hqlQuery = rewrite(query, conf); expected1 = getExpectedQuery("basecube", - "select usersports.name as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "SELECT (usersports.name) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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() @@ -541,7 +734,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { " and usersports.name = 'CRICKET,FOOTBALL' group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); expected2 = getExpectedQuery("basecube", - "select usersports.name as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT (usersports.name) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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() @@ -552,11 +746,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); - assertTrue(lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq1.msr2 msr2, mq2.msr12 msr12 from "), - hqlQuery); - - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.sports <=> mq2.sports"), + 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); } @@ -565,7 +757,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select substr(usersports.name, 3), sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET'"; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select usersports.balias0, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `substr((usersports.name), 3)`, " + + "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" @@ -577,6 +770,17 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareQueries(hqlQuery, expected); // run with chain ref column query = "select sports_abbr, sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and sports = 'CRICKET'"; + expected = getExpectedQuery("basecube", "SELECT (usersports.balias0) as `sports_abbr`, " + + "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" + + " 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.balias1, 'CRICKET') group by usersports.balias0", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, hConf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -588,8 +792,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { String query = "select substr(usersports.name, 3), sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select substr(usersports.name, 3), sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + String expected = getExpectedQuery("basecube", "SELECT substr((usersports.name), 3) as " + + "`substr((usersports.name), 3)`, 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 name" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" @@ -599,7 +804,18 @@ 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 " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", null, + " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); hqlQuery = rewrite(query, conf); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -610,8 +826,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET' and substr(usersports.name, 3) = 'CRI' and (userid = 4 or userid = 5)"; String hqlQuery = rewrite(query, hConf); - String expected = getExpectedQuery("basecube", "select basecube.userid as `uid`, usersports.balias0 as `uname`, " - + " (usersports.balias1) as `sub user`, sum(basecube.msr2) FROM ", + String expected = getExpectedQuery("basecube", "SELECT (basecube.userid) as `uid`, (usersports.balias0) " + + "as `uname`, (usersports.balias1) as `sub user`, 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, " + "collect_set(substr(usersports.name, 3)) as balias1" @@ -639,9 +855,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " sum(msr2) from basecube where " + TWO_DAYS_RANGE + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name as `uname`, substr(usersports.name, 3) as " - + "`sub user`, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + String expected = getExpectedQuery("basecube", "SELECT (usersports.name) as `uname`, substr((usersports.name), 3) " + + "as `sub user`, 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 name" + " from " + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" @@ -662,7 +878,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " and usersports.name in ('CRICKET', 'FOOTBALL')"; String hqlQuery = rewrite(query, hConf); String expected1 = getExpectedQuery("basecube", - "select usersports.balias0 as `expr1`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "SELECT (usersports.balias0) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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 +890,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); String expected2 = getExpectedQuery("basecube", - "select usersports.balias0 as `expr1`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT (usersports.balias0) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) as `alias2` 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,20 +904,19 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - 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 "), + 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("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); + // run with chain ref column query = "select sports_abbr, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports in " + "('CRICKET', 'FOOTBALL')"; hqlQuery = rewrite(query, hConf); expected1 = getExpectedQuery("basecube", - "select usersports.balias0 as `sports_abbr`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "SELECT (usersports.balias0) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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" @@ -710,7 +927,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " group by usersports.balias0", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); expected2 = getExpectedQuery("basecube", - "select usersports.balias0 as `sports_abbr`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT (usersports.balias0) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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" @@ -724,13 +942,10 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); assertTrue(lower.startsWith( - "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith( - "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq1.msr2 msr2, mq2.msr12 msr12 from "), - hqlQuery); - - assertTrue(hqlQuery.contains("mq1 full outer join ") - && hqlQuery.endsWith("mq2 on mq1.sports_abbr <=> mq2.sports_abbr"), + "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); } @@ -742,7 +957,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { + " and usersports.name = 'CRICKET,FOOTBALL'"; String hqlQuery = rewrite(query, conf); String expected1 = getExpectedQuery("basecube", - "select substr(usersports.name, 3) as `expr1`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "SELECT substr((usersports.name), 3) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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() @@ -751,7 +967,8 @@ 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 `expr1`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT substr((usersports.name), 3) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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() @@ -762,20 +979,19 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected1, hqlQuery); TestCubeRewriter.compareContains(expected2, hqlQuery); String lower = hqlQuery.toLowerCase(); - 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 "), + 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("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.expr1 <=> mq2.expr1"), + assertTrue(hqlQuery.contains("UNION ALL") && hqlQuery.endsWith("GROUP BY (basecube.alias0)"), hqlQuery); // run with chain ref column query = "select sports_abbr, msr2, msr12 from basecube where " + TWO_DAYS_RANGE + " and sports = " + "'CRICKET,FOOTBALL'"; hqlQuery = rewrite(query, conf); expected1 = getExpectedQuery("basecube", - "select substr(usersports.name, 3) as `sports_abbr`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + "SELECT substr((usersports.name), 3) as `alias0`, sum((basecube.msr2)) as `alias1`, 0.0 " + + "as `alias2` 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() @@ -784,7 +1000,8 @@ public class TestBridgeTableQueries extends TestQueryRewrite { " and usersports.name = 'CRICKET,FOOTBALL' group by substr(usersports.name, 3)", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); expected2 = getExpectedQuery("basecube", - "select substr(usersports.name, 3) as `sports_abbr`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + "SELECT substr((usersports.name), 3) as `alias0`, 0.0 as `alias1`, sum((basecube.msr12)) " + + "as `alias2` 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() @@ -796,13 +1013,10 @@ public class TestBridgeTableQueries extends TestQueryRewrite { TestCubeRewriter.compareContains(expected2, hqlQuery); lower = hqlQuery.toLowerCase(); assertTrue(lower.startsWith( - "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith( - "select coalesce(mq1.sports_abbr, mq2.sports_abbr) sports_abbr, mq1.msr2 msr2, mq2.msr12 msr12 from "), - hqlQuery); - - assertTrue(hqlQuery.contains("mq1 full outer join ") - && hqlQuery.endsWith("mq2 on mq1.sports_abbr <=> mq2.sports_abbr"), + "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); } @@ -848,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"
