create table test_user
(
    user_id bigint not null,
    is_active bit not null,
    primary key (user_id)
);

create table type_user_role
(
    type_user_role varchar(16) not null,
    primary key (type_user_role)
);

create table test_user_role
(
    user_id bigint not null,
    type_user_role varchar(16) not null,
    valid_from date not null,
    valid_to date not null,
    primary key (user_id, type_user_role, valid_from),
    constraint fk_test_user_role_test_user foreign key (user_id) references test_user(user_id),
    constraint fk_test_user_role_type_user_role foreign key (type_user_role) references type_user_role(type_user_role)
);

create table test_user_workday
(
    user_id bigint not null,
    workday_date date not null,
    primary key (user_id, workday_date),
    constraint fk_test_user_workday_test_user foreign key (user_id) references test_user(user_id)
);

insert into test_user
    (user_id, is_active)
values
    (1, 1),
    (2, 1),
    (3, 0),
    (4, 1),
    (5, 0);
    
insert into type_user_role
    (type_user_role)
values
    ('ROLE_TEST1'),
    ('ROLE_TEST2'),
    ('ROLE_TEST3');
    
insert into test_user_role
    (user_id, type_user_role, valid_from, valid_to)
values
    (1, 'ROLE_TEST1', {d'2021-07-30'}, {d'9999-12-31'}),
    (2, 'ROLE_TEST1', {d'2021-07-30'}, {d'9999-12-31'}),
    (2, 'ROLE_TEST2', {d'2021-07-30'}, {d'9999-12-31'}),
    (3, 'ROLE_TEST2', {d'2021-07-30'}, {d'9999-12-31'}),
    (3, 'ROLE_TEST3', {d'2021-07-30'}, {d'9999-12-31'}),
    (4, 'ROLE_TEST3', {d'2021-07-30'}, {d'9999-12-31'}),
    (5, 'ROLE_TEST2', {d'2021-07-30'}, {d'9999-12-31'});

insert into test_user_workday
    (user_id, workday_date)
select
    user_id, date_sub({d'2021-07-30'}, interval 1 day) as workday_date
from test_user
union
select
    user_id, {d'2021-07-30'} as orkday_date
from test_user
union
select
    user_id, date_add({d'2021-07-30'}, interval 1 day) as workday_date
from test_user;

-- all those selects should produce identical results, e.g. and empty result set
-- unfortunately the first two produce some line (the inactive users)
select *
from test_user_workday tuw
join (
    select
        tuw.user_id, tuw.workday_date
    from test_user_workday tuw
    join test_user tu
        on tuw.user_id = tu.user_id
    left join test_user_role tur
        on tu.user_id = tur.user_id
    where
        tu.is_active = 1
        and tuw.workday_date >= {d'2021-07-30'}
        and tuw.workday_date <= {d'2021-07-30'}
    group by 
        tuw.user_id, tuw.workday_date
) ud
    on tuw.user_id = ud.user_id
    and tuw.workday_date = ud.workday_date
join test_user tu
    on tuw.user_id = tu.user_id
where
    tu.is_active = 0;

select *
from test_user_workday tuw
join (
    select
        tuw.user_id, tuw.workday_date
    from test_user_workday tuw
    join test_user tu
        on tuw.user_id = tu.user_id
    left join test_user_role tur
        on tu.user_id = tur.user_id
    where
        tu.is_active = 1
        and tuw.workday_date between {d'2021-07-30'} and {d'2021-07-30'}
    group by 
        tuw.user_id, tuw.workday_date
) ud
    on tuw.user_id = ud.user_id
    and tuw.workday_date = ud.workday_date
join test_user tu
    on tuw.user_id = tu.user_id
where
    tu.is_active = 0;

-- switching to = solves the problem
select *
from test_user_workday tuw
join (
    select
        tuw.user_id, tuw.workday_date
    from test_user_workday tuw
    join test_user tu
        on tuw.user_id = tu.user_id
    left join test_user_role tur
        on tu.user_id = tur.user_id
    where
        tu.is_active = 1
        and tuw.workday_date = {d'2021-07-30'}
    group by 
        tuw.user_id, tuw.workday_date
) ud
    on tuw.user_id = ud.user_id
    and tuw.workday_date = ud.workday_date
join test_user tu
    on tuw.user_id = tu.user_id
where
    tu.is_active = 0;

-- removing the left join solves the problem, too
select *
from test_user_workday tuw
join (
    select
        tuw.user_id, tuw.workday_date
    from test_user_workday tuw
    join test_user tu
        on tuw.user_id = tu.user_id
    where
        tu.is_active = 1
    group by 
        tuw.user_id, tuw.workday_date
) ud
    on tuw.user_id = ud.user_id
    and tuw.workday_date = ud.workday_date
join test_user tu
    on tuw.user_id = tu.user_id
where
    tu.is_active = 0;
    

