SELECT joining non-overlapping rows when two tables are referenced - Source

Revision: 114142
User: Julian Klemm
Date: 2022-02-22 14:39

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;

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

SELECT joining non-overlapping rows when two tables are referenced - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB