create schema uint_agg;
set current_schema to 'uint_agg';

--uint1
create table u1(a uint1, b int2);
insert into u1 values(null, null),(255, 255),(0, 0),(255, 255),(null, null);
select avg(a), avg(b) from u1;
select bit_and(a), bit_and(b) from u1;
select bit_or(a), bit_or(b) from u1;
select count(a), count(b) from u1;
select count(distinct a), count(distinct b) from u1;
select max(a), max(b) from u1;
select min(a), min(b) from u1;
select stddev(a), stddev(b) from u1;
select stddev_pop(a), stddev_pop(b) from u1;
select stddev_samp(a), stddev_samp(b) from u1;
select sum(a), sum(b) from u1;
select var_pop(a), var_pop(b) from u1;
select var_samp(a), var_samp(b) from u1;
select variance(a), variance(b) from u1;
select listagg(a) within group(order by a) from u1;
select listagg(a, ',') within group(order by a) from u1;

--uint2
create table u2(a uint2, b int4);
insert into u2 values(null, null),(65535, 65535),(0, 0),(65535, 65535),(null, null);
select avg(a), avg(b) from u2;
select bit_and(a), bit_and(b) from u2;
select bit_or(a), bit_or(b) from u2;
select count(a), count(b) from u2;
select count(distinct a), count(distinct b) from u2;
select max(a), max(b) from u2;
select min(a), min(b) from u2;
select stddev(a), stddev(b) from u2;
select stddev_pop(a), stddev_pop(b) from u2;
select stddev_samp(a), stddev_samp(b) from u2;
select sum(a), sum(b) from u2;
select var_pop(a), var_pop(b) from u2;
select var_samp(a), var_samp(b) from u2;
select variance(a), variance(b) from u2;
select listagg(a) within group(order by a) from u2;
select listagg(a, ',') within group(order by a) from u2;

--uint4
create table u4(a uint4, b int8);
insert into u4 values(null, null),(4294967295, 4294967295),(0, 0),(4294967295, 4294967295),(null, null);
select avg(a), avg(b) from u4;
select bit_and(a), bit_and(b) from u4;
select bit_or(a), bit_or(b) from u4;
select count(a), count(b) from u4;
select count(distinct a), count(distinct b) from u4;
select max(a), max(b) from u4;
select min(a), min(b) from u4;
select stddev(a), stddev(b) from u4;
select stddev_pop(a), stddev_pop(b) from u4;
select stddev_samp(a), stddev_samp(b) from u4;
select sum(a), sum(b) from u4;
select var_pop(a), var_pop(b) from u4;
select var_samp(a), var_samp(b) from u4;
select variance(a), variance(b) from u4;
select listagg(a) within group(order by a) from u4;
select listagg(a, ',') within group(order by a) from u4;

--uint8
create table u8(a uint8, b numeric(64, 0));
insert into u8 values(null, null),(18446744073709551615, 18446744073709551615),(0, 0),(18446744073709551615, 18446744073709551615),(null, null);
select avg(a), avg(b) from u8;
select bit_and(a) from u8;
select bit_or(a) from u8;
select count(a), count(b) from u8;
select count(distinct a), count(distinct b) from u8;
select max(a), max(b) from u8;
select min(a), min(b) from u8;
select stddev(a), stddev(b) from u8;
select stddev_pop(a), stddev_pop(b) from u8;
select stddev_samp(a), stddev_samp(b) from u8;
select sum(a), sum(b) from u8;
select var_pop(a), var_pop(b) from u8;
select var_samp(a), var_samp(b) from u8;
select variance(a), variance(b) from u8;
select listagg(a) within group(order by a) from u8;
select listagg(a, ',') within group(order by a) from u8;

create table smp_test(a uint4, b uint8);
insert into smp_test select generate_series(1, 100000), generate_series(2, 100001);
insert into smp_test select a + 100000, b from smp_test;

-- varbinary test
set dolphin.sql_mode = '';
drop table if exists t;
CREATE TABLE t(i INT, li BIGINT, b VARBINARY(8), lb VARBINARY(24), d DOUBLE GENERATED ALWAYS AS (i*1.0));
INSERT INTO t(i, li, b, lb) VALUES
(NULL, NULL, NULL, NULL),
(0, 0, X'0000000000000000',
X'000000000000000000000000000000000000000000000000'),
(0, 0, X'0000000000000000',
X'000000000000000000000000000000000000000000000000'),
(1, 1, X'0000000000000001',
X'000000000000000000000000000000000000000000000001'),
(1, 1, X'0000000000000001',
X'000000000000000000000000000000000000000000000001'),
(2, 2, X'0000000000000002',
X'000000000000000000000000000000000000000000000002'),
(2, 2, X'0000000000000002',
X'000000000000000000000000000000000000000000000002'),
(3, 3, X'0000000000000003',
X'000000000000000000000000000000000000000000000003'),
(3, 3, X'0000000000000003',
X'000000000000000000000000000000000000000000000003'),
(4, 4, X'0000000000000004',
X'000000000000000000000000000000000000000000000004'),
(4, 4, X'0000000000000004',
X'000000000000000000000000000000000000000000000004'),
(5, 5, X'0000000000000005',
X'000000000000000000000000000000000000000000000005'),
(6, 6, X'0000000000000006',
X'000000000000000000000000000000000000000000000006'),
(X'01234567', X'0123456789ABCDEF', X'0123456789ABCDEF',
X'000000000000000000000000000000000123456789ABCDEF'),
(X'01200567', X'0120056789ABCDEF', X'0120056789ABCDEF',
X'0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF'),
(X'01200567', X'0120056789ABCDEF', X'0120056789ABCDEF',
X'0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF'),
(X'01200117', X'0120011789ABCDEF', X'0120011789ABCDEF',
X'0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF');

SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
HEX(BIT_AND(i) OVER w), HEX(BIT_AND(li) OVER w),
HEX(BIT_AND(b) OVER w), HEX(BIT_AND(lb) OVER w),
SUM(d) OVER w AS dsum
FROM t
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
drop table t;
reset dolphin.sql_mode;

set query_dop = 2;
explain(costs off, verbose) select avg(a), avg(b) from smp_test;
explain(costs off, verbose) select bit_and(a), bit_and(b) from smp_test;
explain(costs off, verbose) select bit_or(a), bit_or(b) from smp_test;
explain(costs off, verbose) select count(a), count(b) from smp_test;
explain(costs off, verbose) select count(distinct a), count(distinct b) from smp_test;
explain(costs off, verbose) select max(a), max(b) from smp_test;
explain(costs off, verbose) select min(a), min(b) from smp_test;
explain(costs off, verbose) select stddev(a), stddev(b) from smp_test;
explain(costs off, verbose) select stddev_pop(a), stddev_pop(b) from smp_test;
explain(costs off, verbose) select stddev_samp(a), stddev_samp(b) from smp_test;
explain(costs off, verbose) select sum(a), sum(b) from smp_test;
explain(costs off, verbose) select var_pop(a), var_pop(b) from smp_test;
explain(costs off, verbose) select var_samp(a), var_samp(b) from smp_test;
explain(costs off, verbose) select variance(a), variance(b) from smp_test;
explain(costs off, verbose) select listagg(a) within group(order by a) from smp_test;
explain(costs off, verbose) select listagg(a, ',') within group(order by a) from smp_test;

drop schema uint_agg cascade;
reset current_schema;