blob: aecafb4e03231cf93838789f8bf419a58e9db57e [file] [log] [blame]
/*
* 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.
*/
/*
* Description : This test case is to verify the fix for issue785
* https://code.google.com/p/asterixdb/issues/detail?id=785
* Expected Res : SUCCESS
* Date : 2nd Oct. 2014
*/
drop database tpch if exists;
create database tpch;
use tpch;
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 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 Customer(CustomerType) primary key c_custkey;
create table SelectedNation(NationType) primary key n_nationkey;
with t as (
select element {'n_nationkey':nation.n_nationkey,'n_name':nation.n_name}
from Nation as nation,
SelectedNation as sn
where (nation.n_nationkey = sn.n_nationkey)
),
X as (
select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':sum}
from t as n,
Customer as customer,
Orders as orders
where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
with sum as tpch.sum((
select element o.o_totalprice
from orders as o
))
)
select element {'nation_key':nation_key,'sum_price':(
select element {'orderdate':y.order_date,'sum_price':y.sum_price}
from x as y
order by y.sum_price desc
limit 3
)}
from X as x
group by x.nation_key as nation_key
;