quickstep-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From zu...@apache.org
Subject [12/24] incubator-quickstep git commit: Adds support for IN predicate (#167)
Date Tue, 19 Apr 2016 16:44:25 GMT
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/parser/preprocessed/SqlParser_gen.hpp
----------------------------------------------------------------------
diff --git a/parser/preprocessed/SqlParser_gen.hpp b/parser/preprocessed/SqlParser_gen.hpp
index 9447a22..807a39b 100644
--- a/parser/preprocessed/SqlParser_gen.hpp
+++ b/parser/preprocessed/SqlParser_gen.hpp
@@ -1,8 +1,8 @@
-/* A Bison parser, made by GNU Bison 3.0.2.  */
+/* A Bison parser, made by GNU Bison 3.0.4.  */
 
 /* Bison interface for Yacc-like parsers in C
 
-   Copyright (C) 1984, 1989-1990, 2000-2013 Free Software Foundation, Inc.
+   Copyright (C) 1984, 1989-1990, 2000-2015 Free Software Foundation, Inc.
 
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -108,64 +108,65 @@ extern int quickstep_yydebug;
     TOKEN_GROUP = 318,
     TOKEN_HASH = 319,
     TOKEN_HAVING = 320,
-    TOKEN_INDEX = 321,
-    TOKEN_INNER = 322,
-    TOKEN_INSERT = 323,
-    TOKEN_INTEGER = 324,
-    TOKEN_INTERVAL = 325,
-    TOKEN_INTO = 326,
-    TOKEN_JOIN = 327,
-    TOKEN_KEY = 328,
-    TOKEN_LAST = 329,
-    TOKEN_LEFT = 330,
-    TOKEN_LIMIT = 331,
-    TOKEN_LONG = 332,
-    TOKEN_NULL = 333,
-    TOKEN_NULLS = 334,
-    TOKEN_OFF = 335,
-    TOKEN_ON = 336,
-    TOKEN_ORDER = 337,
-    TOKEN_OUTER = 338,
-    TOKEN_PARTITION = 339,
-    TOKEN_PARTITIONS = 340,
-    TOKEN_PERCENT = 341,
-    TOKEN_PRIMARY = 342,
-    TOKEN_QUIT = 343,
-    TOKEN_RANGE = 344,
-    TOKEN_REAL = 345,
-    TOKEN_REFERENCES = 346,
-    TOKEN_RIGHT = 347,
-    TOKEN_ROW_DELIMITER = 348,
-    TOKEN_SELECT = 349,
-    TOKEN_SET = 350,
-    TOKEN_SMA = 351,
-    TOKEN_SMALLINT = 352,
-    TOKEN_TABLE = 353,
-    TOKEN_THEN = 354,
-    TOKEN_TIME = 355,
-    TOKEN_TIMESTAMP = 356,
-    TOKEN_TRUE = 357,
-    TOKEN_TUPLESAMPLE = 358,
-    TOKEN_UNIQUE = 359,
-    TOKEN_UPDATE = 360,
-    TOKEN_USING = 361,
-    TOKEN_VALUES = 362,
-    TOKEN_VARCHAR = 363,
-    TOKEN_WHEN = 364,
-    TOKEN_WHERE = 365,
-    TOKEN_WITH = 366,
-    TOKEN_YEARMONTH = 367,
-    TOKEN_EOF = 368,
-    TOKEN_LEX_ERROR = 369
+    TOKEN_IN = 321,
+    TOKEN_INDEX = 322,
+    TOKEN_INNER = 323,
+    TOKEN_INSERT = 324,
+    TOKEN_INTEGER = 325,
+    TOKEN_INTERVAL = 326,
+    TOKEN_INTO = 327,
+    TOKEN_JOIN = 328,
+    TOKEN_KEY = 329,
+    TOKEN_LAST = 330,
+    TOKEN_LEFT = 331,
+    TOKEN_LIMIT = 332,
+    TOKEN_LONG = 333,
+    TOKEN_NULL = 334,
+    TOKEN_NULLS = 335,
+    TOKEN_OFF = 336,
+    TOKEN_ON = 337,
+    TOKEN_ORDER = 338,
+    TOKEN_OUTER = 339,
+    TOKEN_PARTITION = 340,
+    TOKEN_PARTITIONS = 341,
+    TOKEN_PERCENT = 342,
+    TOKEN_PRIMARY = 343,
+    TOKEN_QUIT = 344,
+    TOKEN_RANGE = 345,
+    TOKEN_REAL = 346,
+    TOKEN_REFERENCES = 347,
+    TOKEN_RIGHT = 348,
+    TOKEN_ROW_DELIMITER = 349,
+    TOKEN_SELECT = 350,
+    TOKEN_SET = 351,
+    TOKEN_SMA = 352,
+    TOKEN_SMALLINT = 353,
+    TOKEN_TABLE = 354,
+    TOKEN_THEN = 355,
+    TOKEN_TIME = 356,
+    TOKEN_TIMESTAMP = 357,
+    TOKEN_TRUE = 358,
+    TOKEN_TUPLESAMPLE = 359,
+    TOKEN_UNIQUE = 360,
+    TOKEN_UPDATE = 361,
+    TOKEN_USING = 362,
+    TOKEN_VALUES = 363,
+    TOKEN_VARCHAR = 364,
+    TOKEN_WHEN = 365,
+    TOKEN_WHERE = 366,
+    TOKEN_WITH = 367,
+    TOKEN_YEARMONTH = 368,
+    TOKEN_EOF = 369,
+    TOKEN_LEX_ERROR = 370
   };
 #endif
 
 /* Value type.  */
 #if ! defined YYSTYPE && ! defined YYSTYPE_IS_DECLARED
-typedef union YYSTYPE YYSTYPE;
+
 union YYSTYPE
 {
-#line 116 "../SqlParser.ypp" /* yacc.c:1909  */
+#line 117 "../SqlParser.ypp" /* yacc.c:1915  */
 
   quickstep::ParseString *string_value_;
 
@@ -255,8 +256,10 @@ union YYSTYPE
   quickstep::PtrVector<quickstep::ParseSubqueryTableReference> *with_list_;
   quickstep::ParseSubqueryTableReference *with_list_element_;
 
-#line 259 "SqlParser_gen.hpp" /* yacc.c:1909  */
+#line 260 "SqlParser_gen.hpp" /* yacc.c:1915  */
 };
+
+typedef union YYSTYPE YYSTYPE;
 # define YYSTYPE_IS_TRIVIAL 1
 # define YYSTYPE_IS_DECLARED 1
 #endif

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/parser/tests/Select.test
----------------------------------------------------------------------
diff --git a/parser/tests/Select.test b/parser/tests/Select.test
index 18c3f0d..8a10a12 100644
--- a/parser/tests/Select.test
+++ b/parser/tests/Select.test
@@ -1555,3 +1555,133 @@ SelectStatement
   |       +-right_operand=AttributeReference[attribute_name=col4]
   +-from_clause=
     +-TableReference[table=test]
+==
+
+# IN predicate
+SELECT *
+FROM test 
+WHERE col1 IN (1, 3, 5);
+--
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectStar
+  +-where_clause=InValueList
+  | +-test_expression=AttributeReference[attribute_name=col1]
+  | +-value_list=
+  |   +-Literal
+  |   | +-NumericLiteral[numeric_string=1,float_like=false]
+  |   +-Literal
+  |   | +-NumericLiteral[numeric_string=3,float_like=false]
+  |   +-Literal
+  |     +-NumericLiteral[numeric_string=5,float_like=false]
+  +-from_clause=
+    +-TableReference[table=test]
+==
+
+SELECT *
+FROM test
+WHERE col1 IN (FUN(1),
+               col2+col3,
+               CASE WHEN col4 > 0 THEN col5 ELSE col6 END);
+--
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectStar
+  +-where_clause=InValueList
+  | +-test_expression=AttributeReference[attribute_name=col1]
+  | +-value_list=
+  |   +-FunctionCall[name=FUN]
+  |   | +-Literal
+  |   |   +-NumericLiteral[numeric_string=1,float_like=false]
+  |   +-Add
+  |   | +-left_operand=AttributeReference[attribute_name=col2]
+  |   | +-right_operand=AttributeReference[attribute_name=col3]
+  |   +-SearchedCaseExpression
+  |     +-else_result_expression=AttributeReference[attribute_name=col6]
+  |     +-when_clauses=
+  |       +-SearchedWhenClause
+  |         +-condition_predicate=Greater
+  |         | +-left_operand=AttributeReference[attribute_name=col4]
+  |         | +-right_operand=Literal
+  |         |   +-NumericLiteral[numeric_string=0,float_like=false]
+  |         +-result_expression=AttributeReference[attribute_name=col5]
+  +-from_clause=
+    +-TableReference[table=test]
+==
+
+SELECT *
+FROM test
+WHERE col1 NOT IN (col1, col2 + col3);
+--
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectStar
+  +-where_clause=Not
+  | +-InValueList
+  |   +-test_expression=AttributeReference[attribute_name=col1]
+  |   +-value_list=
+  |     +-AttributeReference[attribute_name=col1]
+  |     +-Add
+  |       +-left_operand=AttributeReference[attribute_name=col2]
+  |       +-right_operand=AttributeReference[attribute_name=col3]
+  +-from_clause=
+    +-TableReference[table=test]
+==
+
+SELECT *
+FROM test
+WHERE col1 IN (
+  SELECT SUM(col2+col3)
+  FROM bar
+  GROUP BY col4
+);
+--
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectStar
+  +-where_clause=InTableQuery
+  | +-test_expression=AttributeReference[attribute_name=col1]
+  | +-table_query=SubqueryExpression
+  |   +-Select
+  |     +-select_clause=SelectList
+  |     | +-SelectListItem
+  |     |   +-FunctionCall[name=SUM]
+  |     |     +-Add
+  |     |       +-left_operand=AttributeReference[attribute_name=col2]
+  |     |       +-right_operand=AttributeReference[attribute_name=col3]
+  |     +-group_by=GroupBy
+  |     | +-AttributeReference[attribute_name=col4]
+  |     +-from_clause=
+  |       +-TableReference[table=bar]
+  +-from_clause=
+    +-TableReference[table=test]
+==
+
+SELECT *
+FROM test
+WHERE col1 NOT IN (
+  SELECT col2
+  FROM bar
+  WHERE col3 IN (col4, col5)
+);
+--
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectStar
+  +-where_clause=Not
+  | +-InTableQuery
+  |   +-test_expression=AttributeReference[attribute_name=col1]
+  |   +-table_query=SubqueryExpression
+  |     +-Select
+  |       +-select_clause=SelectList
+  |       | +-SelectListItem
+  |       |   +-AttributeReference[attribute_name=col2]
+  |       +-where_clause=InValueList
+  |       | +-test_expression=AttributeReference[attribute_name=col3]
+  |       | +-value_list=
+  |       |   +-AttributeReference[attribute_name=col4]
+  |       |   +-AttributeReference[attribute_name=col5]
+  |       +-from_clause=
+  |         +-TableReference[table=bar]
+  +-from_clause=
+    +-TableReference[table=test]

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/parser/tests/TPCH.test
----------------------------------------------------------------------
diff --git a/parser/tests/TPCH.test b/parser/tests/TPCH.test
index 45071e4..dfcd6aa 100644
--- a/parser/tests/TPCH.test
+++ b/parser/tests/TPCH.test
@@ -974,7 +974,7 @@ FROM
   lineitem
 WHERE
   o_orderkey = l_orderkey
-  AND l_shipmode in ('REG AIR', 'RAIL')
+  AND l_shipmode IN ('REG AIR', 'RAIL')
   AND l_commitdate < l_receiptdate
   AND l_shipdate < l_commitdate
   AND l_receiptdate >= DATE '1997-01-01'
@@ -984,9 +984,87 @@ GROUP BY
 ORDER BY
   l_shipmode
 --
-ERROR: syntax error (20 : 18)
-  AND l_shipmode in ('REG AIR', 'RAIL')
-                 ^
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectList
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=l_shipmode]
+  | +-SelectListItem[alias=high_line_count]
+  | | +-FunctionCall[name=SUM]
+  | |   +-SearchedCaseExpression
+  | |     +-else_result_expression=Literal
+  | |     | +-NumericLiteral[numeric_string=0,float_like=false]
+  | |     +-when_clauses=
+  | |       +-SearchedWhenClause
+  | |         +-condition_predicate=Or
+  | |         | +-Equal
+  | |         | | +-left_operand=AttributeReference[
+  | |         | | | attribute_name=o_orderpriority]
+  | |         | | +-right_operand=Literal
+  | |         | |   +-StringLiteral[value=1-URGENT]
+  | |         | +-Equal
+  | |         |   +-left_operand=AttributeReference[
+  | |         |   | attribute_name=o_orderpriority]
+  | |         |   +-right_operand=Literal
+  | |         |     +-StringLiteral[value=2-HIGH]
+  | |         +-result_expression=Literal
+  | |           +-NumericLiteral[numeric_string=1,float_like=false]
+  | +-SelectListItem[alias=low_line_count]
+  |   +-FunctionCall[name=SUM]
+  |     +-SearchedCaseExpression
+  |       +-else_result_expression=Literal
+  |       | +-NumericLiteral[numeric_string=0,float_like=false]
+  |       +-when_clauses=
+  |         +-SearchedWhenClause
+  |           +-condition_predicate=And
+  |           | +-NotEqual
+  |           | | +-left_operand=AttributeReference[
+  |           | | | attribute_name=o_orderpriority]
+  |           | | +-right_operand=Literal
+  |           | |   +-StringLiteral[value=1-URGENT]
+  |           | +-NotEqual
+  |           |   +-left_operand=AttributeReference[
+  |           |   | attribute_name=o_orderpriority]
+  |           |   +-right_operand=Literal
+  |           |     +-StringLiteral[value=2-HIGH]
+  |           +-result_expression=Literal
+  |             +-NumericLiteral[numeric_string=1,float_like=false]
+  +-where_clause=And
+  | +-Equal
+  | | +-left_operand=AttributeReference[attribute_name=o_orderkey]
+  | | +-right_operand=AttributeReference[attribute_name=l_orderkey]
+  | +-InValueList
+  | | +-test_expression=AttributeReference[attribute_name=l_shipmode]
+  | | +-value_list=
+  | |   +-Literal
+  | |   | +-StringLiteral[value=REG AIR]
+  | |   +-Literal
+  | |     +-StringLiteral[value=RAIL]
+  | +-Less
+  | | +-left_operand=AttributeReference[attribute_name=l_commitdate]
+  | | +-right_operand=AttributeReference[attribute_name=l_receiptdate]
+  | +-Less
+  | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
+  | | +-right_operand=AttributeReference[attribute_name=l_commitdate]
+  | +-GreaterOrEqual
+  | | +-left_operand=AttributeReference[attribute_name=l_receiptdate]
+  | | +-right_operand=Literal
+  | |   +-StringLiteral[value=1997-01-01,explicit_type=Datetime]
+  | +-Less
+  |   +-left_operand=AttributeReference[attribute_name=l_receiptdate]
+  |   +-right_operand=Add
+  |     +-left_operand=Literal
+  |     | +-StringLiteral[value=1997-01-01,explicit_type=Datetime]
+  |     +-right_operand=Literal
+  |       +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
+  +-group_by=GroupBy
+  | +-AttributeReference[attribute_name=l_shipmode]
+  +-order_by=OrderBy
+  | +-OrderByItem[is_asc=true,nulls_first=false]
+  |   +-AttributeReference[attribute_name=l_shipmode]
+  +-from_clause=
+    +-TableReference[table=orders]
+    +-TableReference[table=lineitem]
 ==
 
 # Query 13
