SELECT joining non-overlapping rows when two tables are referenced
Hello! We have multiple, connected tables that change over time and we need to do queries where we want all information over a certain time period.
I wanted to use either application time or system-versioning of MariaDB for that, currently preferring system versioning since FOR PORTION OF date_period... does not seem to work with SELECT, system versioning seems like less code to write in querries.
Let's assume our database has these two tables tb1 and tb2 with the following corresponding columns and values in them:
tb1
pk fk start end
1 1 2020-01 2021-01
1 2 2021-01 inf
tb2
pk_refd val start end
1 1 2000-01 2022-01
1 2 2022-01 inf
2 4 2000-01 inf
When I do the following query:
SELECT tb1.pk, tb1.fk, tb2.val,
tb1.start_timestamp AS tb1_START, tb1.end_timestamp AS tb1_End,
tb2.start_timestamp AS tb2_START, tb2.end_timestamp AS tb2_End
FROM
tb1 FOR SYSTEM_TIME
FROM '2000-01-01 00:00:00' TO '2030-01-01 00:00:00',
tb2 FOR SYSTEM_TIME
FROM '2000-01-01 00:00:00' TO '2030-01-01 00:00:00'
WHERE tb1.fk = tb2.pk_refd;
I get:
pk fk val tb1_START tb1_End tb2_START tb2_End
1 1 1 2020-01-01 2021-01-01 2000-01-01 2022-01-01
1 1 2 2020-01-01 2021-01-01 2022-01-01 2038-01-19
1 2 3 2021-01-01 2038-01-19 2000-01-01 2038-01-19
(I deleted the exact time and just left the date for readability)
The problem is the entry 1 1 2 2020-01-01 2021-01-01 2022-01-01 2038-01-19 This is not a valid reference since the time periods of the entries don't overlap.
1. Is there some kind of overlap(period1, period2) function or some constraint, trigger or something I can define beforehand so queries like that don't return non overlapping references?
Create Table Statements:
CREATE TABLE tb1 (
pk int,
fk int,
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp),
PRIMARY KEY (pk, start_timestamp)
) WITH SYSTEM VERSIONING;
CREATE TABLE tb2 (
pk_refd int,
val varchar(255),
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp),
PRIMARY KEY(pk_refd, start_timestamp)
) WITH SYSTEM VERSIONING;
-- add foreign Key constraints
ALTER TABLE tb1
ADD CONSTRAINT FK_fk
FOREIGN KEY (fk)
REFERENCES tb2(pk_refd);
-- insert data
-- Beginning of Time
SET timestamp = UNIX_TIMESTAMP('2000-01-01 00:00:00');
INSERT INTO tb2(pk_refd, val) VALUES (1, '1');
INSERT INTO tb2(pk_refd, val) VALUES (2, '3');
-- 2. point in time
SET timestamp = UNIX_TIMESTAMP('2020-01-01 00:00:00');
INSERT INTO tb1(pk, fk) VALUES (1, 1);
-- 3. point in time
SET timestamp = UNIX_TIMESTAMP('2021-01-01 00:00:00');
UPDATE tb1 SET fk = 2 WHERE pk = 1;
-- 4. point in time
SET timestamp = UNIX_TIMESTAMP('2022-01-01 00:00:00');
Update tb2 SET val = '2' WHERE pk_refd = 1;
