You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@quickstep.apache.org by sp...@apache.org on 2016/12/11 17:45:44 UTC
[21/51] [abbrv] [partial] incubator-quickstep git commit: remove c++
files
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/156290a4/parser/tests/TPCH.test
----------------------------------------------------------------------
diff --git a/parser/tests/TPCH.test b/parser/tests/TPCH.test
deleted file mode 100644
index f0ffaa9..0000000
--- a/parser/tests/TPCH.test
+++ /dev/null
@@ -1,2301 +0,0 @@
-# Licensed to the Apache Software Foundation (ASF) under one
-# or more contributor license agreements. See the NOTICE file
-# distributed with this work for additional information
-# regarding copyright ownership. The ASF licenses this file
-# to you 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=Date]
- | +-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
---
-SelectStatement
-+-select_query=Select
- +-select_clause=SelectList
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=s_acctbal]
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=s_name]
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=n_name]
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=p_partkey]
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=p_mfgr]
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=s_address]
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=s_phone]
- | +-SelectListItem
- | +-AttributeReference[attribute_name=s_comment]
- +-where_clause=And
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=p_partkey]
- | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
- | | +-right_operand=AttributeReference[attribute_name=ps_suppkey]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=p_size]
- | | +-right_operand=Literal
- | | +-NumericLiteral[numeric_string=48,float_like=false]
- | +-Like
- | | +-left_operand=AttributeReference[attribute_name=p_type]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=%NICKEL]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
- | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=n_regionkey]
- | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=r_name]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=ASIA]
- | +-Equal
- | +-left_operand=AttributeReference[attribute_name=ps_supplycost]
- | +-right_operand=SubqueryExpression
- | +-Select
- | +-select_clause=SelectList
- | | +-SelectListItem
- | | +-FunctionCall[name=MIN]
- | | +-AttributeReference[attribute_name=ps_supplycost]
- | +-where_clause=And
- | | +-Equal
- | | | +-left_operand=AttributeReference[attribute_name=p_partkey]
- | | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
- | | +-Equal
- | | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
- | | | +-right_operand=AttributeReference[attribute_name=ps_suppkey]
- | | +-Equal
- | | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
- | | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
- | | +-Equal
- | | | +-left_operand=AttributeReference[attribute_name=n_regionkey]
- | | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
- | | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=r_name]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=ASIA]
- | +-from_clause=
- | +-TableReference[table=partsupp]
- | +-TableReference[table=supplier]
- | +-TableReference[table=nation]
- | +-TableReference[table=region]
- +-order_by=OrderBy
- | +-OrderByItem[is_asc=false,nulls_first=true]
- | | +-AttributeReference[attribute_name=s_acctbal]
- | +-OrderByItem[is_asc=true,nulls_first=false]
- | | +-AttributeReference[attribute_name=n_name]
- | +-OrderByItem[is_asc=true,nulls_first=false]
- | | +-AttributeReference[attribute_name=s_name]
- | +-OrderByItem[is_asc=true,nulls_first=false]
- | +-AttributeReference[attribute_name=p_partkey]
- +-limit=LIMIT
- | +-NumericLiteral[numeric_string=100,float_like=false]
- +-from_clause=
- +-TableReference[table=part]
- +-TableReference[table=supplier]
- +-TableReference[table=partsupp]
- +-TableReference[table=nation]
- +-TableReference[table=region]
-==
-
-# Query 3
-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=Date]
- | +-Greater
- | +-left_operand=AttributeReference[attribute_name=l_shipdate]
- | +-right_operand=Literal
- | +-StringLiteral[value=1995-03-17,explicit_type=Date]
- +-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=Date]
- | +-Less
- | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
- | | +-right_operand=Add
- | | +-left_operand=Literal
- | | | +-StringLiteral[value=1995-08-01,explicit_type=Date]
- | | +-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=Date]
- | +-Less
- | +-left_operand=AttributeReference[attribute_name=o_orderdate]
- | +-right_operand=Add
- | +-left_operand=Literal
- | | +-StringLiteral[value=1997-01-01,explicit_type=Date]
- | +-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=Date]
- | +-Less
- | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
- | | +-right_operand=Add
- | | +-left_operand=Literal
- | | | +-StringLiteral[value=1997-01-01,explicit_type=Date]
- | | +-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=Date]
- | +-upper_bound_operand=Literal
- | +-StringLiteral[value=1996-12-31,explicit_type=Date]
- +-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=Date]
- | | +-upper_bound_operand=Literal
- | | +-StringLiteral[value=1996-12-31,explicit_type=Date]
- | +-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=Date]
- | +-Less
- | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
- | | +-right_operand=Add
- | | +-left_operand=Literal
- | | | +-StringLiteral[value=1994-03-01,explicit_type=Date]
- | | +-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
---
-SelectStatement
-+-select_query=Select
- +-select_clause=SelectList
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=ps_partkey]
- | +-SelectListItem[alias=value]
- | +-FunctionCall[name=SUM]
- | +-Multiply
- | +-left_operand=AttributeReference[attribute_name=ps_supplycost]
- | +-right_operand=AttributeReference[attribute_name=ps_availqty]
- +-where_clause=And
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=ps_suppkey]
- | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
- | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
- | +-Equal
- | +-left_operand=AttributeReference[attribute_name=n_name]
- | +-right_operand=Literal
- | +-StringLiteral[value=INDONESIA]
- +-group_by=GroupBy
- | +-AttributeReference[attribute_name=ps_partkey]
- +-having=HAVING
- | +-Greater
- | +-left_operand=FunctionCall[name=SUM]
- | | +-Multiply
- | | +-left_operand=AttributeReference[attribute_name=ps_supplycost]
- | | +-right_operand=AttributeReference[attribute_name=ps_availqty]
- | +-right_operand=SubqueryExpression
- | +-Select
- | +-select_clause=SelectList
- | | +-SelectListItem
- | | +-Multiply
- | | +-left_operand=FunctionCall[name=SUM]
- | | | +-Multiply
- | | | +-left_operand=AttributeReference[
- | | | | attribute_name=ps_supplycost]
- | | | +-right_operand=AttributeReference[
- | | | attribute_name=ps_availqty]
- | | +-right_operand=Literal
- | | +-NumericLiteral[numeric_string=0.0000010000,float_like=true]
- | +-where_clause=And
- | | +-Equal
- | | | +-left_operand=AttributeReference[attribute_name=ps_suppkey]
- | | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
- | | +-Equal
- | | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
- | | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
- | | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=n_name]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=INDONESIA]
- | +-from_clause=
- | +-TableReference[table=partsupp]
- | +-TableReference[table=supplier]
- | +-TableReference[table=nation]
- +-order_by=OrderBy
- | +-OrderByItem[is_asc=false,nulls_first=true]
- | +-AttributeReference[attribute_name=value]
- +-from_clause=
- +-TableReference[table=partsupp]
- +-TableReference[table=supplier]
- +-TableReference[table=nation]
-==
-
-# Query 12
-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=Date]
- | +-Less
- | +-left_operand=AttributeReference[attribute_name=l_receiptdate]
- | +-right_operand=Add
- | +-left_operand=Literal
- | | +-StringLiteral[value=1997-01-01,explicit_type=Date]
- | +-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=Date]
- | +-Less
- | +-left_operand=AttributeReference[attribute_name=l_shipdate]
- | +-right_operand=Add
- | +-left_operand=Literal
- | | +-StringLiteral[value=1994-11-01,explicit_type=Date]
- | +-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
---
-SelectStatement
-+-select_query=Select
-| +-select_clause=SelectList
-| | +-SelectListItem
-| | | +-AttributeReference[attribute_name=s_suppkey]
-| | +-SelectListItem
-| | | +-AttributeReference[attribute_name=s_name]
-| | +-SelectListItem
-| | | +-AttributeReference[attribute_name=s_address]
-| | +-SelectListItem
-| | | +-AttributeReference[attribute_name=s_phone]
-| | +-SelectListItem
-| | +-AttributeReference[attribute_name=total_revenue]
-| +-where_clause=And
-| | +-Equal
-| | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
-| | | +-right_operand=AttributeReference[attribute_name=supplier_no]
-| | +-Equal
-| | +-left_operand=AttributeReference[attribute_name=total_revenue]
-| | +-right_operand=SubqueryExpression
-| | +-Select
-| | +-select_clause=SelectList
-| | | +-SelectListItem
-| | | +-FunctionCall[name=MAX]
-| | | +-AttributeReference[attribute_name=total_revenue]
-| | +-from_clause=
-| | +-TableReference[table=revenue]
-| +-order_by=OrderBy
-| | +-OrderByItem[is_asc=true,nulls_first=false]
-| | +-AttributeReference[attribute_name=s_suppkey]
-| +-from_clause=
-| +-TableReference[table=supplier]
-| +-TableReference[table=revenue]
-+-with_clause=
- +-SubqueryTable
- +-table_signature=TableSignature[table_alias=revenue,
- | columns=(supplier_no, total_revenue)]
- +-SubqueryExpression
- +-Select
- +-select_clause=SelectList
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=l_suppkey]
- | +-SelectListItem
- | +-FunctionCall[name=sum]
- | +-Multiply
- | +-left_operand=AttributeReference[attribute_name=l_extendedprice]
- | +-right_operand=Subtract
- | +-left_operand=Literal
- | | +-NumericLiteral[numeric_string=1,float_like=false]
- | +-right_operand=AttributeReference[attribute_name=l_discount]
- +-where_clause=And
- | +-GreaterOrEqual
- | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=1996-11-01,explicit_type=Date]
- | +-Less
- | +-left_operand=AttributeReference[attribute_name=l_shipdate]
- | +-right_operand=Add
- | +-left_operand=Literal
- | | +-StringLiteral[value=1996-11-01,explicit_type=Date]
- | +-right_operand=Literal
- | +-StringLiteral[value=3 month,explicit_type=YearMonthInterval]
- +-group_by=GroupBy
- | +-AttributeReference[attribute_name=l_suppkey]
- +-from_clause=
- +-TableReference[table=lineitem]
-==
-
-# Query 16
-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
- )
---
-SelectStatement
-+-select_query=Select
- +-select_clause=SelectList
- | +-SelectListItem[alias=avg_yearly]
- | +-Divide
- | +-left_operand=FunctionCall[name=SUM]
- | | +-AttributeReference[attribute_name=l_extendedprice]
- | +-right_operand=Literal
- | +-NumericLiteral[numeric_string=7.0,float_like=true]
- +-where_clause=And
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=p_partkey]
- | | +-right_operand=AttributeReference[attribute_name=l_partkey]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=p_brand]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=Brand#24]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=p_container]
- | | +-right_operand=Literal
- | | +-StringLiteral[value=JUMBO BOX]
- | +-Less
- | +-left_operand=AttributeReference[attribute_name=l_quantity]
- | +-right_operand=SubqueryExpression
- | +-Select
- | +-select_clause=SelectList
- | | +-SelectListItem
- | | +-Multiply
- | | +-left_operand=Literal
- | | | +-NumericLiteral[numeric_string=0.2,float_like=true]
- | | +-right_operand=FunctionCall[name=AVG]
- | | +-AttributeReference[attribute_name=l_quantity]
- | +-where_clause=Equal
- | | +-left_operand=AttributeReference[attribute_name=l_partkey]
- | | +-right_operand=AttributeReference[attribute_name=p_partkey]
- | +-from_clause=
- | +-TableReference[table=lineitem]
- +-from_clause=
- +-TableReference[table=lineitem]
- +-TableReference[table=part]
-==
-
-# Query 18
-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
---
-SelectStatement
-+-select_query=Select
- +-select_clause=SelectList
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=s_name]
- | +-SelectListItem
- | +-AttributeReference[attribute_name=s_address]
- +-where_clause=And
- | +-InTableQuery
- | | +-test_expression=AttributeReference[attribute_name=s_suppkey]
- | | +-table_query=SubqueryExpression
- | | +-Select
- | | +-select_clause=SelectList
- | | | +-SelectListItem
- | | | +-AttributeReference[attribute_name=ps_suppkey]
- | | +-where_clause=And
- | | | +-InTableQuery
- | | | | +-test_expression=AttributeReference[attribute_name=ps_partkey]
- | | | | +-table_query=SubqueryExpression
- | | | | +-Select
- | | | | +-select_clause=SelectList
- | | | | | +-SelectListItem
- | | | | | +-AttributeReference[attribute_name=p_partkey]
- | | | | +-where_clause=Like
- | | | | | +-left_operand=AttributeReference[attribute_name=p_name]
- | | | | | +-right_operand=Literal
- | | | | | +-StringLiteral[value=sandy%]
- | | | | +-from_clause=
- | | | | +-TableReference[table=part]
- | | | +-Greater
- | | | +-left_operand=AttributeReference[attribute_name=ps_availqty]
- | | | +-right_operand=SubqueryExpression
- | | | +-Select
- | | | +-select_clause=SelectList
- | | | | +-SelectListItem
- | | | | +-Multiply
- | | | | +-left_operand=Literal
- | | | | | +-NumericLiteral[numeric_string=0.5,float_like=true]
- | | | | +-right_operand=FunctionCall[name=SUM]
- | | | | +-AttributeReference[attribute_name=l_quantity]
- | | | +-where_clause=And
- | | | | +-Equal
- | | | | | +-left_operand=AttributeReference[attribute_name=l_partkey]
- | | | | | +-right_operand=AttributeReference[
- | | | | | attribute_name=ps_partkey]
- | | | | +-Equal
- | | | | | +-left_operand=AttributeReference[attribute_name=l_suppkey]
- | | | | | +-right_operand=AttributeReference[
- | | | | | attribute_name=ps_suppkey]
- | | | | +-GreaterOrEqual
- | | | | | +-left_operand=AttributeReference[
- | | | | | | attribute_name=l_shipdate]
- | | | | | +-right_operand=Literal
- | | | | | +-StringLiteral[value=1993-01-01,explicit_type=Date]
- | | | | +-Less
- | | | | +-left_operand=AttributeReference[
- | | | | | attribute_name=l_shipdate]
- | | | | +-right_operand=Add
- | | | | +-left_operand=Literal
- | | | | | +-StringLiteral[value=1993-01-01,explicit_type=Date]
- | | | | +-right_operand=Literal
- | | | | +-StringLiteral[value=1 year,
- | | | | explicit_type=YearMonthInterval]
- | | | +-from_clause=
- | | | +-TableReference[table=lineitem]
- | | +-from_clause=
- | | +-TableReference[table=partsupp]
- | +-Equal
- | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
- | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
- | +-Equal
- | +-left_operand=AttributeReference[attribute_name=n_name]
- | +-right_operand=Literal
- | +-StringLiteral[value=GERMANY]
- +-order_by=OrderBy
- | +-OrderByItem[is_asc=true,nulls_first=false]
- | +-AttributeReference[attribute_name=s_name]
- +-from_clause=
- +-TableReference[table=supplier]
- +-TableReference[table=nation]
-==
-
-# Query 21
-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
---
-SelectStatement
-+-select_query=Select
- +-select_clause=SelectList
- | +-SelectListItem
- | | +-AttributeReference[attribute_name=cntrycode]
- | +-SelectListItem[alias=numcust]
- | | +-FunctionCall[name=COUNT,is_star=true]
- | +-SelectListItem[alias=totacctbal]
- | +-FunctionCall[name=SUM]
- | +-AttributeReference[attribute_name=c_acctbal]
- +-group_by=GroupBy
- | +-AttributeReference[attribute_name=cntrycode]
- +-order_by=OrderBy
- | +-OrderByItem[is_asc=true,nulls_first=false]
- | +-AttributeReference[attribute_name=cntrycode]
- +-from_clause=
- +-SubqueryTable
- +-table_signature=TableSignature[table_alias=custsale]
- +-SubqueryExpression
- +-Select
- +-select_clause=SelectList
- | +-SelectListItem[alias=cntrycode]
- | | +-FunctionCall[name=SUBSTR]
- | | +-AttributeReference[attribute_name=c_phone]
- | | +-Literal
- | | | +-NumericLiteral[numeric_string=1,float_like=false]
- | | +-Literal
- | | +-NumericLiteral[numeric_string=2,float_like=false]
- | +-SelectListItem
- | +-AttributeReference[attribute_name=c_acctbal]
- +-where_clause=And
- | +-InValueList
- | | +-test_expression=FunctionCall[name=SUBSTR]
- | | | +-AttributeReference[attribute_name=c_phone]
- | | | +-Literal
- | | | | +-NumericLiteral[numeric_string=1,float_like=false]
- | | | +-Literal
- | | | +-NumericLiteral[numeric_string=2,float_like=false]
- | | +-value_list=
- | | +-Literal
- | | | +-StringLiteral[value=27]
- | | +-Literal
- | | | +-StringLiteral[value=44]
- | | +-Literal
- | | | +-StringLiteral[value=34]
- | | +-Literal
- | | | +-StringLiteral[value=25]
- | | +-Literal
- | | | +-StringLiteral[value=30]
- | | +-Literal
- | | | +-StringLiteral[value=33]
- | | +-Literal
- | | +-StringLiteral[value=23]
- | +-Greater
- | | +-left_operand=AttributeReference[attribute_name=c_acctbal]
- | | +-right_operand=SubqueryExpression
- | | +-Select
- | | +-select_clause=SelectList
- | | | +-SelectListItem
- | | | +-FunctionCall[name=AVG]
- | | | +-AttributeReference[attribute_name=c_acctbal]
- | | +-where_clause=And
- | | | +-Greater
- | | | | +-left_operand=AttributeReference[attribute_name=c_acctbal]
- | | | | +-right_operand=Literal
- | | | | +-NumericLiteral[numeric_string=0.00,float_like=true]
- | | | +-InValueList
- | | | +-test_expression=FunctionCall[name=SUBSTR]
- | | | | +-AttributeReference[attribute_name=c_phone]
- | | | | +-Literal
- | | | | | +-NumericLiteral[numeric_string=1,float_like=false]
- | | | | +-Literal
- | | | | +-NumericLiteral[numeric_string=2,float_like=false]
- | | | +-value_list=
- | | | +-Literal
- | | | | +-StringLiteral[value=27]
- | | | +-Literal
- | | | | +-StringLiteral[value=44]
- | | | +-Literal
- | | | | +-StringLiteral[value=34]
- | | | +-Literal
- | | | | +-StringLiteral[value=25]
- | | | +-Literal
- | | | | +-StringLiteral[value=30]
- | | | +-Literal
- | | | | +-StringLiteral[value=33]
- | | | +-Literal
- | | | +-StringLiteral[value=23]
- | | +-from_clause=
- | | +-TableReference[table=customer]
- | +-Not
- | +-Exists
- | +-subquery=SubqueryExpression
- | +-Select
- | +-select_clause=SelectStar
- | +-where_clause=Equal
- | | +-left_operand=AttributeReference[attribute_name=o_custkey]
- | | +-right_operand=AttributeReference[
- | | attribute_name=c_custkey]
- | +-from_clause=
- | +-TableReference[table=orders]
- +-from_clause=
- +-TableReference[table=customer]
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/156290a4/parser/tests/Update.test
----------------------------------------------------------------------
diff --git a/parser/tests/Update.test b/parser/tests/Update.test
deleted file mode 100644
index 4ae0fcb..0000000
--- a/parser/tests/Update.test
+++ /dev/null
@@ -1,81 +0,0 @@
-# Licensed to the Apache Software Foundation (ASF) under one
-# or more contributor license agreements. See the NOTICE file
-# distributed with this work for additional information
-# regarding copyright ownership. The ASF licenses this file
-# to you 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.
-
-UPDATE TABLE test SET attr = 1
---
-ERROR: syntax error (1 : 8)
-UPDATE TABLE test SET attr = 1
- ^
-==
-
-UPDATE TABLE SET attr = 1
---
-ERROR: syntax error (1 : 8)
-UPDATE TABLE SET attr = 1
- ^
-==
-
-UPDATE test SET attr1 = 1, attr2=5, attr3 = 4 WHERE attr2 < 1 AND attr1 > 5 AND attr1+1<2*attr2
---
-UpdateStatement[relation_name=test]
-+-where_predicate=And
-| +-Less
-| | +-left_operand=AttributeReference[attribute_name=attr2]
-| | +-right_operand=Literal
-| | +-NumericLiteral[numeric_string=1,float_like=false]
-| +-Greater
-| | +-left_operand=AttributeReference[attribute_name=attr1]
-| | +-right_operand=Literal
-| | +-NumericLiteral[numeric_string=5,float_like=false]
-| +-Less
-| +-left_operand=Add
-| | +-left_operand=AttributeReference[attribute_name=attr1]
-| | +-right_operand=Literal
-| | +-NumericLiteral[numeric_string=1,float_like=false]
-| +-right_operand=Multiply
-| +-left_operand=Literal
-| | +-NumericLiteral[numeric_string=2,float_like=false]
-| +-right_operand=AttributeReference[attribute_name=attr2]
-+-assignment=
- +-AttributeAssignment[attribute_name=attr1]
- | +-value=Literal
- | +-NumericLiteral[numeric_string=1,float_like=false]
- +-AttributeAssignment[attribute_name=attr2]
- | +-value=Literal
- | +-NumericLiteral[numeric_string=5,float_like=false]
- +-AttributeAssignment[attribute_name=attr3]
- +-value=Literal
- +-NumericLiteral[numeric_string=4,float_like=false]
-==
-
-UPDATE test SET attr = 4+1
---
-UpdateStatement[relation_name=test]
-+-assignment=
- +-AttributeAssignment[attribute_name=attr]
- +-value=Add
- +-left_operand=Literal
- | +-NumericLiteral[numeric_string=4,float_like=false]
- +-right_operand=Literal
- +-NumericLiteral[numeric_string=1,float_like=false]
-==
-
-UPDATE test WHERE attr2=1
---
-ERROR: syntax error (1 : 13)
-UPDATE test WHERE attr2=1
- ^
http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/156290a4/query_execution/AdmitRequestMessage.hpp
----------------------------------------------------------------------
diff --git a/query_execution/AdmitRequestMessage.hpp b/query_execution/AdmitRequestMessage.hpp
deleted file mode 100644
index dd3d49b..0000000
--- a/query_execution/AdmitRequestMessage.hpp
+++ /dev/null
@@ -1,75 +0,0 @@
-/**
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you 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_EXECUTION_ADMIT_REQUEST_MESSAGE_HPP_
-#define QUICKSTEP_QUERY_EXECUTION_ADMIT_REQUEST_MESSAGE_HPP_
-
-#include <vector>
-
-#include "utility/Macros.hpp"
-
-namespace quickstep {
-
-class QueryHandle;
-
-/** \addtogroup QueryExecution
- * @{
- */
-
-/**
- * @brief A message requesting a query or queries to be admitted to the system.
- **/
-class AdmitRequestMessage {
- public:
- /**
- * @brief Constructor.
- *
- * @param query_handles The handles of the queries requesting to be admitted
- * to the system.
- **/
- explicit AdmitRequestMessage(const std::vector<QueryHandle*> &query_handles)
- : query_handles_(query_handles) {}
-
- /**
- * @brief Constructor for requesting single query admission.
- *
- * @param query_handle The handle of the query requesting to be admitted.
- **/
- explicit AdmitRequestMessage(QueryHandle *query_handle) {
- query_handles_.push_back(query_handle);
- }
-
- /**
- * @brief Get the query handles from this message.
- **/
- const std::vector<QueryHandle*>& getQueryHandles() const {
- return query_handles_;
- }
-
- private:
- std::vector<QueryHandle*> query_handles_;
-
- DISALLOW_COPY_AND_ASSIGN(AdmitRequestMessage);
-};
-
-/** @} */
-
-} // namespace quickstep
-
-#endif // QUICKSTEP_QUERY_EXECUTION_ADMIT_REQUEST_MESSAGE_HPP_