-- INNODB: everything correct
drop table if exists test_timestamps_innodb;
create table if not exists test_timestamps_innodb
(
	someString varchar(1000),
    someDateTime datetime,
    someInt int
) ENGINE=InnoDB;

insert into test_timestamps_innodb values
('bla', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1),
('xyz', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1);

select * from test_timestamps_innodb;

select 
test_timestamps_innodb.someString as c0,
test_timestamps_innodb.someDateTime as c1,
sum(test_timestamps_innodb.someInt) as m0
from test_timestamps_innodb
where test_timestamps_innodb.someDateTime = TIMESTAMP '2022-09-22 00:00:00.0' 
group by test_timestamps_innodb.someString, test_timestamps_innodb.someDateTime; 
-- ==> two rows, correct

drop view if exists test_timestamps_innodb_view;
create view if not exists test_timestamps_innodb_view as 
select 
someString as someString,
case when someDateTime is null then str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') else someDateTime end as someDateTime,
someInt
from test_timestamps_innodb;


select 
test_timestamps_innodb_view.someString as c0,
test_timestamps_innodb_view.someDateTime as c1,
sum(test_timestamps_innodb_view.someInt) as m0
from test_timestamps_innodb_view
where test_timestamps_innodb_view.someDateTime = TIMESTAMP '2022-09-22 00:00:00.0'
group by test_timestamps_innodb_view.someString, test_timestamps_innodb_view.someDateTime; 
-- ==> two rows correct


-- COLUMNSTORE
drop table if exists test_timestamps_columnstore;
create table if not exists test_timestamps_columnstore (
	someString varchar(1000),
    someDateTime datetime,
    someInt int
) ENGINE=Columnstore DEFAULT CHARSET=utf8;

insert into test_timestamps_columnstore values
('bla', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1),
('xyz', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1);

select * from test_timestamps_columnstore;

select 
test_timestamps_columnstore.someString as c0,
test_timestamps_columnstore.someDateTime as c1,
sum(test_timestamps_columnstore.someInt) as m0
from test_timestamps_columnstore
where test_timestamps_columnstore.someDateTime = TIMESTAMP '2022-09-22 00:00:00.0' 
group by test_timestamps_columnstore.someString, test_timestamps_columnstore.someDateTime; 
-- ==> two rows correct

drop view if exists test_timestamps_columnstore_view;
create view if not exists test_timestamps_columnstore_view as 
select 
someString as someString,
case when someDateTime is null then str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') else someDateTime end as someDateTime,
someInt
from test_timestamps_columnstore;

select 
test_timestamps_columnstore_view.someString as c0,
test_timestamps_columnstore_view.someDateTime as c1,
sum(test_timestamps_columnstore_view.someInt) as m0
from test_timestamps_columnstore_view
where test_timestamps_columnstore_view.someDateTime = TIMESTAMP '2022-09-22 00:00:00.0'
group by test_timestamps_columnstore_view.someString, test_timestamps_columnstore_view.someDateTime; 
-- empty set ==> ERROR !!!!