-- this describes some weird behaviour of the temporal tables in mariadb 15.1

-- standard tables yield the expected result
create or replace table x (id Int);
create or replace table x_p (elementId Int, pkey varchar(20), pvalue varchar(20));

insert into x values (1), (2), (3);
insert into x_p values (1, 'gender', 'male');
insert into x_p values (2, 'gender', 'female');
insert into x_p values (3, 'gender', 'male');

drop table if exists tmp1;

create temporary table tmp1
    select xgender.pvalue as gender, xtitle.pvalue as title
    from x
        left join x_p as xgender on x.id = xgender.elementId and xgender.pkey = 'gender'
        left join x_p as xtitle on x.id = xtitle.elementId and xtitle.pkey = 'title';

select * from tmp1;

-- results in
--
-- gender | title
-- -------+------
-- male	  | NULL
-- female | NULL
-- male   |	NULL

-- bi-temporal tables get weird
create or replace table t (
    id INT,
    date_1 DATE,
    date_2 DATE,
    row_start TIMESTAMP(6) AS ROW START INVISIBLE,
    row_end TIMESTAMP(6) AS ROW END INVISIBLE,
    PERIOD FOR application_time(date_1, date_2),
    PERIOD FOR system_time(row_start, row_end))
    WITH SYSTEM VERSIONING;

create or replace table t_p (
    elementId INT,
    pkey VARCHAR(20),
    pvalue VARCHAR(20),
    date_1 DATE,
    date_2 DATE,
    row_start TIMESTAMP(6) AS ROW START INVISIBLE,
    row_end TIMESTAMP(6) AS ROW END INVISIBLE,
    PERIOD FOR application_time(date_1, date_2),
    PERIOD FOR system_time(row_start, row_end))
    WITH SYSTEM VERSIONING;

insert into t values (1, DATE('1000-01-01'), DATE('9999-01-01')), (2, DATE('1000-01-01'), DATE('9999-01-01')), (3,  DATE('1000-01-01'), DATE('9999-01-01'));
insert into t_p values (1, 'gender', 'female', DATE('1000-01-01'), DATE('9999-01-01'));
insert into t_p values (2, 'gender', 'male', DATE('1000-01-01'), DATE('9999-01-01'));
insert into t_p values (3, 'gender', 'female', DATE('1000-01-01'), DATE('9999-01-01'));

drop table if exists tmp2;

create temporary table tmp2
    select tgender.pvalue as gender, ttitle.pvalue as title
    from t
        left join t_p as tgender on t.id = tgender.elementId and tgender.pkey = 'gender'
        left join t_p as ttitle on t.id = ttitle.elementId and ttitle.pkey = 'title';

select * from tmp2;

-- result: empty set
