[MDEV-15146] SQLError[4122]: View is not system versioned Created: 2018-01-31  Updated: 2018-10-26  Resolved: 2018-02-13

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables, Views
Affects Version/s: 10.3.4
Fix Version/s: 10.3.5

Type: Bug Priority: Major
Reporter: Benjamin Bartels Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

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



 Comments   
Comment by Elena Stepanova [ 2018-01-31 ]

Thanks for the report.

Even the literal example from the documentation doesn't work, either the manual is wrong, or something got broken.

Comment by Eugene Kosov (Inactive) [ 2018-01-31 ]

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.

Comment by Sergei Golubchik [ 2018-01-31 ]

Yes, it's non-standard (and the manual says that). But, it's not unique, as far as I remember, DB2 supports it.

Comment by Eugene Kosov (Inactive) [ 2018-01-31 ]

Should MariaDB support that?

Comment by Benjamin Bartels [ 2018-01-31 ]

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.

Comment by Sergei Golubchik [ 2018-02-01 ]

yes, I believe MariaDB should support it. This feature disappeared in 84b718ae7054bce517f7b15d1512ca831191113c as a side effect.

Generated at Thu Feb 08 08:19:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.