drop table if exists memory work;
drop table if exists memory holidays;
drop table if exists memory result;
drop table if exists memory resultRowCount;
drop table if exists memory allResults;


create table memory work (
workID integer,
workDate date
);

create table memory holidays (
holidayID integer,
holidayDate date,
);


insert into memory work values (1, "2003-12-24");
insert into memory work values (2, "2003-12-25");
insert into memory work values (3, "2003-12-26");

insert into memory holidays values (1, "2003-12-25");


select workID
, workDate
, ifnull(holidays.holidayDate, false, true) as isHoliday
into memory result
from memory work
left outer join memory holidays on work.workDate = holidays.
holidayDate
where not ((holidays.holidayDate <> null) and (workID > 0));


select count(*) as rowCount
into memory resultRowCount
from memory result;


select 'Test 1' as testName
, 'not ((holidays.holidayDate <> null) and (workID > 0))' as 
whereExpession
, rowCount
, if(rowCount = 2, 'Result is OK', 
'WRONG; number of retrieved 
records should be 2 not ' + 
cast(rowCount as char(10)) ) as res
into memory allResults
from memory resultRowCOunt;

/*
-- 
-- same as above without condition (workID > 0) which does nothing
--
*/

drop table if exists memory result;
drop table if exists memory resultRowCount;

select workID
, workDate
, ifnull(holidays.holidayDate, false, true) as isHoliday
into memory result
from memory work
left outer join memory holidays on work.workDate = holidays.
holidayDate
where not ((holidays.holidayDate <> null) );


select count(*) as rowCount
into memory resultRowCount
from memory result;


insert into memory allResults
select 'Test 2' as testName
, 'not ((holidays.holidayDate <> null))' as whereExpession
, rowCount
, if(rowCount = 2, 'Result is OK', 
'WRONG; number of retrieved 
records should be 2 not ' + 
cast(rowCount as char(10)) ) as res
from memory resultRowCOunt ;


select * from memory allResults