@@ -1152,9 +1230,79 @@ ORDER BY
   p_type,
   p_size
 --
-ERROR: syntax error (13 : 14)
-  AND p_size IN (32, 42, 9, 18, 50, 30, 12,...
-             ^
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectList
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=p_brand]
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=p_type]
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=p_size]
+  | +-SelectListItem[alias=supplier_cnt]
+  |   +-FunctionCall[name=COUNT,is_distinct=true]
+  |     +-AttributeReference[attribute_name=ps_suppkey]
+  +-where_clause=And
+  | +-Equal
+  | | +-left_operand=AttributeReference[attribute_name=p_partkey]
+  | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
+  | +-NotEqual
+  | | +-left_operand=AttributeReference[attribute_name=p_brand]
+  | | +-right_operand=Literal
+  | |   +-StringLiteral[value=Brand#22]
+  | +-NotLike
+  | | +-left_operand=AttributeReference[attribute_name=p_type]
+  | | +-right_operand=Literal
+  | |   +-StringLiteral[value=ECONOMY BURNISHED%]
+  | +-InValueList
+  | | +-test_expression=AttributeReference[attribute_name=p_size]
+  | | +-value_list=
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=32,float_like=false]
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=42,float_like=false]
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=9,float_like=false]
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=18,float_like=false]
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=50,float_like=false]
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=30,float_like=false]
+  | |   +-Literal
+  | |   | +-NumericLiteral[numeric_string=12,float_like=false]
+  | |   +-Literal
+  | |     +-NumericLiteral[numeric_string=21,float_like=false]
+  | +-Not
+  |   +-InTableQuery
+  |     +-test_expression=AttributeReference[attribute_name=ps_suppkey]
+  |     +-table_query=SubqueryExpression
+  |       +-Select
+  |         +-select_clause=SelectList
+  |         | +-SelectListItem
+  |         |   +-AttributeReference[attribute_name=s_suppkey]
+  |         +-where_clause=Like
+  |         | +-left_operand=AttributeReference[attribute_name=s_comment]
+  |         | +-right_operand=Literal
+  |         |   +-StringLiteral[value=%Customer%Complaints%]
+  |         +-from_clause=
+  |           +-TableReference[table=supplier]
+  +-group_by=GroupBy
+  | +-AttributeReference[attribute_name=p_brand]
+  | +-AttributeReference[attribute_name=p_type]
+  | +-AttributeReference[attribute_name=p_size]
+  +-order_by=OrderBy
+  | +-OrderByItem[is_asc=false,nulls_first=true]
+  | | +-AttributeReference[attribute_name=supplier_cnt]
+  | +-OrderByItem[is_asc=true,nulls_first=false]
+  | | +-AttributeReference[attribute_name=p_brand]
+  | +-OrderByItem[is_asc=true,nulls_first=false]
+  | | +-AttributeReference[attribute_name=p_type]
+  | +-OrderByItem[is_asc=true,nulls_first=false]
+  |   +-AttributeReference[attribute_name=p_size]
+  +-from_clause=
+    +-TableReference[table=partsupp]
+    +-TableReference[table=part]
 ==
 
 # Query 17
@@ -1194,7 +1342,7 @@ FROM
   orders,
   lineitem
 WHERE
