# -------------------------------------------------------------- #
# Test case migrated from Autopilot outer join test suite
#
# Author: Daniel Lee, daniel.lee@mariadb.com
# -------------------------------------------------------------- #
#
--source ../include/have_columnstore.inc
#
USE outerjoin;
#
select count(*) from lineitem join supplier on s_suppkey = l_suppkey right join nation on s_nationkey = n_nationkey;
#
select l_orderkey, sum(l_extendedprice * (1-l_discount)) as revenue,  o_orderdate,   o_shippriority
from customer right join orders on c_custkey = o_custkey right join lineitem on l_orderkey = o_orderkey
where c_mktsegment = 'BUILDING' and
o_orderdate < date '1995-03-03' and
l_shipdate > date '1995-03-03'
group by l_orderkey, o_orderdate,  o_shippriority
order by  revenue desc, o_orderdate;
#
select n_name, sum(l_extendedprice * (1-l_discount)) as revenue
from customer left join orders on c_custkey = o_custkey left join lineitem on l_orderkey = o_orderkey join supplier on l_suppkey = s_suppkey join nation on s_nationkey = n_nationkey right join region on n_regionkey = r_regionkey
where  c_nationkey = s_nationkey + 0 and
r_name = 'AFRICA' and
o_orderdate >= date '1997-01-01' and
o_orderdate < date '1997-01-01' + interval '1' year
group by n_name
order by revenue desc;
#
select  c_count,  count(*) as custdist
from  (   select    c_custkey,    count(o_orderkey) c_count
from    customer left outer join orders on     c_custkey = o_custkey
and o_comment not like '%special%requests%'
group by    c_custkey  ) c_orders
group by  c_count
order by  custdist desc,  c_count desc;
#
select supp_nation, cust_nation, l_year, sum(volume) as revenue
from
 (select n1.n_name as supp_nation, n2.n_name as cust_nation, 
date_format(o_orderdate,'%Y') as l_year, l_extendedprice * (1 - l_discount) as volume
from supplier join lineitem on s_suppkey = l_suppkey join orders on o_orderkey = l_orderkey join customer on  c_custkey = o_custkey right join nation n1 on s_nationkey = n1.n_nationkey left join nation n2 on c_nationkey = n2.n_nationkey
where
l_shipdate between date '1995-01-01' and date '2011-12-31') as shipping 
group by supp_nation, cust_nation, l_year
order by supp_nation, cust_nation, l_year;
#
select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone,  s_comment
from part right join partsupp pso on p_partkey = pso.ps_partkey left join supplier  on s_suppkey = pso.ps_suppkey left join nation on s_nationkey = n_nationkey left join region on n_regionkey = r_regionkey
where
# p_size = 15
pso.ps_supplycost = (select min(psi.ps_supplycost)
    from partsupp psi, supplier, nation, region
    where pso.ps_partkey = psi.ps_partkey
    and s_suppkey = psi.ps_suppkey
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'EUROPE')
order by s_acctbal desc,
n_name,
s_name,
p_partkey;
#