#   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.
#   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.

# Query 1
SELECT
  l_returnflag,
  l_linestatus,
  SUM(l_quantity) AS sum_qty,
  SUM(l_extendedprice) AS sum_base_price,
  SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
  SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
  AVG(l_quantity) AS avg_qty,
  AVG(l_extendedprice) AS avg_price,
  AVG(l_discount) AS avg_disc,
  COUNT(*) AS count_order
FROM
  lineitem
WHERE
  l_shipdate <= DATE '1998-12-01' - INTERVAL '96 day'
GROUP BY
  l_returnflag,
  l_linestatus
ORDER BY
  l_returnflag,
  l_linestatus
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=l_returnflag]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=l_linestatus]
  | +-SelectListItem[alias=sum_qty]
  | | +-FunctionCall[name=SUM]
  | |   +-AttributeReference[attribute_name=l_quantity]
  | +-SelectListItem[alias=sum_base_price]
  | | +-FunctionCall[name=SUM]
  | |   +-AttributeReference[attribute_name=l_extendedprice]
  | +-SelectListItem[alias=sum_disc_price]
  | | +-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]
  | +-SelectListItem[alias=sum_charge]
  | | +-FunctionCall[name=SUM]
  | |   +-Multiply
  | |     +-left_operand=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]
  | |     +-right_operand=Add
  | |       +-left_operand=Literal
  | |       | +-NumericLiteral[numeric_string=1,float_like=false]
  | |       +-right_operand=AttributeReference[attribute_name=l_tax]
  | +-SelectListItem[alias=avg_qty]
  | | +-FunctionCall[name=AVG]
  | |   +-AttributeReference[attribute_name=l_quantity]
  | +-SelectListItem[alias=avg_price]
  | | +-FunctionCall[name=AVG]
  | |   +-AttributeReference[attribute_name=l_extendedprice]
  | +-SelectListItem[alias=avg_disc]
  | | +-FunctionCall[name=AVG]
  | |   +-AttributeReference[attribute_name=l_discount]
  | +-SelectListItem[alias=count_order]
  |   +-FunctionCall[name=COUNT,is_star=true]
  +-where_clause=LessOrEqual
  | +-left_operand=AttributeReference[attribute_name=l_shipdate]
  | +-right_operand=Subtract
  |   +-left_operand=Literal
  |   | +-StringLiteral[value=1998-12-01,explicit_type=Datetime]
  |   +-right_operand=Literal
  |     +-StringLiteral[value=96 day,explicit_type=DatetimeInterval]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=l_returnflag]
  | +-AttributeReference[attribute_name=l_linestatus]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=true,nulls_first=false]
  | | +-AttributeReference[attribute_name=l_returnflag]
  | +-OrderByItem[is_asc=true,nulls_first=false]
  |   +-AttributeReference[attribute_name=l_linestatus]
  +-from_clause=
    +-TableReference[table=lineitem]
==

# Query 2
SELECT
  s_acctbal,
  s_name,
  n_name,
  p_partkey,
  p_mfgr,
  s_address,
  s_phone,
  s_comment
FROM
  part,
  supplier,
  partsupp,
  nation,
  region
WHERE
  p_partkey = ps_partkey
  AND s_suppkey = ps_suppkey
  AND p_size = 48
  AND p_type LIKE '%NICKEL'
  AND s_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'ASIA'
  AND ps_supplycost = (
    SELECT
      MIN(ps_supplycost)
    FROM
      partsupp,
      supplier,
      nation,
      region
    WHERE
      p_partkey = ps_partkey
      AND s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND r_name = 'ASIA'
  )
ORDER BY
  s_acctbal desc,
  n_name,
  s_name,
  p_partkey
LIMIT 100
--
ERROR: syntax error (25 : 5)
    SELECT
    ^
==

# Query 3
SELECT
  l_orderkey,
  SUM(l_extendedprice * (1 - l_discount)) AS revenue,
  o_orderdate,
  o_shippriority
FROM
  customer,
  orders,
  lineitem
WHERE
  c_mktsegment = 'AUTOMOBILE'
  AND c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND o_orderdate < DATE '1995-03-17'
  AND l_shipdate > DATE '1995-03-17'
GROUP BY
  l_orderkey,
  o_orderdate,
  o_shippriority
ORDER BY
  revenue desc,
  o_orderdate
LIMIT 10
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=l_orderkey]
  | +-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]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=o_orderdate]
  | +-SelectListItem
  |   +-AttributeReference[attribute_name=o_shippriority]
  +-where_clause=And
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=c_mktsegment]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=AUTOMOBILE]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=c_custkey]
  | | +-right_operand=AttributeReference[attribute_name=o_custkey]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
  | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
  | +-Less
  | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=1995-03-17,explicit_type=Datetime]
  | +-Greater
  |   +-left_operand=AttributeReference[attribute_name=l_shipdate]
  |   +-right_operand=Literal
  |     +-StringLiteral[value=1995-03-17,explicit_type=Datetime]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=l_orderkey]
  | +-AttributeReference[attribute_name=o_orderdate]
  | +-AttributeReference[attribute_name=o_shippriority]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=false,nulls_first=true]
  | | +-AttributeReference[attribute_name=revenue]
  | +-OrderByItem[is_asc=true,nulls_first=false]
  |   +-AttributeReference[attribute_name=o_orderdate]
  +-limit=LIMIT
  | +-NumericLiteral[numeric_string=10,float_like=false]
  +-from_clause=
    +-TableReference[table=customer]
    +-TableReference[table=orders]
    +-TableReference[table=lineitem]
