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

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

select * from test_timestamps_innodb;

select count(*) from test_timestamps_innodb where YEAR(someDateTime) = 2022;
-- 1, correct

drop view test_timestamps_innodb_view;
create or replace view test_timestamps_innodb_view as 
select 
someString, NVL(someDateTime, str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')) as someDateTime, someInt
from 
test_timestamps_innodb
;

select * from test_timestamps_innodb_view;

select count(*) from test_timestamps_innodb_view where YEAR(someDateTime) = 2022;
-- 1, correct

select count(*) from test_timestamps_innodb_view where YEAR(someDateTime) = 1970;
-- 1, 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', null, 1),
('xyz', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1);

select * from test_timestamps_columnstore;

select count(*) from test_timestamps_columnstore where YEAR(someDateTime) = 2022;
-- 1, correct

drop view test_timestamps_columnstore_view;
create or replace view test_timestamps_columnstore_view as 
select 
someString, NVL(someDateTime, str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')) as someDateTime, someInt
from 
test_timestamps_columnstore
;

select * from test_timestamps_columnstore_view;

select count(*) from test_timestamps_columnstore_view where YEAR(someDateTime) = 2022;
-- 1, correct

select count(*) from test_timestamps_columnstore_view where YEAR(someDateTime) = 1970;
-- 0, INCORRECT !!!