-  o_orderkey in (
+  o_orderkey IN (
     SELECT
       l_orderkey
     FROM
@@ -1216,9 +1364,63 @@ ORDER BY
   o_orderdate
 LIMIT 100
 --
-ERROR: syntax error (13 : 14)
-  o_orderkey in (
-             ^
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectList
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=c_name]
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=c_custkey]
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=o_orderkey]
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=o_orderdate]
+  | +-SelectListItem
+  | | +-AttributeReference[attribute_name=o_totalprice]
+  | +-SelectListItem
+  |   +-FunctionCall[name=sum]
+  |     +-AttributeReference[attribute_name=l_quantity]
+  +-where_clause=And
+  | +-InTableQuery
+  | | +-test_expression=AttributeReference[attribute_name=o_orderkey]
+  | | +-table_query=SubqueryExpression
+  | |   +-Select
+  | |     +-select_clause=SelectList
+  | |     | +-SelectListItem
+  | |     |   +-AttributeReference[attribute_name=l_orderkey]
+  | |     +-group_by=GroupBy
+  | |     | +-AttributeReference[attribute_name=l_orderkey]
+  | |     +-having=HAVING
+  | |     | +-Greater
+  | |     |   +-left_operand=FunctionCall[name=SUM]
+  | |     |   | +-AttributeReference[attribute_name=l_quantity]
+  | |     |   +-right_operand=Literal
+  | |     |     +-NumericLiteral[numeric_string=314,float_like=false]
+  | |     +-from_clause=
+  | |       +-TableReference[table=lineitem]
+  | +-Equal
+  | | +-left_operand=AttributeReference[attribute_name=c_custkey]
+  | | +-right_operand=AttributeReference[attribute_name=o_custkey]
+  | +-Equal
+  |   +-left_operand=AttributeReference[attribute_name=o_orderkey]
+  |   +-right_operand=AttributeReference[attribute_name=l_orderkey]
+  +-group_by=GroupBy
+  | +-AttributeReference[attribute_name=c_name]
+  | +-AttributeReference[attribute_name=c_custkey]
+  | +-AttributeReference[attribute_name=o_orderkey]
+  | +-AttributeReference[attribute_name=o_orderdate]
+  | +-AttributeReference[attribute_name=o_totalprice]
+  +-order_by=OrderBy
+  | +-OrderByItem[is_asc=false,nulls_first=true]
+  | | +-AttributeReference[attribute_name=o_totalprice]
+  | +-OrderByItem[is_asc=true,nulls_first=false]
+  |   +-AttributeReference[attribute_name=o_orderdate]
+  +-limit=LIMIT
+  | +-NumericLiteral[numeric_string=100,float_like=false]
+  +-from_clause=
+    +-TableReference[table=customer]
+    +-TableReference[table=orders]
+    +-TableReference[table=lineitem]
 ==
 
 # Query 19
@@ -1258,9 +1460,162 @@ WHERE
     AND l_shipinstruct = 'DELIVER IN PERSON'
   )
 --
-ERROR: syntax error (10 : 21)
-    AND p_container IN ('SM CASE', 'SM BOX', 'SM PAC...
-                    ^
+SelectStatement
++-select_query=Select
+  +-select_clause=SelectList
+  | +-SelectListItem[alias=revenue]
+  |   +-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=Or
+  | +-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#45]
+  | | +-InValueList
+  | | | +-test_expression=AttributeReference[attribute_name=p_container]
+  | | | +-value_list=
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=SM CASE]
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=SM BOX]
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=SM PACK]
+  | | |   +-Literal
+  | | |     +-StringLiteral[value=SM PKG]
+  | | +-GreaterOrEqual
+  | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
+  | | | +-right_operand=Literal
+  | | |   +-NumericLiteral[numeric_string=2,float_like=false]
+  | | +-LessOrEqual
+  | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
+  | | | +-right_operand=Add
+  | | |   +-left_operand=Literal
+  | | |   | +-NumericLiteral[numeric_string=2,float_like=false]
+  | | |   +-right_operand=Literal
+  | | |     +-NumericLiteral[numeric_string=10,float_like=false]
+  | | +-Between
+  | | | +-check_operand=AttributeReference[attribute_name=p_size]
+  | | | +-lower_bound_operand=Literal
+  | | | | +-NumericLiteral[numeric_string=1,float_like=false]
+  | | | +-upper_bound_operand=Literal
+  | | |   +-NumericLiteral[numeric_string=5,float_like=false]
+  | | +-InValueList
+  | | | +-test_expression=AttributeReference[attribute_name=l_shipmode]
+  | | | +-value_list=
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=AIR]
+  | | |   +-Literal
+  | | |     +-StringLiteral[value=AIR REG]
+  | | +-Equal
+  | |   +-left_operand=AttributeReference[attribute_name=l_shipinstruct]
+  | |   +-right_operand=Literal
+  | |     +-StringLiteral[value=DELIVER IN PERSON]
+  | +-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#12]
+  | | +-InValueList
+  | | | +-test_expression=AttributeReference[attribute_name=p_container]
+  | | | +-value_list=
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=MED BAG]
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=MED BOX]
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=MED PKG]
+  | | |   +-Literal
+  | | |     +-StringLiteral[value=MED PACK]
+  | | +-GreaterOrEqual
+  | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
+  | | | +-right_operand=Literal
+  | | |   +-NumericLiteral[numeric_string=13,float_like=false]
+  | | +-LessOrEqual
+  | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
+  | | | +-right_operand=Add
+  | | |   +-left_operand=Literal
+  | | |   | +-NumericLiteral[numeric_string=13,float_like=false]
+  | | |   +-right_operand=Literal
+  | | |     +-NumericLiteral[numeric_string=10,float_like=false]
+  | | +-Between
+  | | | +-check_operand=AttributeReference[attribute_name=p_size]
+  | | | +-lower_bound_operand=Literal
+  | | | | +-NumericLiteral[numeric_string=1,float_like=false]
+  | | | +-upper_bound_operand=Literal
+  | | |   +-NumericLiteral[numeric_string=10,float_like=false]
+  | | +-InValueList
+  | | | +-test_expression=AttributeReference[attribute_name=l_shipmode]
+  | | | +-value_list=
+  | | |   +-Literal
+  | | |   | +-StringLiteral[value=AIR]
+  | | |   +-Literal
+  | | |     +-StringLiteral[value=AIR REG]
+  | | +-Equal
+  | |   +-left_operand=AttributeReference[attribute_name=l_shipinstruct]
+  | |   +-right_operand=Literal
+  | |     +-StringLiteral[value=DELIVER IN PERSON]
+  | +-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#53]
+  |   +-InValueList
+  |   | +-test_expression=AttributeReference[attribute_name=p_container]
+  |   | +-value_list=
+  |   |   +-Literal
+  |   |   | +-StringLiteral[value=LG CASE]
+  |   |   +-Literal
+  |   |   | +-StringLiteral[value=LG BOX]
+  |   |   +-Literal
+  |   |   | +-StringLiteral[value=LG PACK]
+  |   |   +-Literal
+  |   |     +-StringLiteral[value=LG PKG]
+  |   +-GreaterOrEqual
+  |   | +-left_operand=AttributeReference[attribute_name=l_quantity]
+  |   | +-right_operand=Literal
+  |   |   +-NumericLiteral[numeric_string=24,float_like=false]
+  |   +-LessOrEqual
+  |   | +-left_operand=AttributeReference[attribute_name=l_quantity]
+  |   | +-right_operand=Add
+  |   |   +-left_operand=Literal
+  |   |   | +-NumericLiteral[numeric_string=24,float_like=false]
+  |   |   +-right_operand=Literal
+  |   |     +-NumericLiteral[numeric_string=10,float_like=false]
+  |   +-Between
+  |   | +-check_operand=AttributeReference[attribute_name=p_size]
+  |   | +-lower_bound_operand=Literal
+  |   | | +-NumericLiteral[numeric_string=1,float_like=false]
+  |   | +-upper_bound_operand=Literal
+  |   |   +-NumericLiteral[numeric_string=15,float_like=false]
+  |   +-InValueList
+  |   | +-test_expression=AttributeReference[attribute_name=l_shipmode]
+  |   | +-value_list=
+  |   |   +-Literal
+  |   |   | +-StringLiteral[value=AIR]
+  |   |   +-Literal
+  |   |     +-StringLiteral[value=AIR REG]
+  |   +-Equal
+  |     +-left_operand=AttributeReference[attribute_name=l_shipinstruct]
+  |     +-right_operand=Literal
+  |       +-StringLiteral[value=DELIVER IN PERSON]
+  +-from_clause=
+    +-TableReference[table=lineitem]
+    +-TableReference[table=part]
 ==
 
 # Query 20
@@ -1271,7 +1626,7 @@ FROM
   supplier,
   nation
 WHERE