==

# Query 4
SELECT
  o_orderpriority,
  COUNT(*) AS order_count
FROM
  orders
WHERE
  o_orderdate >= DATE '1995-08-01'
  AND o_orderdate < DATE '1995-08-01' + INTERVAL '3 month'
  AND EXISTS (
    SELECT
      *
    FROM
      lineitem
    WHERE
      l_orderkey = o_orderkey
      AND l_commitdate < l_receiptdate
  )
GROUP BY
  o_orderpriority
ORDER BY
  o_orderpriority
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=o_orderpriority]
  | +-SelectListItem[alias=order_count]
  |   +-FunctionCall[name=COUNT,is_star=true]
  +-where_clause=And
  | +-GreaterOrEqual
  | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=1995-08-01,explicit_type=Datetime]
  | +-Less
  | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
  | | +-right_operand=Add
  | |   +-left_operand=Literal
  | |   | +-StringLiteral[value=1995-08-01,explicit_type=Datetime]
  | |   +-right_operand=Literal
  | |     +-StringLiteral[value=3 month,explicit_type=YearMonthInterval]
  | +-Exists
  |   +-subquery=SubqueryExpression
  |     +-Select
  |       +-select_clause=SelectStar
  |       +-where_clause=And
  |       | +-Equal
  |       | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
  |       | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
  |       | +-Less
  |       |   +-left_operand=AttributeReference[attribute_name=l_commitdate]
  |       |   +-right_operand=AttributeReference[attribute_name=l_receiptdate]
  |       +-from_clause=
  |         +-TableReference[table=lineitem]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=o_orderpriority]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=true,nulls_first=false]
  |   +-AttributeReference[attribute_name=o_orderpriority]
  +-from_clause=
    +-TableReference[table=orders]
==

# Query 5
SELECT
  n_name,
  SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM
  customer,
  orders,
  lineitem,
  supplier,
  nation,
  region
WHERE
  c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND l_suppkey = s_suppkey
  AND c_nationkey = s_nationkey
  AND s_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'AMERICA'
  AND o_orderdate >= DATE '1997-01-01'
  AND o_orderdate < DATE '1997-01-01' + INTERVAL '1 year'
GROUP BY
  n_name
ORDER BY
  revenue DESC
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=n_name]
  | +-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=And
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=c_custkey]
  | | +-right_operand=AttributeReference[attribute_name=o_custkey]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
  | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=l_suppkey]
  | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=c_nationkey]
  | | +-right_operand=AttributeReference[attribute_name=s_nationkey]
  | +-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=AMERICA]
  | +-GreaterOrEqual
  | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=1997-01-01,explicit_type=Datetime]
  | +-Less
  |   +-left_operand=AttributeReference[attribute_name=o_orderdate]
  |   +-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=n_name]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=false,nulls_first=true]
  |   +-AttributeReference[attribute_name=revenue]
  +-from_clause=
    +-TableReference[table=customer]
    +-TableReference[table=orders]
    +-TableReference[table=lineitem]
    +-TableReference[table=supplier]
    +-TableReference[table=nation]
    +-TableReference[table=region]
==

# Query 6
SELECT
  SUM(l_extendedprice * l_discount) AS revenue
FROM
  lineitem
WHERE
  l_shipdate >= DATE '1997-01-01'
  AND l_shipdate < DATE '1997-01-01' + INTERVAL '1 year'
  AND l_discount BETWEEN 0.03 - 0.01 AND 0.03 + 0.01
  AND l_quantity < 25
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem[alias=revenue]
  |   +-FunctionCall[name=SUM]
  |     +-Multiply
  |       +-left_operand=AttributeReference[attribute_name=l_extendedprice]
  |       +-right_operand=AttributeReference[attribute_name=l_discount]
  +-where_clause=And
  | +-GreaterOrEqual
  | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=1997-01-01,explicit_type=Datetime]
  | +-Less
  | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
  | | +-right_operand=Add
  | |   +-left_operand=Literal
  | |   | +-StringLiteral[value=1997-01-01,explicit_type=Datetime]
  | |   +-right_operand=Literal
  | |     +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
  | +-Between
  | | +-check_operand=AttributeReference[attribute_name=l_discount]
  | | +-lower_bound_operand=Subtract
  | | | +-left_operand=Literal
  | | | | +-NumericLiteral[numeric_string=0.03,float_like=true]
  | | | +-right_operand=Literal
  | | |   +-NumericLiteral[numeric_string=0.01,float_like=true]
  | | +-upper_bound_operand=Add
  | |   +-left_operand=Literal
  | |   | +-NumericLiteral[numeric_string=0.03,float_like=true]
  | |   +-right_operand=Literal
  | |     +-NumericLiteral[numeric_string=0.01,float_like=true]
  | +-Less
  |   +-left_operand=AttributeReference[attribute_name=l_quantity]
  |   +-right_operand=Literal
  |     +-NumericLiteral[numeric_string=25,float_like=false]
  +-from_clause=
    +-TableReference[table=lineitem]
