@Test
public void testDimensionQueryWithExpression() throws Exception {
String hqlQuery = rewrite("select citydim.name, cityaddress from" + " citydim", conf);
String joinExpr;
String join1 =
" join "
+ getDbName()
+ "c1_ziptable cityzip on"
+ " citydim.zipcode = cityzip.code and (cityzip.dt = 'latest')";
String join2 =
" join "
+ getDbName()
+ "c1_statetable citystate on"
+ " citydim.stateid = citystate.id and (citystate.dt = 'latest')";
String join3 =
" join "
+ getDbName()
+ "c1_countrytable citycountry on"
+ " citystate.countryid = citycountry.id";
joinExpr = join2 + join3 + join1;
String expected =
getExpectedQuery(
"citydim",
"SELECT citydim.name, concat((citydim.name), \":\", (citystate.name ),"
+ " \":\",(citycountry.name), \":\" , ( cityzip . code )) FROM ",
joinExpr,
null,
null,
"c1_citytable",
true);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionToJoin() throws Exception {
// expression which results in join
String hqlQuery =
rewrite(
"select cityAndState, avgmsr from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'",
conf);
String join1 =
" join "
+ getDbName()
+ "c1_citytable cubecity"
+ " on testcube.cityid = cubecity.id and (cubecity.dt = 'latest') ";
String join2 =
" join"
+ getDbName()
+ "c1_statetable cubestate on"
+ " testcube.stateid = cubestate.id and (cubestate.dt = 'latest')";
String expected =
getExpectedQuery(
cubeName,
"select concat(cubecity.name, \":\", cubestate.name),"
+ " avg(testcube.msr1 + testcube.msr2) FROM ",
join2 + join1,
null,
" and substr(testcube.dim1, 3) != 'XYZ'"
+ " group by concat(cubecity.name, \":\", cubestate.name)",
null,
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testDimensionQueryWithTableAliasColumnAlias() throws Exception {
String hqlQuery =
rewrite("select ct.name cname, ct.cityaddress caddr from" + " citydim ct", conf);
String joinExpr =
""
+ " join "
+ getDbName()
+ "c1_statetable citystate on ct.stateid = citystate.id and (citystate.dt = 'latest')"
+ " join "
+ getDbName()
+ "c1_countrytable citycountry on citystate.countryid = citycountry.id"
+ " join "
+ getDbName()
+ "c1_ziptable cityzip on ct.zipcode = cityzip.code and (cityzip.dt = 'latest')";
String expected =
getExpectedQuery(
"ct",
"SELECT ct.name as `cname`, concat((ct.name), \":\", (citystate.name ),"
+ " \":\",(citycountry.name), \":\" , ( cityzip . code )) as `caddr` FROM ",
joinExpr,
null,
null,
"c1_citytable",
true);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testMultipleExpressionsPickingSecondExpression() throws Exception {
String hqlQuery = rewrite("select equalsums from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select (max(testCube.msr3) + sum(testCube.msr2))/100 FROM ",
null,
null,
getWhereForHourly2days(cubeName, "C1_testfact2"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testCubeQueryExpressionSelectionAlongWithColumn() throws Exception {
String hqlQuery =
rewrite("select dim1, roundedmsr2 from testCube" + " where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select testcube.dim1, round(sum(testcube.msr2)/1000) FROM ",
null,
" group by testcube.dim1",
getWhereForDailyAndHourly2days(cubeName, "c1_summary1"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testCubeQueryExpressionSelection() throws Exception {
// select with expression
String hqlQuery = rewrite("cube select avgmsr from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select avg(testCube.msr1 + testCube.msr2) FROM ",
null,
null,
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExprDimAttribute() throws Exception {
// select with expression
String hqlQuery = rewrite("select substrexpr from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select distinct substr(testCube.dim1, 3) FROM ",
null,
null,
getWhereForDailyAndHourly2days(cubeName, "c1_summary1"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testMaterializedExpressionPickingExpression() throws Exception {
// select with expression
String hqlQuery = rewrite("select msr5 from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select sum(testCube.msr2) + max(testCube.msr3) FROM ",
null,
null,
getWhereForHourly2days(cubeName, "C1_testfact2"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testDimensionQueryExpressionInSelectToGroupby() throws Exception {
String hqlQuery = rewrite("select id, AggrExpr from citydim", conf);
String expected =
getExpectedQuery(
"citydim",
"select citydim.id, count(citydim.name) FROM ",
null,
null,
" group by citydim.id",
"c1_citytable",
true);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInWhereBeforeTimerange() throws Exception {
String hqlQuery =
rewrite(
"select SUM(msr2) from testCube" + " where substrexpr != 'XYZ' and " + TWO_DAYS_RANGE,
conf);
String expected =
getExpectedQuery(
cubeName,
"select sum(testcube.msr2) FROM ",
"substr(testCube.dim1, 3) != 'XYZ'",
null,
getWhereForDailyAndHourly2days(cubeName, "c1_summary1"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testNestedExpressionInWhere() throws Exception {
String hqlQuery =
rewrite(
"select avgmsr from testCube" + " where " + TWO_DAYS_RANGE + " and indiasubstr = true",
conf);
String expected =
getExpectedQuery(
cubeName,
"select avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and (substr(testCube.dim1, 3) = 'INDIA') = true",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testMaterializedExpressionPickingMaterializedValue() throws Exception {
Configuration newConf = new Configuration(conf);
newConf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2");
newConf.set(CubeQueryConfUtil.getValidFactTablesKey(cubeName), "testFact");
String hqlQuery = rewrite("select msr5 from testCube where " + TWO_DAYS_RANGE, newConf);
String expected =
getExpectedQuery(
cubeName,
"select testcube.msr5 FROM ",
null,
null,
getWhereForDailyAndHourly2days(cubeName, "C2_testfact"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInSelectAndWhere() throws Exception {
// expression in select and where
String hqlQuery =
rewrite(
"select avgmsr from testCube" + " where " + TWO_DAYS_RANGE + " and substrexpr != 'XYZ'",
conf);
String expected =
getExpectedQuery(
cubeName,
"select avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ'",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testCubeExpressionWithColumnAlias() throws Exception {
// expression with column alias
String hqlQuery =
rewrite(
"select TC.substrexpr as subdim1, TC.avgmsr from testCube TC"
+ " where "
+ TWO_DAYS_RANGE
+ " and subdim1 != 'XYZ'",
conf);
String expected =
getExpectedQuery(
"tc",
"select substr(tc.dim1, 3) as `subdim1`, avg(tc.msr1 + tc.msr2) FROM ",
null,
" and subdim1 != 'XYZ' group by substr(tc.dim1, 3)",
getWhereForHourly2days("tc", "C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInSelectToGroupbyWithComplexExpression() throws Exception {
String hqlQuery =
rewrite(
"select booleancut, summsrs from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 ,"
+ " ((1000 + sum(testCube.msr1) + sum(testCube.msr2))/100) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ' group by testCube.dim1 != 'x' AND testCube.dim2 != 10",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInGroupbyToSelect() throws Exception {
// expression with groupby
String hqlQuery =
rewrite(
"select avgmsr from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ' group by booleancut",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 ,"
+ " avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ'"
+ " group by testCube.dim1 != 'x' AND testCube.dim2 != 10",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionWithAliasInOrderby() throws Exception {
String hqlQuery =
rewrite(
"cube select booleancut bc, msr2 from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'"
+ " having msr6 > 100.0 order by bc",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 as `bc`,"
+ " sum(testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ' "
+ " group by testCube.dim1 != 'x' AND testCube.dim2 != 10"
+ " having (sum(testCube.msr2) + max(testCube.msr3))/ count(testcube.msr4) > 100.0"
+ " order by bc asc",
getWhereForDailyAndHourly2days(cubeName, "c1_summary2"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInHaving() throws Exception {
// expression with having clause
String hqlQuery =
rewrite(
"cube select booleancut, avgmsr from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'"
+ " having msr6 > 100.0",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 ,"
+ " avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ' "
+ " group by testCube.dim1 != 'x' AND testCube.dim2 != 10"
+ " having (sum(testCube.msr2) + max(testCube.msr3))/ count(testcube.msr4) > 100.0",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
