Return-Path: X-Original-To: apmail-asterixdb-commits-archive@minotaur.apache.org Delivered-To: apmail-asterixdb-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AFED319BAD for ; Mon, 28 Mar 2016 20:18:54 +0000 (UTC) Received: (qmail 86025 invoked by uid 500); 28 Mar 2016 20:18:54 -0000 Delivered-To: apmail-asterixdb-commits-archive@asterixdb.apache.org Received: (qmail 85936 invoked by uid 500); 28 Mar 2016 20:18:54 -0000 Mailing-List: contact commits-help@asterixdb.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@asterixdb.incubator.apache.org Delivered-To: mailing list commits@asterixdb.incubator.apache.org Received: (qmail 85919 invoked by uid 99); 28 Mar 2016 20:18:54 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Mar 2016 20:18:54 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id CE7CACDD7F for ; Mon, 28 Mar 2016 20:18:53 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -4.216 X-Spam-Level: X-Spam-Status: No, score=-4.216 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_DNSWL_HI=-5, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-0.996] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id teWbPoWl3Ho0 for ; Mon, 28 Mar 2016 20:18:44 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with SMTP id 4C2025FBAB for ; Mon, 28 Mar 2016 20:18:30 +0000 (UTC) Received: (qmail 82875 invoked by uid 99); 28 Mar 2016 20:18:29 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Mar 2016 20:18:29 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id EDE14E976E; Mon, 28 Mar 2016 20:18:28 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: buyingyi@apache.org To: commits@asterixdb.incubator.apache.org Date: Mon, 28 Mar 2016 20:18:57 -0000 Message-Id: In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [30/51] [partial] incubator-asterixdb git commit: ASTERIXDB-1226: implement SQL++ core group-by semantics and syntatic sugars. http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.2.update.sqlpp new file mode 100644 index 0000000..ed34b74 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted; + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.3.query.sqlpp new file mode 100644 index 0000000..286a593 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item/q10_returned_item.3.query.sqlpp @@ -0,0 +1,69 @@ +/* + * 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. + */ + +USE tpch; + + +SELECT c_custkey AS c_custkey, + c_name AS c_name, + sum(locn.l_extendedprice * (1 - locn.l_discount)) AS revenue, + c_acctbal AS c_acctbal, + n_name AS n_name, + c_address AS c_address, + c_phone AS c_phone, + c_comment AS c_comment +FROM ( + SELECT ocn.c_custkey AS c_custkey, + ocn.c_name AS c_name, + ocn.c_acctbal AS c_acctbal, + ocn.n_name AS n_name, + ocn.c_address AS c_address, + ocn.c_phone AS c_phone, + ocn.c_comment AS c_comment, + l.l_extendedprice AS l_extendedprice, + l.l_discount AS l_discount + FROM LineItem AS l, + ( + SELECT c.c_custkey AS c_custkey, + c.c_name AS c_name, + c.c_acctbal AS c_acctbal, + n.n_name AS n_name, + c.c_address AS c_address, + c.c_phone AS c_phone, + c.c_comment AS c_comment, + o.o_orderkey AS o_orderkey + from Orders as o, + Customer as c, + Nation as n + WHERE c.c_custkey = o.o_custkey AND o.o_orderdate >= '1993-10-01' + AND o.o_orderdate < '1994-01-01' AND c.c_nationkey = n.n_nationkey + ) AS ocn + WHERE l.l_orderkey = ocn.o_orderkey and l.l_returnflag = 'R' + ) AS locn +GROUP BY locn.c_custkey AS c_custkey, + locn.c_name AS c_name, + locn.c_acctbal AS c_acctbal, + locn.c_phone AS c_phone, + locn.n_name AS n_name, + locn.c_address AS c_address, + locn.c_comment AS c_comment +ORDER BY revenue DESC +LIMIT 20 +; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.1.ddl.sqlpp new file mode 100644 index 0000000..907be7b --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int64, + l_partkey : int64, + l_suppkey : int64, + l_linenumber : int64, + l_quantity : int64, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int64, + o_custkey : int64, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int64, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int64, + c_name : string, + c_address : string, + c_nationkey : int64, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int64, + s_name : string, + s_address : string, + s_nationkey : int64, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int64, + n_name : string, + n_regionkey : int64, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int64, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int64, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int64, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int64, + ps_suppkey : int64, + ps_availqty : int64, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp new file mode 100644 index 0000000..fd24025 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp @@ -0,0 +1,70 @@ +/* + * 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. + */ + + +USE tpch; + + +SELECT c_custkey AS c_custkey, + c_name AS c_name, + sum(locn.l_extendedprice * (1 - locn.l_discount)) AS revenue, + c_acctbal AS c_acctbal, + n_name AS n_name, + c_address AS c_address, + c_phone AS c_phone, + c_comment AS c_comment +FROM ( + SELECT ocn.c_custkey AS c_custkey, + ocn.c_name AS c_name, + ocn.c_acctbal AS c_acctbal, + ocn.n_name AS n_name, + ocn.c_address AS c_address, + ocn.c_phone AS c_phone, + ocn.c_comment AS c_comment, + l.l_extendedprice AS l_extendedprice, + l.l_discount AS l_discount + FROM LineItem AS l, + ( + SELECT c.c_custkey AS c_custkey, + c.c_name AS c_name, + c.c_acctbal AS c_acctbal, + n.n_name AS n_name, + c.c_address AS c_address, + c.c_phone AS c_phone, + c.c_comment AS c_comment, + o.o_orderkey AS o_orderkey + from Orders as o, + Customer as c, + Nation as n + WHERE c.c_custkey = o.o_custkey AND o.o_orderdate >= '1993-10-01' + AND o.o_orderdate < '1994-01-01' AND c.c_nationkey = n.n_nationkey + ) AS ocn + WHERE l.l_orderkey = ocn.o_orderkey and l.l_returnflag = 'R' + ) AS locn +GROUP BY locn.c_custkey AS c_custkey, + locn.c_name AS c_name, + locn.c_acctbal AS c_acctbal, + locn.c_phone AS c_phone, + locn.n_name AS n_name, + locn.c_address AS c_address, + locn.c_comment AS c_comment +ORDER BY revenue DESC +LIMIT 20 +; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.1.ddl.sqlpp new file mode 100644 index 0000000..907be7b --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int64, + l_partkey : int64, + l_suppkey : int64, + l_linenumber : int64, + l_quantity : int64, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int64, + o_custkey : int64, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int64, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int64, + c_name : string, + c_address : string, + c_nationkey : int64, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int64, + s_name : string, + s_address : string, + s_nationkey : int64, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int64, + n_name : string, + n_regionkey : int64, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int64, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int64, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int64, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int64, + ps_suppkey : int64, + ps_availqty : int64, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp new file mode 100644 index 0000000..0e84e68 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp @@ -0,0 +1,54 @@ +/* + * 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. + */ + +use tpch; + + +WITH sum as sum( + ( + SELECT ELEMENT ps.ps_supplycost * ps.ps_availqty + FROM Partsupp AS ps, + ( + SELECT s.s_suppkey s_suppkey + FROM Supplier as s, + Nation as n + WHERE s.s_nationkey = n.n_nationkey + ) AS sn + WHERE ps.ps_suppkey = sn.s_suppkey + ) +) + +SELECT t1.ps_partkey AS partkey, + t1.part_value AS part_value +FROM ( + SELECT ps_partkey AS ps_partkey, + tpch.sum(ps.ps_supplycost * ps.ps_availqty) AS part_value + FROM Partsupp ps, + ( + SELECT s.s_suppkey s_suppkey + FROM Supplier AS s, + Nation as n + WHERE s.s_nationkey = n.n_nationkey + ) sn + WHERE ps.ps_suppkey = sn.s_suppkey + GROUP BY ps.ps_partkey AS ps_partkey +) t1 +WHERE t1.part_value > sum * 0.00001 +ORDER BY t1.part_value DESC +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.1.ddl.sqlpp new file mode 100644 index 0000000..176cdc2 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int32, + l_partkey : int32, + l_suppkey : int32, + l_linenumber : int32, + l_quantity : int32, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int32, + o_custkey : int32, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int32, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int32, + c_name : string, + c_address : string, + c_nationkey : int32, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int32, + s_name : string, + s_address : string, + s_nationkey : int32, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int32, + n_name : string, + n_regionkey : int32, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int32, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int32, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int32, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int32, + ps_suppkey : int32, + ps_availqty : int32, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.3.query.sqlpp new file mode 100644 index 0000000..0d63443 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q12_shipping/q12_shipping.3.query.sqlpp @@ -0,0 +1,33 @@ +/* + * 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. + */ + +use tpch; + + +SELECT l_shipmode l_shipmode, + sum("switch-case"(o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH',true,1,false,0)) high_line_count, + sum("switch-case"(o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH',true,0,false,1)) low_line_count +FROM LineItem l, + Orders o +WHERE o.o_orderkey = l.l_orderkey AND l.l_commitdate < l.l_receiptdate AND + l.l_shipdate < l.l_commitdate AND l.l_receiptdate >= '1994-01-01' AND + l.l_receiptdate < '1995-01-01' AND (l.l_shipmode = 'MAIL' OR l.l_shipmode = 'SHIP') +GROUP BY l.l_shipmode as l_shipmode +ORDER BY l_shipmode +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.1.ddl.sqlpp new file mode 100644 index 0000000..907be7b --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int64, + l_partkey : int64, + l_suppkey : int64, + l_linenumber : int64, + l_quantity : int64, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int64, + o_custkey : int64, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int64, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int64, + c_name : string, + c_address : string, + c_nationkey : int64, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int64, + s_name : string, + s_address : string, + s_nationkey : int64, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int64, + n_name : string, + n_regionkey : int64, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int64, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int64, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int64, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int64, + ps_suppkey : int64, + ps_availqty : int64, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp new file mode 100644 index 0000000..24794de --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp @@ -0,0 +1,40 @@ +/* + * 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. + */ + +USE tpch; + +SELECT c_count AS c_count, count(gco) AS custdist +FROM ( + SELECT c_custkey AS c_custkey, sum(co.o_orderkey_count) AS c_count + FROM ( + SELECT c.c_custkey AS c_custkey, + count( + ( + select element o.o_orderkey + from Orders as o + where c.c_custkey = o.o_custkey and not(like(o.o_comment,'%special%requests%')) + ) + ) AS o_orderkey_count + from Customer c + ) co + GROUP BY co.c_custkey c_custkey +) gco +GROUP BY gco.c_count as c_count +ORDER BY custdist desc,c_count DESC +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp new file mode 100644 index 0000000..176cdc2 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int32, + l_partkey : int32, + l_suppkey : int32, + l_linenumber : int32, + l_quantity : int32, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int32, + o_custkey : int32, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int32, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int32, + c_name : string, + c_address : string, + c_nationkey : int32, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int32, + s_name : string, + s_address : string, + s_nationkey : int32, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int32, + n_name : string, + n_regionkey : int32, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int32, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int32, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int32, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int32, + ps_suppkey : int32, + ps_availqty : int32, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp new file mode 100644 index 0000000..db669d6 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp @@ -0,0 +1,30 @@ +/* + * 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. + */ + +use tpch; + + +select element 100.0 * + sum("switch-case"(like(p.p_type,'PROMO%'),true,(l.l_extendedprice * (1 - l.l_discount)),false,0.0)) + / sum(l.l_extendedprice * (1 - l.l_discount)) +FROM LineItem l, + Part p +WHERE l.l_partkey = p.p_partkey AND l.l_shipdate >= '1995-09-01' AND l.l_shipdate < '1995-10-01' +GROUP BY 1 AS t +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp new file mode 100644 index 0000000..907be7b --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int64, + l_partkey : int64, + l_suppkey : int64, + l_linenumber : int64, + l_quantity : int64, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int64, + o_custkey : int64, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int64, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int64, + c_name : string, + c_address : string, + c_nationkey : int64, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int64, + s_name : string, + s_address : string, + s_nationkey : int64, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int64, + n_name : string, + n_regionkey : int64, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int64, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int64, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int64, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int64, + ps_suppkey : int64, + ps_availqty : int64, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp new file mode 100644 index 0000000..1a036cc --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp @@ -0,0 +1,46 @@ +/* + * 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. + */ + +USE tpch; + + +declare function revenue() { +( + SELECT l_suppkey AS supplier_no, + sum(l.l_extendedprice * (1 - l.l_discount)) AS total_revenue + FROM LineItem l + WHERE l.l_shipdate >= '1996-01-01' and l.l_shipdate < '1996-04-01' + GROUP BY l.l_suppkey l_suppkey +) +}; + +WITH m AS max(( + SELECT ELEMENT r2.total_revenue + FROM revenue() r2 +)) +SELECT s.s_suppkey s_suppkey, + s.s_name s_name, + s.s_address s_address, + s.s_phone s_phone, + r.total_revenue total_revenue +FROM Supplier s, + revenue() r +WHERE s.s_suppkey = r.supplier_no AND r.total_revenue < m + 0.000000001 + AND r.total_revenue > m - 0.000000001 +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp new file mode 100644 index 0000000..907be7b --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int64, + l_partkey : int64, + l_suppkey : int64, + l_linenumber : int64, + l_quantity : int64, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int64, + o_custkey : int64, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int64, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int64, + c_name : string, + c_address : string, + c_nationkey : int64, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int64, + s_name : string, + s_address : string, + s_nationkey : int64, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int64, + n_name : string, + n_regionkey : int64, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int64, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int64, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int64, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int64, + ps_suppkey : int64, + ps_availqty : int64, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp new file mode 100644 index 0000000..1d154a6 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp @@ -0,0 +1,48 @@ +/* + * 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. + */ + +USE tpch; + + +declare function tmp() { +( + SELECT psp.p_brand p_brand, psp.p_type p_type, psp.p_size p_size, psp.ps_suppkey ps_suppkey + FROM ( + SELECT p.p_brand p_brand, p.p_type p_type, p.p_size p_size, ps.ps_suppkey ps_suppkey + FROM Partsupp AS ps, + Part AS p + WHERE p.p_partkey = ps.ps_partkey AND p.p_brand != 'Brand#45' AND + not(tpch.like(p.p_type,'MEDIUM POLISHED%')) + ) AS psp, + Supplier AS s + WHERE psp.ps_suppkey = s.s_suppkey AND not(like(s.s_comment,'%Customer%Complaints%')) +) +}; + +SELECT p_brand p_brand, p_type p_type, p_size p_size, count(t2.ps_suppkey) supplier_cnt +FROM ( + SELECT p_brand1 p_brand, p_type1 p_type, p_size1 p_size, ps_suppkey1 ps_suppkey + FROM tpch.tmp() t + WHERE t.p_size = 49 OR t.p_size = 14 OR t.p_size = 23 OR t.p_size = 45 OR t.p_size = 19 + OR t.p_size = 3 OR t.p_size = 36 OR t.p_size = 9 + GROUP BY t.p_brand as p_brand1,t.p_type as p_type1,t.p_size as p_size1,t.ps_suppkey as ps_suppkey1 +) AS t2 +GROUP BY t2.p_brand p_brand,t2.p_type p_type,t2.p_size p_size +ORDER BY supplier_cnt DESC,p_brand,p_type,p_size +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp new file mode 100644 index 0000000..907be7b --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int64, + l_partkey : int64, + l_suppkey : int64, + l_linenumber : int64, + l_quantity : int64, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int64, + o_custkey : int64, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int64, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int64, + c_name : string, + c_address : string, + c_nationkey : int64, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int64, + s_name : string, + s_address : string, + s_nationkey : int64, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int64, + n_name : string, + n_regionkey : int64, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int64, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int64, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int64, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int64, + ps_suppkey : int64, + ps_availqty : int64, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp new file mode 100644 index 0000000..471a145 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +SELECT l_partkey t_partkey, + count(l) t_count, + 0.2 * avg(l.l_quantity) t_avg_quantity, + max(l.l_suppkey) t_max_suppkey, + max(l.l_linenumber) t_max_linenumber, + avg(l.l_extendedprice) t_avg_extendedprice, + avg(l.l_discount) t_avg_discount, + avg(l.l_tax) t_avg_tax, + max(l.l_shipdate) t_max_shipdate, + min(l.l_commitdate) t_min_commitdate, + min(l.l_receiptdate) t_min_receiptdate, + max(l.l_comment) t_max_comment +FROM LineItem AS l +GROUP BY l.l_partkey AS l_partkey +ORDER BY l_partkey +; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp new file mode 100644 index 0000000..176cdc2 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp @@ -0,0 +1,134 @@ +/* + * 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. + */ + +drop database tpch if exists; +create database tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : int32, + l_partkey : int32, + l_suppkey : int32, + l_linenumber : int32, + l_quantity : int32, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +} + +create type tpch.OrderType as + closed { + o_orderkey : int32, + o_custkey : int32, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : int32, + o_comment : string +} + +create type tpch.CustomerType as + closed { + c_custkey : int32, + c_name : string, + c_address : string, + c_nationkey : int32, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +} + +create type tpch.SupplierType as + closed { + s_suppkey : int32, + s_name : string, + s_address : string, + s_nationkey : int32, + s_phone : string, + s_acctbal : double, + s_comment : string +} + +create type tpch.NationType as + closed { + n_nationkey : int32, + n_name : string, + n_regionkey : int32, + n_comment : string +} + +create type tpch.RegionType as + closed { + r_regionkey : int32, + r_name : string, + r_comment : string +} + +create type tpch.PartType as + closed { + p_partkey : int32, + p_name : string, + p_mfgr : string, + p_brand : string, + p_type : string, + p_size : int32, + p_container : string, + p_retailprice : double, + p_comment : string +} + +create type tpch.PartSuppType as + closed { + ps_partkey : int32, + ps_suppkey : int32, + ps_availqty : int32, + ps_supplycost : double, + ps_comment : string +} + +create table LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +create table Orders(OrderType) primary key o_orderkey; + +create table Supplier(SupplierType) primary key s_suppkey; + +create table Region(RegionType) primary key r_regionkey; + +create table Nation(NationType) primary key n_nationkey; + +create table Part(PartType) primary key p_partkey; + +create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey; + +create table Customer(CustomerType) primary key c_custkey; + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp new file mode 100644 index 0000000..97a0b31 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp @@ -0,0 +1,38 @@ +/* + * 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. + */ + +use tpch; + + +load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")); + +load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")); + http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp new file mode 100644 index 0000000..4519fa6 --- /dev/null +++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp @@ -0,0 +1,37 @@ +/* + * 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. + */ + +USE tpch; + + +WITH tmp AS +( + SELECT l_partkey t_partkey, 0.2 * avg(l.l_quantity) t_avg_quantity + FROM LineItem AS l + GROUP BY l.l_partkey AS l_partkey +) + +SELECT ELEMENT sum(( + SELECT ELEMENT l.l_extendedprice + FROM tmp t, + LineItem l, + Part p + WHERE p.p_partkey = l.l_partkey AND p.p_container = 'MED BOX' + AND l.l_partkey = t.t_partkey AND l.l_quantity < t.t_avg_quantity + )) / 7.0;