==

# Query 7
SELECT
  supp_nation,
  cust_nation,
  l_year,
  SUM(volume) AS revenue
FROM
  (
    SELECT
      n1.n_name AS supp_nation,
      n2.n_name AS cust_nation,
      EXTRACT(year FROM l_shipdate) AS l_year,
      l_extendedprice * (1 - l_discount) AS volume
    FROM
      supplier,
      lineitem,
      orders,
      customer,
      nation n1,
      nation n2
    WHERE
      s_suppkey = l_suppkey
      AND o_orderkey = l_orderkey
      AND c_custkey = o_custkey
      AND s_nationkey = n1.n_nationkey
      AND c_nationkey = n2.n_nationkey
      AND (
        (n1.n_name = 'ETHIOPIA' AND n2.n_name = 'UNITED STATES')
        OR (n1.n_name = 'UNITED STATES' AND n2.n_name = 'ETHIOPIA')
      )
      AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
  ) AS shipping
GROUP BY
  supp_nation,
  cust_nation,
  l_year
ORDER BY
  supp_nation,
  cust_nation,
  l_year
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=supp_nation]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=cust_nation]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=l_year]
  | +-SelectListItem[alias=revenue]
  |   +-FunctionCall[name=SUM]
  |     +-AttributeReference[attribute_name=volume]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=supp_nation]
  | +-AttributeReference[attribute_name=cust_nation]
  | +-AttributeReference[attribute_name=l_year]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=true,nulls_first=false]
  | | +-AttributeReference[attribute_name=supp_nation]
  | +-OrderByItem[is_asc=true,nulls_first=false]
  | | +-AttributeReference[attribute_name=cust_nation]
  | +-OrderByItem[is_asc=true,nulls_first=false]
  |   +-AttributeReference[attribute_name=l_year]
  +-from_clause=
    +-SubqueryTable
      +-table_signature=TableSignature[table_alias=shipping]
      +-SubqueryExpression
        +-Select
          +-select_clause=SelectList
          | +-SelectListItem[alias=supp_nation]
          | | +-AttributeReference[attribute_name=n_name,relation_name=n1]
          | +-SelectListItem[alias=cust_nation]
          | | +-AttributeReference[attribute_name=n_name,relation_name=n2]
          | +-SelectListItem[alias=l_year]
          | | +-Extract[unit=year]
          | |   +-date_expression=AttributeReference[attribute_name=l_shipdate]
          | +-SelectListItem[alias=volume]
          |   +-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
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
          | | +-right_operand=AttributeReference[attribute_name=l_suppkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=o_orderkey]
          | | +-right_operand=AttributeReference[attribute_name=l_orderkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=c_custkey]
          | | +-right_operand=AttributeReference[attribute_name=o_custkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
          | | +-right_operand=AttributeReference[attribute_name=n_nationkey,
          | |   relation_name=n1]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=c_nationkey]
          | | +-right_operand=AttributeReference[attribute_name=n_nationkey,
          | |   relation_name=n2]
          | +-Or
          | | +-And
          | | | +-Equal
          | | | | +-left_operand=AttributeReference[attribute_name=n_name,
          | | | | | relation_name=n1]
          | | | | +-right_operand=Literal
          | | | |   +-StringLiteral[value=ETHIOPIA]
          | | | +-Equal
          | | |   +-left_operand=AttributeReference[attribute_name=n_name,
          | | |   | relation_name=n2]
          | | |   +-right_operand=Literal
          | | |     +-StringLiteral[value=UNITED STATES]
          | | +-And
          | |   +-Equal
          | |   | +-left_operand=AttributeReference[attribute_name=n_name,
          | |   | | relation_name=n1]
          | |   | +-right_operand=Literal
          | |   |   +-StringLiteral[value=UNITED STATES]
          | |   +-Equal
          | |     +-left_operand=AttributeReference[attribute_name=n_name,
          | |     | relation_name=n2]
          | |     +-right_operand=Literal
          | |       +-StringLiteral[value=ETHIOPIA]
          | +-Between
          |   +-check_operand=AttributeReference[attribute_name=l_shipdate]
          |   +-lower_bound_operand=Literal
          |   | +-StringLiteral[value=1995-01-01,explicit_type=Datetime]
          |   +-upper_bound_operand=Literal
          |     +-StringLiteral[value=1996-12-31,explicit_type=Datetime]
          +-from_clause=
            +-TableReference[table=supplier]
            +-TableReference[table=lineitem]
            +-TableReference[table=orders]
            +-TableReference[table=customer]
            +-TableReference[table=nation]
            | +-table_signature=TableSignature[table_alias=n1]
            +-TableReference[table=nation]
              +-table_signature=TableSignature[table_alias=n2]
