On 05/01/2020 13:50, Vik Fearing wrote: > Okay, now that I see your tests, I understand why everything is broken. > You only test FROM-TO and with a really wide interval. There are no > tests for AS OF and no tests for BETWEEN-AND.
I have started working on some better test cases for you. The attached .sql and .out tests should pass, and they are some of the tests that I'll be putting your next version through. There are many more tests that need to be added. Once all the desired functionality is there, I'll start reviewing the code itself. Keep up the good work, and let me know if I can do anything to help you. -- Vik Fearing
system_versioned_table.sql
Description: application/sql
/*
* CREATE TABLE
*/
-- invalid datatype
CREATE TABLE stest1 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS ROW START,
end_timestamp integer GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
ERROR: the data type of row end time must be timestamp
-- references to other column in period columns
CREATE TABLE stest1 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS ROW START,
end_timestamp timestamp GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (a, end_timestamp)
) WITH SYSTEM VERSIONING;
ERROR: The period start time parameter must equal the name of row start time
column
CREATE TABLE stest1 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS ROW START,
end_timestamp timestamp GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (start_timestamp, a)
) WITH SYSTEM VERSIONING;
ERROR: The period end time parameter must equal the name of row end time
column
CREATE TABLE stest1 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS ROW START,
end_timestamp timestamp GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (end_timestamp, start_timestamp)
) WITH SYSTEM VERSIONING;
ERROR: The period start time parameter must equal the name of row start time
column
-- duplicate system time column
CREATE TABLE stest1 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS row START,
start_timestamp1 timestamp GENERATED ALWAYS AS row START,
end_timestamp timestamp GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
ERROR: row start time can not be specified multiple time
CREATE TABLE stest1 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS row START,
end_timestamp timestamp GENERATED ALWAYS AS ROW END,
end_timestamp1 timestamp GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
ERROR: row end time can not be specified multiple time
-- success
CREATE TABLE stest0 (
a integer PRIMARY KEY,
start_timestamp timestamp GENERATED ALWAYS AS ROW START,
end_timestamp timestamp GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
-- default system time column usage
CREATE TABLE stest2 (
a integer
) WITH SYSTEM VERSIONING;
\d stest2
Table "public.stest2"
Column | Type | Collation | Nullable |
Default
-----------+-----------------------------+-----------+----------+-------------------------------
a | integer | | |
StartTime | timestamp without time zone | | not null | generated
always as row start
EndTime | timestamp without time zone | | not null | generated
always as row end
-- ALTER TABLE tbName ADD SYSTEM VERSIONING
CREATE TABLE stest3 (
a integer
);
\d stest3
Table "public.stest3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
ALTER TABLE stest3 ADD SYSTEM VERSIONING;
\d stest3
Table "public.stest3"
Column | Type | Collation | Nullable |
Default
-----------+-----------------------------+-----------+----------+-------------------------------
a | integer | | |
StartTime | timestamp without time zone | | not null | generated
always as row start
EndTime | timestamp without time zone | | not null | generated
always as row end
-- ALTER TABLE tbName DROP SYSTEM VERSIONING
ALTER TABLE stest3 DROP SYSTEM VERSIONING;
\d stest3
Table "public.stest3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
-- ALTER TABLE
ALTER TABLE stest0 ALTER start_timestamp DROP NOT NULL;
ERROR: column "start_timestamp" of relation "stest0" is system time column
ALTER TABLE stest0 ALTER start_timestamp DROP NOT NULL;
ERROR: column "start_timestamp" of relation "stest0" is system time column
ALTER TABLE stest0 ALTER COLUMN start_timestamp SET DATA TYPE character;
ERROR: column "start_timestamp" of relation "stest0" is system time column
--truncation
truncate table stest0;
ERROR: cannot truncate system versioned table
-- test UPDATE/DELETE
INSERT INTO stest0 VALUES (1);
INSERT INTO stest0 VALUES (2);
INSERT INTO stest0 VALUES (3);
SELECT now() AS ts1 \gset
SELECT a FROM stest0 ORDER BY a;
a
---
1
2
3
(3 rows)
SELECT a FROM stest0 FOR system_time FROM '-infinity' TO 'infinity' ORDER BY a;
a
---
1
2
3
(3 rows)
UPDATE stest0 SET a = 4 WHERE a = 1;
SELECT now() AS ts2 \gset
SELECT a FROM stest0 ORDER BY a;
a
---
2
3
4
(3 rows)
SELECT a FROM stest0 FOR system_time FROM '-infinity' TO 'infinity' ORDER BY a;
a
---
1
2
3
4
(4 rows)
DELETE FROM stest0 WHERE a = 2;
SELECT now() AS ts3 \gset
SELECT a FROM stest0 ORDER BY a;
a
---
3
4
(2 rows)
SELECT a FROM stest0 FOR system_time FROM '-infinity' TO 'infinity' ORDER BY a;
a
---
1
2
3
4
(4 rows)
INSERT INTO stest0 VALUES (5);
SELECT a FROM stest0 ORDER BY a;
a
---
3
4
5
(3 rows)
SELECT a FROM stest0 FOR system_time FROM '-infinity' TO 'infinity' ORDER BY a;
a
---
1
2
3
4
5
(5 rows)
/*
* Temporal Queries
*/
-- AS OF ...
SELECT a FROM stest0 FOR system_time AS OF :'ts1' ORDER BY start_timestamp, a;
a
---
1
2
3
(3 rows)
SELECT a FROM stest0 FOR system_time AS OF :'ts2' ORDER BY start_timestamp, a;
a
---
2
3
4
(3 rows)
SELECT a FROM stest0 FOR system_time AS OF :'ts3' ORDER BY start_timestamp, a;
a
---
3
4
(2 rows)
-- BETWEEN ... AND ...
SELECT a FROM stest0 FOR system_time BETWEEN :'ts1' AND :'ts2' ORDER BY
start_timestamp, a;
a
---
1
2
3
4
(4 rows)
SELECT a FROM stest0 FOR system_time BETWEEN :'ts2' AND :'ts3' ORDER BY
start_timestamp, a;
a
---
2
3
4
(3 rows)
SELECT a FROM stest0 FOR system_time BETWEEN :'ts1' AND :'ts3' ORDER BY
start_timestamp, a;
a
---
1
2
3
4
(4 rows)
-- BETWEEN ASYMMETRIC ... AND ...
SELECT a FROM stest0 FOR system_time BETWEEN ASYMMETRIC :'ts1' AND :'ts2' ORDER
BY start_timestamp, a;
a
---
1
2
3
4
(4 rows)
SELECT a FROM stest0 FOR system_time BETWEEN ASYMMETRIC :'ts2' AND :'ts3' ORDER
BY start_timestamp, a;
a
---
2
3
4
(3 rows)
SELECT a FROM stest0 FOR system_time BETWEEN ASYMMETRIC :'ts1' AND :'ts3' ORDER
BY start_timestamp, a;
a
---
1
2
3
4
(4 rows)
-- BETWEEN SYMMETRIC ... AND ...
SELECT a FROM stest0 FOR system_time BETWEEN SYMMETRIC :'ts2' AND :'ts1' ORDER
BY start_timestamp, a;
a
---
1
2
3
4
(4 rows)
SELECT a FROM stest0 FOR system_time BETWEEN SYMMETRIC :'ts3' AND :'ts2' ORDER
BY start_timestamp, a;
a
---
2
3
4
(3 rows)
SELECT a FROM stest0 FOR system_time BETWEEN SYMMETRIC :'ts3' AND :'ts1' ORDER
BY start_timestamp, a;
a
---
1
2
3
4
(4 rows)
-- FROM ... TO ...
SELECT a FROM stest0 FOR system_time FROM :'ts1' TO :'ts2' ORDER BY
start_timestamp, a;
a
---
1
2
3
4
(4 rows)
SELECT a FROM stest0 FOR system_time FROM :'ts2' TO :'ts3' ORDER BY
start_timestamp, a;
a
---
2
3
4
(3 rows)
SELECT a FROM stest0 FOR system_time FROM :'ts1' TO :'ts3' ORDER BY
start_timestamp, a;
a
---
1
2
3
4
(4 rows)
/*
* JOINS
*/
CREATE TABLE stestx (x int, y int);
INSERT INTO stestx VALUES (11, 1), (22, 2), (33, 3);
SELECT a, x, y
FROM stestx
INNER JOIN stest0 ON stestx.y = stest0.a;
a | x | y
---+----+---
3 | 33 | 3
(1 row)
SELECT a, x, y
FROM stestx
LEFT OUTER JOIN stest ON stestx.y = stest0.a;
a | x | y
---+----+---
| 11 | 1
| 22 | 2
3 | 33 | 3
(3 rows)
SELECT a, x, y
FROM stestx
RIGHT OUTER JOIN stest ON stestx.y = stest0.a;
a | x | y
---+----+---
3 | 33 | 3
5 | |
4 | |
(3 rows)
SELECT a, x, y
FROM stestx
FULL OUTER JOIN stest ON stestx.y = stest0.a;
a | x | y
---+----+---
| 11 | 1
| 22 | 2
3 | 33 | 3
5 | |
4 | |
(5 rows)
DROP TABLE stestx;
-- views
CREATE VIEW stest1v AS SELECT a FROM stest0;
CREATE VIEW stest2v AS select a from stest0 for system_time from '2000-01-01
00:00:00.00000' to 'infinity' ORDER BY a;
SELECT * FROM stest1v;
a
---
3
4
5
(3 rows)
SELECT * FROM stest2v;
a
---
1
2
3
4
5
(5 rows)
DROP VIEW stest1v;
DROP VIEW stest2v;
-- CTEs
WITH foo AS (SELECT a FROM stest0) SELECT * FROM foo;
a
---
3
4
5
(3 rows)
WITH foo AS (select a from stest0 for system_time from '2000-01-01
00:00:00.00000' to 'infinity' ORDER BY a) SELECT * FROM foo;
a
---
1
2
3
4
5
(5 rows)
-- inheritance
CREATE TABLE stest1 () INHERITS (stest0);
SELECT * FROM stest1;
a | start_timestamp | end_timestamp
---+-----------------+---------------
(0 rows)
\d stest1
Table "public.stest1"
Column | Type | Collation | Nullable |
Default
-----------------+-----------------------------+-----------+----------+-------------------------------
a | integer | | not null |
start_timestamp | timestamp without time zone | | not null |
generated always as row start
end_timestamp | timestamp without time zone | | not null |
generated always as row end
Inherits: stest0
INSERT INTO stest1 VALUES (4);
SELECT a FROM stest1;
a
---
4
(1 row)
