http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/fuzzyjoin/dblp-csx-3_5.4/dblp-csx-3_5.4.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/fuzzyjoin/dblp-csx-3_5.4/dblp-csx-3_5.4.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/fuzzyjoin/dblp-csx-3_5.4/dblp-csx-3_5.4.3.query.sqlpp index dbde905..5f0f083 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/fuzzyjoin/dblp-csx-3_5.4/dblp-csx-3_5.4.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/fuzzyjoin/dblp-csx-3_5.4/dblp-csx-3_5.4.3.query.sqlpp @@ -72,7 +72,7 @@ from CSX as paperCSX, sim as fuzzyjoin.`similarity-jaccard-prefix`(lenDBLP,tokensDBLP,lenCSX,tokensCSX,prefixTokenDBLP,0.500000f) where ((prefixTokenDBLP = prefixTokenCSX) and (sim >= 0.500000f)) /* +hash */ - group by idDBLP as idDBLP,idCSX as idCSX + group by idDBLP as idDBLP,idCSX as idCSX,sim as sim ) as ridpair where (ridpair.idDBLP = paperDBLP.id) ) as paperDBLPridpair
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.13.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.13.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.13.query.sqlpp index f63fc2e..a30c374 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.13.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.13.query.sqlpp @@ -25,11 +25,11 @@ WITH tmp AS WHERE l_commitdate < l_receiptdate ) -SELECT o_orderpriority AS order_priority, count(*) AS count -FROM tpch2.Orders -JOIN tmp -ON l_orderkey = o_orderkey -WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01' -GROUP BY o_orderpriority -ORDER BY o_orderpriority +SELECT o.o_orderpriority AS order_priority, count(*) AS count +FROM tpch2.Orders o +JOIN tmp t +ON t.l_orderkey = o.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o.o_orderpriority ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.3.query.sqlpp index f63fc2e..a30c374 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.3.query.sqlpp @@ -25,11 +25,11 @@ WITH tmp AS WHERE l_commitdate < l_receiptdate ) -SELECT o_orderpriority AS order_priority, count(*) AS count -FROM tpch2.Orders -JOIN tmp -ON l_orderkey = o_orderkey -WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01' -GROUP BY o_orderpriority -ORDER BY o_orderpriority +SELECT o.o_orderpriority AS order_priority, count(*) AS count +FROM tpch2.Orders o +JOIN tmp t +ON t.l_orderkey = o.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o.o_orderpriority ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.7.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.7.query.sqlpp index f63fc2e..a30c374 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.7.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/all_datasets/all_datasets.7.query.sqlpp @@ -25,11 +25,11 @@ WITH tmp AS WHERE l_commitdate < l_receiptdate ) -SELECT o_orderpriority AS order_priority, count(*) AS count -FROM tpch2.Orders -JOIN tmp -ON l_orderkey = o_orderkey -WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01' -GROUP BY o_orderpriority -ORDER BY o_orderpriority +SELECT o.o_orderpriority AS order_priority, count(*) AS count +FROM tpch2.Orders o +JOIN tmp t +ON t.l_orderkey = o.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o.o_orderpriority ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.13.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.13.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.13.query.sqlpp index 77667ab..ce9e993 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.13.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.13.query.sqlpp @@ -26,11 +26,11 @@ WITH tmp AS WHERE l_commitdate < l_receiptdate ) -SELECT o_orderpriority AS order_priority, count(*) AS count -FROM Orders -JOIN tmp -ON l_orderkey = o_orderkey -WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01' -GROUP BY o_orderpriority -ORDER BY o_orderpriority +SELECT o.o_orderpriority AS order_priority, count(*) AS count +FROM Orders o +JOIN tmp t +ON t.l_orderkey = o.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o.o_orderpriority ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.3.query.sqlpp index 77667ab..ce9e993 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.3.query.sqlpp @@ -26,11 +26,11 @@ WITH tmp AS WHERE l_commitdate < l_receiptdate ) -SELECT o_orderpriority AS order_priority, count(*) AS count -FROM Orders -JOIN tmp -ON l_orderkey = o_orderkey -WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01' -GROUP BY o_orderpriority -ORDER BY o_orderpriority +SELECT o.o_orderpriority AS order_priority, count(*) AS count +FROM Orders o +JOIN tmp t +ON t.l_orderkey = o.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o.o_orderpriority ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.7.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.7.query.sqlpp index 77667ab..ce9e993 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.7.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rebalance/single_dataverse/single_dataverse.7.query.sqlpp @@ -26,11 +26,11 @@ WITH tmp AS WHERE l_commitdate < l_receiptdate ) -SELECT o_orderpriority AS order_priority, count(*) AS count -FROM Orders -JOIN tmp -ON l_orderkey = o_orderkey -WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01' -GROUP BY o_orderpriority -ORDER BY o_orderpriority +SELECT o.o_orderpriority AS order_priority, count(*) AS count +FROM Orders o +JOIN tmp t +ON t.l_orderkey = o.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o.o_orderpriority ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/conflict-fields-dataset/conflict-fields-dataset.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/conflict-fields-dataset/conflict-fields-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/conflict-fields-dataset/conflict-fields-dataset.3.query.sqlpp index 2645ddc..6560931 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/conflict-fields-dataset/conflict-fields-dataset.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/conflict-fields-dataset/conflict-fields-dataset.3.query.sqlpp @@ -21,4 +21,5 @@ use test; select * from samptable s1, samptable s2 -where samptable > 0; +where samptable > 0 +order by s1.id http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.1.ddl.sqlpp new file mode 100644 index 0000000..71e62d6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.1.ddl.sqlpp @@ -0,0 +1,28 @@ +/* + * 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 dataverse test if exists; +create dataverse test; +use test; + +create type orderType as { + oid: bigint +}; + +create dataset orders(orderType) primary key oid; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.2.update.sqlpp new file mode 100644 index 0000000..4ec4dfb --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.2.update.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 test; + +insert into orders ({'oid' : 100, 'pid': 0, 'amount': 1}); +insert into orders ({'oid' : 101, 'pid': 1, 'amount': 2}); +insert into orders ({'oid' : 102, 'pid': 2, 'amount': 3}); +insert into orders ({'oid' : 103, 'pid': 1, 'amount': 4}); +insert into orders ({'oid' : 104, 'pid': 0, 'amount': 6}); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.3.query.sqlpp new file mode 100644 index 0000000..323a0e3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/groupby_rename_with_sugar/groupby_rename_with_sugar.3.query.sqlpp @@ -0,0 +1,25 @@ +/* + * 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 test; + +select pid, sum(amount) as s, (select value g from g order by g.ord.oid) as g +from orders o +group by pid group as g(o as ord) +order by pid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.1.ddl.sqlpp new file mode 100644 index 0000000..c577bea --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.1.ddl.sqlpp @@ -0,0 +1,35 @@ +/* + * 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 dataverse test if exists; +create dataverse test; +use test; + +create type partType as { + pid: bigint +}; + +create type orderType as { + oid: bigint, + pid: bigint +}; + +create dataset parts(partType) primary key pid; + +create dataset orders(orderType) primary key oid; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.2.update.sqlpp new file mode 100644 index 0000000..3e5e1d1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.2.update.sqlpp @@ -0,0 +1,29 @@ +/* + * 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 test; + +insert into parts ({'pid': 0}); +insert into parts ({'pid': 1}); + +insert into orders ({'oid' : 100, 'pid': 0}); +insert into orders ({'oid' : 101, 'pid': 1}); +insert into orders ({'oid' : 102, 'pid': 99}); +insert into orders ({'oid' : 103, 'pid': 1}); +insert into orders ({'oid' : 104, 'pid': 0}); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.3.query.sqlpp new file mode 100644 index 0000000..01ed3a5 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/resolution/order_1/order_1.3.query.sqlpp @@ -0,0 +1,24 @@ +/* + * 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 test; + +select value oid from orders +where pid in (select value pid from parts) +order by oid http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/from/from.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/from/from.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/from/from.3.query.sqlpp index 53fb0cf..cf61511 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/from/from.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/from/from.3.query.sqlpp @@ -24,9 +24,9 @@ SELECT * FROM Customer c, Orders o, LineItem l -WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey - AND l_orderkey = o_orderkey AND o_orderdate < '1995-03-15' - AND l_shipdate > '1995-03-15' -ORDER BY l_linenumber, l_orderkey +WHERE c.c_mktsegment = 'BUILDING' AND c.c_custkey = o.o_custkey + AND l.l_orderkey = o.o_orderkey AND o.o_orderdate < '1995-03-15' + AND l.l_shipdate > '1995-03-15' +ORDER BY l.l_linenumber, l.l_orderkey LIMIT 3; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/join/join.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/join/join.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/join/join.3.query.sqlpp index 5eeee7c..00f37cd 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/join/join.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/join/join.3.query.sqlpp @@ -22,11 +22,11 @@ USE tpch; SELECT * FROM Customer c -JOIN Orders o ON c_custkey = o_custkey -JOIN LineItem l ON l_orderkey = o_orderkey -WHERE c_mktsegment = 'BUILDING' - AND o_orderdate < '1995-03-15' - AND l_shipdate > '1995-03-15' -ORDER BY l_linenumber, l_orderkey +JOIN Orders o ON c.c_custkey = o.o_custkey +JOIN LineItem l ON l.l_orderkey = o.o_orderkey +WHERE c.c_mktsegment = 'BUILDING' + AND o.o_orderdate < '1995-03-15' + AND l.l_shipdate > '1995-03-15' +ORDER BY l.l_linenumber, l.l_orderkey LIMIT 3; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/mixed/mixed.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/mixed/mixed.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/mixed/mixed.3.query.sqlpp index 95ac04e..188401b 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/mixed/mixed.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/mixed/mixed.3.query.sqlpp @@ -20,13 +20,13 @@ USE tpch; -SELECT c_custkey, *, o_orderkey +SELECT c.c_custkey, *, o.o_orderkey FROM Customer c, Orders o, LineItem l -WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey - AND l_orderkey = o_orderkey AND o_orderdate < '1995-03-15' - AND l_shipdate > '1995-03-15' -ORDER BY l_linenumber, l_orderkey +WHERE c.c_mktsegment = 'BUILDING' AND c.c_custkey = o.o_custkey + AND l.l_orderkey = o.o_orderkey AND o.o_orderdate < '1995-03-15' + AND l.l_shipdate > '1995-03-15' +ORDER BY l.l_linenumber, l.l_orderkey LIMIT 3; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/no_star/no_star.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/no_star/no_star.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/no_star/no_star.3.query.sqlpp index 14ae8d1..16e0471 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/no_star/no_star.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/select-star/no_star/no_star.3.query.sqlpp @@ -24,9 +24,9 @@ SELECT FROM Customer c, Orders o, LineItem l -WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey - AND l_orderkey = o_orderkey AND o_orderdate < '1995-03-15' - AND l_shipdate > '1995-03-15' -ORDER BY l_linenumber, l_orderkey +WHERE c.c_mktsegment = 'BUILDING' AND c.c_custkey = o.o_custkey + AND l.l_orderkey = o.o_orderkey AND o.o_orderdate < '1995-03-15' + AND l.l_shipdate > '1995-03-15' +ORDER BY l.l_linenumber, l.l_orderkey LIMIT 3; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp index ce3159a..0f9b329 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temporal/overlap_bins_gby_1/overlap_bins_gby_1.3.query.sqlpp @@ -25,7 +25,7 @@ use test; select element {'timebin':bin, - 'count':count((select value 1 from g)), + 'count':coll_count((select value 1 from g)), 'total_ms':coll_sum(( select element `ms_from_day_time_duration`(`duration-from-interval`(`get-overlapping-interval`(bin, g.i2.interval))) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp index f4f6d52..fa3764a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp @@ -23,24 +23,24 @@ use tpcds; with customer_total_return as (select sr_customer_sk as ctr_customer_sk ,sr_store_sk as ctr_store_sk -,sum(sr_fee) as ctr_total_return -from store_returns -,date_dim -where sr_returned_date_sk = d_date_sk -and d_year = 2000 -group by sr_customer_sk -,sr_store_sk) +,sum(sr.sr_fee) as ctr_total_return +from store_returns sr +,date_dim d +where sr.sr_returned_date_sk = d.d_date_sk +and d.d_year = 2000 +group by sr.sr_customer_sk +,sr.sr_store_sk) -select c_customer_id +select c.c_customer_id from customer_total_return ctr1 -,store -,customer +,store s +,customer c where ctr1.ctr_total_return > ( select value (avg(ctr2.ctr_total_return)*1.2) from customer_total_return ctr2 where ctr1.ctr_store_sk = ctr2.ctr_store_sk)[0] -and s_store_sk = ctr1.ctr_store_sk -and s_state = 'TN' -and ctr1.ctr_customer_sk = c_customer_sk -order by c_customer_id +and s.s_store_sk = ctr1.ctr_store_sk +and s.s_state = 'TN' +and ctr1.ctr_customer_sk = c.c_customer_sk +order by c.c_customer_id limit 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q03/q03.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q03/q03.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q03/q03.3.query.sqlpp index 67f4a14..612a8c5 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q03/q03.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q03/q03.3.query.sqlpp @@ -20,10 +20,10 @@ USE tpcds; -SELECT dt.d_year +SELECT d_year ,item.i_brand_id brand_id ,item.i_brand brand - ,sum(ss_ext_sales_price) sum_agg + ,sum(store_sales.ss_ext_sales_price) sum_agg FROM date_dim dt ,store_sales ,item @@ -34,7 +34,7 @@ AND dt.d_moy=12 GROUP BY dt.d_year ,item.i_brand ,item.i_brand_id -ORDER BY dt.d_year +ORDER BY d_year ,sum_agg desc ,brand_id LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q07/q07.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q07/q07.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q07/q07.3.query.sqlpp index 14c8840..6f949b9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q07/q07.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q07/q07.3.query.sqlpp @@ -21,20 +21,20 @@ USE tpcds; SELECT i_item_id, - avg(ss_quantity) agg1, - avg(ss_list_price) agg2, - avg(ss_coupon_amt) agg3, - avg(ss_sales_price) agg4 -FROM store_sales, customer_demographics, date_dim, item, promotion -WHERE ss_sold_date_sk = d_date_sk -AND ss_item_sk = i_item_sk -AND ss_cdemo_sk = cd_demo_sk -AND ss_promo_sk = p_promo_sk -AND cd_gender = 'F' -AND cd_marital_status = 'W' -AND cd_education_status = 'Primary' -AND (p_channel_email = 'N' OR p_channel_event = 'N') -AND d_year = 1998 -GROUP BY i_item_id + avg(ss.ss_quantity) agg1, + avg(ss.ss_list_price) agg2, + avg(ss.ss_coupon_amt) agg3, + avg(ss.ss_sales_price) agg4 +FROM store_sales ss, customer_demographics cd, date_dim d, item i, promotion p +WHERE ss.ss_sold_date_sk = d.d_date_sk +AND ss.ss_item_sk = i.i_item_sk +AND ss.ss_cdemo_sk = cd.cd_demo_sk +AND ss.ss_promo_sk = p.p_promo_sk +AND cd.cd_gender = 'F' +AND cd.cd_marital_status = 'W' +AND cd.cd_education_status = 'Primary' +AND (p.p_channel_email = 'N' OR p.p_channel_event = 'N') +AND d.d_year = 1998 +GROUP BY i.i_item_id ORDER BY i_item_id LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp index ceb7134..484757a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp @@ -21,20 +21,20 @@ USE tpcds; SELECT ca_zip - ,sum(cs_sales_price) -FROM catalog_sales - ,customer - ,customer_address - ,date_dim -WHERE cs_bill_customer_sk = c_customer_sk -AND c_current_addr_sk = ca_address_sk -AND ( substr(ca_zip,1,5) in ['85669', '86197','88274','83405','86475', + ,sum(cs.cs_sales_price) +FROM catalog_sales cs + ,customer c + ,customer_address ca + ,date_dim d +WHERE cs.cs_bill_customer_sk = c.c_customer_sk +AND c.c_current_addr_sk = ca.ca_address_sk +AND ( substr(ca.ca_zip,1,5) in ['85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792'] -OR ca_state in ['CA','WA','GA'] -OR cs_sales_price > 500) -AND cs_sold_date_sk = d_date_sk -AND d_qoy = 2 -AND d_year = 2000 -GROUP BY ca_zip +OR ca.ca_state in ['CA','WA','GA'] +OR cs.cs_sales_price > 500) +AND cs.cs_sold_date_sk = d.d_date_sk +AND d.d_qoy = 2 +AND d.d_year = 2000 +GROUP BY ca.ca_zip ORDER BY ca_zip LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp index 12ca91f..5e3b339 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp @@ -21,21 +21,21 @@ USE tpcds; SELECT i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, - SUM(ss_ext_sales_price) ext_price -FROM date_dim, store_sales, item,customer,customer_address,store -WHERE d_date_sk = ss_sold_date_sk -AND ss_item_sk = i_item_sk -AND i_manager_id=7 -AND d_moy=11 -AND d_year=1999 -AND ss_customer_sk = c_customer_sk -AND c_current_addr_sk = ca_address_sk -AND substr(ca_zip,1,5) != substr(s_zip,1,5) -AND ss_store_sk = s_store_sk -GROUP BY i_brand - ,i_brand_id - ,i_manufact_id - ,i_manufact + SUM(ss.ss_ext_sales_price) ext_price +FROM date_dim d, store_sales ss, item i, customer c, customer_address ca, store s +WHERE d.d_date_sk = ss.ss_sold_date_sk +AND ss.ss_item_sk = i.i_item_sk +AND i.i_manager_id=7 +AND d.d_moy=11 +AND d.d_year=1999 +AND ss.ss_customer_sk = c.c_customer_sk +AND c.c_current_addr_sk = ca.ca_address_sk +AND substr(ca.ca_zip,1,5) != substr(s.s_zip,1,5) +AND ss.ss_store_sk = s.s_store_sk +GROUP BY i.i_brand + ,i.i_brand_id + ,i.i_manufact_id + ,i.i_manufact ORDER BY ext_price desc ,i_brand ,i_brand_id http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp index d39c5bd..a62a7c6 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp @@ -23,24 +23,24 @@ USE tpcds; SELECT * FROM (SELECT w_warehouse_name ,i_item_id - ,SUM(CASE WHEN date(d_date) < date('1998-04-08') - THEN inv_quantity_on_hand + ,SUM(CASE WHEN date(d.d_date) < date('1998-04-08') + THEN inv.inv_quantity_on_hand ELSE 0 END) AS inv_before - ,SUM(CASE WHEN date(d_date) >= date('1998-04-08') - THEN inv_quantity_on_hand + ,SUM(CASE WHEN date(d.d_date) >= date('1998-04-08') + THEN inv.inv_quantity_on_hand ELSE 0 END) AS inv_after - FROM inventory - ,warehouse - ,item - ,date_dim - WHERE i_current_price >= 0.99 - AND i_current_price <= 1.49 - AND i_item_sk = inv_item_sk - AND inv_warehouse_sk = w_warehouse_sk - AND inv_date_sk = d_date_sk - AND date(d_date) >= date('1998-03-09') - AND date(d_date) <= date('1998-05-08') - GROUP BY w_warehouse_name, i_item_id) x + FROM inventory inv + ,warehouse w + ,item i + ,date_dim d + WHERE i.i_current_price >= 0.99 + AND i.i_current_price <= 1.49 + AND i.i_item_sk = inv.inv_item_sk + AND inv.inv_warehouse_sk = w.w_warehouse_sk + AND inv.inv_date_sk = d.d_date_sk + AND date(d.d_date) >= date('1998-03-09') + AND date(d.d_date) <= date('1998-05-08') + GROUP BY w.w_warehouse_name, i.i_item_id) x WHERE (CASE WHEN inv_before > 0 THEN inv_after / inv_before ELSE null http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp index 9456961..c24c10e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp @@ -31,31 +31,31 @@ with ssales as ,i_manager_id ,i_units ,i_size - ,SUM(ss_sales_price) netpaid -FROM store_sales - ,store_returns - ,store - ,item - ,customer - ,customer_address -WHERE ss_ticket_number = sr_ticket_number - AND ss_item_sk = sr_item_sk - AND ss_customer_sk = c_customer_sk - AND ss_item_sk = i_item_sk - AND ss_store_sk = s_store_sk - AND c_birth_country = UPPERCASE(ca_country) - AND s_zip = ca_zip -AND s_market_id=7 -GROUP BY c_last_name - ,c_first_name - ,s_store_name - ,ca_state - ,s_state - ,i_color - ,i_current_price - ,i_manager_id - ,i_units - ,i_size) + ,SUM(ss.ss_sales_price) netpaid +FROM store_sales ss + ,store_returns sr + ,store s + ,item i + ,customer c + ,customer_address ca +WHERE ss.ss_ticket_number = sr.sr_ticket_number + AND ss.ss_item_sk = sr.sr_item_sk + AND ss.ss_customer_sk = c.c_customer_sk + AND ss.ss_item_sk = i.i_item_sk + AND ss.ss_store_sk = s.s_store_sk + AND c.c_birth_country = UPPERCASE(ca.ca_country) + AND s.s_zip = ca.ca_zip +AND s.s_market_id=7 +GROUP BY c.c_last_name + ,c.c_first_name + ,s.s_store_name + ,ca.ca_state + ,s.s_state + ,i.i_color + ,i.i_current_price + ,i.i_manager_id + ,i.i_units + ,i.i_size) SELECT c_last_name ,c_first_name ,s_store_name http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp index 0c80d1d..86ffa7b 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp @@ -31,31 +31,31 @@ WITH ssales AS ,i_manager_id ,i_units ,i_size - ,SUM(ss_sales_price) netpaid -FROM store_sales - ,store_returns - ,store - ,item - ,customer - ,customer_address -WHERE ss_ticket_number = sr_ticket_number - AND ss_item_sk = sr_item_sk - AND ss_customer_sk = c_customer_sk - AND ss_item_sk = i_item_sk - AND ss_store_sk = s_store_sk - AND c_birth_country = uppercase(ca_country) - AND s_zip = ca_zip - AND s_market_id = 7 -GROUP BY c_last_name - ,c_first_name - ,s_store_name - ,ca_state - ,s_state - ,i_color - ,i_current_price - ,i_manager_id - ,i_units - ,i_size) + ,SUM(ss.ss_sales_price) netpaid +FROM store_sales ss + ,store_returns sr + ,store s + ,item i + ,customer c + ,customer_address ca +WHERE ss.ss_ticket_number = sr.sr_ticket_number + AND ss.ss_item_sk = sr.sr_item_sk + AND ss.ss_customer_sk = c.c_customer_sk + AND ss.ss_item_sk = i.i_item_sk + AND ss.ss_store_sk = s.s_store_sk + AND c.c_birth_country = uppercase(ca.ca_country) + AND s.s_zip = ca.ca_zip + AND s.s_market_id = 7 +GROUP BY c.c_last_name + ,c.c_first_name + ,s.s_store_name + ,ca.ca_state + ,s.s_state + ,i.i_color + ,i.i_current_price + ,i.i_manager_id + ,i.i_units + ,i.i_size) SELECT c_last_name ,c_first_name ,s_store_name http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp index 8245836..7a473f8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp @@ -25,42 +25,42 @@ SELECT ,i_item_desc ,s_store_id ,s_store_name - ,SUM(ss_net_profit) as store_sales_profit - ,SUM(sr_net_loss) as store_returns_loss - ,SUM(cs_net_profit) as catalog_sales_profit + ,SUM(ss.ss_net_profit) as store_sales_profit + ,SUM(sr.sr_net_loss) as store_returns_loss + ,SUM(cs.cs_net_profit) as catalog_sales_profit FROM - store_sales - ,store_returns - ,catalog_sales + store_sales ss + ,store_returns sr + ,catalog_sales cs ,date_dim d1 ,date_dim d2 ,date_dim d3 - ,store - ,item + ,store s + ,item i WHERE d1.d_moy = 4 AND d1.d_year = 2000 - AND d1.d_date_sk = ss_sold_date_sk - AND i_item_sk = ss_item_sk - AND s_store_sk = ss_store_sk - AND ss_customer_sk = sr_customer_sk - AND ss_item_sk = sr_item_sk - AND ss_ticket_number = sr_ticket_number - AND sr_returned_date_sk = d2.d_date_sk + AND d1.d_date_sk = ss.ss_sold_date_sk + AND i.i_item_sk = ss.ss_item_sk + AND s.s_store_sk = ss.ss_store_sk + AND ss.ss_customer_sk = sr.sr_customer_sk + AND ss.ss_item_sk = sr.sr_item_sk + AND ss.ss_ticket_number = sr.sr_ticket_number + AND sr.sr_returned_date_sk = d2.d_date_sk AND d2.d_moy >= 4 AND d2.d_moy <= 10 AND d2.d_year = 2000 - AND sr_customer_sk = cs_bill_customer_sk - AND sr_item_sk = cs_item_sk - AND cs_sold_date_sk = d3.d_date_sk + AND sr.sr_customer_sk = cs.cs_bill_customer_sk + AND sr.sr_item_sk = cs.cs_item_sk + AND cs.cs_sold_date_sk = d3.d_date_sk AND d3.d_moy >= 4 AND d3.d_moy <= 10 AND d3.d_year = 2000 GROUP BY - i_item_id - ,i_item_desc - ,s_store_id - ,s_store_name + i.i_item_id + ,i.i_item_desc + ,s.s_store_id + ,s.s_store_name ORDER BY i_item_id ,i_item_desc http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp index d5c38ce..7f219b7 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp @@ -21,20 +21,20 @@ USE tpcds; SELECT i_item_id, - avg(cs_quantity) agg1, - avg(cs_list_price) agg2, - avg(cs_coupon_amt) agg3, - avg(cs_sales_price) agg4 - FROM catalog_sales, customer_demographics, date_dim, item, promotion - WHERE cs_sold_date_sk = d_date_sk AND - cs_item_sk = i_item_sk AND - cs_bill_cdemo_sk = cd_demo_sk AND - cs_promo_sk = p_promo_sk AND - cd_gender = 'F' AND - cd_marital_status = 'W' AND - cd_education_status = 'Primary' AND - (p_channel_email = 'N' OR p_channel_event = 'N') AND - d_year = 1998 - GROUP BY i_item_id + avg(cs.cs_quantity) agg1, + avg(cs.cs_list_price) agg2, + avg(cs.cs_coupon_amt) agg3, + avg(cs.cs_sales_price) agg4 + FROM catalog_sales cs, customer_demographics cd, date_dim d, item i, promotion p + WHERE cs.cs_sold_date_sk = d.d_date_sk AND + cs.cs_item_sk = i.i_item_sk AND + cs.cs_bill_cdemo_sk = cd.cd_demo_sk AND + cs.cs_promo_sk = p.p_promo_sk AND + cd.cd_gender = 'F' AND + cd.cd_marital_status = 'W' AND + cd.cd_education_status = 'Primary' AND + (p.p_channel_email = 'N' OR p.p_channel_event = 'N') AND + d.d_year = 1998 + GROUP BY i.i_item_id ORDER BY i_item_id LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp index e2ba54d..2b7270f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp @@ -25,39 +25,39 @@ SELECT ,i_item_desc ,s_store_id ,s_store_name - ,SUM(ss_quantity) as store_sales_quantity - ,SUM(sr_return_quantity) as store_returns_quantity - ,SUM(cs_quantity) as catalog_sales_quantity + ,SUM(ss.ss_quantity) as store_sales_quantity + ,SUM(sr.sr_return_quantity) as store_returns_quantity + ,SUM(cs.cs_quantity) as catalog_sales_quantity FROM - store_sales - ,store_returns - ,catalog_sales + store_sales ss + ,store_returns sr + ,catalog_sales cs ,date_dim d1 ,date_dim d2 ,date_dim d3 - ,store - ,item + ,store s + ,item i WHERE d1.d_moy = 4 AND d1.d_year = 1999 - AND d1.d_date_sk = ss_sold_date_sk - AND i_item_sk = ss_item_sk - AND s_store_sk = ss_store_sk - AND ss_customer_sk = sr_customer_sk - AND ss_item_sk = sr_item_sk - AND ss_ticket_number = sr_ticket_number - AND sr_returned_date_sk = d2.d_date_sk + AND d1.d_date_sk = ss.ss_sold_date_sk + AND i.i_item_sk = ss.ss_item_sk + AND s.s_store_sk = ss.ss_store_sk + AND ss.ss_customer_sk = sr.sr_customer_sk + AND ss.ss_item_sk = sr.sr_item_sk + AND ss.ss_ticket_number = sr.sr_ticket_number + AND sr.sr_returned_date_sk = d2.d_date_sk AND d2.d_moy >= 4 AND d2.d_moy <= 4 + 3 AND d2.d_year = 1999 - AND sr_customer_sk = cs_bill_customer_sk - AND sr_item_sk = cs_item_sk - AND cs_sold_date_sk = d3.d_date_sk + AND sr.sr_customer_sk = cs.cs_bill_customer_sk + AND sr.sr_item_sk = cs.cs_item_sk + AND cs.cs_sold_date_sk = d3.d_date_sk AND d3.d_year IN [1999,1999+1,1999+2] GROUP BY - i_item_id - ,i_item_desc - ,s_store_id - ,s_store_name + i.i_item_id + ,i.i_item_desc + ,s.s_store_id + ,s.s_store_name ORDER BY i_item_id ,i_item_desc http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp index 0793006..8ba0feb 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp @@ -23,28 +23,28 @@ USE tpcds; WITH customer_total_return as (SELECT wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, - sum(wr_return_amt) as ctr_total_return - FROM web_returns - ,date_dim - ,customer_address - where wr_returned_date_sk = d_date_sk - AND d_year =2002 - AND wr_returning_addr_sk = ca_address_sk - group by wr_returning_customer_sk - ,ca_state) - SELECT c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag - ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address - ,c_last_review_date,ctr_total_return + sum(wr.wr_return_amt) as ctr_total_return + FROM web_returns wr + ,date_dim d + ,customer_address ca + where wr.wr_returned_date_sk = d.d_date_sk + AND d.d_year =2002 + AND wr.wr_returning_addr_sk = ca.ca_address_sk + group by wr.wr_returning_customer_sk + ,ca.ca_state) + SELECT c.c_customer_id,c.c_salutation,c.c_first_name,c.c_last_name,c.c_preferred_cust_flag + ,c.c_birth_day,c.c_birth_month,c.c_birth_year,c.c_birth_country,c.c_login,c.c_email_address + ,c.c_last_review_date,ctr1.ctr_total_return FROM customer_total_return ctr1 - ,customer_address - ,customer + ,customer_address ca + ,customer c where ctr1.ctr_total_return > (SELECT VALUE (AVG(ctr2.ctr_total_return)*1.2) FROM customer_total_return ctr2 where ctr1.ctr_state = ctr2.ctr_state)[0] - AND ca_address_sk = c_current_addr_sk - AND ca_state = 'IL' - AND ctr1.ctr_customer_sk = c_customer_sk - ORDER BY c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag - ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address - ,c_last_review_date,ctr_total_return + AND ca.ca_address_sk = c.c_current_addr_sk + AND ca.ca_state = 'IL' + AND ctr1.ctr_customer_sk = c.c_customer_sk + ORDER BY c.c_customer_id,c.c_salutation,c.c_first_name,c.c_last_name,c.c_preferred_cust_flag + ,c.c_birth_day,c.c_birth_month,c.c_birth_year,c.c_birth_country,c.c_login,c.c_email_address + ,c.c_last_review_date,ctr1.ctr_total_return LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp index b869d5e..1768a95 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp @@ -21,17 +21,17 @@ USE tpcds; WITH ss AS - (SELECT ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales - FROM store_sales,date_dim,customer_address - WHERE ss_sold_date_sk = d_date_sk - AND ss_addr_sk=ca_address_sk - GROUP BY ca_county,d_qoy, d_year), + (SELECT ca_county,d_qoy, d_year,sum(ss.ss_ext_sales_price) as store_sales + FROM store_sales ss,date_dim d,customer_address ca + WHERE ss.ss_sold_date_sk = d.d_date_sk + AND ss.ss_addr_sk=ca.ca_address_sk + GROUP BY ca.ca_county,d.d_qoy, d.d_year), ws AS - (SELECT ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales - FROM web_sales,date_dim,customer_address - WHERE ws_sold_date_sk = d_date_sk - AND ws_bill_addr_sk=ca_address_sk - GROUP BY ca_county,d_qoy, d_year) + (SELECT ca_county,d_qoy, d_year,sum(ws.ws_ext_sales_price) as web_sales + FROM web_sales ws,date_dim d,customer_address ca + WHERE ws.ws_sold_date_sk = d.d_date_sk + AND ws.ws_bill_addr_sk=ca.ca_address_sk + GROUP BY ca.ca_county,d.d_qoy, d.d_year) SELECT /* tt */ ss1.ca_county ,ss1.d_year http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp index e81d182..b5908e8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp @@ -20,12 +20,12 @@ USE tpcds; -SELECT c_last_name - ,c_first_name - ,c_salutation - ,c_preferred_cust_flag - ,ss_ticket_number - ,cnt +SELECT c.c_last_name + ,c.c_first_name + ,c.c_salutation + ,c.c_preferred_cust_flag + ,dn.ss_ticket_number + ,dn.cnt FROM (select ss_ticket_number ,ss_customer_sk @@ -48,8 +48,8 @@ SELECT c_last_name AND date_dim.d_year in [1998,1998+1,1998+2] AND store.s_county in ['Williamson County','Williamson County','Williamson County','Williamson County', 'Williamson County','Williamson County','Williamson County','Williamson County'] -GROUP BY ss_ticket_number,ss_customer_sk GROUP AS dngrp) dn, customer -WHERE dn.ss_customer_sk = c_customer_sk - AND cnt >= 15 - AND cnt <= 20 -ORDER BY c_last_name,c_first_name,c_salutation,c_preferred_cust_flag desc; \ No newline at end of file +GROUP BY store_sales.ss_ticket_number,store_sales.ss_customer_sk GROUP AS dngrp) dn, customer c +WHERE dn.ss_customer_sk = c.c_customer_sk + AND dn.cnt >= 15 + AND dn.cnt <= 20 +ORDER BY c.c_last_name,c.c_first_name,c.c_salutation,c.c_preferred_cust_flag desc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp index df1b580..e05720c 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp @@ -23,14 +23,14 @@ USE tpcds; SELECT i_item_id ,i_item_desc ,i_current_price - FROM item, inventory, date_dim, catalog_sales - WHERE i_current_price >= 22 AND i_current_price <= 22 + 30 - AND inv_item_sk = i_item_sk - AND d_date_sk=inv_date_sk - AND date(d_date) >= date('2001-06-02') AND date(d_date) <= date('2001-08-01') - AND i_manufact_id in [678,964,918,849] - AND inv_quantity_on_hand >= 100 AND inv_quantity_on_hand <= 500 - AND cs_item_sk = i_item_sk - GROUP BY i_item_id,i_item_desc,i_current_price + FROM item i, inventory inv, date_dim d, catalog_sales cs + WHERE i.i_current_price >= 22 AND i.i_current_price <= 22 + 30 + AND inv.inv_item_sk = i.i_item_sk + AND d.d_date_sk=inv.inv_date_sk + AND date(d.d_date) >= date('2001-06-02') AND date(d.d_date) <= date('2001-08-01') + AND i.i_manufact_id in [678,964,918,849] + AND inv.inv_quantity_on_hand >= 100 AND inv.inv_quantity_on_hand <= 500 + AND cs.cs_item_sk = i.i_item_sk + GROUP BY i.i_item_id,i.i_item_desc,i.i_current_price ORDER BY i_item_id LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp index cee34eb..94d3975 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp @@ -23,7 +23,7 @@ USE tpcds; SELECT dt.d_year ,item.i_category_id ,item.i_category - ,sum(ss_ext_sales_price) + ,sum(store_sales.ss_ext_sales_price) FROM date_dim dt ,store_sales ,item @@ -35,7 +35,7 @@ SELECT dt.d_year GROUP BY dt.d_year ,item.i_category_id ,item.i_category - ORDER BY SUM(ss_ext_sales_price) desc,dt.d_year + ORDER BY SUM(store_sales.ss_ext_sales_price) desc,dt.d_year ,item.i_category_id ,item.i_category LIMIT 100 ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp index 6e77ed4..4db7758 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp @@ -21,18 +21,18 @@ USE tpcds; SELECT s_store_name, s_store_id, - SUM(CASE WHEN (d_day_name='Sunday') THEN ss_sales_price ELSE null END) sun_sales, - SUM(CASE WHEN (d_day_name='Monday') THEN ss_sales_price ELSE null END) mon_sales, - SUM(CASE WHEN (d_day_name='Tuesday') THEN ss_sales_price ELSE null END) tue_sales, - SUM(CASE WHEN (d_day_name='Wednesday') THEN ss_sales_price ELSE null END) wed_sales, - SUM(CASE WHEN (d_day_name='Thursday') THEN ss_sales_price ELSE null END) thu_sales, - SUM(CASE WHEN (d_day_name='Friday') THEN ss_sales_price ELSE null END) fri_sales, - SUM(CASE WHEN (d_day_name='Saturday') THEN ss_sales_price ELSE null END) sat_sales - FROM date_dim, store_sales, store - where d_date_sk = ss_sold_date_sk and - s_store_sk = ss_store_sk and - s_gmt_offset = -5 and - d_year = 1998 - GROUP BY s_store_name, s_store_id + SUM(CASE WHEN (d.d_day_name='Sunday') THEN ss.ss_sales_price ELSE null END) sun_sales, + SUM(CASE WHEN (d.d_day_name='Monday') THEN ss.ss_sales_price ELSE null END) mon_sales, + SUM(CASE WHEN (d.d_day_name='Tuesday') THEN ss.ss_sales_price ELSE null END) tue_sales, + SUM(CASE WHEN (d.d_day_name='Wednesday') THEN ss.ss_sales_price ELSE null END) wed_sales, + SUM(CASE WHEN (d.d_day_name='Thursday') THEN ss.ss_sales_price ELSE null END) thu_sales, + SUM(CASE WHEN (d.d_day_name='Friday') THEN ss.ss_sales_price ELSE null END) fri_sales, + SUM(CASE WHEN (d.d_day_name='Saturday') THEN ss.ss_sales_price ELSE null END) sat_sales + FROM date_dim d, store_sales ss, store s + where d.d_date_sk = ss.ss_sold_date_sk and + s.s_store_sk = ss.ss_store_sk and + s.s_gmt_offset = -5 and + d.d_year = 1998 + GROUP BY s.s_store_name, s.s_store_id ORDER BY s_store_name, s_store_id,sun_sales,mon_sales,tue_sales,wed_sales,thu_sales,fri_sales,sat_sales LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp index 83acd4a..d2a0d85 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp @@ -19,20 +19,20 @@ USE tpcds; -SELECT ca_zip, ca_county, SUM(ws_sales_price) - FROM web_sales, customer, customer_address, date_dim, item i2 - WHERE ws_bill_customer_sk = c_customer_sk - AND c_current_addr_sk = ca_address_sk - AND ws_item_sk = i2.i_item_sk - AND ( substr(ca_zip,1,5) IN ['85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792'] +SELECT ca_zip, ca_county, SUM(ws.ws_sales_price) + FROM web_sales ws, customer c, customer_address ca, date_dim d, item i2 + WHERE ws.ws_bill_customer_sk = c.c_customer_sk + AND c.c_current_addr_sk = ca.ca_address_sk + AND ws.ws_item_sk = i2.i_item_sk + AND ( substr(ca.ca_zip,1,5) IN ['85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792'] OR i2.i_item_id IN (SELECT VALUE i1.i_item_id FROM item i1 WHERE i1.i_item_sk in [2, 3, 5, 7, 11, 13, 17, 19, 23, 29] ) ) - AND ws_sold_date_sk = d_date_sk - AND d_qoy = 2 and d_year = 2000 - GROUP BY ca_zip, ca_county + AND ws.ws_sold_date_sk = d.d_date_sk + AND d.d_qoy = 2 and d.d_year = 2000 + GROUP BY ca.ca_zip, ca.ca_county ORDER BY ca_zip, ca_county LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp index 873d78b..202f579 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp @@ -20,18 +20,18 @@ USE tpcds; -SELECT c_last_name - ,c_first_name - ,ca_city - ,bought_city - ,ss_ticket_number - ,amt,profit +SELECT customer.c_last_name + ,customer.c_first_name + ,current_addr.ca_city + ,dn.bought_city + ,dn.ss_ticket_number + ,dn.amt,dn.profit FROM (SELECT ss_ticket_number ,ss_customer_sk ,ca_city bought_city - ,sum(ss_coupon_amt) amt - ,sum(ss_net_profit) profit + ,sum(store_sales.ss_coupon_amt) amt + ,sum(store_sales.ss_net_profit) profit FROM store_sales,date_dim,store,household_demographics,customer_address WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk AND store_sales.ss_store_sk = store.s_store_sk @@ -42,13 +42,13 @@ SELECT c_last_name AND date_dim.d_dow in [6,0] AND date_dim.d_year in [1999,1999+1,1999+2] AND store.s_city in ['Midway','Fairview','Fairview','Fairview','Fairview'] - GROUP BY ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city) dn,customer,customer_address current_addr - WHERE ss_customer_sk = c_customer_sk + GROUP BY store_sales.ss_ticket_number,store_sales.ss_customer_sk,store_sales.ss_addr_sk,customer_address.ca_city) dn,customer,customer_address current_addr + WHERE dn.ss_customer_sk = customer.c_customer_sk AND customer.c_current_addr_sk = current_addr.ca_address_sk - AND current_addr.ca_city != bought_city - ORDER BY c_last_name - ,c_first_name - ,ca_city - ,bought_city - ,ss_ticket_number + AND current_addr.ca_city != dn.bought_city + ORDER BY customer.c_last_name + ,customer.c_first_name + ,current_addr.ca_city + ,dn.bought_city + ,dn.ss_ticket_number LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp index 5bf8683..b7d4152 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp @@ -31,40 +31,40 @@ SELECT ,s_county ,s_state ,s_zip - ,SUM((CASE WHEN (sr_returned_date_sk - ss_sold_date_sk) <= 30 THEN 1 ELSE 0 END)) AS c30_days - ,SUM((CASE WHEN ((sr_returned_date_sk - ss_sold_date_sk) > 30 AND - (sr_returned_date_sk - ss_sold_date_sk) <= 60) THEN 1 ELSE 0 END )) AS c31_60_days - ,SUM((CASE WHEN ((sr_returned_date_sk - ss_sold_date_sk) > 60 AND - (sr_returned_date_sk - ss_sold_date_sk) <= 90) THEN 1 ELSE 0 END)) AS c61_90_days - ,SUM((CASE WHEN ((sr_returned_date_sk - ss_sold_date_sk) > 90 AND - (sr_returned_date_sk - ss_sold_date_sk) <= 120) THEN 1 ELSE 0 END)) AS c91_120_days - ,SUM((CASE WHEN (sr_returned_date_sk - ss_sold_date_sk) > 120 THEN 1 ELSE 0 END)) AS gt120_days + ,SUM((CASE WHEN (sr.sr_returned_date_sk - ss.ss_sold_date_sk) <= 30 THEN 1 ELSE 0 END)) AS c30_days + ,SUM((CASE WHEN ((sr.sr_returned_date_sk - ss.ss_sold_date_sk) > 30 AND + (sr.sr_returned_date_sk - ss.ss_sold_date_sk) <= 60) THEN 1 ELSE 0 END )) AS c31_60_days + ,SUM((CASE WHEN ((sr.sr_returned_date_sk - ss.ss_sold_date_sk) > 60 AND + (sr.sr_returned_date_sk - ss.ss_sold_date_sk) <= 90) THEN 1 ELSE 0 END)) AS c61_90_days + ,SUM((CASE WHEN ((sr.sr_returned_date_sk - ss.ss_sold_date_sk) > 90 AND + (sr.sr_returned_date_sk - ss.ss_sold_date_sk) <= 120) THEN 1 ELSE 0 END)) AS c91_120_days + ,SUM((CASE WHEN (sr.sr_returned_date_sk - ss.ss_sold_date_sk) > 120 THEN 1 ELSE 0 END)) AS gt120_days FROM - store_sales - ,store_returns - ,store + store_sales ss + ,store_returns sr + ,store s ,date_dim d1 ,date_dim d2 WHERE d2.d_year = 2000 AND d2.d_moy = 9 -AND ss_ticket_number = sr_ticket_number -AND ss_item_sk = sr_item_sk -AND ss_sold_date_sk = d1.d_date_sk -AND sr_returned_date_sk = d2.d_date_sk -AND ss_customer_sk = sr_customer_sk -AND ss_store_sk = s_store_sk +AND ss.ss_ticket_number = sr.sr_ticket_number +AND ss.ss_item_sk = sr.sr_item_sk +AND ss.ss_sold_date_sk = d1.d_date_sk +AND sr.sr_returned_date_sk = d2.d_date_sk +AND ss.ss_customer_sk = sr.sr_customer_sk +AND ss.ss_store_sk = s.s_store_sk GROUP BY - s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip + s.s_store_name + ,s.s_company_id + ,s.s_street_number + ,s.s_street_name + ,s.s_street_type + ,s.s_suite_number + ,s.s_city + ,s.s_county + ,s.s_state + ,s.s_zip ORDER BY s_store_name ,s_company_id ,s_street_number http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp index 6c3ad96..4b27365 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp @@ -23,7 +23,7 @@ USE tpcds; SELECT dt.d_year ,item.i_brand_id brand_id ,item.i_brand brand - ,SUM(ss_ext_sales_price) ext_price + ,SUM(store_sales.ss_ext_sales_price) ext_price FROM date_dim dt ,store_sales ,item @@ -35,7 +35,7 @@ SELECT dt.d_year GROUP BY dt.d_year ,item.i_brand ,item.i_brand_id - ORDER BY dt.d_year + ORDER BY d_year ,ext_price desc ,brand_id LIMIT 100 ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp index 4928de3..d30e267 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp @@ -21,13 +21,13 @@ USE tpcds; SELECT i_brand_id brand_id, i_brand brand, - SUM(ss_ext_sales_price) ext_price - FROM date_dim, store_sales, item - WHERE d_date_sk = ss_sold_date_sk - AND ss_item_sk = i_item_sk - AND i_manager_id=36 - AND d_moy=12 - AND d_year=2001 - GROUP BY i_brand, i_brand_id + SUM(ss.ss_ext_sales_price) ext_price + FROM date_dim d, store_sales ss, item i + WHERE d.d_date_sk = ss.ss_sold_date_sk + AND ss.ss_item_sk = i.i_item_sk + AND i.i_manager_id=36 + AND d.d_moy=12 + AND d.d_year=2001 + GROUP BY i.i_brand, i.i_brand_id ORDER BY ext_price desc, i_brand_id LIMIT 100 ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp index ff45e4d..638219e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp @@ -23,41 +23,41 @@ USE tpcds; WITH wss AS (SELECT d_week_seq, ss_store_sk, - SUM(CASE WHEN d_day_name='Sunday' THEN ss_sales_price ELSE null END) sun_sales, - SUM(CASE WHEN d_day_name='Monday' THEN ss_sales_price ELSE null END) mon_sales, - SUM(CASE WHEN d_day_name='Tuesday' THEN ss_sales_price ELSE null END) tue_sales, - SUM(CASE WHEN d_day_name='Wednesday' THEN ss_sales_price ELSE null END) wed_sales, - SUM(CASE WHEN d_day_name='Thursday' THEN ss_sales_price ELSE null END) thu_sales, - SUM(CASE WHEN d_day_name='Friday' THEN ss_sales_price ELSE null END) fri_sales, - SUM(CASE WHEN d_day_name='Saturday' THEN ss_sales_price ELSE null END) sat_sales - FROM store_sales,date_dim - WHERE d_date_sk = ss_sold_date_sk - GROUP BY d_week_seq,ss_store_sk + SUM(CASE WHEN d.d_day_name='Sunday' THEN ss.ss_sales_price ELSE null END) sun_sales, + SUM(CASE WHEN d.d_day_name='Monday' THEN ss.ss_sales_price ELSE null END) mon_sales, + SUM(CASE WHEN d.d_day_name='Tuesday' THEN ss.ss_sales_price ELSE null END) tue_sales, + SUM(CASE WHEN d.d_day_name='Wednesday' THEN ss.ss_sales_price ELSE null END) wed_sales, + SUM(CASE WHEN d.d_day_name='Thursday' THEN ss.ss_sales_price ELSE null END) thu_sales, + SUM(CASE WHEN d.d_day_name='Friday' THEN ss.ss_sales_price ELSE null END) fri_sales, + SUM(CASE WHEN d.d_day_name='Saturday' THEN ss.ss_sales_price ELSE null END) sat_sales + FROM store_sales ss,date_dim d + WHERE d.d_date_sk = ss.ss_sold_date_sk + GROUP BY d.d_week_seq,ss.ss_store_sk ) - SELECT s_store_name1,s_store_id1,d_week_seq1 - ,sun_sales1/sun_sales2,mon_sales1/mon_sales2 - ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2 - ,fri_sales1/fri_sales2,sat_sales1/sat_sales2 + SELECT y.s_store_name1,y.s_store_id1,y.d_week_seq1 + ,y.sun_sales1/x.sun_sales2,y.mon_sales1/x.mon_sales2 + ,y.tue_sales1/x.tue_sales2,y.wed_sales1/x.wed_sales2,y.thu_sales1/x.thu_sales2 + ,y.fri_sales1/x.fri_sales2,y.sat_sales1/x.sat_sales2 FROM - (SELECT s_store_name s_store_name1,wss.d_week_seq d_week_seq1 - ,s_store_id s_store_id1,sun_sales sun_sales1 - ,mon_sales mon_sales1,tue_sales tue_sales1 - ,wed_sales wed_sales1,thu_sales thu_sales1 - ,fri_sales fri_sales1,sat_sales sat_sales1 - FROM wss,store,date_dim d + (SELECT s.s_store_name s_store_name1,wss.d_week_seq d_week_seq1 + ,s.s_store_id s_store_id1,wss.sun_sales sun_sales1 + ,wss.mon_sales mon_sales1,wss.tue_sales tue_sales1 + ,wss.wed_sales wed_sales1,wss.thu_sales thu_sales1 + ,wss.fri_sales fri_sales1,wss.sat_sales sat_sales1 + FROM wss,store s,date_dim d WHERE d.d_week_seq = wss.d_week_seq AND - ss_store_sk = s_store_sk AND - d_month_seq >= 1185 AND d_month_seq <= 1185 + 11) y, - (SELECT s_store_name s_store_name2,wss.d_week_seq d_week_seq2 - ,s_store_id s_store_id2,sun_sales sun_sales2 - ,mon_sales mon_sales2,tue_sales tue_sales2 - ,wed_sales wed_sales2,thu_sales thu_sales2 - ,fri_sales fri_sales2,sat_sales sat_sales2 - FROM wss,store,date_dim d + wss.ss_store_sk = s.s_store_sk AND + d.d_month_seq >= 1185 AND d.d_month_seq <= 1185 + 11) y, + (SELECT s.s_store_name s_store_name2,wss.d_week_seq d_week_seq2 + ,s.s_store_id s_store_id2,wss.sun_sales sun_sales2 + ,wss.mon_sales mon_sales2,wss.tue_sales tue_sales2 + ,wss.wed_sales wed_sales2,wss.thu_sales thu_sales2 + ,wss.fri_sales fri_sales2,wss.sat_sales sat_sales2 + FROM wss,store s,date_dim d WHERE d.d_week_seq = wss.d_week_seq AND - ss_store_sk = s_store_sk AND - d_month_seq >= 1185+ 12 AND d_month_seq <= 1185 + 23) x - WHERE s_store_id1=s_store_id2 - AND d_week_seq1=d_week_seq2-52 - ORDER BY s_store_name1,s_store_id1,d_week_seq1 + wss.ss_store_sk = s.s_store_sk AND + d.d_month_seq >= 1185+ 12 AND d.d_month_seq <= 1185 + 23) x + WHERE y.s_store_id1=x.s_store_id2 + AND y.d_week_seq1=x.d_week_seq2-52 + ORDER BY y.s_store_name1,y.s_store_id1,y.d_week_seq1 LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp index 4d45927..b328964 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp @@ -21,34 +21,34 @@ USE tpcds; SELECT - SUBSTR(w_warehouse_name,1,20) - ,sm_type - ,web_name - ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) <= 30 THEN 1 ELSE 0 END) AS c30_days - ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 30 AND - (ws_ship_date_sk - ws_sold_date_sk) <= 60 THEN 1 ELSE 0 END ) AS c31_60_days - ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 60 AND - (ws_ship_date_sk - ws_sold_date_sk) <= 90 THEN 1 ELSE 0 END) AS c61_90_days - ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 90 AND - (ws_ship_date_sk - ws_sold_date_sk) <= 120 THEN 1 ELSE 0 END) AS c91_120_days - ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 120 THEN 1 ELSE 0 END) AS gt120_days + SUBSTR(w.w_warehouse_name,1,20) + ,sm.sm_type + ,web.web_name + ,SUM(CASE WHEN (ws.ws_ship_date_sk - ws.ws_sold_date_sk) <= 30 THEN 1 ELSE 0 END) AS c30_days + ,SUM(CASE WHEN (ws.ws_ship_date_sk - ws.ws_sold_date_sk) > 30 AND + (ws.ws_ship_date_sk - ws.ws_sold_date_sk) <= 60 THEN 1 ELSE 0 END ) AS c31_60_days + ,SUM(CASE WHEN (ws.ws_ship_date_sk - ws.ws_sold_date_sk) > 60 AND + (ws.ws_ship_date_sk - ws.ws_sold_date_sk) <= 90 THEN 1 ELSE 0 END) AS c61_90_days + ,SUM(CASE WHEN (ws.ws_ship_date_sk - ws.ws_sold_date_sk) > 90 AND + (ws.ws_ship_date_sk - ws.ws_sold_date_sk) <= 120 THEN 1 ELSE 0 END) AS c91_120_days + ,SUM(CASE WHEN (ws.ws_ship_date_sk - ws.ws_sold_date_sk) > 120 THEN 1 ELSE 0 END) AS gt120_days FROM - web_sales - ,warehouse - ,ship_mode - ,web_site - ,date_dim + web_sales ws + ,warehouse w + ,ship_mode sm + ,web_site web + ,date_dim d WHERE - d_month_seq >= 1212 AND d_month_seq <= 1212 + 11 -AND ws_ship_date_sk = d_date_sk -AND ws_warehouse_sk = w_warehouse_sk -AND ws_ship_mode_sk = sm_ship_mode_sk -AND ws_web_site_sk = web_site_sk + d.d_month_seq >= 1212 AND d.d_month_seq <= 1212 + 11 +AND ws.ws_ship_date_sk = d.d_date_sk +AND ws.ws_warehouse_sk = w.w_warehouse_sk +AND ws.ws_ship_mode_sk = sm.sm_ship_mode_sk +AND ws.ws_web_site_sk = web.web_site_sk GROUP BY - SUBSTR(w_warehouse_name,1,20) - ,sm_type - ,web_name -ORDER BY SUBSTR(w_warehouse_name,1,20) + SUBSTR(w.w_warehouse_name,1,20) + ,sm.sm_type + ,web.web_name +ORDER BY SUBSTR(w.w_warehouse_name,1,20) ,sm_type ,web_name LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp index 5e5833b..d227de9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp @@ -20,20 +20,20 @@ USE tpcds; - SELECT c_last_name - ,c_first_name - ,ca_city - ,bought_city - ,ss_ticket_number - ,extended_price - ,extended_tax - ,list_price + SELECT customer.c_last_name + ,customer.c_first_name + ,current_addr.ca_city + ,dn.bought_city + ,dn.ss_ticket_number + ,dn.extended_price + ,dn.extended_tax + ,dn.list_price FROM (SELECT ss_ticket_number ,ss_customer_sk ,ca_city bought_city - ,sum(ss_ext_sales_price) extended_price - ,sum(ss_ext_list_price) list_price - ,sum(ss_ext_tax) extended_tax + ,sum(store_sales.ss_ext_sales_price) extended_price + ,sum(store_sales.ss_ext_list_price) list_price + ,sum(store_sales.ss_ext_tax) extended_tax FROM store_sales ,date_dim ,store @@ -48,14 +48,14 @@ USE tpcds; household_demographics.hd_vehicle_count= 3) AND date_dim.d_year in [1999,1999+1,1999+2] AND store.s_city IN ['Midway','Fairview'] - GROUP BY ss_ticket_number - ,ss_customer_sk - ,ss_addr_sk,ca_city) dn + GROUP BY store_sales.ss_ticket_number + ,store_sales.ss_customer_sk + ,store_sales.ss_addr_sk,customer_address.ca_city) dn ,customer ,customer_address current_addr - WHERE ss_customer_sk = c_customer_sk + WHERE dn.ss_customer_sk = customer.c_customer_sk AND customer.c_current_addr_sk = current_addr.ca_address_sk - AND current_addr.ca_city != bought_city - ORDER BY c_last_name - ,ss_ticket_number + AND current_addr.ca_city != dn.bought_city + ORDER BY customer.c_last_name + ,dn.ss_ticket_number LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp index 0036755..304af8e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp @@ -20,12 +20,12 @@ USE tpcds; -SELECT c_last_name - ,c_first_name - ,c_salutation - ,c_preferred_cust_flag - ,ss_ticket_number - ,cnt FROM +SELECT c.c_last_name + ,c.c_first_name + ,c.c_salutation + ,c.c_preferred_cust_flag + ,dj.ss_ticket_number + ,dj.cnt FROM (SELECT ss_ticket_number ,ss_customer_sk ,len(djgrp) cnt @@ -41,8 +41,8 @@ SELECT c_last_name household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count ELSE null END) > 1 AND date_dim.d_year in [1998,1998+1,1998+2] AND store.s_county in ['Williamson County','Williamson County','Williamson County','Williamson County'] - GROUP BY ss_ticket_number,ss_customer_sk - GROUP AS djgrp) dj,customer - WHERE ss_customer_sk = c_customer_sk - AND cnt >= 1 AND cnt <= 5 - ORDER BY cnt desc; \ No newline at end of file + GROUP BY store_sales.ss_ticket_number,store_sales.ss_customer_sk + GROUP AS djgrp) dj,customer c + WHERE dj.ss_customer_sk = c.c_customer_sk + AND dj.cnt >= 1 AND dj.cnt <= 5 + ORDER BY dj.cnt desc; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp index f9a9a5c..0bd5649 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp @@ -21,13 +21,13 @@ USE tpcds; SELECT - c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit + c.c_last_name,c.c_first_name,substr(ms.s_city,1,30),ms.ss_ticket_number,ms.amt,ms.profit FROM (SELECT ss_ticket_number ,ss_customer_sk ,store.s_city - ,sum(ss_coupon_amt) amt - ,sum(ss_net_profit) profit + ,sum(store_sales.ss_coupon_amt) amt + ,sum(store_sales.ss_net_profit) profit FROM store_sales,date_dim,store,household_demographics WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk AND store_sales.ss_store_sk = store.s_store_sk @@ -36,7 +36,7 @@ SELECT AND date_dim.d_dow = 1 AND date_dim.d_year IN [1998,1998+1,1998+2] AND store.s_number_employees >= 200 AND store.s_number_employees <= 295 - group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer - WHERE ss_customer_sk = c_customer_sk - ORDER BY c_last_name,c_first_name,SUBSTR(s_city,1,30), profit + group by store_sales.ss_ticket_number,store_sales.ss_customer_sk,store_sales.ss_addr_sk,store.s_city) ms,customer c + WHERE ms.ss_customer_sk = c.c_customer_sk + ORDER BY c.c_last_name,c.c_first_name,SUBSTR(ms.s_city,1,30), ms.profit LIMIT 100; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp index 1dbe2ef..d3e4372 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp @@ -23,28 +23,28 @@ USE tpcds; WITH customer_total_return AS (SELECT cr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, - SUM(cr_return_amt_inc_tax) as ctr_total_return - FROM catalog_returns - ,date_dim - ,customer_address - WHERE cr_returned_date_sk = d_date_sk - AND d_year =1998 - AND cr_returning_addr_sk = ca_address_sk - GROUP BY cr_returning_customer_sk - ,ca_state ) - SELECT c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name - ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset - ,ca_location_type,ctr_total_return + SUM(cr.cr_return_amt_inc_tax) as ctr_total_return + FROM catalog_returns cr + ,date_dim d + ,customer_address ca + WHERE cr.cr_returned_date_sk = d.d_date_sk + AND d.d_year =1998 + AND cr.cr_returning_addr_sk = ca.ca_address_sk + GROUP BY cr.cr_returning_customer_sk + ,ca.ca_state ) + SELECT c.c_customer_id,c.c_salutation,c.c_first_name,c.c_last_name,ca.ca_street_number,ca.ca_street_name + ,ca.ca_street_type,ca.ca_suite_number,ca.ca_city,ca.ca_county,ca.ca_state,ca.ca_zip,ca.ca_country,ca.ca_gmt_offset + ,ca.ca_location_type,ctr1.ctr_total_return FROM customer_total_return ctr1 - ,customer_address - ,customer + ,customer_address ca + ,customer c WHERE ctr1.ctr_total_return > (SELECT VALUE (AVG(ctr2.ctr_total_return)*1.2) FROM customer_total_return ctr2 WHERE ctr1.ctr_state = ctr2.ctr_state)[0] - AND ca_address_sk = c_current_addr_sk - AND ca_state = 'IL' - AND ctr1.ctr_customer_sk = c_customer_sk - ORDER BY c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name - ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset - ,ca_location_type,ctr_total_return + AND ca.ca_address_sk = c.c_current_addr_sk + AND ca.ca_state = 'IL' + AND ctr1.ctr_customer_sk = c.c_customer_sk + ORDER BY c.c_customer_id,c.c_salutation,c.c_first_name,c.c_last_name,ca.ca_street_number,ca.ca_street_name + ,ca.ca_street_type,ca.ca_suite_number,ca.ca_city,ca.ca_county,ca.ca_state,ca.ca_zip,ca.ca_country,ca.ca_gmt_offset + ,ca.ca_location_type,ca.ctr_total_return LIMIT 100; \ No newline at end of file