==

# Query 8
SELECT
  o_year,
  SUM(CASE
    WHEN nation = 'UNITED STATES' THEN volume
    ELSE 0
  END) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(year FROM o_orderdate) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      part,
      supplier,
      lineitem,
      orders,
      customer,
      nation n1,
      nation n2,
      region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
      AND p_type = 'MEDIUM ANODIZED NICKEL'
  ) AS all_nations
GROUP BY
  o_year
ORDER BY
  o_year
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=o_year]
  | +-SelectListItem[alias=mkt_share]
  |   +-Divide
  |     +-left_operand=FunctionCall[name=SUM]
  |     | +-SearchedCaseExpression
  |     |   +-else_result_expression=Literal
  |     |   | +-NumericLiteral[numeric_string=0,float_like=false]
  |     |   +-when_clauses=
  |     |     +-SearchedWhenClause
  |     |       +-condition_predicate=Equal
  |     |       | +-left_operand=AttributeReference[attribute_name=nation]
  |     |       | +-right_operand=Literal
  |     |       |   +-StringLiteral[value=UNITED STATES]
  |     |       +-result_expression=AttributeReference[attribute_name=volume]
  |     +-right_operand=FunctionCall[name=SUM]
  |       +-AttributeReference[attribute_name=volume]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=o_year]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=true,nulls_first=false]
  |   +-AttributeReference[attribute_name=o_year]
  +-from_clause=
    +-SubqueryTable
      +-table_signature=TableSignature[table_alias=all_nations]
      +-SubqueryExpression
        +-Select
          +-select_clause=SelectList
          | +-SelectListItem[alias=o_year]
          | | +-Extract[unit=year]
          | |   +-date_expression=AttributeReference[attribute_name=o_orderdate]
          | +-SelectListItem[alias=volume]
          | | +-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]
          | +-SelectListItem[alias=nation]
          |   +-AttributeReference[attribute_name=n_name,relation_name=n2]
          +-where_clause=And
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=p_partkey]
          | | +-right_operand=AttributeReference[attribute_name=l_partkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
          | | +-right_operand=AttributeReference[attribute_name=l_suppkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
          | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=o_custkey]
          | | +-right_operand=AttributeReference[attribute_name=c_custkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=c_nationkey]
          | | +-right_operand=AttributeReference[attribute_name=n_nationkey,
          | |   relation_name=n1]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=n_regionkey,
          | | | relation_name=n1]
          | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=r_name]
          | | +-right_operand=Literal
          | |   +-StringLiteral[value=AMERICA]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
          | | +-right_operand=AttributeReference[attribute_name=n_nationkey,
          | |   relation_name=n2]
          | +-Between
          | | +-check_operand=AttributeReference[attribute_name=o_orderdate]
          | | +-lower_bound_operand=Literal
          | | | +-StringLiteral[value=1995-01-01,explicit_type=Datetime]
          | | +-upper_bound_operand=Literal
          | |   +-StringLiteral[value=1996-12-31,explicit_type=Datetime]
          | +-Equal
          |   +-left_operand=AttributeReference[attribute_name=p_type]
          |   +-right_operand=Literal
          |     +-StringLiteral[value=MEDIUM ANODIZED NICKEL]
          +-from_clause=
            +-TableReference[table=part]
            +-TableReference[table=supplier]
            +-TableReference[table=lineitem]
            +-TableReference[table=orders]
            +-TableReference[table=customer]
            +-TableReference[table=nation]
            | +-table_signature=TableSignature[table_alias=n1]
            +-TableReference[table=nation]
            | +-table_signature=TableSignature[table_alias=n2]
            +-TableReference[table=region]
==

# Query 9
SELECT
  nation,
  o_year,
  SUM(amount) AS sum_profit
FROM
  (
    SELECT
      n_name AS nation,
      EXTRACT(year FROM o_orderdate) AS o_year,
      l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
    FROM
      part,
      supplier,
      lineitem,
      partsupp,
      orders,
      nation
    WHERE
      s_suppkey = l_suppkey
      AND ps_suppkey = l_suppkey
      AND ps_partkey = l_partkey
      AND p_partkey = l_partkey
      AND o_orderkey = l_orderkey
      AND s_nationkey = n_nationkey
      AND p_name LIKE '%ghost%'
  ) AS profit
GROUP BY
  nation,
  o_year
