parser/tests/TPCH.test

#   Copyright 2011-2015 Quickstep Technologies LLC.
#   Copyright 2015 Pivotal Software, Inc.
#
#   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
--
ERROR: syntax error (10 : 5)
    SELECT
    ^
==

# 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
--
ERROR: syntax error (11 : 20)
      EXTRACT(year FROM l_shipdate) AS l_year,
                   ^
==

# 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
--
ERROR: syntax error (4 : 5)
    when nation = 'UNITED STATES' the...
    ^
==

# 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
--
ERROR: syntax error (9 : 20)
      EXTRACT(year FROM o_orderdate) AS o_year,
                   ^
==

# 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
--
ERROR: syntax error (4 : 5)
    when o_orderpriority = '1-URGEN...
    ^
==

# 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
--
ERROR: OUTER JOIN is not supported yet (10 : 16)
      customer LEFT OUTER JOIN orders ON
               ^
==

# 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'
--
ERROR: syntax error (3 : 5)
    when p_type LIKE 'PROMO%'
    ^
==

# 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
--
ERROR: syntax error (5 : 9)
  COUNT(distinct ps_suppkey) AS supplie...
        ^
==

# 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
--
ERROR: syntax error (13 : 14)
  o_orderkey in (
             ^
==

# 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'
  )
--
ERROR: syntax error (10 : 21)
    AND p_container IN ('SM CASE', 'SM BOX', 'SM PAC...
                    ^
==

# 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 (8 : 13)
  s_suppkey in (
            ^
==

# 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
--
ERROR: syntax error (15 : 5)
    SELECT
    ^
==

# 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 (13 : 29)
      SUBSTR(c_phone, 1, 2) in
                            ^