-- source ../include/have_columnstore.inc

--disable_warnings
DROP DATABASE IF EXISTS `mcol-5476`;
--enable_warnings
CREATE DATABASE `mcol-5476`;
USE `mcol-5476`;

create table t1 (a int, b int) engine=columnstore;
insert into t1 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 2);
#prep2aggregate
sorted_result;
select sum(a), abs(b), abs(b) from t1 group by abs(b), abs(b);
sorted_result;
select sum(a), abs(b), abs(b) from t1 group by abs(b);
#prep2distinctaggregate
sorted_result;
select sum(distinct a), abs(b), abs(b) from t1 group by abs(b), abs(b);
sorted_result;
select sum(distinct a), abs(b), abs(b) from t1 group by abs(b);


create table t2 (a int, b int, c varchar(20)) engine=columnstore;
insert into t2 values (1, 1, "abc"), (2, 1, "abc"), (1, 2, "abcd"), (3, 2, "abcd");
#prep2aggregate
sorted_result;
select sum(a), abs(b), length(c), abs(b), length(c) from t2 group by abs(b), length(c);
sorted_result;
select sum(a), abs(b), abs(b), length(c), length(c) from t2 group by abs(b), length(c);
sorted_result;
select sum(a), abs(b), length(c), abs(b), length(c) from t2 group by abs(b), abs(b), length(c), length(c);
sorted_result;
select sum(a), abs(b), length(c), abs(b), length(c) from t2 group by abs(b), length(c), length(c), abs(b);
#prep2distinctaggregate
sorted_result;
select sum(distinct a), abs(b), length(c), abs(b), length(c) from t2 group by abs(b), length(c) order by abs(b);
sorted_result;
select sum(distinct a), abs(b), abs(b), length(c), length(c) from t2 group by abs(b), length(c) order by abs(b);
sorted_result;
select sum(distinct a), abs(b), length(c), abs(b), length(c) from t2 group by abs(b), abs(b), length(c), length(c);
sorted_result;
select sum(distinct a), abs(b), length(c), abs(b), length(c) from t2 group by abs(b), length(c), length(c), abs(b);

#Joins
#prep1distinctaggregate
sorted_result;
select sum(distinct t1.a), abs(t2.b), abs(t2.b) from t1 join t2 on t1.a = t2.a group by abs(t2.b);
#prep1aggregate
sorted_result;
select sum(t1.a), abs(t2.b), abs(t2.b) from t1 join t2 on t1.a = t2.a group by abs(t2.b);

#User test case
create table t3 (a datetime, b int) engine=columnstore;
insert into t3 values ("2007-01-30 21:31:07", 1), ("2007-01-30 21:31:07", 3), ("2007-01-29 21:31:07", 1), ("2007-01-29 21:31:07", 2);
sorted_result;
select distinct DAYOFWEEK(a) as C1, DAYOFWEEK(a) as C2, SUM(b) from t3 group by DAYOFWEEK(a), DAYOFWEEK(a);

DROP TABLE t1, t2, t3;
DROP DATABASE `mcol-5476`;