ORDER BY
  nation,
  o_year DESC
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=nation]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=o_year]
  | +-SelectListItem[alias=sum_profit]
  |   +-FunctionCall[name=SUM]
  |     +-AttributeReference[attribute_name=amount]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=nation]
  | +-AttributeReference[attribute_name=o_year]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=true,nulls_first=false]
  | | +-AttributeReference[attribute_name=nation]
  | +-OrderByItem[is_asc=false,nulls_first=true]
  |   +-AttributeReference[attribute_name=o_year]
  +-from_clause=
    +-SubqueryTable
      +-table_signature=TableSignature[table_alias=profit]
      +-SubqueryExpression
        +-Select
          +-select_clause=SelectList
          | +-SelectListItem[alias=nation]
          | | +-AttributeReference[attribute_name=n_name]
          | +-SelectListItem[alias=o_year]
          | | +-Extract[unit=year]
          | |   +-date_expression=AttributeReference[attribute_name=o_orderdate]
          | +-SelectListItem[alias=amount]
          |   +-Subtract
          |     +-left_operand=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]
          |     +-right_operand=Multiply
          |       +-left_operand=AttributeReference[attribute_name=ps_supplycost]
          |       +-right_operand=AttributeReference[attribute_name=l_quantity]
          +-where_clause=And
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
          | | +-right_operand=AttributeReference[attribute_name=l_suppkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=ps_suppkey]
          | | +-right_operand=AttributeReference[attribute_name=l_suppkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=ps_partkey]
          | | +-right_operand=AttributeReference[attribute_name=l_partkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=p_partkey]
          | | +-right_operand=AttributeReference[attribute_name=l_partkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=o_orderkey]
          | | +-right_operand=AttributeReference[attribute_name=l_orderkey]
          | +-Equal
          | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
          | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
          | +-Like
          |   +-left_operand=AttributeReference[attribute_name=p_name]
          |   +-right_operand=Literal
          |     +-StringLiteral[value=%ghost%]
          +-from_clause=
            +-TableReference[table=part]
            +-TableReference[table=supplier]
            +-TableReference[table=lineitem]
            +-TableReference[table=partsupp]
            +-TableReference[table=orders]
            +-TableReference[table=nation]
==

# Query 10
SELECT
  c_custkey,
  c_name,
  SUM(l_extendedprice * (1 - l_discount)) AS revenue,
  c_acctbal,
  n_name,
  c_address,
  c_phone,
  c_comment
FROM
  customer,
  orders,
  lineitem,
  nation
WHERE
  c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND o_orderdate >= DATE '1994-03-01'
  AND o_orderdate < DATE '1994-03-01' + INTERVAL '3 month'
  AND l_returnflag = 'R'
  AND c_nationkey = n_nationkey
GROUP BY
  c_custkey,
  c_name,
  c_acctbal,
  c_phone,
  n_name,
  c_address,
  c_comment
ORDER BY
  revenue DESC
LIMIT 20
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=c_custkey]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=c_name]
  | +-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]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=c_acctbal]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=n_name]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=c_address]
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=c_phone]
  | +-SelectListItem
  |   +-AttributeReference[attribute_name=c_comment]
  +-where_clause=And
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=c_custkey]
  | | +-right_operand=AttributeReference[attribute_name=o_custkey]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
  | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
  | +-GreaterOrEqual
  | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=1994-03-01,explicit_type=Datetime]
  | +-Less
  | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
  | | +-right_operand=Add
  | |   +-left_operand=Literal
  | |   | +-StringLiteral[value=1994-03-01,explicit_type=Datetime]
  | |   +-right_operand=Literal
  | |     +-StringLiteral[value=3 month,explicit_type=YearMonthInterval]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=l_returnflag]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=R]
  | +-Equal
  |   +-left_operand=AttributeReference[attribute_name=c_nationkey]
  |   +-right_operand=AttributeReference[attribute_name=n_nationkey]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=c_custkey]
  | +-AttributeReference[attribute_name=c_name]
  | +-AttributeReference[attribute_name=c_acctbal]
  | +-AttributeReference[attribute_name=c_phone]
  | +-AttributeReference[attribute_name=n_name]
  | +-AttributeReference[attribute_name=c_address]
  | +-AttributeReference[attribute_name=c_comment]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=false,nulls_first=true]
  |   +-AttributeReference[attribute_name=revenue]
  +-limit=LIMIT
  | +-NumericLiteral[numeric_string=20,float_like=false]
  +-from_clause=
    +-TableReference[table=customer]
    +-TableReference[table=orders]
    +-TableReference[table=lineitem]
    +-TableReference[table=nation]
==

# Query 11
SELECT
  ps_partkey,
  SUM(ps_supplycost * ps_availqty) AS value
FROM
  partsupp,
  supplier,
  nation
WHERE
  ps_suppkey = s_suppkey
  AND s_nationkey = n_nationkey
  AND n_name = 'INDONESIA'
GROUP BY
  ps_partkey HAVING
    SUM(ps_supplycost * ps_availqty) > (
      SELECT
        SUM(ps_supplycost * ps_availqty) * 0.0000010000
      FROM
        partsupp,
        supplier,
        nation
      WHERE
        ps_suppkey = s_suppkey
        AND s_nationkey = n_nationkey
        AND n_name = 'INDONESIA'
    )
ORDER BY
  value DESC
