-- COLUMNSTORE IS BUGGY:
-- =====================

create table if not exists A_columnstore (
	ID int
) ENGINE=Columnstore DEFAULT CHARSET=UTF8;


create table if not exists B_columnstore (
	ID int,
    someDate datetime,
    FK_A_ID int
) ENGINE=Columnstore DEFAULT CHARSET=UTF8;

insert into A_columnstore values (1), (2);
select * from A_columnstore;

insert into B_columnstore values (1, '2020-01-01 12:00:00', 1);

select A_ID, B_ID, SOMEDATE, SOMEDATE_YEAR_DAY0, SOMEDATE_YEAR_DAY1, DAYOFYEAR(SOMEDATE_YEAR_DAY) as SOMEDATE_YEAR_DAY2, SOMEDATE_YEAR_DAY3, SOMEDATE_YEAR_DAY4 FROM
(
select 
A_COLUMNSTORE.ID A_ID,
B_COLUMNSTORE.ID B_ID,
SOMEDATE as SOMEDATE, -- COALSESCE NOT CALLED, YIELDS NULL AS EXPECTED
DAYOFYEAR(COALESCE(SOMEDATE,STR_TO_DATE('1970-01-01', '%Y-%m-%d'))) AS SOMEDATE_YEAR_DAY0, -- DOES NOT WORK, BUG!!!
DAYOFYEAR(ifnull(SOMEDATE,STR_TO_DATE('1970-01-01', '%Y-%m-%d'))) AS SOMEDATE_YEAR_DAY1, -- DOES NOT WORK, BUG!!!
COALESCE(SOMEDATE,STR_TO_DATE('1970-01-01', '%Y-%m-%d')) AS SOMEDATE_YEAR_DAY, -- WORKS, AS DAYOFYEAR IS CALLED _OUTSIDE_ ==> SOMEDATE_YEAR_DAY2
DAYOFYEAR(if(SOMEDATE is null,STR_TO_DATE('1970-01-01', '%Y-%m-%d'),SOMEDATE)) AS SOMEDATE_YEAR_DAY3, -- WORKS
case when SOMEDATE is null Then DAYOFYEAR(STR_TO_DATE('1970-01-01', '%Y-%m-%d')) ELSE DAYOFYEAR(SOMEDATE) END SOMEDATE_YEAR_DAY4 -- WORKS
FROM
A_COLUMNSTORE
		LEFT JOIN B_COLUMNSTORE B_COLUMNSTORE ON A_COLUMNSTORE.ID = B_COLUMNSTORE.FK_A_ID
) a
;


-- INNODB WORKS AS EXPECTED:
-- =========================

create table if not exists A_innodb (
	ID int
) ENGINE=innodb DEFAULT CHARSET=UTF8;


create table if not exists B_innodb (
	ID int,
    someDate datetime,
    FK_A_ID int
) ENGINE=innodb DEFAULT CHARSET=UTF8;

insert into A_innodb values (1), (2);
select * from A_innodb;

insert into B_innodb values (1, '2020-01-01 12:00:00', 1);

select A_ID, B_ID, SOMEDATE, SOMEDATE_YEAR_DAY0, SOMEDATE_YEAR_DAY1, DAYOFYEAR(SOMEDATE_YEAR_DAY) as SOMEDATE_YEAR_DAY2, SOMEDATE_YEAR_DAY3, SOMEDATE_YEAR_DAY4 FROM
(
select 
A_innodb.ID A_ID,
B_innodb.ID B_ID,
SOMEDATE as SOMEDATE, -- COALSESCE NOT CALLED, YIELDS NULL AS EXPECTED
DAYOFYEAR(COALESCE(SOMEDATE,STR_TO_DATE('1970-01-01', '%Y-%m-%d'))) AS SOMEDATE_YEAR_DAY0, -- WORKS AS EXPECTED
DAYOFYEAR(ifnull(SOMEDATE,STR_TO_DATE('1970-01-01', '%Y-%m-%d'))) AS SOMEDATE_YEAR_DAY1, -- WORKS AS EXPECTED
COALESCE(SOMEDATE,STR_TO_DATE('1970-01-01', '%Y-%m-%d')) AS SOMEDATE_YEAR_DAY, -- WORKS AS EXPECTED
DAYOFYEAR(if(SOMEDATE is null,STR_TO_DATE('1970-01-01', '%Y-%m-%d'),SOMEDATE)) AS SOMEDATE_YEAR_DAY3, -- WORKS AS EXPECTED
case when SOMEDATE is null Then DAYOFYEAR(STR_TO_DATE('1970-01-01', '%Y-%m-%d')) ELSE DAYOFYEAR(SOMEDATE) END SOMEDATE_YEAR_DAY4 -- WORKS AS EXPECTED
FROM
A_innodb
		LEFT JOIN B_innodb B_innodb ON A_innodb.ID = B_innodb.FK_A_ID
) a
;