Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.6
-
None
-
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)
|
|