You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@lens.apache.org by am...@apache.org on 2014/12/01 09:07:06 UTC
incubator-lens git commit: LENS-67 : Fixes ColumnarSQLRewriter for
queries with alias names for tables ( Sushil Mohanty via amareshwari)
Repository: incubator-lens
Updated Branches:
refs/heads/master 826605a5a -> f5e5eccc7
LENS-67 : Fixes ColumnarSQLRewriter for queries with alias names for tables ( Sushil Mohanty via amareshwari)
Project: http://git-wip-us.apache.org/repos/asf/incubator-lens/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-lens/commit/f5e5eccc
Tree: http://git-wip-us.apache.org/repos/asf/incubator-lens/tree/f5e5eccc
Diff: http://git-wip-us.apache.org/repos/asf/incubator-lens/diff/f5e5eccc
Branch: refs/heads/master
Commit: f5e5eccc7b9964abc97fde3abd7b22500f7c3833
Parents: 826605a
Author: Amareshwari Sriramdasu <am...@inmobi.com>
Authored: Mon Dec 1 13:36:53 2014 +0530
Committer: Amareshwari Sriramdasu <am...@inmobi.com>
Committed: Mon Dec 1 13:36:53 2014 +0530
----------------------------------------------------------------------
.../lens/driver/jdbc/ColumnarSQLRewriter.java | 85 +++++-
.../driver/jdbc/TestColumnarSQLRewriter.java | 268 ++++++++++++-------
2 files changed, 247 insertions(+), 106 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-lens/blob/f5e5eccc/lens-driver-jdbc/src/main/java/org/apache/lens/driver/jdbc/ColumnarSQLRewriter.java
----------------------------------------------------------------------
diff --git a/lens-driver-jdbc/src/main/java/org/apache/lens/driver/jdbc/ColumnarSQLRewriter.java b/lens-driver-jdbc/src/main/java/org/apache/lens/driver/jdbc/ColumnarSQLRewriter.java
index 20031db..d41d899 100644
--- a/lens-driver-jdbc/src/main/java/org/apache/lens/driver/jdbc/ColumnarSQLRewriter.java
+++ b/lens-driver-jdbc/src/main/java/org/apache/lens/driver/jdbc/ColumnarSQLRewriter.java
@@ -106,6 +106,9 @@ public class ColumnarSQLRewriter implements QueryRewriter {
/** The map agg tab alias. */
private Map<String, String> mapAggTabAlias = new HashMap<String, String>();
+ /** The map aliases. */
+ private Map<String, String> mapAliases = new HashMap<String, String>();
+
/** The Constant LOG. */
private static final Log LOG = LogFactory.getLog(ColumnarSQLRewriter.class);
@@ -587,6 +590,7 @@ public class ColumnarSQLRewriter implements QueryRewriter {
joinCondition.setLength(0);
selectTree = fromTree = joinTree = whereTree = groupByTree = havingTree = orderByTree = null;
selectAST = fromAST = joinAST = whereAST = groupByAST = havingAST = orderByAST = null;
+ mapAliases.clear();
limit = null;
}
@@ -614,6 +618,40 @@ public class ColumnarSQLRewriter implements QueryRewriter {
return query;
}
+ /**
+ * Replace alias in AST trees
+ *
+ * @throws HiveException
+ */
+
+ public void replaceAliasInAST() throws HiveException {
+ updateAliasFromAST(fromAST);
+ if (fromTree != null) {
+ replaceAlias(fromAST);
+ fromTree = HQLParser.getString(fromAST);
+ }
+ if (selectTree != null) {
+ replaceAlias(selectAST);
+ selectTree = HQLParser.getString(selectAST);
+ }
+ if (whereTree != null) {
+ replaceAlias(whereAST);
+ whereTree = HQLParser.getString(whereAST);
+ }
+ if (groupByTree != null) {
+ replaceAlias(groupByAST);
+ groupByTree = HQLParser.getString(groupByAST);
+ }
+ if (orderByTree != null) {
+ replaceAlias(orderByAST);
+ groupByTree = HQLParser.getString(orderByAST);
+ }
+ if (havingTree != null) {
+ replaceAlias(havingAST);
+ havingTree = HQLParser.getString(havingAST);
+ }
+ }
+
/*
* Construct the rewritten query using trees
*/
@@ -629,8 +667,8 @@ public class ColumnarSQLRewriter implements QueryRewriter {
public void buildQuery() throws SemanticException, HiveException {
analyzeInternal();
replaceWithUnderlyingStorage(fromAST);
- fromTree = HQLParser.getString(fromAST);
+ replaceAliasInAST();
getFilterInJoinCond(fromAST);
getAggregateColumns(selectAST);
getJoinCond(fromAST);
@@ -684,7 +722,6 @@ public class ColumnarSQLRewriter implements QueryRewriter {
}
//for subquery with count function should be replaced with sum in outer query
if (selectTree.toLowerCase().matches("(.*)count\\((.*)")) {
- System.out.println(selectTree);
selectTree = selectTree.replaceAll("count\\(", "sum\\(");
}
// construct query with fact sub query
@@ -725,6 +762,50 @@ public class ColumnarSQLRewriter implements QueryRewriter {
}
}
+ /**
+ * Update alias and map old alias with new one
+ *
+ * @param from
+ */
+ private void updateAliasFromAST(ASTNode from) {
+
+ String newAlias = "";
+ String table = "";
+ if (TOK_TABREF == from.getToken().getType()) {
+ ASTNode tabName = (ASTNode) from.getChild(0);
+ if (tabName.getChildCount() == 2) {
+ table = tabName.getChild(0).getText() + "_" + tabName.getChild(1).getText();
+ } else {
+ table = tabName.getChild(0).getText();
+ }
+ if (from.getChildCount() > 1) {
+ ASTNode alias = (ASTNode) from.getChild(1);
+ newAlias = table + "_" + from.getChild(1).getText();
+ mapAliases.put(alias.getText(), newAlias);
+ alias.getToken().setText(newAlias);
+ }
+ }
+ for (int i = 0; i < from.getChildCount(); i++) {
+ updateAliasFromAST((ASTNode) from.getChild(i));
+
+ }
+ }
+
+ /**
+ * Update alias in all AST trees
+ *
+ * @param tree
+ */
+ private void replaceAlias(ASTNode tree) {
+ if (TOK_TABLE_OR_COL == tree.getToken().getType()) {
+ ASTNode alias = (ASTNode) tree.getChild(0);
+ alias.getToken().setText(mapAliases.get(tree.getChild(0).toString()));
+ }
+ for (int i = 0; i < tree.getChildCount(); i++) {
+ replaceAlias((ASTNode) tree.getChild(i));
+ }
+ }
+
/*
* Construct final query using all trees
*/
http://git-wip-us.apache.org/repos/asf/incubator-lens/blob/f5e5eccc/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java
----------------------------------------------------------------------
diff --git a/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java b/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java
index 6823ada..968b2e5 100644
--- a/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java
+++ b/lens-driver-jdbc/src/test/java/org/apache/lens/driver/jdbc/TestColumnarSQLRewriter.java
@@ -236,22 +236,25 @@ public class TestColumnarSQLRewriter {
String query2 = "select count(distinct id) from location_dim location_dim";
String actual2 = qtest.rewrite(query2, conf);
- String expected2 = "select count( distinct id ) from location_dim location_dim";
+ String expected2 = "select count( distinct id ) from location_dim location_dim_location_dim ";
compareQueries(expected2, actual2);
- String query3 = "select count(distinct location_dim.id) from global_dw.location_dim location_dim";
+ String query3 = "select count(distinct location_dim.id) from db.location_dim location_dim";
String actual3 = qtest.rewrite(query3, conf);
- String expected3 = "select count( distinct ( location_dim . id )) from global_dw.location_dim location_dim";
+ String expected3 = "select count( distinct ( db_location_dim_location_dim . id )) from db.location_dim db_location_dim_location_dim";
compareQueries(expected3, actual3);
- String query4 = "select count(distinct location_dim.id) from global_dw.location_dim location_dim "
- + "left outer join global_dw.item_dim item_dim on location_dim.id = item_dim.id "
+ String query4 = "select count(distinct location_dim.id) from db.location_dim location_dim "
+ + "left outer join db.item_dim item_dim on location_dim.id = item_dim.id "
+ "right outer join time_dim time_dim on location_dim.id = time_dim.id ";
String actual4 = qtest.rewrite(query4, conf);
- String expected4 = "select count( distinct ( location_dim . id )) from global_dw.location_dim location_dim "
- + "right outer join time_dim time_dim on (( location_dim . id ) = ( time_dim . id )) "
- + "left outer join global_dw.item_dim item_dim on (( location_dim . id ) = ( item_dim . id ))";
+ String expected4 = " select count( distinct ( db_location_dim_location_dim . id )) "
+ + "from db.location_dim db_location_dim_location_dim right outer join "
+ + "time_dim time_dim_time_dim on (( db_location_dim_location_dim . id ) = ( time_dim_time_dim . id )) "
+ + "left outer join db.item_dim db_item_dim_item_dim "
+ + "on (( db_location_dim_location_dim . id ) = ( db_item_dim_item_dim . id ))";
compareQueries(expected4, actual4);
+
}
/**
@@ -279,12 +282,11 @@ public class TestColumnarSQLRewriter {
SessionState.start(conf);
-
+
String rwq = qtest.rewrite(query, conf);
- String expected = "inner join location_dim location_dim on "
- + "((( fact . location_key ) = ( location_dim . location_key )) "
- + "and (( location_dim . location_name ) = 'test123' )) "
- + "inner join time_dim time_dim on (( fact . time_key ) = ( time_dim . time_key ))";
+ String expected = "inner join location_dim location_dim_location_dim on ((( sales_fact_fact . location_key ) = "
+ + "( location_dim_location_dim . location_key )) and (( location_dim_location_dim . location_name ) = 'test123' )) "
+ + "inner join time_dim time_dim_time_dim on (( sales_fact_fact . time_key ) = ( time_dim_time_dim . time_key ))";
String actual = qtest.joinCondition.toString();
compareQueries(expected, actual);
@@ -292,7 +294,7 @@ public class TestColumnarSQLRewriter {
/**
* Test all filter cond.
- *
+ *
* @throws ParseException
* the parse exception
* @throws SemanticException
@@ -316,11 +318,13 @@ public class TestColumnarSQLRewriter {
SessionState.start(conf);
String rwq = qtest.rewrite(query, conf);
- Set<String> actual = setOf(qtest.rightFilter);
- Assert.assertEquals(
- actual,
- setOf("(( location_dim . location_name ) = 'test123' )",
- "( time_dim . time_key ) between '2013-01-01' and '2013-01-31'", ""));
+ String expected = "(( location_dim_location_dim . location_name ) = 'test123' ), , "
+ + "( time_dim_time_dim . time_key ) between '2013-01-01' and '2013-01-31' , "
+ + "( time_dim_time_dim . time_key ) between '2013-01-01' and '2013-01-31' ]";
+ String actual = qtest.rightFilter.toString();
+
+ compareQueries(expected, actual);
+
}
/**
@@ -350,13 +354,13 @@ public class TestColumnarSQLRewriter {
SessionState.start(conf);
String rwq = qtest.rewrite(query, conf);
- Set<String> aggrActual = setOf(qtest.aggColumn);
- Set<String> expectedAggr = setOf("sum(( fact . units_sold )) as sum_fact_units_sold",
- "min(( fact . dollars_sold )) as min_fact_dollars_sold",
- "avg(( fact . dollars_sold )) as avg_fact_dollars_sold",
- "sum(( fact . dollars_sold )) as sum_fact_dollars_sold",
- "max(( fact . dollars_sold )) as max_fact_dollars_sold");
- Assert.assertEquals(aggrActual, expectedAggr);
+ String actual = qtest.aggColumn.toString();
+ String expected = "[sum(( sales_fact_fact . dollars_sold )) as sum_sales_fact_fact_dollars_sold, "
+ + "sum(( sales_fact_fact . units_sold )) as sum_sales_fact_fact_units_sold, "
+ + "avg(( sales_fact_fact . dollars_sold )) as avg_sales_fact_fact_dollars_sold, "
+ + "min(( sales_fact_fact . dollars_sold )) as min_sales_fact_fact_dollars_sold, "
+ + "max(( sales_fact_fact . dollars_sold )) as max_sales_fact_fact_dollars_sold]";
+ Assert.assertEquals(expected, actual);
}
/**
@@ -387,7 +391,7 @@ public class TestColumnarSQLRewriter {
SessionState.start(conf);
String rwq = qtest.rewrite(query, conf);
- String expected = "fact.time_key,fact.location_key,fact.item_key,";
+ String expected = "sales_fact_fact.time_key,sales_fact_fact.location_key,sales_fact_fact.item_key,";
String actual = qtest.factKeys.toString();
compareQueries(expected, actual);
}
@@ -420,11 +424,12 @@ public class TestColumnarSQLRewriter {
SessionState.start(conf);
String rwq = qtest.rewrite(query, conf);
- String expected = "fact.time_key in ( select time_dim.time_key from time_dim where ( time_dim . time_key ) "
- + "between '2013-01-01' and '2013-01-31' ) and fact.location_key in ( select location_dim.location_key "
- + "from location_dim where (( location_dim . location_name ) = 'test123' ) ) and "
- + "fact.item_key in ( select item_dim.item_key from item_dim "
- + "where (( item_dim . item_name ) = 'item_1' ) ) and ";
+ String expected = "sales_fact_fact.time_key in ( select time_dim_time_dim.time_key from time_dim_time_dim where "
+ + "( time_dim_time_dim . time_key ) between '2013-01-01' and '2013-01-31' ) and sales_fact_fact.location_key in "
+ + "( select location_dim_location_dim.location_key from location_dim_location_dim where "
+ + "(( location_dim_location_dim . location_name ) = 'test123' ) ) and sales_fact_fact.item_key in "
+ + "( select item_dim_item_dim.item_key from item_dim_item_dim "
+ + "where (( item_dim_item_dim . item_name ) = 'item_1' ) ) and";
String actual = qtest.allSubQueries.toString();
compareQueries(expected, actual);
}
@@ -460,26 +465,35 @@ public class TestColumnarSQLRewriter {
String actual = qtest.rewrite(query, conf);
- String expected = "select ( fact . time_key ), ( time_dim . day_of_week ), date(( time_dim . day )), "
- + "( item_dim . item_key ), case when (sum(sum_fact_dollars_sold) = 0 ) then 0.0 "
- + "else sum(sum_fact_dollars_sold) end dollars_sold , format(sum(sum_fact_units_sold), 4 ), "
- + "format(avg(avg_fact_dollars_sold), '##################.###' ), min(min_fact_dollars_sold), "
- + "max(max_fact_dollars_sold) from (select fact.time_key,fact.location_key,fact.item_key,"
- + "sum(( fact . dollars_sold )) as sum_fact_dollars_sold, sum(( fact . units_sold )) as sum_fact_units_sold, "
- + "avg(( fact . dollars_sold )) as avg_fact_dollars_sold, min(( fact . dollars_sold )) as min_fact_dollars_sold, "
- + "max(( fact . dollars_sold )) as max_fact_dollars_sold from sales_fact fact where fact.time_key "
- + "in ( select time_dim.time_key from time_dim where ( time_dim . time_key ) between date_add( '2013-01-01' , interval 1 day) "
- + "and date_sub( '2013-01-31' , interval 3 day) ) and fact.location_key in ( select location_dim.location_key from location_dim "
- + "where (( location_dim . location_name ) = 'test123' ) ) and fact.item_key in "
- + "( select item_dim.item_key from item_dim where (( item_dim . item_name ) = 'item_1' ) ) "
- + "group by fact.time_key,fact.location_key,fact.item_key) fact inner join item_dim item_dim "
- + "on ((( fact . item_key ) = ( item_dim . item_key )) and (( location_dim . location_name ) = 'test123' )) "
- + "inner join location_dim location_dim on (( fact . location_key ) = ( location_dim . location_key )) "
- + "inner join time_dim time_dim on (( fact . time_key ) = ( time_dim . time_key )) "
- + "where (( time_dim . time_key ) between date_add( '2013-01-01' , interval 1 day) and "
- + "date_sub( '2013-01-31' , interval 3 day) and (( item_dim . item_name ) = 'item_1' )) "
- + "group by ( fact . time_key ), ( time_dim . day_of_week ), ( time_dim . day ), "
- + "( item_dim . item_key ) order by dollars_sold asc";
+ String expected = "select ( sales_fact_fact . time_key ), ( time_dim_time_dim . day_of_week ), "
+ + "date(( time_dim_time_dim . day )), ( item_dim_item_dim . item_key ), "
+ + "case when (sum(sum_sales_fact_fact_dollars_sold) = 0 ) then 0.0 "
+ + "else sum(sum_sales_fact_fact_dollars_sold) end dollars_sold , format(sum(sum_sales_fact_fact_units_sold), 4 ), "
+ + "format(avg(avg_sales_fact_fact_dollars_sold), '##################.###' ), "
+ + "min(min_sales_fact_fact_dollars_sold), max(max_sales_fact_fact_dollars_sold) "
+ + "from (select sales_fact_fact.time_key,sales_fact_fact.location_key,sales_fact_fact.item_key,"
+ + "sum(( sales_fact_fact . dollars_sold )) as sum_sales_fact_fact_dollars_sold, "
+ + "sum(( sales_fact_fact . units_sold )) as sum_sales_fact_fact_units_sold, "
+ + "avg(( sales_fact_fact . dollars_sold )) as avg_sales_fact_fact_dollars_sold, "
+ + "min(( sales_fact_fact . dollars_sold )) as min_sales_fact_fact_dollars_sold, "
+ + "max(( sales_fact_fact . dollars_sold )) as max_sales_fact_fact_dollars_sold "
+ + "from sales_fact sales_fact_fact where sales_fact_fact.time_key in "
+ + "( select time_dim_time_dim.time_key from time_dim_time_dim where "
+ + "( time_dim_time_dim . time_key ) between date_add( '2013-01-01' , interval 1 day) "
+ + "and date_sub( '2013-01-31' , interval 3 day) ) and sales_fact_fact.location_key in "
+ + " ( select location_dim_location_dim.location_key from location_dim_location_dim "
+ + "where (( location_dim_location_dim . location_name ) = 'test123' ) ) and "
+ + "sales_fact_fact.item_key in ( select item_dim_item_dim.item_key from "
+ + "item_dim_item_dim where (( item_dim_item_dim . item_name ) = 'item_1' ) ) "
+ + "group by sales_fact_fact.time_key,sales_fact_fact.location_key,sales_fact_fact.item_key) "
+ + "sales_fact_fact inner join item_dim item_dim_item_dim on ((( sales_fact_fact . item_key ) = "
+ + "( item_dim_item_dim . item_key )) and (( location_dim_location_dim . location_name ) = 'test123' )) "
+ + "inner join location_dim location_dim_location_dim on (( sales_fact_fact . location_key ) = "
+ + "( location_dim_location_dim . location_key )) inner join time_dim time_dim_time_dim on"
+ + " (( sales_fact_fact . time_key ) = ( time_dim_time_dim . time_key )) where"
+ + " (( time_dim_time_dim . time_key ) between date_add( '2013-01-01' , interval 1 day) and "
+ + "date_sub( '2013-01-31' , interval 3 day) and (( item_dim_item_dim . item_name ) = 'item_1' )) "
+ + "group by dollars_sold asc order by dollars_sold asc";
compareQueries(expected, actual);
}
@@ -524,39 +538,48 @@ public class TestColumnarSQLRewriter {
SessionState.start(conf);
String actual = qtest.rewrite(query, conf);
- String expected = "select ( fact . time_key ), ( time_dim . day_of_week ), ( time_dim . day ), "
- + "case when (sum(sum_fact_dollars_sold) = 0 ) then 0.0 else sum(sum_fact_dollars_sold) end dollars_sold "
- + "from (select fact.time_key,fact.location_key,sum(( fact . dollars_sold )) as sum_fact_dollars_sold "
- + "from sales_fact fact where fact.time_key in ( select time_dim.time_key from time_dim where "
- + "( time_dim . time_key ) between '2013-01-01' and '2013-01-05' ) and fact.location_key in "
- + " ( select location_dim.location_key from location_dim where (( location_dim . location_name ) = 'test123' ) ) "
- + " group by fact.time_key,fact.location_key) fact inner join location_dim location_dim "
- + "on ((( fact . location_key ) = ( location_dim . location_key )) "
- + "and (( location_dim . location_name ) = 'test123' )) inner join time_dim "
- + "time_dim on (( fact . time_key ) = ( time_dim . time_key )) where ( time_dim . time_key ) "
- + "between '2013-01-01' and '2013-01-05' group by ( fact . time_key ), ( time_dim . day_of_week ), "
- + "( time_dim . day ) order by dollars_sold asc union all select ( fact . time_key ), ( time_dim . day_of_week ), "
- + "( time_dim . day ), case when (sum(sum_fact_dollars_sold) = 0 ) then 0.0 else sum(sum_fact_dollars_sold) "
- + "end dollars_sold from (select fact.time_key,fact.location_key,sum(( fact . dollars_sold )) as sum_fact_dollars_sold "
- + "from sales_fact fact where fact.time_key in ( select time_dim.time_key from time_dim where ( time_dim . time_key ) "
- + "between '2013-02-01' and '2013-02-05' ) and fact.location_key in "
- + "( select location_dim.location_key from location_dim where (( location_dim . location_name ) = 'test123' ) ) "
- + "group by fact.time_key,fact.location_key) fact inner join location_dim "
- + "location_dim on ((( fact . location_key ) = ( location_dim . location_key )) and "
- + "(( location_dim . location_name ) = 'test123' )) inner join time_dim time_dim on (( fact . time_key ) = "
- + "( time_dim . time_key )) where ( time_dim . time_key ) between '2013-02-01' and '2013-02-05' group by "
- + "( fact . time_key ), ( time_dim . day_of_week ), ( time_dim . day ) order by dollars_sold asc "
- + "union all select ( fact . time_key ), ( time_dim . day_of_week ), ( time_dim . day ), "
- + "case when (sum(sum_fact_dollars_sold) = 0 ) then 0.0 else sum(sum_fact_dollars_sold) end dollars_sold "
- + "from (select fact.time_key,fact.location_key,sum(( fact . dollars_sold )) as sum_fact_dollars_sold "
- + "from sales_fact fact where fact.time_key in ( select time_dim.time_key from time_dim where "
- + "( time_dim . time_key ) between '2013-03-01' and '2013-03-05' ) and fact.location_key in "
- + "( select location_dim.location_key from location_dim where (( location_dim . location_name ) = 'test123' ) ) "
- + "group by fact.time_key,fact.location_key) fact inner join location_dim location_dim on "
- + "((( fact . location_key ) = ( location_dim . location_key )) and (( location_dim . location_name ) = 'test123' )) "
- + "inner join time_dim time_dim on (( fact . time_key ) = ( time_dim . time_key )) "
- + "where ( time_dim . time_key ) between '2013-03-01' and '2013-03-05' group by ( fact . time_key ), "
- + "( time_dim . day_of_week ), ( time_dim . day ) order by dollars_sold asc";
+ String expected = "select ( sales_fact_fact . time_key ), ( time_dim_time_dim . day_of_week ), "
+ + "( time_dim_time_dim . day ), case when (sum(sum_sales_fact_fact_dollars_sold) = 0 ) then 0.0 "
+ + "else sum(sum_sales_fact_fact_dollars_sold) end dollars_sold from "
+ + "(select sales_fact_fact.time_key,sales_fact_fact.location_key,sum(( sales_fact_fact . dollars_sold )) "
+ + "as sum_sales_fact_fact_dollars_sold from sales_fact sales_fact_fact where sales_fact_fact.time_key in "
+ + " ( select time_dim_time_dim.time_key from time_dim_time_dim where "
+ + "( time_dim_time_dim . time_key ) between '2013-01-01' and '2013-01-05' ) "
+ + "and sales_fact_fact.location_key in ( select location_dim_location_dim.location_key "
+ + "from location_dim_location_dim where (( location_dim_location_dim . location_name ) = 'test123' ) ) "
+ + "group by sales_fact_fact.time_key,sales_fact_fact.location_key) sales_fact_fact "
+ + "inner join location_dim location_dim_location_dim on ((( sales_fact_fact . location_key ) = "
+ + "( location_dim_location_dim . location_key )) and (( location_dim_location_dim . location_name ) = 'test123' )) "
+ + "inner join time_dim time_dim_time_dim on (( sales_fact_fact . time_key ) = ( time_dim_time_dim . time_key )) "
+ + "where ( time_dim_time_dim . time_key ) between '2013-01-01' and '2013-01-05' group by dollars_sold asc "
+ + "order by dollars_sold asc union all select ( sales_fact_fact . time_key ), ( time_dim_time_dim . day_of_week ), "
+ + "( time_dim_time_dim . day ), case when (sum(sum_sales_fact_fact_dollars_sold) = 0 ) then 0.0 "
+ + "else sum(sum_sales_fact_fact_dollars_sold) end dollars_sold from (select sales_fact_fact.time_key,"
+ + "sales_fact_fact.location_key,sum(( sales_fact_fact . dollars_sold )) as sum_sales_fact_fact_dollars_sold from "
+ + "sales_fact sales_fact_fact where sales_fact_fact.time_key in ( select time_dim_time_dim.time_key from "
+ + "time_dim_time_dim where ( time_dim_time_dim . time_key ) between '2013-02-01' and '2013-02-05' ) and "
+ + "sales_fact_fact.location_key in ( select location_dim_location_dim.location_key from location_dim_location_dim "
+ + "where (( location_dim_location_dim . location_name ) = 'test123' ) ) "
+ + "group by sales_fact_fact.time_key,sales_fact_fact.location_key) sales_fact_fact "
+ + "inner join location_dim location_dim_location_dim on ((( sales_fact_fact . location_key ) = "
+ + "( location_dim_location_dim . location_key )) and (( location_dim_location_dim . location_name ) = 'test123' )) "
+ + "inner join time_dim time_dim_time_dim on (( sales_fact_fact . time_key ) = ( time_dim_time_dim . time_key )) "
+ + "where ( time_dim_time_dim . time_key ) between '2013-02-01' and '2013-02-05' group by dollars_sold "
+ + "asc order by dollars_sold asc union all select ( sales_fact_fact . time_key ), "
+ + "( time_dim_time_dim . day_of_week ), ( time_dim_time_dim . day ), case when "
+ + "(sum(sum_sales_fact_fact_dollars_sold) = 0 ) then 0.0 else sum(sum_sales_fact_fact_dollars_sold) end "
+ + "dollars_sold from (select sales_fact_fact.time_key,sales_fact_fact.location_key,"
+ + "sum(( sales_fact_fact . dollars_sold )) as sum_sales_fact_fact_dollars_sold from sales_fact sales_fact_fact "
+ + "where sales_fact_fact.time_key in ( select time_dim_time_dim.time_key from time_dim_time_dim where "
+ + "( time_dim_time_dim . time_key ) between '2013-03-01' and '2013-03-05' ) and sales_fact_fact.location_key "
+ + "in ( select location_dim_location_dim.location_key from location_dim_location_dim where "
+ + "(( location_dim_location_dim . location_name ) = 'test123' ) ) group by "
+ + "sales_fact_fact.time_key,sales_fact_fact.location_key) sales_fact_fact "
+ + "inner join location_dim location_dim_location_dim on ((( sales_fact_fact . location_key ) = "
+ + "( location_dim_location_dim . location_key )) and (( location_dim_location_dim . location_name ) "
+ + "= 'test123' )) inner join time_dim time_dim_time_dim on (( sales_fact_fact . time_key ) = "
+ + "( time_dim_time_dim . time_key )) where ( time_dim_time_dim . time_key ) between '2013-03-01' "
+ + "and '2013-03-05' group by dollars_sold asc order by dollars_sold asc";
compareQueries(expected, actual);
}
@@ -565,17 +588,17 @@ public class TestColumnarSQLRewriter {
public void testNoAggCol() throws ParseException, SemanticException, LensException {
String query = "SELECT distinct ( location_dim . id ) FROM location_dim " +
- "location_dim join time_dim time_dim on location_dim.time_id = time_dim.id " +
- "WHERE ( time_dim . full_date ) between '2013-01-01 00:00:00' and '2013-01-04 00:00:00' LIMIT 10 ";
+ "location_dim join time_dim time_dim on location_dim.time_id = time_dim.id " +
+ "WHERE ( time_dim . full_date ) between '2013-01-01 00:00:00' and '2013-01-04 00:00:00' LIMIT 10 ";
SessionState.start(conf);
-
+
String actual = qtest.rewrite(query, conf);
- String expected = "select distinct ( location_dim . id ) from location_dim location_dim " +
- "inner join time_dim time_dim on (( location_dim . time_id ) = ( time_dim . id )) " +
- "where ( time_dim . full_date ) between '2013-01-01 00:00:00' and '2013-01-04 00:00:00' limit 10";
+ String expected = "select distinct ( location_dim_location_dim . id ) from location_dim location_dim_location_dim "
+ + "inner join time_dim time_dim_time_dim on (( location_dim_location_dim . time_id ) = ( time_dim_time_dim . id )) "
+ + "where ( time_dim_time_dim . full_date ) between '2013-01-01 00:00:00' and '2013-01-04 00:00:00' limit 10";
compareQueries(expected, actual);
-
+
}
@Test
@@ -584,18 +607,55 @@ public void testCountReplace() throws ParseException, SemanticException, LensExc
String query = "SELECT count(location_dim.name) FROM location_dim " +
"location_dim join time_dim time_dim on location_dim.time_id = time_dim.id " +
"WHERE ( time_dim . full_date ) between '2013-01-01 00:00:00' and '2013-01-04 00:00:00' LIMIT 10 ";
-
- SessionState.start(conf);
-
- String actual = qtest.rewrite(query, conf);
- String expected = "select sum(count_location_dim_name) from (select location_dim.time_id,count(( location_dim . name )) " +
- "as count_location_dim_name from location_dim location_dim where location_dim.time_id in " +
- "( select time_dim.id from time_dim where ( time_dim . full_date ) between " +
- "'2013-01-01 00:00:00' and '2013-01-04 00:00:00' ) group by location_dim.time_id) " +
- "location_dim inner join time_dim time_dim on (( location_dim . time_id ) = ( time_dim . id )) " +
- "where ( time_dim . full_date ) between '2013-01-01 00:00:00' and '2013-01-04 00:00:00' limit 10";
- compareQueries(expected, actual);
-}
+
+ SessionState.start(conf);
+
+ String actual = qtest.rewrite(query, conf);
+ String expected = "select sum(count_location_dim_location_dim_name) from "
+ + "(select location_dim_location_dim.time_id,count(( location_dim_location_dim . name )) "
+ + "as count_location_dim_location_dim_name from location_dim location_dim_location_dim "
+ + "where location_dim_location_dim.time_id in ( select time_dim_time_dim.id "
+ + "from time_dim_time_dim where ( time_dim_time_dim . full_date ) between "
+ + "'2013-01-01 00:00:00' and '2013-01-04 00:00:00' ) group by location_dim_location_dim.time_id) "
+ + "location_dim_location_dim inner join time_dim time_dim_time_dim on "
+ + "(( location_dim_location_dim . time_id ) = ( time_dim_time_dim . id )) "
+ + "where ( time_dim_time_dim . full_date ) between "
+ + "'2013-01-01 00:00:00' and '2013-01-04 00:00:00' limit 10";
+ compareQueries(expected, actual);
+ }
+
+ @Test
+ public void testReplaceAlias() throws ParseException, SemanticException, LensException {
+
+ String query = "select fact.time_key,time_dim.day_of_week,time_dim.day,"
+ + "case when sum(fact.dollars_sold) = 0 then 0.0 else sum(fact.dollars_sold) end dollars_sold "
+ + "from db.sales_fact as fact " + "inner join time_dim as time_dim on fact.time_key = time_dim.time_key "
+ + "inner join location_dim location_dim on fact.location_key = location_dim.location_key "
+ + "and location_dim.location_name = 'test123' "
+ + "where time_dim.time_key between '2013-01-01' and '2013-01-31' "
+ + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold desc ";
+
+ SessionState.start(conf);
+
+ String actual = qtest.rewrite(query, conf);
+ String expected = "select ( db_sales_fact_fact . time_key ), ( time_dim_time_dim . day_of_week ), "
+ + "( time_dim_time_dim . day ), case when (sum(sum_db_sales_fact_fact_dollars_sold) = 0 ) then 0.0 "
+ + "else sum(sum_db_sales_fact_fact_dollars_sold) end dollars_sold from "
+ + "(select db_sales_fact_fact.time_key,db_sales_fact_fact.location_key,sum(( db_sales_fact_fact . dollars_sold )) "
+ + "as sum_db_sales_fact_fact_dollars_sold from db.sales_fact db_sales_fact_fact "
+ + "where db_sales_fact_fact.time_key in ( select time_dim_time_dim.time_key "
+ + "from time_dim_time_dim where ( time_dim_time_dim . time_key ) between '2013-01-01' and '2013-01-31' ) "
+ + "and db_sales_fact_fact.location_key in ( select location_dim_location_dim.location_key "
+ + "from location_dim_location_dim where (( location_dim_location_dim . location_name ) = 'test123' ) ) "
+ + "group by db_sales_fact_fact.time_key,db_sales_fact_fact.location_key) db_sales_fact_fact "
+ + "inner join location_dim location_dim_location_dim on ((( db_sales_fact_fact . location_key ) "
+ + "= ( location_dim_location_dim . location_key )) and (( location_dim_location_dim . location_name )"
+ + " = 'test123' )) inner join time_dim time_dim_time_dim on (( db_sales_fact_fact . time_key ) "
+ + "= ( time_dim_time_dim . time_key )) where ( time_dim_time_dim . time_key ) between '2013-01-01' "
+ + "and '2013-01-31' group by dollars_sold desc order by dollars_sold desc";
+
+ compareQueries(expected, actual);
+ }
/**
* Test replace db name.