--
ERROR: syntax error (15 : 7)
      SELECT
      ^
==

# Query 12
SELECT
  l_shipmode,
  SUM(CASE
    WHEN o_orderpriority = '1-URGENT'
      OR o_orderpriority = '2-HIGH'
      THEN 1
    ELSE 0
  END) AS high_line_count,
  SUM(CASE
    WHEN o_orderpriority <> '1-URGENT'
      AND o_orderpriority <> '2-HIGH'
      THEN 1
    ELSE 0
  END) AS low_line_count
FROM
  orders,
  lineitem
WHERE
  o_orderkey = l_orderkey
  AND l_shipmode IN ('REG AIR', 'RAIL')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= DATE '1997-01-01'
  AND l_receiptdate < DATE '1997-01-01' + INTERVAL '1 year'
GROUP BY
  l_shipmode
ORDER BY
  l_shipmode
--
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
SELECT
  c_count,
  COUNT(*) AS custdist
FROM
  (
    SELECT
      c_custkey,
      COUNT(o_orderkey)
    FROM
      customer LEFT OUTER JOIN orders ON
        c_custkey = o_custkey
        AND o_comment NOT LIKE '%special%requests%'
    GROUP BY
      c_custkey
  ) AS  c_orders (c_custkey, c_count)
GROUP BY
  c_count
ORDER BY
  custdist desc,
  c_count DESC
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=c_count]
  | +-SelectListItem[alias=custdist]
  |   +-FunctionCall[name=COUNT,is_star=true]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=c_count]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=false,nulls_first=true]
  | | +-AttributeReference[attribute_name=custdist]
  | +-OrderByItem[is_asc=false,nulls_first=true]
  |   +-AttributeReference[attribute_name=c_count]
  +-from_clause=
    +-SubqueryTable
      +-table_signature=TableSignature[table_alias=c_orders,
      | columns=(c_custkey, c_count)]
      +-SubqueryExpression
        +-Select
          +-select_clause=SelectList
          | +-SelectListItem
          | | +-AttributeReference[attribute_name=c_custkey]
          | +-SelectListItem
          |   +-FunctionCall[name=COUNT]
          |     +-AttributeReference[attribute_name=o_orderkey]
          +-group_by=GroupBy
          | +-AttributeReference[attribute_name=c_custkey]
          +-from_clause=
            +-JoinedTable[join_type=LeftOuterJoin]
              +-left_table=TableReference[table=customer]
              +-right_table=TableReference[table=orders]
              +-join_predicate=And
                +-Equal
                | +-left_operand=AttributeReference[attribute_name=c_custkey]
                | +-right_operand=AttributeReference[attribute_name=o_custkey]
                +-NotLike
                  +-left_operand=AttributeReference[attribute_name=o_comment]
                  +-right_operand=Literal
                    +-StringLiteral[value=%special%requests%]
==

# Query 14
SELECT
  100.00 * SUM(CASE
    WHEN p_type LIKE 'PROMO%'
      THEN l_extendedprice * (1 - l_discount)
    ELSE 0
  END) / SUM(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
  lineitem,
  part
WHERE
  l_partkey = p_partkey
  AND l_shipdate >= DATE '1994-11-01'
  AND l_shipdate < DATE '1994-11-01' + INTERVAL '1 month'
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem[alias=promo_revenue]
  |   +-Divide
  |     +-left_operand=Multiply
  |     | +-left_operand=Literal
  |     | | +-NumericLiteral[numeric_string=100.00,float_like=true]
  |     | +-right_operand=FunctionCall[name=SUM]
  |     |   +-SearchedCaseExpression
  |     |     +-else_result_expression=Literal
  |     |     | +-NumericLiteral[numeric_string=0,float_like=false]
  |     |     +-when_clauses=
  |     |       +-SearchedWhenClause
  |     |         +-condition_predicate=Like
  |     |         | +-left_operand=AttributeReference[attribute_name=p_type]
  |     |         | +-right_operand=Literal
  |     |         |   +-StringLiteral[value=PROMO%]
  |     |         +-result_expression=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]
  |     +-right_operand=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
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=l_partkey]
  | | +-right_operand=AttributeReference[attribute_name=p_partkey]
  | +-GreaterOrEqual
  | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=1994-11-01,explicit_type=Datetime]
  | +-Less
  |   +-left_operand=AttributeReference[attribute_name=l_shipdate]
  |   +-right_operand=Add
  |     +-left_operand=Literal
  |     | +-StringLiteral[value=1994-11-01,explicit_type=Datetime]
  |     +-right_operand=Literal
  |       +-StringLiteral[value=1 month,explicit_type=YearMonthInterval]
  +-from_clause=
    +-TableReference[table=lineitem]
    +-TableReference[table=part]
==

# Query 15
WITH revenue (supplier_no, total_revenue) AS (
SELECT
  l_suppkey,
  sum(l_extendedprice * (1-l_discount))
FROM
  lineitem
  WHERE
  l_shipdate >= DATE '1996-11-01'
  AND l_shipdate < DATE '1996-11-01' + INTERVAL '3 month'
GROUP BY
  l_suppkey
) SELECT
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
FROM
  supplier,
  revenue
