[MDEV-16245] CREATE TABLE AS SELECT * does not preserve SYSTEM VERSIONING Created: 2018-05-22  Updated: 2018-07-05  Resolved: 2018-05-24

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Zdravelina Sokolovska (Inactive) Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 7.4


 Description   

CREATE TABLE AS SELECT * does not preserve SYSTEM VERSIONING

table A is SYSTEM VERSIONING , the new created table B is not SYSTEM VERSIONING

MariaDB [foo]>  CREATE TABLE fr ( c1 TIME(6), c2 DATETIME(6), c3 TIMESTAMP(6) );
 
Query OK, 0 rows affected (0.175 sec)
 
 
 
MariaDB [foo]>  INSERT INTO fr VALUES ('17:51:04.7771', '2014-09-08 17:51:04.777222222', '2014-09-08 17:51:04.777222222');
 
Query OK, 1 row affected, 2 warnings (0.046 sec)
 
 
 
MariaDB [foo]>  INSERT INTO fr VALUES ('08:55:12.000' ,'2018-05-18 08:55:12.000123',  '2014-09-08 17:51:04.77722' );
 
Query OK, 1 row affected (0.108 sec)
 



 
 
MariaDB [foo]> ALTER TABLE fr  ADD SYSTEM VERSIONING;
 
Query OK, 2 rows affected (0.733 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 



 
 
MariaDB [foo]> INSERT INTO fr VALUES ('08:55:12.111' ,'2018-05-18 08:55:12.000124',  '2014-09-08 17:51:04.1177722' );
 
Query OK, 1 row affected, 1 warning (0.045 sec)
 



MariaDB [foo]> select * ,ROW_START, ROW_END from  fr ;
 
+-----------------+----------------------------+----------------------------+----------------------------+----------------------------+
 
| c1              | c2                         | c3                         | ROW_START                  | ROW_END                    |
 
+-----------------+----------------------------+----------------------------+----------------------------+----------------------------+
 
| 17:51:04.777100 | 2014-09-08 17:51:04.777222 | 2014-09-08 17:51:04.777222 | 2018-05-22 17:15:01.559989 | 2038-01-19 05:14:07.999999 |
 
| 08:55:12.000000 | 2018-05-18 08:55:12.000123 | 2014-09-08 17:51:04.777220 | 2018-05-22 17:15:01.559989 | 2038-01-19 05:14:07.999999 |
 
| 08:55:12.111000 | 2018-05-18 08:55:12.000124 | 2014-09-08 17:51:04.117772 | 2018-05-22 17:15:41.865364 | 2038-01-19 05:14:07.999999 |
 
+-----------------+----------------------------+----------------------------+----------------------------+----------------------------+
 
3 rows in set (0.001 sec)
 



MariaDB [(none)]> create database foo1 ;
 
Query OK, 1 row affected (0.037 sec)
 
 
 
MariaDB [(none)]> create table foo1.fr  select * from  foo.fr ;
 
Query OK, 3 rows affected (0.293 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 



 
 
MariaDB [(none)]>  select * ,ROW_START, ROW_END from  foo1.fr ;
 
ERROR 1054 (42S22): Unknown column 'ROW_START' in 'field list'
 



 
 
MariaDB [(none)]> show create table  foo1.fr ;
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                      |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| fr    | CREATE TABLE `fr` (
 
  `c1` time(6) DEFAULT NULL,
 
  `c2` datetime(6) DEFAULT NULL,
 
  `c3` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 



MariaDB [(none)]>  show create table  foo.fr ;
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                             |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| fr    | CREATE TABLE `fr` (
 
  `c1` time(6) DEFAULT NULL,
 
  `c2` datetime(6) DEFAULT NULL,
 
  `c3` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Sergei Golubchik [ 2018-05-24 ]

SQL Standard (part 2, 11.3 <table definition>) says:

c) If WITH <system versioning clause> is specified and a <table period definition> that specifies SYSTEM_TIME is specified, then a system-versioned table is defined.
d) Otherwise, a regular persistent base table is defined.

So when WITH SYSTEM VERSIONING is not specified, the table must not be system-versioned table.

Comment by Zdravelina Sokolovska (Inactive) [ 2018-07-05 ]

Actually the 1st table is specified WITH SYSTEM VERSIONING .
So the seconds table has to be the same as the first.
The statement however does not preserve SYSTEM VERSIONING
CREATE TABLE B SELECT * from A ;

MariaDB [ffr]> show create table fr ;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

fr CREATE TABLE `fr` (
`c1` time(6) DEFAULT NULL,
`c2` datetime(6) DEFAULT NULL,
`c3` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 *WITH SYSTEM VERSIONING 		*
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [ffr]> create table sheet select * from fr ;
Query OK, 2 rows affected (0.188 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [ffr]> show create table sheet;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

sheet CREATE TABLE `sheet` (
`c1` time(6) DEFAULT NULL,
`c2` datetime(6) DEFAULT NULL,
`c3` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

*WITH SYSTEM VERSIONING should be explicitly specified in the statement
CREATE TARGET_TABLE SELECT * from SOURCE_TABLE
Although the SOURCE_TABLE is WITH SYSTEM VERSIONING*

MariaDB [ffr]> create table sheet1 WITH SYSTEM VERSIONING select * from fr ;
Query OK, 2 rows affected (0.162 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [ffr]> show create table sheet1;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

sheet1 CREATE TABLE `sheet1` (
`c1` time(6) DEFAULT NULL,
`c2` datetime(6) DEFAULT NULL,
`c3` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [ffr]>

Comment by Zdravelina Sokolovska (Inactive) [ 2018-07-05 ]

*h3. Problem Summary *
Actually the 1st table is specified WITH SYSTEM VERSIONING .
So the seconds table has to be the same as the first.
The statement however does not preserve SYSTEM VERSIONING
CREATE TABLE B SELECT * from A ;

*TO Workaround the issue *
WITH SYSTEM VERSIONING should be explicitly specified in the statement
CREATE TARGET_TABLE SELECT * from SOURCE_TABLE
Although the SOURCE_TABLE is WITH SYSTEM VERSIONING

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