Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.4
-
None
-
Windows 10 x64
Ubuntu 17.10 x64
Description
According to the System-Versioned Table documentation you are supposed to be able to create a view from a system versioned table and work with it just like with the versioned table itself. However in the below test case I am encountering SQLError[4122] "Table 'v1' is not system versioned" when running the query.
Testcase:
DROP DATABASE IF EXISTS test;
|
CREATE DATABASE test;
|
USE test;
|
|
|
CREATE TABLE User
|
(
|
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
Name VARCHAR(20) NOT NULL
|
);
|
|
|
CREATE TABLE Product
|
(
|
Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
Name VARCHAR(20) NOT NULL,
|
Price DECIMAL UNSIGNED NOT NULL,
|
UserId INT UNSIGNED NOT NULL,
|
FOREIGN KEY(UserId) REFERENCES User(Id)
|
) WITH SYSTEM VERSIONING;
|
|
|
INSERT INTO User(Name) VALUES ("admin");
|
|
|
INSERT INTO Product(Name, Price, UserId) VALUES ("bottle", 1.0, 1);
|
UPDATE Product SET Price = 2.0 WHERE Id = 1;
|
|
|
SELECT * FROM Product;
|
SELECT Name, Price, row_start, row_end FROM Product FOR SYSTEM_TIME ALL;
|
|
|
CREATE VIEW v1 AS SELECT * FROM Product;
|
SELECT * FROM v1 FOR SYSTEM_TIME ALL; #Error here
|
Attachments
Activity
Quote from ISO/IEC 9075-2:2016(E), 7.6 <table reference>
c) If the table specified by TOQN is a system-versioned table and <query system time period specification> is not specified, then FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP is implicit.
d) If TP immediately contains <query system time period specification> QSTPS, then:
i) The table specified by TOQN shall be a system-versioned table.
IIRC this worked in past before merge to 10.3. But it was removed as not standard extension. serg knows better.
Yes, it's non-standard (and the manual says that). But, it's not unique, as far as I remember, DB2 supports it.
Well I think it could certainly be useful for ORM's without support for System Versioned tables. You could create a view of a table and map it to an object, this way you have a type safe representation of the versioning without the need for an ORM to implement explicit support.
yes, I believe MariaDB should support it. This feature disappeared in 84b718ae7054bce517f7b15d1512ca831191113c as a side effect.
Thanks for the report.
Even the literal example from the documentation doesn't work, either the manual is wrong, or something got broken.