WHERE
  s_suppkey = supplier_no AND total_revenue = (
SELECT
  MAX(total_revenue)
FROM
  revenue
)
ORDER BY
  s_suppkey
--
ERROR: syntax error (23 : 1)
SELECT
^
==

# Query 16
SELECT
  p_brand,
  p_type,
  p_size,
  COUNT(distinct ps_suppkey) AS supplier_cnt
FROM
  partsupp,
  part
WHERE
  p_partkey = ps_partkey
  AND p_brand <> 'Brand#22'
  AND p_type NOT LIKE 'ECONOMY BURNISHED%'
  AND p_size IN (32, 42, 9, 18, 50, 30, 12, 21)
  AND ps_suppkey NOT IN (
    SELECT
      s_suppkey
    FROM
      supplier
    WHERE
      s_comment LIKE '%Customer%Complaints%'
  )
GROUP BY
  p_brand,
  p_type,
  p_size
ORDER BY
  supplier_cnt DESC,
  p_brand,
  p_type,
  p_size
--
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
SELECT
  SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
  lineitem,
  part
WHERE
  p_partkey = l_partkey
  AND p_brand = 'Brand#24'
  AND p_container = 'JUMBO BOX'
  AND l_quantity < (
    SELECT
      0.2 * AVG(l_quantity)
    FROM
      lineitem
    WHERE
      l_partkey = p_partkey
  )
--
ERROR: syntax error (11 : 5)
    SELECT
    ^
==

# Query 18
SELECT
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice,
  sum(l_quantity)
FROM
  customer,
  orders,
  lineitem
WHERE
  o_orderkey IN (
    SELECT
      l_orderkey
    FROM
      lineitem
    GROUP BY
      l_orderkey HAVING
        SUM(l_quantity) > 314
  )
  AND c_custkey = o_custkey
  AND o_orderkey = l_orderkey
GROUP BY
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice
ORDER BY
  o_totalprice desc,
  o_orderdate
LIMIT 100
--
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
SELECT
  SUM(l_extendedprice* (1 - l_discount)) AS revenue
FROM
  lineitem,
  part
WHERE
  (
    p_partkey = l_partkey
    AND p_brand = 'Brand#45'
    AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    AND l_quantity >= 2 AND l_quantity <= 2 + 10
    AND p_size BETWEEN 1 AND 5
    AND l_shipmode IN ('AIR', 'AIR REG')
    AND l_shipinstruct = 'DELIVER IN PERSON'
  )
  OR
  (
    p_partkey = l_partkey
    AND p_brand = 'Brand#12'
    AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    AND l_quantity >= 13 AND l_quantity <= 13 + 10
    AND p_size BETWEEN 1 AND 10
    AND l_shipmode IN ('AIR', 'AIR REG')
    AND l_shipinstruct = 'DELIVER IN PERSON'
  )
  OR
  (
    p_partkey = l_partkey
    AND p_brand = 'Brand#53'
    AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    AND l_quantity >= 24 AND l_quantity <= 24 + 10
    AND p_size BETWEEN 1 AND 15
    AND l_shipmode IN ('AIR', 'AIR REG')
    AND l_shipinstruct = 'DELIVER IN PERSON'
  )
--
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
SELECT
  s_name,
  s_address
FROM
  supplier,
  nation
WHERE
  s_suppkey IN (
    SELECT
      ps_suppkey
    FROM
      partsupp
    WHERE
      ps_partkey IN (
        SELECT
          p_partkey
        FROM
          part
        WHERE
          p_name LIKE 'sandy%'
      )
      AND ps_availqty > (
        SELECT
          0.5 * SUM(l_quantity)
        FROM
          lineitem
        WHERE
          l_partkey = ps_partkey
          AND l_suppkey = ps_suppkey
          AND l_shipdate >= DATE '1993-01-01'
          AND l_shipdate < DATE '1993-01-01' + INTERVAL '1 year'
      )
  )
  AND s_nationkey = n_nationkey
  AND n_name = 'GERMANY'
ORDER BY
  s_name
--
ERROR: syntax error (23 : 9)
        SELECT
        ^
==

# Query 21
SELECT
  s_name,
  count(*) AS numwait
FROM
  supplier,
  lineitem l1,
  orders,
  nation
WHERE
  s_suppkey = l1.l_suppkey
  AND o_orderkey = l1.l_orderkey
  AND o_orderstatus = 'F'
  AND l1.l_receiptdate > l1.l_commitdate
  AND EXISTS (
    SELECT
      *
    FROM
      lineitem l2
    WHERE
      l2.l_orderkey = l1.l_orderkey
      AND l2.l_suppkey <> l1.l_suppkey
  )
  AND NOT EXISTS (
    SELECT
      *
    FROM
      lineitem l3
    WHERE
      l3.l_orderkey = l1.l_orderkey
      AND l3.l_suppkey <> l1.l_suppkey
      AND l3.l_receiptdate > l3.l_commitdate
  )
  AND s_nationkey = n_nationkey
  AND n_name = 'CANADA'
