Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15146

SQLError[4122]: View is not system versioned

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.4
    • 10.3.5
    • Versioned Tables, Views
    • 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

          Ascending order - Click to sort in descending order
          bbartels Benjamin Bartels created issue -
          bbartels Benjamin Bartels made changes -
          Field Original Value New Value
          Description According to the [System-Versioned Table documentation|https://mariadb.com/kb/en/library/system-versioned-tables/] you are supposed to be able to create a view form a system versioned table and work with it just like with the versioned table itself. However in the below Testcase I am encountering SQLError[4122] when running the query.

          Testcase:
          {code:SQL}
          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
          {code}
          		According to the [System-Versioned Table documentation|https://mariadb.com/kb/en/library/system-versioned-tables/] you are supposed to be able to create a view form a system versioned table and work with it just like with the versioned table itself. However in the below Testcase I am encountering SQLError[4122] "Table 'v1' is not system versioned" when running the query.

          Testcase:
          {code:SQL}
          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
          {code}
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova made changes -
          Fix Version/s 10.3 [ 22126 ]
          Assignee Alexander Krizhanovsky [ krizhanovsky ]
          serg Sergei Golubchik made changes -
          Assignee Alexander Krizhanovsky [ krizhanovsky ] Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.13 [ 228 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.13 [ 228 ] 10.2.13, 10.3.5-1 [ 228, 229 ]
          serg Sergei Golubchik made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.3.5 [ 22905 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.13, 10.3.5-1 [ 228, 229 ]
          bbartels Benjamin Bartels made changes -
          Description According to the [System-Versioned Table documentation|https://mariadb.com/kb/en/library/system-versioned-tables/] you are supposed to be able to create a view form a system versioned table and work with it just like with the versioned table itself. However in the below Testcase I am encountering SQLError[4122] "Table 'v1' is not system versioned" when running the query.

          Testcase:
          {code:SQL}
          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
          {code}
          		According to the [System-Versioned Table documentation|https://mariadb.com/kb/en/library/system-versioned-tables/] 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:
          {code:SQL}
          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
          {code}
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 85338 ] MariaDB v4 [ 153700 ]

          People

            serg Sergei Golubchik
            bbartels Benjamin Bartels
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.