You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@lens.apache.org by pu...@apache.org on 2017/02/08 02:23:35 UTC
[3/7] lens git commit: feature upadte 2 with query writing flow
completed (Few test cases need to be fixed though)
http://git-wip-us.apache.org/repos/asf/lens/blob/4af769ee/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..76618a7 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`, sum(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`, sum(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`, sum(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`, sum(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,23 @@ 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 +319,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 +340,24 @@ 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 +368,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 +382,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 +402,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"
@@ -320,6 +422,24 @@ public class TestBridgeTableQueries extends TestQueryRewrite {
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 +453,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 +478,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);
}
@@ -366,8 +505,9 @@ public class TestBridgeTableQueries extends TestQueryRewrite {
String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE
+ " 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 "
+ 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 "
@@ -380,6 +520,17 @@ public class TestBridgeTableQueries extends TestQueryRewrite {
// 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 "
+ + 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"));
hqlQuery = rewrite(query, hConf);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@@ -391,7 +542,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 +556,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 +578,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 +591,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 +611,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`, sum(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 +621,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`, sum(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 +633,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`, sum(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 +652,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`, sum(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 +664,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 +679,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`, sum(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 +688,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`, sum(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 +700,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`, sum(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 +720,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`, sum(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 +732,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 +743,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 +756,16 @@ 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 +777,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"
@@ -600,6 +790,16 @@ 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,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 +810,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 +839,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 +862,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`, sum(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 +874,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`, sum(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 +888,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`, sum(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 +911,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`, sum(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 +926,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 +941,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`, sum(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 +951,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`, sum(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 +963,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`, sum(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 +984,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`, sum(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 +997,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);
}