You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@quickstep.apache.org by zu...@apache.org on 2016/05/05 06:16:43 UTC
[07/30] incubator-quickstep git commit: Adds support for scalar
subqueries (#185)
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/parser/tests/Select.test
----------------------------------------------------------------------
diff --git a/parser/tests/Select.test b/parser/tests/Select.test
index 8a10a12..e70ee5c 100644
--- a/parser/tests/Select.test
+++ b/parser/tests/Select.test
@@ -543,16 +543,40 @@ SelectStatement
# Subqueries are not supported yet in clauses other than the FROM clause.
SELECT (select * FROM test) FROM test
--
-ERROR: syntax error (1 : 9)
-SELECT (select * FROM test) FROM test
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | +-SubqueryExpression
+ | +-Select
+ | +-select_clause=SelectStar
+ | +-from_clause=
+ | +-TableReference[table=test]
+ +-from_clause=
+ +-TableReference[table=test]
==
SELECT 1 FROM test WHERE 1 > (select 1 FROM test)
--
-ERROR: syntax error (1 : 31)
-SELECT 1 FROM test WHERE 1 > (select 1 FROM test)
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | +-Literal
+ | +-NumericLiteral[numeric_string=1,float_like=false]
+ +-where_clause=Greater
+ | +-left_operand=Literal
+ | | +-NumericLiteral[numeric_string=1,float_like=false]
+ | +-right_operand=SubqueryExpression
+ | +-Select
+ | +-select_clause=SelectList
+ | | +-SelectListItem
+ | | +-Literal
+ | | +-NumericLiteral[numeric_string=1,float_like=false]
+ | +-from_clause=
+ | +-TableReference[table=test]
+ +-from_clause=
+ +-TableReference[table=test]
==
#
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/parser/tests/TPCH.test
----------------------------------------------------------------------
diff --git a/parser/tests/TPCH.test b/parser/tests/TPCH.test
index 2d12df5..12d85ce 100644
--- a/parser/tests/TPCH.test
+++ b/parser/tests/TPCH.test
@@ -148,9 +148,97 @@ ORDER BY
p_partkey
LIMIT 100
--
-ERROR: syntax error (25 : 5)
- SELECT
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=s_acctbal]
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=s_name]
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=n_name]
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=p_partkey]
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=p_mfgr]
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=s_address]
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=s_phone]
+ | +-SelectListItem
+ | +-AttributeReference[attribute_name=s_comment]
+ +-where_clause=And
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=p_partkey]
+ | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
+ | | +-right_operand=AttributeReference[attribute_name=ps_suppkey]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=p_size]
+ | | +-right_operand=Literal
+ | | +-NumericLiteral[numeric_string=48,float_like=false]
+ | +-Like
+ | | +-left_operand=AttributeReference[attribute_name=p_type]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=%NICKEL]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
+ | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=n_regionkey]
+ | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=r_name]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=ASIA]
+ | +-Equal
+ | +-left_operand=AttributeReference[attribute_name=ps_supplycost]
+ | +-right_operand=SubqueryExpression
+ | +-Select
+ | +-select_clause=SelectList
+ | | +-SelectListItem
+ | | +-FunctionCall[name=MIN]
+ | | +-AttributeReference[attribute_name=ps_supplycost]
+ | +-where_clause=And
+ | | +-Equal
+ | | | +-left_operand=AttributeReference[attribute_name=p_partkey]
+ | | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
+ | | +-Equal
+ | | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
+ | | | +-right_operand=AttributeReference[attribute_name=ps_suppkey]
+ | | +-Equal
+ | | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
+ | | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
+ | | +-Equal
+ | | | +-left_operand=AttributeReference[attribute_name=n_regionkey]
+ | | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
+ | | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=r_name]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=ASIA]
+ | +-from_clause=
+ | +-TableReference[table=partsupp]
+ | +-TableReference[table=supplier]
+ | +-TableReference[table=nation]
+ | +-TableReference[table=region]
+ +-order_by=OrderBy
+ | +-OrderByItem[is_asc=false,nulls_first=true]
+ | | +-AttributeReference[attribute_name=s_acctbal]
+ | +-OrderByItem[is_asc=true,nulls_first=false]
+ | | +-AttributeReference[attribute_name=n_name]
+ | +-OrderByItem[is_asc=true,nulls_first=false]
+ | | +-AttributeReference[attribute_name=s_name]
+ | +-OrderByItem[is_asc=true,nulls_first=false]
+ | +-AttributeReference[attribute_name=p_partkey]
+ +-limit=LIMIT
+ | +-NumericLiteral[numeric_string=100,float_like=false]
+ +-from_clause=
+ +-TableReference[table=part]
+ +-TableReference[table=supplier]
+ +-TableReference[table=partsupp]
+ +-TableReference[table=nation]
+ +-TableReference[table=region]
==
# Query 3
@@ -949,9 +1037,70 @@ GROUP BY
ORDER BY
value DESC
--
-ERROR: syntax error (15 : 7)
- SELECT
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=ps_partkey]
+ | +-SelectListItem[alias=value]
+ | +-FunctionCall[name=SUM]
+ | +-Multiply
+ | +-left_operand=AttributeReference[attribute_name=ps_supplycost]
+ | +-right_operand=AttributeReference[attribute_name=ps_availqty]
+ +-where_clause=And
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=ps_suppkey]
+ | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
+ | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
+ | +-Equal
+ | +-left_operand=AttributeReference[attribute_name=n_name]
+ | +-right_operand=Literal
+ | +-StringLiteral[value=INDONESIA]
+ +-group_by=GroupBy
+ | +-AttributeReference[attribute_name=ps_partkey]
+ +-having=HAVING
+ | +-Greater
+ | +-left_operand=FunctionCall[name=SUM]
+ | | +-Multiply
+ | | +-left_operand=AttributeReference[attribute_name=ps_supplycost]
+ | | +-right_operand=AttributeReference[attribute_name=ps_availqty]
+ | +-right_operand=SubqueryExpression
+ | +-Select
+ | +-select_clause=SelectList
+ | | +-SelectListItem
+ | | +-Multiply
+ | | +-left_operand=FunctionCall[name=SUM]
+ | | | +-Multiply
+ | | | +-left_operand=AttributeReference[
+ | | | | attribute_name=ps_supplycost]
+ | | | +-right_operand=AttributeReference[
+ | | | attribute_name=ps_availqty]
+ | | +-right_operand=Literal
+ | | +-NumericLiteral[numeric_string=0.0000010000,float_like=true]
+ | +-where_clause=And
+ | | +-Equal
+ | | | +-left_operand=AttributeReference[attribute_name=ps_suppkey]
+ | | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
+ | | +-Equal
+ | | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
+ | | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
+ | | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=n_name]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=INDONESIA]
+ | +-from_clause=
+ | +-TableReference[table=partsupp]
+ | +-TableReference[table=supplier]
+ | +-TableReference[table=nation]
+ +-order_by=OrderBy
+ | +-OrderByItem[is_asc=false,nulls_first=true]
+ | +-AttributeReference[attribute_name=value]
+ +-from_clause=
+ +-TableReference[table=partsupp]
+ +-TableReference[table=supplier]
+ +-TableReference[table=nation]
==
# Query 12
@@ -1230,9 +1379,72 @@ FROM
ORDER BY
s_suppkey
--
-ERROR: syntax error (23 : 1)
-SELECT
-^
+SelectStatement
++-select_query=Select
+| +-select_clause=SelectList
+| | +-SelectListItem
+| | | +-AttributeReference[attribute_name=s_suppkey]
+| | +-SelectListItem
+| | | +-AttributeReference[attribute_name=s_name]
+| | +-SelectListItem
+| | | +-AttributeReference[attribute_name=s_address]
+| | +-SelectListItem
+| | | +-AttributeReference[attribute_name=s_phone]
+| | +-SelectListItem
+| | +-AttributeReference[attribute_name=total_revenue]
+| +-where_clause=And
+| | +-Equal
+| | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
+| | | +-right_operand=AttributeReference[attribute_name=supplier_no]
+| | +-Equal
+| | +-left_operand=AttributeReference[attribute_name=total_revenue]
+| | +-right_operand=SubqueryExpression
+| | +-Select
+| | +-select_clause=SelectList
+| | | +-SelectListItem
+| | | +-FunctionCall[name=MAX]
+| | | +-AttributeReference[attribute_name=total_revenue]
+| | +-from_clause=
+| | +-TableReference[table=revenue]
+| +-order_by=OrderBy
+| | +-OrderByItem[is_asc=true,nulls_first=false]
+| | +-AttributeReference[attribute_name=s_suppkey]
+| +-from_clause=
+| +-TableReference[table=supplier]
+| +-TableReference[table=revenue]
++-with_clause=
+ +-SubqueryTable
+ +-table_signature=TableSignature[table_alias=revenue,
+ | columns=(supplier_no, total_revenue)]
+ +-SubqueryExpression
+ +-Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=l_suppkey]
+ | +-SelectListItem
+ | +-FunctionCall[name=sum]
+ | +-Multiply
+ | +-left_operand=AttributeReference[attribute_name=l_extendedprice]
+ | +-right_operand=Subtract
+ | +-left_operand=Literal
+ | | +-NumericLiteral[numeric_string=1,float_like=false]
+ | +-right_operand=AttributeReference[attribute_name=l_discount]
+ +-where_clause=And
+ | +-GreaterOrEqual
+ | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=1996-11-01,explicit_type=Datetime]
+ | +-Less
+ | +-left_operand=AttributeReference[attribute_name=l_shipdate]
+ | +-right_operand=Add
+ | +-left_operand=Literal
+ | | +-StringLiteral[value=1996-11-01,explicit_type=Datetime]
+ | +-right_operand=Literal
+ | +-StringLiteral[value=3 month,explicit_type=YearMonthInterval]
+ +-group_by=GroupBy
+ | +-AttributeReference[attribute_name=l_suppkey]
+ +-from_clause=
+ +-TableReference[table=lineitem]
==
# Query 16
@@ -1361,9 +1573,46 @@ WHERE
l_partkey = p_partkey
)
--
-ERROR: syntax error (11 : 5)
- SELECT
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem[alias=avg_yearly]
+ | +-Divide
+ | +-left_operand=FunctionCall[name=SUM]
+ | | +-AttributeReference[attribute_name=l_extendedprice]
+ | +-right_operand=Literal
+ | +-NumericLiteral[numeric_string=7.0,float_like=true]
+ +-where_clause=And
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=p_partkey]
+ | | +-right_operand=AttributeReference[attribute_name=l_partkey]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=p_brand]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=Brand#24]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=p_container]
+ | | +-right_operand=Literal
+ | | +-StringLiteral[value=JUMBO BOX]
+ | +-Less
+ | +-left_operand=AttributeReference[attribute_name=l_quantity]
+ | +-right_operand=SubqueryExpression
+ | +-Select
+ | +-select_clause=SelectList
+ | | +-SelectListItem
+ | | +-Multiply
+ | | +-left_operand=Literal
+ | | | +-NumericLiteral[numeric_string=0.2,float_like=true]
+ | | +-right_operand=FunctionCall[name=AVG]
+ | | +-AttributeReference[attribute_name=l_quantity]
+ | +-where_clause=Equal
+ | | +-left_operand=AttributeReference[attribute_name=l_partkey]
+ | | +-right_operand=AttributeReference[attribute_name=p_partkey]
+ | +-from_clause=
+ | +-TableReference[table=lineitem]
+ +-from_clause=
+ +-TableReference[table=lineitem]
+ +-TableReference[table=part]
==
# Query 18
@@ -1694,9 +1943,87 @@ WHERE
ORDER BY
s_name
--
-ERROR: syntax error (23 : 9)
- SELECT
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=s_name]
+ | +-SelectListItem
+ | +-AttributeReference[attribute_name=s_address]
+ +-where_clause=And
+ | +-InTableQuery
+ | | +-test_expression=AttributeReference[attribute_name=s_suppkey]
+ | | +-table_query=SubqueryExpression
+ | | +-Select
+ | | +-select_clause=SelectList
+ | | | +-SelectListItem
+ | | | +-AttributeReference[attribute_name=ps_suppkey]
+ | | +-where_clause=And
+ | | | +-InTableQuery
+ | | | | +-test_expression=AttributeReference[attribute_name=ps_partkey]
+ | | | | +-table_query=SubqueryExpression
+ | | | | +-Select
+ | | | | +-select_clause=SelectList
+ | | | | | +-SelectListItem
+ | | | | | +-AttributeReference[attribute_name=p_partkey]
+ | | | | +-where_clause=Like
+ | | | | | +-left_operand=AttributeReference[attribute_name=p_name]
+ | | | | | +-right_operand=Literal
+ | | | | | +-StringLiteral[value=sandy%]
+ | | | | +-from_clause=
+ | | | | +-TableReference[table=part]
+ | | | +-Greater
+ | | | +-left_operand=AttributeReference[attribute_name=ps_availqty]
+ | | | +-right_operand=SubqueryExpression
+ | | | +-Select
+ | | | +-select_clause=SelectList
+ | | | | +-SelectListItem
+ | | | | +-Multiply
+ | | | | +-left_operand=Literal
+ | | | | | +-NumericLiteral[numeric_string=0.5,float_like=true]
+ | | | | +-right_operand=FunctionCall[name=SUM]
+ | | | | +-AttributeReference[attribute_name=l_quantity]
+ | | | +-where_clause=And
+ | | | | +-Equal
+ | | | | | +-left_operand=AttributeReference[attribute_name=l_partkey]
+ | | | | | +-right_operand=AttributeReference[
+ | | | | | attribute_name=ps_partkey]
+ | | | | +-Equal
+ | | | | | +-left_operand=AttributeReference[attribute_name=l_suppkey]
+ | | | | | +-right_operand=AttributeReference[
+ | | | | | attribute_name=ps_suppkey]
+ | | | | +-GreaterOrEqual
+ | | | | | +-left_operand=AttributeReference[
+ | | | | | | attribute_name=l_shipdate]
+ | | | | | +-right_operand=Literal
+ | | | | | +-StringLiteral[value=1993-01-01,explicit_type=Datetime]
+ | | | | +-Less
+ | | | | +-left_operand=AttributeReference[
+ | | | | | attribute_name=l_shipdate]
+ | | | | +-right_operand=Add
+ | | | | +-left_operand=Literal
+ | | | | | +-StringLiteral[value=1993-01-01,
+ | | | | | explicit_type=Datetime]
+ | | | | +-right_operand=Literal
+ | | | | +-StringLiteral[value=1 year,
+ | | | | explicit_type=YearMonthInterval]
+ | | | +-from_clause=
+ | | | +-TableReference[table=lineitem]
+ | | +-from_clause=
+ | | +-TableReference[table=partsupp]
+ | +-Equal
+ | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
+ | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
+ | +-Equal
+ | +-left_operand=AttributeReference[attribute_name=n_name]
+ | +-right_operand=Literal
+ | +-StringLiteral[value=GERMANY]
+ +-order_by=OrderBy
+ | +-OrderByItem[is_asc=true,nulls_first=false]
+ | +-AttributeReference[attribute_name=s_name]
+ +-from_clause=
+ +-TableReference[table=supplier]
+ +-TableReference[table=nation]
==
# Query 21
@@ -1870,6 +2197,106 @@ GROUP BY
ORDER BY
cntrycode
--
-ERROR: syntax error (16 : 9)
- SELECT
- ^
+SelectStatement
++-select_query=Select
+ +-select_clause=SelectList
+ | +-SelectListItem
+ | | +-AttributeReference[attribute_name=cntrycode]
+ | +-SelectListItem[alias=numcust]
+ | | +-FunctionCall[name=COUNT,is_star=true]
+ | +-SelectListItem[alias=totacctbal]
+ | +-FunctionCall[name=SUM]
+ | +-AttributeReference[attribute_name=c_acctbal]
+ +-group_by=GroupBy
+ | +-AttributeReference[attribute_name=cntrycode]
+ +-order_by=OrderBy
+ | +-OrderByItem[is_asc=true,nulls_first=false]
+ | +-AttributeReference[attribute_name=cntrycode]
+ +-from_clause=
+ +-SubqueryTable
+ +-table_signature=TableSignature[table_alias=custsale]
+ +-SubqueryExpression
+ +-Select
+ +-select_clause=SelectList
+ | +-SelectListItem[alias=cntrycode]
+ | | +-FunctionCall[name=SUBSTR]
+ | | +-AttributeReference[attribute_name=c_phone]
+ | | +-Literal
+ | | | +-NumericLiteral[numeric_string=1,float_like=false]
+ | | +-Literal
+ | | +-NumericLiteral[numeric_string=2,float_like=false]
+ | +-SelectListItem
+ | +-AttributeReference[attribute_name=c_acctbal]
+ +-where_clause=And
+ | +-InValueList
+ | | +-test_expression=FunctionCall[name=SUBSTR]
+ | | | +-AttributeReference[attribute_name=c_phone]
+ | | | +-Literal
+ | | | | +-NumericLiteral[numeric_string=1,float_like=false]
+ | | | +-Literal
+ | | | +-NumericLiteral[numeric_string=2,float_like=false]
+ | | +-value_list=
+ | | +-Literal
+ | | | +-StringLiteral[value=27]
+ | | +-Literal
+ | | | +-StringLiteral[value=44]
+ | | +-Literal
+ | | | +-StringLiteral[value=34]
+ | | +-Literal
+ | | | +-StringLiteral[value=25]
+ | | +-Literal
+ | | | +-StringLiteral[value=30]
+ | | +-Literal
+ | | | +-StringLiteral[value=33]
+ | | +-Literal
+ | | +-StringLiteral[value=23]
+ | +-Greater
+ | | +-left_operand=AttributeReference[attribute_name=c_acctbal]
+ | | +-right_operand=SubqueryExpression
+ | | +-Select
+ | | +-select_clause=SelectList
+ | | | +-SelectListItem
+ | | | +-FunctionCall[name=AVG]
+ | | | +-AttributeReference[attribute_name=c_acctbal]
+ | | +-where_clause=And
+ | | | +-Greater
+ | | | | +-left_operand=AttributeReference[attribute_name=c_acctbal]
+ | | | | +-right_operand=Literal
+ | | | | +-NumericLiteral[numeric_string=0.00,float_like=true]
+ | | | +-InValueList
+ | | | +-test_expression=FunctionCall[name=SUBSTR]
+ | | | | +-AttributeReference[attribute_name=c_phone]
+ | | | | +-Literal
+ | | | | | +-NumericLiteral[numeric_string=1,float_like=false]
+ | | | | +-Literal
+ | | | | +-NumericLiteral[numeric_string=2,float_like=false]
+ | | | +-value_list=
+ | | | +-Literal
+ | | | | +-StringLiteral[value=27]
+ | | | +-Literal
+ | | | | +-StringLiteral[value=44]
+ | | | +-Literal
+ | | | | +-StringLiteral[value=34]
+ | | | +-Literal
+ | | | | +-StringLiteral[value=25]
+ | | | +-Literal
+ | | | | +-StringLiteral[value=30]
+ | | | +-Literal
+ | | | | +-StringLiteral[value=33]
+ | | | +-Literal
+ | | | +-StringLiteral[value=23]
+ | | +-from_clause=
+ | | +-TableReference[table=customer]
+ | +-Not
+ | +-Exists
+ | +-subquery=SubqueryExpression
+ | +-Select
+ | +-select_clause=SelectStar
+ | +-where_clause=Equal
+ | | +-left_operand=AttributeReference[attribute_name=o_custkey]
+ | | +-right_operand=AttributeReference[
+ | | attribute_name=c_custkey]
+ | +-from_clause=
+ | +-TableReference[table=orders]
+ +-from_clause=
+ +-TableReference[table=customer]
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/expressions/SubqueryExpression.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/SubqueryExpression.cpp b/query_optimizer/expressions/SubqueryExpression.cpp
index 0459212..3a33fe1 100644
--- a/query_optimizer/expressions/SubqueryExpression.cpp
+++ b/query_optimizer/expressions/SubqueryExpression.cpp
@@ -40,9 +40,14 @@ namespace expressions {
}
std::vector<AttributeReferencePtr> SubqueryExpression::getReferencedAttributes() const {
- // SubqueryExpression should be eliminated before any place that needs
- // a call of getReferencedAttributes.
- LOG(FATAL) << "SubqueryExpression::getReferencedAttributes() is not implemented";
+ // Note(jianqiao): Here simply return an empty set so that we skip the validation
+ // for this expression at the end of the resolving phase (otherwise we need to
+ // revise the Validate() function to deal with OUTER scoped attributes). Note
+ // that SubqueryExpression will always be eliminated by UnnestSubqueries as the
+ // first logical optimization pass in LogicalGenerator. So any dangling attribute
+ // will still be detected by Validate() at the end of the logical optimization
+ // phase.
+ return {};
}
void SubqueryExpression::getFieldStringItems(
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/resolver/Resolver.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/resolver/Resolver.cpp b/query_optimizer/resolver/Resolver.cpp
index f70dae0..8ddcf3f 100644
--- a/query_optimizer/resolver/Resolver.cpp
+++ b/query_optimizer/resolver/Resolver.cpp
@@ -1997,8 +1997,12 @@ E::ScalarPtr Resolver::resolveExpression(
expression_resolution_info);
}
case ParseExpression::kSubqueryExpression: {
- THROW_SQL_ERROR_AT(&parse_expression)
- << "Subquery expression in a non-FROM clause is not supported yet";
+ const std::vector<const Type*> type_hints = { type_hint };
+ return resolveSubqueryExpression(
+ static_cast<const ParseSubqueryExpression&>(parse_expression),
+ &type_hints,
+ expression_resolution_info,
+ true /* has_single_column */);
}
case ParseExpression::kExtract: {
const ParseExtractFunction &parse_extract =
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/rules/CMakeLists.txt
----------------------------------------------------------------------
diff --git a/query_optimizer/rules/CMakeLists.txt b/query_optimizer/rules/CMakeLists.txt
index bc70d2d..7032af5 100644
--- a/query_optimizer/rules/CMakeLists.txt
+++ b/query_optimizer/rules/CMakeLists.txt
@@ -135,6 +135,7 @@ target_link_libraries(quickstep_queryoptimizer_rules_UnnestSubqueries
quickstep_queryoptimizer_logical_HashJoin
quickstep_queryoptimizer_logical_Logical
quickstep_queryoptimizer_logical_LogicalType
+ quickstep_queryoptimizer_logical_MultiwayCartesianJoin
quickstep_queryoptimizer_logical_PatternMatcher
quickstep_queryoptimizer_logical_Project
quickstep_queryoptimizer_logical_TopLevelPlan
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/rules/UnnestSubqueries.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/rules/UnnestSubqueries.cpp b/query_optimizer/rules/UnnestSubqueries.cpp
index 7852577..d22ab8e 100644
--- a/query_optimizer/rules/UnnestSubqueries.cpp
+++ b/query_optimizer/rules/UnnestSubqueries.cpp
@@ -18,7 +18,9 @@
#include "query_optimizer/rules/UnnestSubqueries.hpp"
#include <algorithm>
+#include <functional>
#include <memory>
+#include <set>
#include <utility>
#include <vector>
@@ -41,6 +43,7 @@
#include "query_optimizer/logical/HashJoin.hpp"
#include "query_optimizer/logical/Logical.hpp"
#include "query_optimizer/logical/LogicalType.hpp"
+#include "query_optimizer/logical/MultiwayCartesianJoin.hpp"
#include "query_optimizer/logical/PatternMatcher.hpp"
#include "query_optimizer/logical/Project.hpp"
#include "query_optimizer/logical/TopLevelPlan.hpp"
@@ -62,7 +65,8 @@ struct CorrelatedQueryInfo {
enum class JoinType {
kInnerJoin = 0,
kLeftSemiJoin,
- kLeftAntiJoin
+ kLeftAntiJoin,
+ kCartesianJoin
};
CorrelatedQueryInfo(const JoinType join_type_in,
@@ -464,9 +468,29 @@ L::LogicalPtr UnnestSubqueriesForNonRootLogical::eliminateNestedScalarQueries(co
new_child = node->children()[0];
}
- for (CorrelatedQueryInfo &correlated_query_info : correlated_query_info_vec) {
- DCHECK(!correlated_query_info.probe_join_attributes.empty());
+ // Join uncorrelated subqueries early.
+ L::LogicalPtr uncorrelated_query_child;
+ for (const CorrelatedQueryInfo &correlated_query_info : correlated_query_info_vec) {
+ if (correlated_query_info.join_type == CorrelatedQueryInfo::JoinType::kCartesianJoin) {
+ // The only case for this nested loop join is that it is an uncorrelated
+ // subquery which returns a scalar (single column and single row) result.
+ DCHECK(correlated_query_info.probe_join_attributes.empty());
+ DCHECK_EQ(0u, correlated_query_info.non_hash_join_predicates.size());
+ if (uncorrelated_query_child == nullptr) {
+ uncorrelated_query_child = correlated_query_info.correlated_query;
+ } else {
+ uncorrelated_query_child = L::MultiwayCartesianJoin::Create(
+ { uncorrelated_query_child, correlated_query_info.correlated_query });
+ }
+ }
+ }
+ if (uncorrelated_query_child != nullptr) {
+ new_child = L::MultiwayCartesianJoin::Create({ new_child, uncorrelated_query_child });
+ }
+
+ for (const CorrelatedQueryInfo &correlated_query_info : correlated_query_info_vec) {
if (correlated_query_info.join_type == CorrelatedQueryInfo::JoinType::kInnerJoin) {
+ DCHECK(!correlated_query_info.probe_join_attributes.empty());
DCHECK(correlated_query_info.non_hash_join_predicates.empty())
<< correlated_query_info.non_hash_join_predicates[0]->toString();
new_child = L::HashJoin::Create(new_child,
@@ -475,7 +499,9 @@ L::LogicalPtr UnnestSubqueriesForNonRootLogical::eliminateNestedScalarQueries(co
correlated_query_info.build_join_attributes,
nullptr, /* residual_predicate */
L::HashJoin::JoinType::kInnerJoin);
- } else {
+ } else if (correlated_query_info.join_type == CorrelatedQueryInfo::JoinType::kLeftSemiJoin ||
+ correlated_query_info.join_type == CorrelatedQueryInfo::JoinType::kLeftAntiJoin) {
+ DCHECK(!correlated_query_info.probe_join_attributes.empty());
E::PredicatePtr filter_predicate;
if (correlated_query_info.non_hash_join_predicates.size() > 1u) {
filter_predicate = E::LogicalAnd::Create(correlated_query_info.non_hash_join_predicates);
@@ -545,16 +571,11 @@ E::ExpressionPtr UnnestSubqueriesForExpession::applyInternal(
if (probe_join_attributes.empty()) {
DCHECK(non_hash_join_predicates.empty());
DCHECK_EQ(1u, new_subquery->getOutputAttributes().size()) << node->toString();
- const E::AttributeReferencePtr new_outer_attribute_reference =
- E::AttributeReference::Create(context_->nextExprId(),
- output_attribute->attribute_name(),
- output_attribute->attribute_alias(),
- output_attribute->relation_name(),
- output_attribute->getValueType(),
- E::AttributeReferenceScope::kOuter);
- uncorrelated_subqueries_->emplace(new_outer_attribute_reference->id(),
- new_subquery);
- return new_outer_attribute_reference;
+ correlated_query_info_vec_->emplace_back(CorrelatedQueryInfo::JoinType::kCartesianJoin,
+ new_subquery,
+ std::move(probe_join_attributes),
+ std::move(build_join_attributes),
+ std::move(non_hash_join_predicates));
} else {
correlated_query_info_vec_->emplace_back(CorrelatedQueryInfo::JoinType::kInnerJoin,
new_subquery,
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/tests/execution_generator/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/execution_generator/Select.test b/query_optimizer/tests/execution_generator/Select.test
index 0618ae2..47c1077 100644
--- a/query_optimizer/tests/execution_generator/Select.test
+++ b/query_optimizer/tests/execution_generator/Select.test
@@ -820,11 +820,56 @@ WHERE i NOT IN (
+-----------+
==
-# TODO(team): Support uncorrelated queries.
-# SELECT COUNT(*)
-# FROM test
-# WHERE double_col < 0
-# AND long_col > (SELECT AVG(long_col) FROM test)
+# Scalar subquery expression
+SELECT *
+FROM test
+WHERE double_col < 0
+ AND long_col > (SELECT AVG(long_col) FROM test);
+--
++-----------+--------------------+---------------+------------------------+--------------------+
+|int_col |long_col |float_col |double_col |char_col |
++-----------+--------------------+---------------+------------------------+--------------------+
+| -15| 225| 3.87298346| -58.094750193111253| -15 3.872983|
+| -17| 289| 4.12310553| -70.092795635500224| -17 4.123106|
+| -19| 361| 4.35889912| -82.81907992727281| -19 4.358899|
+| -21| 441| 4.5825758| -96.234089594072643| -21 4.582576|
+| -23| 529| 4.79583168| -110.30412503619254| -23 4.795832|
++-----------+--------------------+---------------+------------------------+--------------------+
+==
+
+SELECT i + (
+ SELECT SUM(j)
+ FROM generate_series(1, 10) AS gs(j))
+FROM generate_series(1, 5) AS gs(i);
+--
++----------------------+
+|(i+SubqueryExpression)|
++----------------------+
+| 56|
+| 57|
+| 58|
+| 59|
+| 60|
++----------------------+
+==
+
+SELECT i + (
+ SELECT SUM(j)
+ FROM generate_series(1, 10) AS gs1(j),
+ generate_series(1, 10) AS gs2(k)
+ WHERE i = k AND j <= k)
+FROM generate_series(1, 5) AS gs(i);
+--
++----------------------+
+|(i+SubqueryExpression)|
++----------------------+
+| 2|
+| 5|
+| 9|
+| 14|
+| 20|
++----------------------+
+==
# TODO(team): Fix Issue #9 to enable COUNT(*).
SELECT COUNT(long_col)
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/tests/logical_generator/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/logical_generator/Select.test b/query_optimizer/tests/logical_generator/Select.test
index c6d4201..6dff3e7 100644
--- a/query_optimizer/tests/logical_generator/Select.test
+++ b/query_optimizer/tests/logical_generator/Select.test
@@ -896,3 +896,373 @@ TopLevelPlan
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
+-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+==
+
+# Scalar subquery expressions
+SELECT x + (SELECT SUM(y) FROM c)
+FROM b;
+--
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-[]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-join_predicate=Literal[value=true]
+| +-project_list=
+| +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x)
+FROM b;
+--
+TopLevelPlan
++-plan=Project
+| +-input=HashJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-left_join_attributes=
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-right_join_attributes=
+| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| +-project_list=
+| +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT *
+FROM b
+WHERE b.x > (SELECT SUM(y) FROM c);
+--
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-[]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-join_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-project_list=
+| +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+==
+
+SELECT *
+FROM b
+WHERE b.x > (SELECT SUM(y) FROM c WHERE b.w = c.x);
+--
+TopLevelPlan
++-plan=Project
+| +-input=Filter
+| | +-input=HashJoin
+| | | +-left=TableReference[relation_name=b]
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | | +-right=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=c]
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Long NULL]
+| | | | | +-AggregateFunction[function=SUM]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-project_list=
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Long NULL]
+| | | +-left_join_attributes=
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-right_join_attributes=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | +-filter_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-project_list=
+| +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+==
+
+SELECT x + (
+ SELECT SUM(y) + (SELECT SUM(w) FROM a WHERE a.y > 10)
+ FROM c
+ WHERE b.w = c.x AND c.x < 10)
+FROM b;
+--
+TopLevelPlan
++-plan=Project
+| +-input=HashJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=NestedLoopsJoin
+| | | | +-left=Aggregate
+| | | | | +-input=Filter
+| | | | | | +-input=TableReference[relation_name=c]
+| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | | | +-filter_predicate=Less
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | +-Literal[value=10,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Long NULL]
+| | | | | +-AggregateFunction[function=SUM]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-right=Project
+| | | | | +-input=Aggregate
+| | | | | | +-input=Filter
+| | | | | | | +-input=TableReference[relation_name=a]
+| | | | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | | | | | | | +-AttributeReference[id=6,name=x,relation=a,type=Int]
+| | | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | | | | | +-AttributeReference[id=8,name=z,relation=a,type=Int]
+| | | | | | | +-filter_predicate=Greater
+| | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | | | | +-Literal[value=10,type=Int]
+| | | | | | +-grouping_expressions=
+| | | | | | | +-[]
+| | | | | | +-aggregate_expressions=
+| | | | | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | | type=Long NULL]
+| | | | | | +-AggregateFunction[function=SUM]
+| | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | | | | +-project_list=
+| | | | | +-Alias[id=9,name=,alias=SUM(w),relation=,type=Long NULL]
+| | | | | +-AttributeReference[id=9,name=,alias=$aggregate0,
+| | | | | relation=$aggregate,type=Long NULL]
+| | | | +-join_predicate=Literal[value=true]
+| | | +-project_list=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=,
+| | | type=Long NULL]
+| | | +-Add
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Long NULL]
+| | | +-AttributeReference[id=9,name=,alias=SUM(w),relation=,
+| | | type=Long NULL]
+| | +-left_join_attributes=
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-right_join_attributes=
+| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| +-project_list=
+| +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression),
+| relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT x * (SELECT SUM(y) FROM c) + (SELECT AVG(y) FROM d)
+FROM b;
+--
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=NestedLoopsJoin
+| | | +-left=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=c]
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-[]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Long NULL]
+| | | | | +-AggregateFunction[function=SUM]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-project_list=
+| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Long NULL]
+| | | +-right=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=d]
+| | | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
+| | | | | | +-AttributeReference[id=6,name=z,relation=d,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-[]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Double NULL]
+| | | | | +-AggregateFunction[function=AVG]
+| | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
+| | | | +-project_list=
+| | | | +-Alias[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
+| | | | +-AttributeReference[id=7,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Double NULL]
+| | | +-join_predicate=Literal[value=true]
+| | +-join_predicate=Literal[value=true]
+| +-project_list=
+| +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression),
+| relation=,type=Double NULL]
+| +-Add
+| +-Multiply
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
++-output_attributes=
+ +-AttributeReference[id=8,name=,
+ alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL]
+==
+
+SELECT x * (SELECT SUM(y) FROM c)
+FROM b
+WHERE w < (SELECT AVG(y) FROM d);
+--
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=NestedLoopsJoin
+| | | +-left=TableReference[relation_name=b]
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | | +-right=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=d]
+| | | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
+| | | | | | +-AttributeReference[id=3,name=z,relation=d,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-[]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Double NULL]
+| | | | | +-AggregateFunction[function=AVG]
+| | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
+| | | | +-project_list=
+| | | | +-Alias[id=4,name=,alias=AVG(y),relation=,type=Double NULL]
+| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Double NULL]
+| | | +-join_predicate=Less
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=4,name=,alias=AVG(y),relation=,type=Double NULL]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=5,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-[]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-Alias[id=7,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-join_predicate=Literal[value=true]
+| +-project_list=
+| +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL]
+| +-Multiply
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=7,name=,alias=SUM(y),relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT x + (SELECT SUM(y) FROM c WHERE b.w > c.x)
+FROM b;
+--
+ERROR: Non-equality join predicate is not allowed in scalar subqueries
+==
+
+SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x AND b.x > c.y)
+FROM b;
+--
+ERROR: Non-equality join predicate is not allowed in scalar subqueries
+==
+
+SELECT x + (
+ SELECT SUM(y) + (
+ SELECT SUM(w)
+ FROM a WHERE a.y = b.x)
+ FROM c
+ WHERE b.w = c.x AND c.x < 10)
+FROM b;
+--
+ERROR: Nested queries can only reference attributes in the outer query one level above
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/tests/physical_generator/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/physical_generator/Select.test b/query_optimizer/tests/physical_generator/Select.test
index 2fa3720..b405bc9 100644
--- a/query_optimizer/tests/physical_generator/Select.test
+++ b/query_optimizer/tests/physical_generator/Select.test
@@ -2113,3 +2113,617 @@ TopLevelPlan
| +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+-output_attributes=
+-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+==
+
+
+# Scalar subquery expressions
+SELECT x + (SELECT SUM(y) FROM c)
+FROM b;
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-[]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-join_predicate=Literal[value=true]
+| +-project_list=
+| +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+[Physical Plan]
+TopLevelPlan
++-plan=NestedLoopsJoin
+| +-left=TableReference[relation=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=Aggregate
+| | +-input=TableReference[relation=c]
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-grouping_expressions=
+| | | +-[]
+| | +-aggregate_expressions=
+| | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| +-join_predicate=Literal[value=true]
+| +-project_expressions=
+| +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x)
+FROM b;
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=HashJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-left_join_attributes=
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-right_join_attributes=
+| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| +-project_list=
+| +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+[Physical Plan]
+TopLevelPlan
++-plan=HashJoin
+| +-left=TableReference[relation=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=Aggregate
+| | +-input=TableReference[relation=c]
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-grouping_expressions=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | +-aggregate_expressions=
+| | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| +-project_expressions=
+| | +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| | +-Add
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Long NULL]
+| +-left_join_attributes=
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-right_join_attributes=
+| +-AttributeReference[id=2,name=x,relation=c,type=Int]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT *
+FROM b
+WHERE b.x > (SELECT SUM(y) FROM c);
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-[]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-join_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-project_list=
+| +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+[Physical Plan]
+TopLevelPlan
++-plan=NestedLoopsJoin
+| +-left=TableReference[relation=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=Aggregate
+| | +-input=TableReference[relation=c]
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-grouping_expressions=
+| | | +-[]
+| | +-aggregate_expressions=
+| | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| +-join_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Long NULL]
+| +-project_expressions=
+| +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+==
+
+SELECT *
+FROM b
+WHERE b.x > (SELECT SUM(y) FROM c WHERE b.w = c.x);
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=Filter
+| | +-input=HashJoin
+| | | +-left=TableReference[relation_name=b]
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | | +-right=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=c]
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Long NULL]
+| | | | | +-AggregateFunction[function=SUM]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-project_list=
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Long NULL]
+| | | +-left_join_attributes=
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-right_join_attributes=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | +-filter_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-project_list=
+| +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+[Physical Plan]
+TopLevelPlan
++-plan=HashJoin
+| +-left=TableReference[relation=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=Aggregate
+| | +-input=TableReference[relation=c]
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-grouping_expressions=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | +-aggregate_expressions=
+| | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| +-residual_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Long NULL]
+| +-project_expressions=
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-left_join_attributes=
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-right_join_attributes=
+| +-AttributeReference[id=2,name=x,relation=c,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+==
+
+SELECT x + (
+ SELECT SUM(y) + (SELECT SUM(w) FROM a WHERE a.y > 10)
+ FROM c
+ WHERE b.w = c.x AND c.x < 10)
+FROM b;
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=HashJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Project
+| | | +-input=NestedLoopsJoin
+| | | | +-left=Aggregate
+| | | | | +-input=Filter
+| | | | | | +-input=TableReference[relation_name=c]
+| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | | | +-filter_predicate=Less
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | +-Literal[value=10,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Long NULL]
+| | | | | +-AggregateFunction[function=SUM]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-right=Project
+| | | | | +-input=Aggregate
+| | | | | | +-input=Filter
+| | | | | | | +-input=TableReference[relation_name=a]
+| | | | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | | | | | | | +-AttributeReference[id=6,name=x,relation=a,type=Int]
+| | | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | | | | | +-AttributeReference[id=8,name=z,relation=a,type=Int]
+| | | | | | | +-filter_predicate=Greater
+| | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | | | | +-Literal[value=10,type=Int]
+| | | | | | +-grouping_expressions=
+| | | | | | | +-[]
+| | | | | | +-aggregate_expressions=
+| | | | | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | | type=Long NULL]
+| | | | | | +-AggregateFunction[function=SUM]
+| | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | | | | +-project_list=
+| | | | | +-Alias[id=9,name=,alias=SUM(w),relation=,type=Long NULL]
+| | | | | +-AttributeReference[id=9,name=,alias=$aggregate0,
+| | | | | relation=$aggregate,type=Long NULL]
+| | | | +-join_predicate=Literal[value=true]
+| | | +-project_list=
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=,
+| | | type=Long NULL]
+| | | +-Add
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Long NULL]
+| | | +-AttributeReference[id=9,name=,alias=SUM(w),relation=,
+| | | type=Long NULL]
+| | +-left_join_attributes=
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-right_join_attributes=
+| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| +-project_list=
+| +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression),
+| relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+[Physical Plan]
+TopLevelPlan
++-plan=HashJoin
+| +-left=TableReference[relation=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=NestedLoopsJoin
+| | +-left=Aggregate
+| | | +-input=TableReference[relation=c]
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-filter_predicate=Less
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-Literal[value=10,type=Int]
+| | | +-grouping_expressions=
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-aggregate_expressions=
+| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | | +-AggregateFunction[function=SUM]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-right=Aggregate
+| | | +-input=TableReference[relation=a]
+| | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | | | +-AttributeReference[id=6,name=x,relation=a,type=Int]
+| | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | +-AttributeReference[id=8,name=z,relation=a,type=Int]
+| | | +-filter_predicate=Greater
+| | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | +-Literal[value=10,type=Int]
+| | | +-grouping_expressions=
+| | | | +-[]
+| | | +-aggregate_expressions=
+| | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | | +-AggregateFunction[function=SUM]
+| | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | +-join_predicate=Literal[value=true]
+| | +-project_expressions=
+| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=,
+| | type=Long NULL]
+| | +-Add
+| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-AttributeReference[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Long NULL]
+| +-project_expressions=
+| | +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| | +-Add
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression),
+| | relation=,type=Long NULL]
+| +-left_join_attributes=
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-right_join_attributes=
+| +-AttributeReference[id=2,name=x,relation=c,type=Int]
++-output_attributes=
+ +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT x * (SELECT SUM(y) FROM c) + (SELECT AVG(y) FROM d)
+FROM b;
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=NestedLoopsJoin
+| | | +-left=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=c]
+| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-[]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Long NULL]
+| | | | | +-AggregateFunction[function=SUM]
+| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | | +-project_list=
+| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Long NULL]
+| | | +-right=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=d]
+| | | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
+| | | | | | +-AttributeReference[id=6,name=z,relation=d,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-[]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Double NULL]
+| | | | | +-AggregateFunction[function=AVG]
+| | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
+| | | | +-project_list=
+| | | | +-Alias[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
+| | | | +-AttributeReference[id=7,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Double NULL]
+| | | +-join_predicate=Literal[value=true]
+| | +-join_predicate=Literal[value=true]
+| +-project_list=
+| +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression),
+| relation=,type=Double NULL]
+| +-Add
+| +-Multiply
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
++-output_attributes=
+ +-AttributeReference[id=8,name=,
+ alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL]
+[Physical Plan]
+TopLevelPlan
++-plan=NestedLoopsJoin
+| +-left=TableReference[relation=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=NestedLoopsJoin
+| | +-left=Aggregate
+| | | +-input=TableReference[relation=c]
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-grouping_expressions=
+| | | | +-[]
+| | | +-aggregate_expressions=
+| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | | +-AggregateFunction[function=SUM]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-right=Aggregate
+| | | +-input=TableReference[relation=d]
+| | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
+| | | | +-AttributeReference[id=6,name=z,relation=d,type=Int]
+| | | +-grouping_expressions=
+| | | | +-[]
+| | | +-aggregate_expressions=
+| | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Double NULL]
+| | | +-AggregateFunction[function=AVG]
+| | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
+| | +-join_predicate=Literal[value=true]
+| | +-project_expressions=
+| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Double NULL]
+| +-join_predicate=Literal[value=true]
+| +-project_expressions=
+| +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression),
+| relation=,type=Double NULL]
+| +-Add
+| +-Multiply
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
++-output_attributes=
+ +-AttributeReference[id=8,name=,
+ alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL]
+==
+
+SELECT x * (SELECT SUM(y) FROM c)
+FROM b
+WHERE w < (SELECT AVG(y) FROM d);
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=NestedLoopsJoin
+| | +-left=NestedLoopsJoin
+| | | +-left=TableReference[relation_name=b]
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | | +-right=Project
+| | | | +-input=Aggregate
+| | | | | +-input=TableReference[relation_name=d]
+| | | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
+| | | | | | +-AttributeReference[id=3,name=z,relation=d,type=Int]
+| | | | | +-grouping_expressions=
+| | | | | | +-[]
+| | | | | +-aggregate_expressions=
+| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | type=Double NULL]
+| | | | | +-AggregateFunction[function=AVG]
+| | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
+| | | | +-project_list=
+| | | | +-Alias[id=4,name=,alias=AVG(y),relation=,type=Double NULL]
+| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | | | relation=$aggregate,type=Double NULL]
+| | | +-join_predicate=Less
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=4,name=,alias=AVG(y),relation=,type=Double NULL]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=c]
+| | | | | +-AttributeReference[id=5,name=x,relation=c,type=Int]
+| | | | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-[]
+| | | | +-aggregate_expressions=
+| | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | | type=Long NULL]
+| | | | +-AggregateFunction[function=SUM]
+| | | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
+| | | +-project_list=
+| | | +-Alias[id=7,name=,alias=SUM(y),relation=,type=Long NULL]
+| | | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | +-join_predicate=Literal[value=true]
+| +-project_list=
+| +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL]
+| +-Multiply
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=7,name=,alias=SUM(y),relation=,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=,
+ type=Long NULL]
+[Physical Plan]
+TopLevelPlan
++-plan=NestedLoopsJoin
+| +-left=NestedLoopsJoin
+| | +-left=TableReference[relation=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-right=Aggregate
+| | | +-input=TableReference[relation=d]
+| | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
+| | | | +-AttributeReference[id=3,name=z,relation=d,type=Int]
+| | | +-grouping_expressions=
+| | | | +-[]
+| | | +-aggregate_expressions=
+| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Double NULL]
+| | | +-AggregateFunction[function=AVG]
+| | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
+| | +-join_predicate=Less
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Double NULL]
+| | +-project_expressions=
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-right=Aggregate
+| | +-input=TableReference[relation=c]
+| | | +-AttributeReference[id=5,name=x,relation=c,type=Int]
+| | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
+| | +-grouping_expressions=
+| | | +-[]
+| | +-aggregate_expressions=
+| | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=6,name=y,relation=c,type=Int]
+| +-join_predicate=Literal[value=true]
+| +-project_expressions=
+| +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL]
+| +-Multiply
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
+| type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=,
+ type=Long NULL]
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/9b3d9156/query_optimizer/tests/resolver/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/resolver/Select.test b/query_optimizer/tests/resolver/Select.test
index 82bdb55..00ff18a 100644
--- a/query_optimizer/tests/resolver/Select.test
+++ b/query_optimizer/tests/resolver/Select.test
@@ -2793,3 +2793,147 @@ WHERE i IN (
ERROR: Subquery must return exactly one column (3 : 12)
WHERE i IN (
^
+==
+
+# Scalar subquery expressions
+SELECT x + (SELECT SUM(y) FROM c)
+FROM b;
+--
+TopLevelPlan
++-plan=Project
+| +-input=TableReference[relation_name=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-project_list=
+| +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-SubqueryExpression
+| +-subquery=Project
+| +-input=Aggregate
+| | +-input=TableReference[relation_name=c]
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-grouping_expressions=
+| | | +-[]
+| | +-aggregate_expressions=
+| | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| +-project_list=
+| +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| +-AttributeReference[id=4,name=,alias=$aggregate0,
+| relation=$aggregate,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]
+==
+
+SELECT *
+FROM b
+WHERE b.x > (SELECT SUM(y) FROM c);
+--
+TopLevelPlan
++-plan=Project
+| +-input=Filter
+| | +-input=TableReference[relation_name=b]
+| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-filter_predicate=Greater
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| | +-SubqueryExpression
+| | +-subquery=Project
+| | +-input=Aggregate
+| | | +-input=TableReference[relation_name=c]
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-grouping_expressions=
+| | | | +-[]
+| | | +-aggregate_expressions=
+| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | | type=Long NULL]
+| | | +-AggregateFunction[function=SUM]
+| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | +-project_list=
+| | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+| | +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | relation=$aggregate,type=Long NULL]
+| +-project_list=
+| +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
++-output_attributes=
+ +-AttributeReference[id=0,name=w,relation=b,type=Int]
+ +-AttributeReference[id=1,name=x,relation=b,type=Int]
+==
+
+SELECT x + (
+ SELECT SUM(y) + (SELECT SUM(w) FROM a WHERE a.y > 10)
+ FROM c
+ WHERE b.w = c.x AND c.x < 10)
+FROM b;
+--
+TopLevelPlan
++-plan=Project
+| +-input=TableReference[relation_name=b]
+| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
+| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-project_list=
+| +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
+| +-Add
+| +-AttributeReference[id=1,name=x,relation=b,type=Int]
+| +-SubqueryExpression
+| +-subquery=Project
+| +-input=Aggregate
+| | +-input=Filter
+| | | +-input=TableReference[relation_name=c]
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| | | +-filter_predicate=And
+| | | +-Equal
+| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int,
+| | | | | is_outer_reference=true]
+| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-Less
+| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
+| | | +-Literal[value=10,type=Int]
+| | +-grouping_expressions=
+| | | +-[]
+| | +-aggregate_expressions=
+| | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
+| | type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=3,name=y,relation=c,type=Int]
+| +-project_list=
+| +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=,
+| type=Long NULL]
+| +-Add
+| +-AttributeReference[id=4,name=,alias=$aggregate0,
+| | relation=$aggregate,type=Long NULL]
+| +-SubqueryExpression
+| +-subquery=Project
+| +-input=Aggregate
+| | +-input=Filter
+| | | +-input=TableReference[relation_name=a]
+| | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
+| | | | +-AttributeReference[id=6,name=x,relation=a,type=Int]
+| | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | | +-AttributeReference[id=8,name=z,relation=a,type=Int]
+| | | +-filter_predicate=Greater
+| | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
+| | | +-Literal[value=10,type=Int]
+| | +-grouping_expressions=
+| | | +-[]
+| | +-aggregate_expressions=
+| | +-Alias[id=9,name=,alias=$aggregate0,
+| | relation=$aggregate,type=Long NULL]
+| | +-AggregateFunction[function=SUM]
+| | +-AttributeReference[id=5,name=w,relation=a,
+| | type=Int]
+| +-project_list=
+| +-Alias[id=9,name=,alias=SUM(w),relation=,type=Long NULL]
+| +-AttributeReference[id=9,name=,alias=$aggregate0,
+| relation=$aggregate,type=Long NULL]
++-output_attributes=
+ +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
+ type=Long NULL]