# -------------------------------------------------------------- #
# Test case migrated from regression test suite: MCOL-3485.sql
#
# Author: Daniel Lee, daniel.lee@mariadb.com
# -------------------------------------------------------------- #
#
--source ../include/have_columnstore.inc
#
USE tpch1;
#
--disable_warnings
drop table if exists t;
--enable_warnings

create table t(a int, b varchar(10), c decimal(7,2)) engine=columnstore;
insert into t(a,b,c) values (1,'x',10),(2,'x',11), (3, 'x', 12), (1,'y',12),(2,'y',11), (3, 'y', 10);
 
#-- order by bu
select a as bu, 
sum(case when b='x' then c else null end) as 'x',
sum(case when b='y' then c else null end) as 'y'
from t
group by bu
order by bu;

#--order by x
select
a as bu, 
sum(case when b='x' then c else null end) as 'x',
sum(case when b='y' then c else null end) as 'y'
from t
group by bu
order by x;

#-- order by y
select
a as bu, 
sum(case when b='x' then c else null end) as 'x',
sum(case when b='y' then c else null end) as 'y'
from t
group by bu
order by y;

#-- order by 2nd col, no alias
select
a as bu, 
sum(case when b='x' then c else null end) as '',
sum(case when b='y' then c else null end) as 'y'
from t
group by bu
order by 2;

#-- order by 3rd col, no alias
select
a as bu, 
sum(case when b='x' then c else null end) as 'x',
sum(case when b='y' then c else null end) as 'y'
from t
group by bu
order by 3;

--disable_warnings
drop table if exists t;
--enable_warnings
#