You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-29 08:21:15 +03:00
4c9d6e39ac
* Restructured test suites and added autopilot and extended suites * Updated autopilot with correct branch - develop * Moved setup test case to a 'setup' directory, for consistency * Fixed a path issue * Updated some tests cases to keep up with development Co-authored-by: root <root@rocky8.localdomain>
64 lines
2.8 KiB
Plaintext
Executable File
64 lines
2.8 KiB
Plaintext
Executable File
# -------------------------------------------------------------- #
|
|
# 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;
|
|
#
|