GROUP BY
  s_name
ORDER BY
  numwait desc,
  s_name
LIMIT 100
--
SelectStatement
+-select_query=Select
  +-select_clause=SelectList
  | +-SelectListItem
  | | +-AttributeReference[attribute_name=s_name]
  | +-SelectListItem[alias=numwait]
  |   +-FunctionCall[name=count,is_star=true]
  +-where_clause=And
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
  | | +-right_operand=AttributeReference[attribute_name=l_suppkey,
  | |   relation_name=l1]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=o_orderkey]
  | | +-right_operand=AttributeReference[attribute_name=l_orderkey,
  | |   relation_name=l1]
  | +-Equal
  | | +-left_operand=AttributeReference[attribute_name=o_orderstatus]
  | | +-right_operand=Literal
  | |   +-StringLiteral[value=F]
  | +-Greater
  | | +-left_operand=AttributeReference[attribute_name=l_receiptdate,
  | | | relation_name=l1]
  | | +-right_operand=AttributeReference[attribute_name=l_commitdate,
  | |   relation_name=l1]
  | +-Exists
  | | +-subquery=SubqueryExpression
  | |   +-Select
  | |     +-select_clause=SelectStar
  | |     +-where_clause=And
  | |     | +-Equal
  | |     | | +-left_operand=AttributeReference[attribute_name=l_orderkey,
  | |     | | | relation_name=l2]
  | |     | | +-right_operand=AttributeReference[attribute_name=l_orderkey,
  | |     | |   relation_name=l1]
  | |     | +-NotEqual
  | |     |   +-left_operand=AttributeReference[attribute_name=l_suppkey,
  | |     |   | relation_name=l2]
  | |     |   +-right_operand=AttributeReference[attribute_name=l_suppkey,
  | |     |     relation_name=l1]
  | |     +-from_clause=
  | |       +-TableReference[table=lineitem]
  | |         +-table_signature=TableSignature[table_alias=l2]
  | +-Not
  | | +-Exists
  | |   +-subquery=SubqueryExpression
  | |     +-Select
  | |       +-select_clause=SelectStar
  | |       +-where_clause=And
  | |       | +-Equal
  | |       | | +-left_operand=AttributeReference[attribute_name=l_orderkey,
  | |       | | | relation_name=l3]
  | |       | | +-right_operand=AttributeReference[attribute_name=l_orderkey,
  | |       | |   relation_name=l1]
  | |       | +-NotEqual
  | |       | | +-left_operand=AttributeReference[attribute_name=l_suppkey,
  | |       | | | relation_name=l3]
  | |       | | +-right_operand=AttributeReference[attribute_name=l_suppkey,
  | |       | |   relation_name=l1]
  | |       | +-Greater
  | |       |   +-left_operand=AttributeReference[attribute_name=l_receiptdate,
  | |       |   | relation_name=l3]
  | |       |   +-right_operand=AttributeReference[attribute_name=l_commitdate,
  | |       |     relation_name=l3]
  | |       +-from_clause=
  | |         +-TableReference[table=lineitem]
  | |           +-table_signature=TableSignature[table_alias=l3]
  | +-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=CANADA]
  +-group_by=GroupBy
  | +-AttributeReference[attribute_name=s_name]
  +-order_by=OrderBy
  | +-OrderByItem[is_asc=false,nulls_first=true]
  | | +-AttributeReference[attribute_name=numwait]
  | +-OrderByItem[is_asc=true,nulls_first=false]
  |   +-AttributeReference[attribute_name=s_name]
  +-limit=LIMIT
  | +-NumericLiteral[numeric_string=100,float_like=false]
  +-from_clause=
    +-TableReference[table=supplier]
    +-TableReference[table=lineitem]
    | +-table_signature=TableSignature[table_alias=l1]
    +-TableReference[table=orders]
    +-TableReference[table=nation]
==

# Query 22
SELECT
  cntrycode,
  COUNT(*) AS numcust,
  SUM(c_acctbal) AS totacctbal
FROM
  (
    SELECT
      SUBSTR(c_phone, 1, 2) AS cntrycode,
      c_acctbal
    FROM
      customer
    WHERE
      SUBSTR(c_phone, 1, 2) IN
        ('27', '44', '34', '25', '30', '33', '23')
      AND c_acctbal > (
        SELECT
          AVG(c_acctbal)
        FROM
          customer
        WHERE
          c_acctbal > 0.00
          AND SUBSTR(c_phone, 1, 2) IN
            ('27', '44', '34', '25', '30', '33', '23')
      )
      AND NOT EXISTS (
        SELECT *
        FROM
          orders
        WHERE
          o_custkey = c_custkey
      )
  ) AS custsale
GROUP BY
  cntrycode
ORDER BY
  cntrycode
--
ERROR: syntax error (16 : 9)
        SELECT
        ^