-- COLUMNSTORE:
-- -- Integer:
create table null_avg_test_cs (
	some_int int
) ENGINE=Columnstore;

insert into null_avg_test_cs values (1), (2), (3), (4);

SELECT DISTINCT
   AVG(case when some_int = 4 then null else some_int end)
FROM null_avg_test_cs;
-- 2: correct.

-- -- datetime:
create table null_avg_test_date_cs (
	some_timestamp datetime
) ENGINE=Columnstore;

insert into null_avg_test_date_cs values ("2021-01-01 00:00:00"), ("2022-01-01 00:00:00"), ("2023-01-01 00:00:00"), ("2024-01-01 00:00:00");

SELECT DISTINCT
   AVG(case when Year(some_timestamp)=2024 then null else Year(some_timestamp) end)
FROM null_avg_test_date_cs;
-- 999.999, INCORRECT!!!

-- COLUMNSTORE WITH NULL DIRECTLY STORED:
-- -- datetime:
create table null_avg_test_date_cs2 (
	some_timestamp datetime
) ENGINE=Columnstore;

insert into null_avg_test_date_cs2 values ("2021-01-01 00:00:00"), ("2022-01-01 00:00:00"), ("2023-01-01 00:00:00"), (null);

SELECT DISTINCT
   AVG(YEAR(some_timestamp))
FROM null_avg_test_date_cs2;
-- 999.999, INCORRECT!!!



-- INNODB:
-- -- datetime:
create table null_avg_test_date_innodb (
	some_timestamp datetime
) ENGINE=InnoDB;

insert into null_avg_test_date_innodb values ("2021-01-01 00:00:00"), ("2022-01-01 00:00:00"), ("2023-01-01 00:00:00"), ("2024-01-01 00:00:00");

SELECT DISTINCT
   AVG(case when Year(some_timestamp)=2024 then null else Year(some_timestamp) end)
FROM null_avg_test_date_innodb;
-- 2022, correct.