-  s_suppkey in (
+  s_suppkey IN (
     SELECT
       ps_suppkey
     FROM
@@ -1302,9 +1657,9 @@ WHERE
 ORDER BY
   s_name
 --
-ERROR: syntax error (8 : 13)
-  s_suppkey in (
-            ^
+ERROR: syntax error (23 : 9)
+        SELECT
+        ^
 ==
 
 # Query 21
@@ -1453,7 +1808,7 @@ FROM
     FROM
       customer
     WHERE
-      SUBSTR(c_phone, 1, 2) in
+      SUBSTR(c_phone, 1, 2) IN
         ('27', '44', '34', '25', '30', '33', '23')
       AND c_acctbal > (
         SELECT
@@ -1462,10 +1817,10 @@ FROM
           customer
         WHERE
           c_acctbal > 0.00
-          AND SUBSTR(c_phone, 1, 2) in
+          AND SUBSTR(c_phone, 1, 2) IN
             ('27', '44', '34', '25', '30', '33', '23')
       )
-      AND NOT exists (
+      AND NOT EXISTS (
         SELECT *
         FROM
           orders
@@ -1478,6 +1833,6 @@ GROUP BY
 ORDER BY
   cntrycode
 --
-ERROR: syntax error (13 : 29)
-      SUBSTR(c_phone, 1, 2) in
-                            ^
+ERROR: syntax error (16 : 9)
+        SELECT
+        ^

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/CMakeLists.txt
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/CMakeLists.txt b/query_optimizer/expressions/CMakeLists.txt
index ca064dc..6c40741 100644
--- a/query_optimizer/expressions/CMakeLists.txt
+++ b/query_optimizer/expressions/CMakeLists.txt
@@ -28,6 +28,8 @@ add_library(quickstep_queryoptimizer_expressions_Expression ../../empty_src.cpp
 add_library(quickstep_queryoptimizer_expressions_ExpressionType ../../empty_src.cpp ExpressionType.hpp)
 add_library(quickstep_queryoptimizer_expressions_ExprId ../../empty_src.cpp ExprId.hpp)
 add_library(quickstep_queryoptimizer_expressions_ExpressionUtil ExpressionUtil.cpp ExpressionUtil.hpp)
+add_library(quickstep_queryoptimizer_expressions_InTableQuery InTableQuery.cpp InTableQuery.hpp)
+add_library(quickstep_queryoptimizer_expressions_InValueList InValueList.cpp InValueList.hpp)
 add_library(quickstep_queryoptimizer_expressions_LogicalAnd LogicalAnd.cpp LogicalAnd.hpp)
 add_library(quickstep_queryoptimizer_expressions_LogicalNot LogicalNot.cpp LogicalNot.hpp)
 add_library(quickstep_queryoptimizer_expressions_LogicalOr LogicalOr.cpp LogicalOr.hpp)
@@ -138,6 +140,31 @@ target_link_libraries(quickstep_queryoptimizer_expressions_ExpressionUtil
                       quickstep_queryoptimizer_expressions_ExprId
                       quickstep_queryoptimizer_expressions_NamedExpression
                       quickstep_queryoptimizer_expressions_PatternMatcher)
+target_link_libraries(quickstep_queryoptimizer_expressions_InTableQuery
+                      quickstep_queryoptimizer_OptimizerTree
+                      quickstep_queryoptimizer_expressions_AttributeReference
+                      quickstep_queryoptimizer_expressions_ExprId
+                      quickstep_queryoptimizer_expressions_Expression
+                      quickstep_queryoptimizer_expressions_ExpressionType
+                      quickstep_queryoptimizer_expressions_Predicate
+                      quickstep_queryoptimizer_expressions_Scalar
+                      quickstep_queryoptimizer_expressions_SubqueryExpression
+                      quickstep_utility_Macros)
+target_link_libraries(quickstep_queryoptimizer_expressions_InValueList
+                      quickstep_queryoptimizer_OptimizerTree
+                      quickstep_expressions_predicate_DisjunctionPredicate
+                      quickstep_expressions_predicate_Predicate
+                      quickstep_queryoptimizer_expressions_AttributeReference
+                      quickstep_queryoptimizer_expressions_ComparisonExpression
+                      quickstep_queryoptimizer_expressions_ExprId
+                      quickstep_queryoptimizer_expressions_Expression
+                      quickstep_queryoptimizer_expressions_ExpressionType
+                      quickstep_queryoptimizer_expressions_Predicate
+                      quickstep_queryoptimizer_expressions_Scalar
+                      quickstep_types_operations_comparisons_ComparisonFactory
+                      quickstep_types_operations_comparisons_ComparisonID
+                      quickstep_utility_Cast
+                      quickstep_utility_Macros)
 target_link_libraries(quickstep_queryoptimizer_expressions_LogicalAnd
                       glog
                       quickstep_expressions_predicate_ConjunctionPredicate
@@ -289,6 +316,8 @@ target_link_libraries(quickstep_queryoptimizer_expressions
                       quickstep_queryoptimizer_expressions_ExpressionType
                       quickstep_queryoptimizer_expressions_ExprId
                       quickstep_queryoptimizer_expressions_ExpressionUtil
+                      quickstep_queryoptimizer_expressions_InTableQuery
+                      quickstep_queryoptimizer_expressions_InValueList
                       quickstep_queryoptimizer_expressions_LogicalAnd
                       quickstep_queryoptimizer_expressions_LogicalNot
                       quickstep_queryoptimizer_expressions_LogicalOr

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/ExpressionType.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/ExpressionType.hpp b/query_optimizer/expressions/ExpressionType.hpp
index afd6f81..23770e0 100644
--- a/query_optimizer/expressions/ExpressionType.hpp
+++ b/query_optimizer/expressions/ExpressionType.hpp
@@ -39,6 +39,8 @@ enum class ExpressionType {
   kCast,
   kComparisonExpression,
   kExists,
+  kInTableQuery,
+  kInValueList,
   kLogicalAnd,
   kLogicalOr,
   kLogicalNot,

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/InTableQuery.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/InTableQuery.cpp b/query_optimizer/expressions/InTableQuery.cpp
new file mode 100644
index 0000000..b4abbe0
--- /dev/null
+++ b/query_optimizer/expressions/InTableQuery.cpp
@@ -0,0 +1,53 @@
+/**
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin—Madison.
+ *
+ *   Licensed under the Apache License, Version 2.0 (the "License");
+ *   you may not use this file except in compliance with the License.
+ *   You may obtain a copy of the License at
+ *
+ *       http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *   Unless required by applicable law or agreed to in writing, software
+ *   distributed under the License is distributed on an "AS IS" BASIS,
+ *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *   See the License for the specific language governing permissions and
+ *   limitations under the License.
+ **/
+
+#include "query_optimizer/expressions/InTableQuery.hpp"
+
+#include <string>
+#include <vector>
+
+#include "query_optimizer/OptimizerTree.hpp"
+#include "query_optimizer/expressions/ExprId.hpp"
+
+#include "glog/logging.h"
+
+namespace quickstep {
+namespace optimizer {
+namespace expressions {
+
+::quickstep::Predicate* InTableQuery::concretize(
+    const std::unordered_map<ExprId, const CatalogAttribute*> &substitution_map) const {
+  LOG(FATAL) << "InTableQuery predicate should not be concretized";
+}
+
+void InTableQuery::getFieldStringItems(
+    std::vector<std::string> *inline_field_names,
+    std::vector<std::string> *inline_field_values,
+    std::vector<std::string> *non_container_child_field_names,
+    std::vector<OptimizerTreeBaseNodePtr> *non_container_child_fields,
+    std::vector<std::string> *container_child_field_names,
+    std::vector<std::vector<OptimizerTreeBaseNodePtr>> *container_child_fields) const {
+  non_container_child_field_names->push_back("test_expression");
+  non_container_child_fields->push_back(test_expression_);
+
+  non_container_child_field_names->push_back("table_query");
+  non_container_child_fields->push_back(table_query_);
+}
+
+}  // namespace expressions
+}  // namespace optimizer
+}  // namespace quickstep

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/InTableQuery.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/InTableQuery.hpp b/query_optimizer/expressions/InTableQuery.hpp
new file mode 100644
index 0000000..e4abf22
--- /dev/null
+++ b/query_optimizer/expressions/InTableQuery.hpp
@@ -0,0 +1,143 @@
+/**
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin—Madison.
+ *
+ *   Licensed under the Apache License, Version 2.0 (the "License");
+ *   you may not use this file except in compliance with the License.
+ *   You may obtain a copy of the License at
+ *
+ *       http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *   Unless required by applicable law or agreed to in writing, software
+ *   distributed under the License is distributed on an "AS IS" BASIS,
+ *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *   See the License for the specific language governing permissions and
+ *   limitations under the License.
+ **/
+
+#ifndef QUICKSTEP_QUERY_OPTIMIZER_EXPRESSIONS_IN_TABLE_QUERY_HPP_
+#define QUICKSTEP_QUERY_OPTIMIZER_EXPRESSIONS_IN_TABLE_QUERY_HPP_
+
+#include <memory>
+#include <string>
+#include <unordered_map>
+#include <vector>
+
+#include "query_optimizer/OptimizerTree.hpp"
+#include "query_optimizer/expressions/AttributeReference.hpp"
+#include "query_optimizer/expressions/ExprId.hpp"
+#include "query_optimizer/expressions/Expression.hpp"
+#include "query_optimizer/expressions/ExpressionType.hpp"
+#include "query_optimizer/expressions/Predicate.hpp"
+#include "query_optimizer/expressions/Scalar.hpp"
+#include "query_optimizer/expressions/SubqueryExpression.hpp"
+#include "utility/Macros.hpp"
+
+#include "glog/logging.h"
+
+namespace quickstep {
+
+class CatalogAttribute;
+class Predicate;
+
+namespace optimizer {
+namespace expressions {
+
+/** \addtogroup OptimizerExpressions
+ *  @{
+ */
+
+class InTableQuery;
+typedef std::shared_ptr<const InTableQuery> InTableQueryPtr;
+
+/**
+ * @brief IN predicate with a subquery.
+ */
+class InTableQuery : public Predicate {
+ public:
+  ExpressionType getExpressionType() const override {
+    return ExpressionType::kInTableQuery;
+  }
+
+  std::string getName() const override {
+    return "InTableQuery";
+  }
+
+  bool isConstant() const override {
+    return false;
+  }
+
+  /**
+   * @return The expression to test with the result of the table subquery.
+   */
+  const ScalarPtr& test_expression() const {
+    return test_expression_;
+  }
+
+  /**
+   * @return The table subquery that returns a single column to search for
+   *         the value of the test expression.
+   */
+  const SubqueryExpressionPtr& table_query() const {
+    return table_query_;
+  }
+
+  ExpressionPtr copyWithNewChildren(
+      const std::vector<ExpressionPtr> &new_children) const override {
+    DCHECK_EQ(2u, new_children.size());
+    return Create(std::static_pointer_cast<const Scalar>(new_children[0]),
+                  std::static_pointer_cast<const SubqueryExpression>(new_children[1]));
+  }
+
+  std::vector<AttributeReferencePtr> getReferencedAttributes() const override {
+    return {};
+  }
+
+  ::quickstep::Predicate* concretize(
+      const std::unordered_map<ExprId, const CatalogAttribute*> &substitution_map) const override;
+
+  /**
+   * @brief Create an IN predicate with a subquery.
+   *
+   * @param test_expression The expression to test with the result of a table subquery.
+   * @param table_query The table subquery that returns a single column to search for
+   *        the value of the test expression.
+   *
+   * @return An immutable IN predicate node.
+   */
+  static InTableQueryPtr Create(const ScalarPtr &test_expression,
+                                const SubqueryExpressionPtr &table_query) {
+    return InTableQueryPtr(new InTableQuery(test_expression, table_query));
+  }
+
+ protected:
+  void getFieldStringItems(
+      std::vector<std::string> *inline_field_names,
+      std::vector<std::string> *inline_field_values,
+      std::vector<std::string> *non_container_child_field_names,
+      std::vector<OptimizerTreeBaseNodePtr> *non_container_child_fields,
+      std::vector<std::string> *container_child_field_names,
+      std::vector<std::vector<OptimizerTreeBaseNodePtr>> *container_child_fields) const override;
+
+ private:
+  InTableQuery(const ScalarPtr &test_expression,
+               const SubqueryExpressionPtr &table_query)
+      : test_expression_(test_expression),
+        table_query_(table_query) {
+    addChild(test_expression_);
+    addChild(table_query_);
+  }
+
+  ScalarPtr test_expression_;
+  SubqueryExpressionPtr table_query_;
+
+  DISALLOW_COPY_AND_ASSIGN(InTableQuery);
+};
+
+/** @} */
+
+}  // namespace expressions
+}  // namespace optimizer
+}  // namespace quickstep
+
+#endif  // QUICKSTEP_QUERY_OPTIMIZER_EXPRESSIONS_IN_TABLE_QUERY_HPP_

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/InValueList.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/InValueList.cpp b/query_optimizer/expressions/InValueList.cpp
new file mode 100644
index 0000000..dd77d95
--- /dev/null
+++ b/query_optimizer/expressions/InValueList.cpp
@@ -0,0 +1,72 @@
+/**
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin—Madison.
+ *
+ *   Licensed under the Apache License, Version 2.0 (the "License");
+ *   you may not use this file except in compliance with the License.
+ *   You may obtain a copy of the License at
+ *
+ *       http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *   Unless required by applicable law or agreed to in writing, software
+ *   distributed under the License is distributed on an "AS IS" BASIS,
+ *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *   See the License for the specific language governing permissions and
+ *   limitations under the License.
+ **/
+
+#include "query_optimizer/expressions/InValueList.hpp"
+
+#include <string>
+#include <vector>
+
+#include "expressions/predicate/DisjunctionPredicate.hpp"
+#include "expressions/predicate/Predicate.hpp"
+#include "query_optimizer/OptimizerTree.hpp"
+#include "query_optimizer/expressions/ComparisonExpression.hpp"
+#include "query_optimizer/expressions/ExprId.hpp"
+#include "query_optimizer/expressions/Predicate.hpp"
+#include "query_optimizer/expressions/Scalar.hpp"
+#include "types/operations/comparisons/ComparisonID.hpp"
+#include "types/operations/comparisons/ComparisonFactory.hpp"
+#include "utility/Cast.hpp"
+
+namespace quickstep {
+namespace optimizer {
+namespace expressions {
+
+::quickstep::Predicate* InValueList::concretize(
+    const std::unordered_map<ExprId, const CatalogAttribute*> &substitution_map) const {
+  std::unique_ptr<quickstep::DisjunctionPredicate>
+      disjunction_predicate(new quickstep::DisjunctionPredicate());
+  for (const ScalarPtr &match_expression : match_expressions_) {
+    const PredicatePtr match_predicate =
+        ComparisonExpression::Create(
+            quickstep::ComparisonFactory::GetComparison(quickstep::ComparisonID::kEqual),
+            test_expression_,
+            match_expression);
+
+    disjunction_predicate->addPredicate(
+        match_predicate->concretize(substitution_map));
+  }
+  return disjunction_predicate.release();
+}
+
+void InValueList::getFieldStringItems(
+    std::vector<std::string> *inline_field_names,
+    std::vector<std::string> *inline_field_values,
+    std::vector<std::string> *non_container_child_field_names,
+    std::vector<OptimizerTreeBaseNodePtr> *non_container_child_fields,
+    std::vector<std::string> *container_child_field_names,
+    std::vector<std::vector<OptimizerTreeBaseNodePtr>> *container_child_fields) const {
+  non_container_child_field_names->push_back("test_expression");
+  non_container_child_fields->push_back(test_expression_);
+
+  container_child_field_names->push_back("match_expressions");
+  container_child_fields->push_back(
+      CastSharedPtrVector<OptimizerTreeBase>(match_expressions_));
+}
+
+}  // namespace expressions
+}  // namespace optimizer
+}  // namespace quickstep

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/InValueList.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/InValueList.hpp b/query_optimizer/expressions/InValueList.hpp
new file mode 100644
index 0000000..9fc2ace
--- /dev/null
+++ b/query_optimizer/expressions/InValueList.hpp
@@ -0,0 +1,157 @@
+/**
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin—Madison.
+ *
+ *   Licensed under the Apache License, Version 2.0 (the "License");
+ *   you may not use this file except in compliance with the License.
+ *   You may obtain a copy of the License at
+ *
+ *       http://www.apache.org/licenses/LICENSE-2.0
+ *
+ *   Unless required by applicable law or agreed to in writing, software
+ *   distributed under the License is distributed on an "AS IS" BASIS,
+ *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *   See the License for the specific language governing permissions and
+ *   limitations under the License.
+ **/
+
+#ifndef QUICKSTEP_QUERY_OPTIMIZER_EXPRESSIONS_IN_VALUE_LIST_HPP_
+#define QUICKSTEP_QUERY_OPTIMIZER_EXPRESSIONS_IN_VALUE_LIST_HPP_
+
+#include <cstddef>
+#include <memory>
+#include <string>
+#include <unordered_map>
+#include <vector>
+
+#include "query_optimizer/OptimizerTree.hpp"
+#include "query_optimizer/expressions/AttributeReference.hpp"
+#include "query_optimizer/expressions/ExprId.hpp"
+#include "query_optimizer/expressions/Expression.hpp"
+#include "query_optimizer/expressions/ExpressionType.hpp"
+#include "query_optimizer/expressions/Predicate.hpp"
+#include "query_optimizer/expressions/Scalar.hpp"
+#include "utility/Macros.hpp"
+
+#include "glog/logging.h"
+
+namespace quickstep {
+
+class CatalogAttribute;
+class Predicate;
+
+namespace optimizer {
+namespace expressions {
+
+/** \addtogroup OptimizerExpressions
+ *  @{
+ */
+
+class InValueList;
+typedef std::shared_ptr<const InValueList> InValueListPtr;
+
+/**
+ * @brief IN predicate with a value list.
+ */
+class InValueList : public Predicate {
+ public:
+  ExpressionType getExpressionType() const override {
+    return ExpressionType::kInValueList;
+  }
+
+  std::string getName() const override {
+    return "InValueList";
+  }
+
+  bool isConstant() const override {
+    return false;
+  }
+
+  /**
+   * @return The expression to test with a value list.
+   */
+  const ScalarPtr& test_expression() const {
+    return test_expression_;
+  }
+
+  /**
+   * @return Expressions to search for the value of the test_expression.
+   */
+  const std::vector<ScalarPtr>& match_expressions() const {
+    return match_expressions_;
+  }
+
+  ExpressionPtr copyWithNewChildren(
+      const std::vector<ExpressionPtr> &new_children) const override {
+    DCHECK_EQ(children().size(), new_children.size());
+    std::vector<ScalarPtr> new_match_expressions;
+    for (std::size_t idx = 1; idx < new_children.size(); ++idx) {
+      new_match_expressions.emplace_back(
+          std::static_pointer_cast<const Scalar>(new_children[idx]));
+    }
+    return Create(std::static_pointer_cast<const Scalar>(new_children[0]),
+                  new_match_expressions);
+  }
+
+  std::vector<AttributeReferencePtr> getReferencedAttributes() const override {
+    std::vector<AttributeReferencePtr> referenced_attrs =
+        test_expression_->getReferencedAttributes();
+    for (const ScalarPtr &match_expression : match_expressions_) {
+      const std::vector<AttributeReferencePtr> referenced_attrs_in_match_expr =
+          match_expression->getReferencedAttributes();
+      referenced_attrs.insert(referenced_attrs.end(),
+                              referenced_attrs_in_match_expr.begin(),
+                              referenced_attrs_in_match_expr.end());
+    }
+    return referenced_attrs;
+  }
+
+  ::quickstep::Predicate* concretize(
+      const std::unordered_map<ExprId, const CatalogAttribute*> &substitution_map) const override;
+
+  /**
+   * @brief Create an IN predicate with a value list.
+   *
+   * @param test_expression The expression to test with a value list.
+   * @param match_expressions Expressions to search for the value of the test_expression.
+   *
+   * @return An immutable IN predicate node with a value list.
+   */
+  static InValueListPtr Create(const ScalarPtr &test_expression,
+                               const std::vector<ScalarPtr> &match_expressions) {
+    return InValueListPtr(new InValueList(test_expression, match_expressions));
+  }
+
+ protected:
+  void getFieldStringItems(
+      std::vector<std::string> *inline_field_names,
+      std::vector<std::string> *inline_field_values,
+      std::vector<std::string> *non_container_child_field_names,
+      std::vector<OptimizerTreeBaseNodePtr> *non_container_child_fields,
+      std::vector<std::string> *container_child_field_names,
+      std::vector<std::vector<OptimizerTreeBaseNodePtr>> *container_child_fields) const override;
+
+ private:
+  InValueList(const ScalarPtr &test_expression,
+              const std::vector<ScalarPtr> &match_expressions)
+      : test_expression_(test_expression),
+        match_expressions_(match_expressions) {
+    addChild(test_expression_);
+    for (const ScalarPtr &match_expression : match_expressions_) {
+      addChild(match_expression);
+    }
+  }
+
+  ScalarPtr test_expression_;
+  std::vector<ScalarPtr> match_expressions_;
+
+  DISALLOW_COPY_AND_ASSIGN(InValueList);
+};
+
+/** @} */
+
+}  // namespace expressions
+}  // namespace optimizer
+}  // namespace quickstep
+
+#endif  // QUICKSTEP_QUERY_OPTIMIZER_EXPRESSIONS_IN_VALUE_LIST_HPP_

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/expressions/PatternMatcher.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/expressions/PatternMatcher.hpp b/query_optimizer/expressions/PatternMatcher.hpp
index 528b1e6..87bc52a 100644
--- a/query_optimizer/expressions/PatternMatcher.hpp
+++ b/query_optimizer/expressions/PatternMatcher.hpp
@@ -38,6 +38,8 @@ class Cast;
 class ComparisonExpression;
 class Count;
 class Exists;
+class InTableQuery;
+class InValueList;
 class LogicalAnd;
 class LogicalNot;
 class LogicalOr;
@@ -119,6 +121,8 @@ using SomeBinaryExpression = SomeExpressionNode<BinaryExpression, ExpressionType
 using SomeCast = SomeExpressionNode<Cast, ExpressionType::kCast>;
 using SomeComparisonExpression = SomeExpressionNode<ComparisonExpression, ExpressionType::kComparisonExpression>;
 using SomeExists = SomeExpressionNode<Exists, ExpressionType::kExists>;
+using SomeInTableQuery = SomeExpressionNode<InTableQuery, ExpressionType::kInTableQuery>;
+using SomeInValueList = SomeExpressionNode<InValueList, ExpressionType::kInValueList>;
 using SomeLogicalAnd = SomeExpressionNode<LogicalAnd, ExpressionType::kLogicalAnd>;
 using SomeLogicalNot = SomeExpressionNode<LogicalNot, ExpressionType::kLogicalNot>;
 using SomeLogicalOr = SomeExpressionNode<LogicalOr, ExpressionType::kLogicalOr>;
@@ -128,6 +132,8 @@ using SomeNamedExpression = SomeExpressionNode<NamedExpression,
 using SomePredicate = SomeExpressionNode<Predicate,
                                          ExpressionType::kComparisonExpression,
                                          ExpressionType::kExists,
+                                         ExpressionType::kInTableQuery,
+                                         ExpressionType::kInValueList,
                                          ExpressionType::kLogicalAnd,
                                          ExpressionType::kLogicalNot,
                                          ExpressionType::kLogicalOr,

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/resolver/CMakeLists.txt
----------------------------------------------------------------------
diff --git a/query_optimizer/resolver/CMakeLists.txt b/query_optimizer/resolver/CMakeLists.txt
index 72f5bd7..f8ffa72 100644
--- a/query_optimizer/resolver/CMakeLists.txt
+++ b/query_optimizer/resolver/CMakeLists.txt
@@ -1,5 +1,7 @@
 #   Copyright 2011-2015 Quickstep Technologies LLC.
 #   Copyright 2015 Pivotal Software, Inc.
+#   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+#     University of Wisconsin—Madison.
 #
 #   Licensed under the Apache License, Version 2.0 (the "License");
 #   you may not use this file except in compliance with the License.
@@ -49,6 +51,7 @@ target_link_libraries(quickstep_queryoptimizer_resolver_Resolver
                       quickstep_parser_ParseOrderBy
                       quickstep_parser_ParsePredicate
                       quickstep_parser_ParsePredicateExists
+                      quickstep_parser_ParsePredicateInTableQuery
                       quickstep_parser_ParseSelect
                       quickstep_parser_ParseSelectionClause
                       quickstep_parser_ParseSimpleTableReference
@@ -68,6 +71,8 @@ target_link_libraries(quickstep_queryoptimizer_resolver_Resolver
                       quickstep_queryoptimizer_expressions_Exists
                       quickstep_queryoptimizer_expressions_ExprId
                       quickstep_queryoptimizer_expressions_ExpressionUtil
+                      quickstep_queryoptimizer_expressions_InTableQuery
+                      quickstep_queryoptimizer_expressions_InValueList
                       quickstep_queryoptimizer_expressions_LogicalAnd
                       quickstep_queryoptimizer_expressions_LogicalNot
                       quickstep_queryoptimizer_expressions_LogicalOr

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/resolver/Resolver.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/resolver/Resolver.cpp b/query_optimizer/resolver/Resolver.cpp
index f44aae8..8323c33 100644
--- a/query_optimizer/resolver/Resolver.cpp
+++ b/query_optimizer/resolver/Resolver.cpp
@@ -47,6 +47,7 @@
 #include "parser/ParseOrderBy.hpp"
 #include "parser/ParsePredicate.hpp"
 #include "parser/ParsePredicateExists.hpp"
+#include "parser/ParsePredicateInTableQuery.hpp"
 #include "parser/ParseSelect.hpp"
 #include "parser/ParseSelectionClause.hpp"
 #include "parser/ParseSimpleTableReference.hpp"
@@ -66,6 +67,8 @@
 #include "query_optimizer/expressions/Exists.hpp"
 #include "query_optimizer/expressions/ExprId.hpp"
 #include "query_optimizer/expressions/ExpressionUtil.hpp"
+#include "query_optimizer/expressions/InTableQuery.hpp"
+#include "query_optimizer/expressions/InValueList.hpp"
 #include "query_optimizer/expressions/LogicalAnd.hpp"
 #include "query_optimizer/expressions/LogicalNot.hpp"
 #include "query_optimizer/expressions/LogicalOr.hpp"
@@ -115,6 +118,8 @@
 #include "utility/SqlError.hpp"
 #include "utility/StringUtil.hpp"
 
+#include "glog/logging.h"
+
 namespace quickstep {
 namespace optimizer {
 namespace resolver {
@@ -1188,13 +1193,21 @@ L::LogicalPtr Resolver::resolveSelect(
 E::SubqueryExpressionPtr Resolver::resolveSubqueryExpression(
     const ParseSubqueryExpression &parse_subquery_expression,
     const std::vector<const Type*> *type_hints,
-    ExpressionResolutionInfo *expression_resolution_info) {
+    ExpressionResolutionInfo *expression_resolution_info,
+    const bool has_single_column) {
   L::LogicalPtr logical_subquery =
       resolveSelect(*parse_subquery_expression.query(),
                     "" /* select_name */,
                     type_hints,
                     &expression_resolution_info->name_resolver);
 
+  // Raise SQL error if the subquery is expected to return only one column but
+  // it returns multiple columns.
+  if (has_single_column && logical_subquery->getOutputAttributes().size() > 1u) {
+    THROW_SQL_ERROR_AT(&parse_subquery_expression)
+        << "Subquery must return exactly one column";
+  }
+
   if (!context_->has_nested_queries()) {
     context_->set_has_nested_queries();
   }
@@ -2523,7 +2536,74 @@ E::PredicatePtr Resolver::resolvePredicate(
       return E::Exists::Create(
           resolveSubqueryExpression(*exists.subquery(),
                                     nullptr /* type_hints */,
-                                    expression_resolution_info));
+                                    expression_resolution_info,
+                                    false /* has_single_column */));
+    }
+    case ParsePredicate::kInTableQuery: {
+      const ParsePredicateInTableQuery &in_table_query =
+          static_cast<const ParsePredicateInTableQuery&>(parse_predicate);
+
+      ExpressionResolutionInfo test_expr_resolution_info(*expression_resolution_info);
+      const E::ScalarPtr test_expression =
+          resolveExpression(*in_table_query.test_expression(),
+                            nullptr /* type_hint */,
+                            &test_expr_resolution_info);
+      if (test_expr_resolution_info.hasAggregate() && !expression_resolution_info->hasAggregate()) {
+        expression_resolution_info->parse_aggregate_expression =
+            test_expr_resolution_info.parse_aggregate_expression;
+      }
+
+      ExpressionResolutionInfo table_query_resolution_info(*expression_resolution_info);
+      const std::vector<const Type*> type_hints = { &test_expression->getValueType() };
+      const E::SubqueryExpressionPtr table_query =
+          resolveSubqueryExpression(*in_table_query.table_query(),
+                                    &type_hints,
+                                    &table_query_resolution_info,
+                                    true /* has_single_column */);
+      return E::InTableQuery::Create(test_expression,
+                                     table_query);
+    }
+    case ParsePredicate::kInValueList: {
+      const ParsePredicateInValueList &in_value_list =
+          static_cast<const ParsePredicateInValueList&>(parse_predicate);
+
+      ExpressionResolutionInfo test_expr_resolution_info(*expression_resolution_info);
+      const E::ScalarPtr test_expression =
+          resolveExpression(*in_value_list.test_expression(),
+                            nullptr /* type_hint */,
+                            &test_expr_resolution_info);
+      if (test_expr_resolution_info.hasAggregate() && !expression_resolution_info->hasAggregate()) {
+        expression_resolution_info->parse_aggregate_expression =
+            test_expr_resolution_info.parse_aggregate_expression;
+      }
+
+      std::vector<E::ScalarPtr> match_expressions;
+      for (const ParseExpression &parse_match_expression : *in_value_list.value_list()) {
+        ExpressionResolutionInfo match_expr_resolution_info(*expression_resolution_info);
+        E::ScalarPtr match_expression =
+            resolveExpression(parse_match_expression,
+                              &test_expression->getValueType(),
+                              &match_expr_resolution_info);
+
+        const Comparison &equality_comparison =
+            ComparisonFactory::GetComparison(ComparisonID::kEqual);
+        if (!equality_comparison.canCompareTypes(match_expression->getValueType(),
+                                                 test_expression->getValueType())) {
+          THROW_SQL_ERROR_AT(&parse_match_expression)
+              << "The value expression has the type "
+              << match_expression->getValueType().getName()
+              << ", which cannot be compared with the type of the test expression "
+              << test_expression->getValueType().getName();
+        }
+
+        if (match_expr_resolution_info.hasAggregate() && !expression_resolution_info->hasAggregate()) {
+          expression_resolution_info->parse_aggregate_expression =
+              match_expr_resolution_info.parse_aggregate_expression;
+        }
+        match_expressions.emplace_back(match_expression);
+      }
+      return E::InValueList::Create(test_expression,
+                                    match_expressions);
     }
     default:
       LOG(FATAL) << "Unknown predicate: " << parse_predicate.toString();

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/resolver/Resolver.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/resolver/Resolver.hpp b/query_optimizer/resolver/Resolver.hpp
index 39b5388..31afe18 100644
--- a/query_optimizer/resolver/Resolver.hpp
+++ b/query_optimizer/resolver/Resolver.hpp
@@ -445,11 +445,14 @@ class Resolver {
    * @param type_hints The type hints for output columns by the subquery.
    * @param expression_resolution_info Resolution info that contains the name
    *        resolver and info to be updated after resolution.
+   * @param has_single_column True if the subquery is expected to return only
+   *        one column in the result.
    */
   expressions::SubqueryExpressionPtr resolveSubqueryExpression(
       const ParseSubqueryExpression &parse_subquery_expression,
       const std::vector<const Type*> *type_hints,
-      ExpressionResolutionInfo *expression_resolution_info);
+      ExpressionResolutionInfo *expression_resolution_info,
+      const bool has_single_column);
 
   /**
    * @brief Resolves a relation name to a pointer to the corresponding

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/rules/CMakeLists.txt
----------------------------------------------------------------------
diff --git a/query_optimizer/rules/CMakeLists.txt b/query_optimizer/rules/CMakeLists.txt
index 03dcc50..3461a5e 100644
--- a/query_optimizer/rules/CMakeLists.txt
+++ b/query_optimizer/rules/CMakeLists.txt
@@ -118,6 +118,7 @@ target_link_libraries(quickstep_queryoptimizer_rules_UnnestSubqueries
                       quickstep_queryoptimizer_expressions_Expression
                       quickstep_queryoptimizer_expressions_ExpressionType
                       quickstep_queryoptimizer_expressions_ExpressionUtil
+                      quickstep_queryoptimizer_expressions_InTableQuery
                       quickstep_queryoptimizer_expressions_LogicalAnd
                       quickstep_queryoptimizer_expressions_LogicalNot
                       quickstep_queryoptimizer_expressions_LogicalOr
@@ -136,6 +137,7 @@ target_link_libraries(quickstep_queryoptimizer_rules_UnnestSubqueries
                       quickstep_queryoptimizer_rules_BottomUpRule
                       quickstep_queryoptimizer_rules_Rule
                       quickstep_types_operations_comparisons_Comparison
+                      quickstep_types_operations_comparisons_ComparisonFactory
                       quickstep_types_operations_comparisons_ComparisonID
                       quickstep_utility_Macros
                       quickstep_utility_SqlError)

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/rules/UnnestSubqueries.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/rules/UnnestSubqueries.cpp b/query_optimizer/rules/UnnestSubqueries.cpp
index 2dca36e..7852577 100644
--- a/query_optimizer/rules/UnnestSubqueries.cpp
+++ b/query_optimizer/rules/UnnestSubqueries.cpp
@@ -29,6 +29,7 @@
 #include "query_optimizer/expressions/ExprId.hpp"
 #include "query_optimizer/expressions/ExpressionType.hpp"
 #include "query_optimizer/expressions/ExpressionUtil.hpp"
+#include "query_optimizer/expressions/InTableQuery.hpp"
 #include "query_optimizer/expressions/LogicalAnd.hpp"
 #include "query_optimizer/expressions/LogicalNot.hpp"
 #include "query_optimizer/expressions/LogicalOr.hpp"
@@ -45,6 +46,7 @@
 #include "query_optimizer/logical/TopLevelPlan.hpp"
 #include "query_optimizer/rules/Rule.hpp"
 #include "types/operations/comparisons/Comparison.hpp"
+#include "types/operations/comparisons/ComparisonFactory.hpp"
 #include "types/operations/comparisons/ComparisonID.hpp"
 #include "utility/SqlError.hpp"
 
@@ -300,6 +302,7 @@ E::ExpressionPtr UnnestSubqueriesForNonRootLogical::eliminateOuterAttributeRefer
     std::vector<E::AttributeReferencePtr> *probe_join_attributes,
     std::vector<E::AttributeReferencePtr> *build_join_attributes,
     std::vector<E::PredicatePtr> *non_hash_join_predicates) {
+  DCHECK(expression->getExpressionType() != E::ExpressionType::kInTableQuery);
   DCHECK(expression->getExpressionType() != E::ExpressionType::kExists);
   DCHECK(expression->getExpressionType() != E::ExpressionType::kSubqueryExpression);
 
@@ -569,6 +572,13 @@ E::ExpressionPtr UnnestSubqueriesForExpession::applyInternal(
       transformExists(static_cast<const E::Exists&>(*node));
       return E::ExpressionPtr();
     }
+    case E::ExpressionType::kInTableQuery: {
+      if (!allow_exists_or_in) {
+        THROW_SQL_ERROR() << "IN(table query) can only appear in (un-nested) NOT, AND or by itself";
+      }
+      transformInTableQuery(static_cast<const E::InTableQuery&>(*node));
+      return E::ExpressionPtr();
+    }
     case E::ExpressionType::kLogicalNot: {
       const E::LogicalNot &logical_not = static_cast<const E::LogicalNot&>(*node);
       const E::PredicatePtr &operand = logical_not.operand();
@@ -579,6 +589,13 @@ E::ExpressionPtr UnnestSubqueriesForExpession::applyInternal(
         transformExists(static_cast<const E::Exists&>(*operand));
         correlated_query_info_vec_->back().join_type = CorrelatedQueryInfo::JoinType::kLeftAntiJoin;
         return E::PredicatePtr();
+      } else if (operand->getExpressionType() == E::ExpressionType::kInTableQuery) {
+        if (!allow_exists_or_in) {
+          THROW_SQL_ERROR() << "IN(table query) can only appear in (un-nested) NOT, AND or by itself";
+        }
+        transformInTableQuery(static_cast<const E::InTableQuery&>(*operand));
+        correlated_query_info_vec_->back().join_type = CorrelatedQueryInfo::JoinType::kLeftAntiJoin;
+        return E::PredicatePtr();
       }
       const E::ExpressionPtr new_operand =
           applyInternal(false /* allow_exists_or_in */,
@@ -688,6 +705,48 @@ void UnnestSubqueriesForExpession::transformExists(
                                            std::move(non_hash_join_predicates));
 }
 
+void UnnestSubqueriesForExpession::transformInTableQuery(
+    const E::InTableQuery &in_table_query) {
+  std::vector<E::AttributeReferencePtr> probe_join_attributes;
+  std::vector<E::AttributeReferencePtr> build_join_attributes;
+  std::vector<E::PredicatePtr> non_hash_join_predicates;
+  UnnestSubqueriesForNonRootLogical unnest_logical_rule(false,  // scalar_query
+                                                        visible_attributes_from_outer_query_,
+                                                        context_,
+                                                        uncorrelated_subqueries_,
+                                                        &probe_join_attributes,
+                                                        &build_join_attributes,
+                                                        &non_hash_join_predicates);
+  const L::LogicalPtr subquery = in_table_query.table_query()->subquery();
+  const L::LogicalPtr new_subquery = unnest_logical_rule.apply(subquery);
+
+  DCHECK(!new_subquery->getOutputAttributes().empty());
+  const E::AttributeReferencePtr join_attr_in_subquery = subquery->getOutputAttributes()[0];
+
+  E::AttributeReferencePtr test_attribute;
+  if (E::SomeAttributeReference::MatchesWithConditionalCast(in_table_query.test_expression(),
+                                                            &test_attribute)) {
+    probe_join_attributes.emplace_back(test_attribute);
+    build_join_attributes.emplace_back(join_attr_in_subquery);
+  } else {
+    if (!probe_join_attributes.empty()) {
+      non_hash_join_predicates.emplace_back(
+          E::ComparisonExpression::Create(ComparisonFactory::GetComparison(ComparisonID::kEqual),
+                                          in_table_query.test_expression(),
+                                          join_attr_in_subquery));
+    } else {
+      // TODO(qzeng): We can actually add a project to precompute the test expression.
+      THROW_SQL_ERROR() << "Cannot find an equality join predicate for IN";
+    }
+  }
+
+  correlated_query_info_vec_->emplace_back(CorrelatedQueryInfo::JoinType::kLeftSemiJoin,
+                                           new_subquery,
+                                           std::move(probe_join_attributes),
+                                           std::move(build_join_attributes),
+                                           std::move(non_hash_join_predicates));
+}
+
 E::ExpressionPtr DeOuterAttributeReference::applyToNode(const E::ExpressionPtr &input) {
   E::AttributeReferencePtr attr;
   if (E::SomeAttributeReference::MatchesWithConditionalCast(input, &attr) &&

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/query_optimizer/rules/UnnestSubqueries.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/rules/UnnestSubqueries.hpp b/query_optimizer/rules/UnnestSubqueries.hpp
index 1ab48ac..d51dfd5 100644
--- a/query_optimizer/rules/UnnestSubqueries.hpp
+++ b/query_optimizer/rules/UnnestSubqueries.hpp
@@ -41,6 +41,7 @@ class OptimizerContext;
 namespace expressions {
 
 class Exists;
+class InTableQuery;
 
 }
 
@@ -179,8 +180,22 @@ class UnnestSubqueriesForExpession : public Rule<expressions::Expression> {
       const bool allow_exists_or_in,
       const expressions::ExpressionPtr &node);
 
+  /**
+   * @brief Transform an EXIST predicate into a HashLeftSemiJoin and store the
+   *        transformed results into correlated_query_info_vec_
+   *
+   * @param exists_predicate The EXISTS predicate to be transformed.
+   */
   void transformExists(const expressions::Exists &exists_predicate);
 
+  /**
+   * @brief Transform an IN predicate into a HashLeftSemiJoin and store the
+   *        transformed results into correlated_query_info_vec_
+   *
+   * @param in_table_query The IN predicate to be transformed.
+   */
+  void transformInTableQuery(const expressions::InTableQuery &in_table_query);
+
   OptimizerContext *context_;
   std::unordered_map<expressions::ExprId, logical::LogicalPtr> *uncorrelated_subqueries_;
   std::vector<CorrelatedQueryInfo> *correlated_query_info_vec_;

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/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 9bfa27c..0618ae2 100644
--- a/query_optimizer/tests/execution_generator/Select.test
+++ b/query_optimizer/tests/execution_generator/Select.test
@@ -764,6 +764,62 @@ WHERE
 +-----------+
 ==
 
+# IN predicate
+SELECT *
+FROM generate_series(1, 5) AS gs(i)
+WHERE i IN (2, 4);
+--
++-----------+
+|i          |
++-----------+
+|          2|
+|          4|
++-----------+
+==
+
+SELECT *
+FROM generate_series(1, 5) AS gs(i)
+WHERE i NOT IN (2, 4);
+--
++-----------+
+|i          |
++-----------+
+|          1|
+|          3|
+|          5|
++-----------+
+==
+
+SELECT *
+FROM generate_series(1, 5) AS gs(i)
+WHERE i NOT IN (i*i-6, CASE WHEN i < 3 THEN 1 ELSE 4 END);
+--
++-----------+
+|i          |
++-----------+
+|          2|
+|          5|
++-----------+
+==
+
+SELECT *
+FROM generate_series(1, 10) AS gs(i)
+WHERE i NOT IN (
+  SELECT *
+  FROM generate_series(2, 10, 2)
+);
+--
++-----------+
+|i          |
++-----------+
+|          1|
+|          3|
+|          5|
+|          7|
+|          9|
++-----------+
+==
+
 # TODO(team): Support uncorrelated queries.
 # SELECT COUNT(*)
 # FROM test

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/0fd8c039/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 a7a9cf8..c6d4201 100644
--- a/query_optimizer/tests/logical_generator/Select.test
+++ b/query_optimizer/tests/logical_generator/Select.test
@@ -720,3 +720,179 @@ TopLevelPlan
 |   +-AttributeReference[id=1,name=i,relation=,type=Int]
 +-output_attributes=
   +-AttributeReference[id=1,name=i,relation=,type=Int]
+==
+
+# IN predicate
+SELECT char_col
+FROM test
+WHERE int_col IN (1, 2, 3);
+--
+TopLevelPlan
++-plan=Project
+| +-input=Filter
+| | +-input=TableReference[relation_name=Test,relation_alias=test]
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
+| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
+| | |   type=VarChar(20) NULL]
+| | +-filter_predicate=InValueList
+| |   +-test_expression=AttributeReference[id=0,name=int_col,relation=test,
+| |   | type=Int NULL]
+| |   +-match_expressions=
+| |     +-Literal[value=1,type=Int]
+| |     +-Literal[value=2,type=Int]
+| |     +-Literal[value=3,type=Int]
+| +-project_list=
+|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
++-output_attributes=
+  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+==
+
+SELECT char_col
+FROM test
+WHERE int_col*2 NOT IN (
+        long_col+1,
+        CASE WHEN float_col > 0 THEN 1
+             ELSE double_col END);
+--
+TopLevelPlan
++-plan=Project
+| +-input=Filter
+| | +-input=TableReference[relation_name=Test,relation_alias=test]
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
+| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
+| | |   type=VarChar(20) NULL]
+| | +-filter_predicate=NOT
+| |   +-InValueList
+| |     +-test_expression=Multiply
+| |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| |     | +-Literal[value=2,type=Int]
+| |     +-match_expressions=
+| |       +-Add
+| |       | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| |       | +-Literal[value=1,type=Int]
+| |       +-SearchedCase
+| |         +-else_result_expression=AttributeReference[id=3,name=double_col,
+| |         | relation=test,type=Double NULL]
+| |         +-condition_perdicates=
+| |         | +-Greater
+| |         |   +-AttributeReference[id=2,name=float_col,relation=test,
+| |         |   | type=Float]
+| |         |   +-Literal[value=0,type=Int]
+| |         +-conditional_result_expressions=
+| |           +-Cast[target_type=Double NULL]
+| |             +-operand=Literal[value=1,type=Int]
+| +-project_list=
+|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
++-output_attributes=
+  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+==
+
+SELECT char_col
+FROM test
+WHERE int_col IN (
+  SELECT SUM(long_col) - 10
+  FROM test
+  GROUP BY vchar_col
+);
+--
+TopLevelPlan
++-plan=Project
+| +-input=HashLeftSemiJoin
+| | +-left=TableReference[relation_name=Test,relation_alias=test]
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
+| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
+| | |   type=VarChar(20) NULL]
+| | +-right=Project
+| | | +-input=Aggregate
+| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
+| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
+| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
+| | | | | | type=Double NULL]
+| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
+| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
+| | | | |   type=VarChar(20) NULL]
+| | | | +-grouping_expressions=
+| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
+| | | | |   type=VarChar(20) NULL]
+| | | | +-aggregate_expressions=
+| | | |   +-Alias[id=12,name=,alias=$aggregate0,relation=$aggregate,
+| | | |     type=Long NULL]
+| | | |     +-AggregateFunction[function=SUM]
+| | | |       +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| | | +-project_list=
+| | |   +-Alias[id=13,name=,alias=(SUM(long_col)-10),relation=,type=Long NULL]
+| | |     +-Subtract
+| | |       +-AttributeReference[id=12,name=,alias=$aggregate0,
+| | |       | relation=$aggregate,type=Long NULL]
+| | |       +-Literal[value=10,type=Int]
+| | +-left_join_attributes=
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | +-right_join_attributes=
+| |   +-AttributeReference[id=13,name=,alias=(SUM(long_col)-10),relation=,
+| |     type=Long NULL]
+| +-project_list=
+|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
++-output_attributes=
+  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+==
+
+SELECT char_col
+FROM test
+WHERE int_col NOT IN (
+  SELECT long_col
+  FROM test
+  WHERE long_col IN (1, 2)
+);
+--
+TopLevelPlan
++-plan=Project
+| +-input=HashLeftAntiJoin
+| | +-left=TableReference[relation_name=Test,relation_alias=test]
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
+| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
+| | |   type=VarChar(20) NULL]
+| | +-right=Project
+| | | +-input=Filter
+| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
+| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
+| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
+| | | | | | type=Double NULL]
+| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
+| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
+| | | | |   type=VarChar(20) NULL]
+| | | | +-filter_predicate=InValueList
+| | | |   +-test_expression=AttributeReference[id=7,name=long_col,relation=test,
+| | | |   | type=Long]
+| | | |   +-match_expressions=
+| | | |     +-Literal[value=1,type=Long]
+| | | |     +-Literal[value=2,type=Long]
+| | | +-project_list=
+| | |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| | +-left_join_attributes=
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | +-right_join_attributes=
+| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| +-project_list=
+|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
++-output_attributes=
+  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]


Mime
View raw message