Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.4
-
None
Description
Note: Possibly it's the matter of documentation, but before we proceed with documenting it, it needs to be confirmed as intended behavior.
CURRENT_TIMESTAMP freezes its value upon the beginning of the statement/function/trigger execution. Given that the row end column is added automatically to the PK, it means that certain DML statements that work with non-versioned tables will become impossible with versioning. Here is basic example for a single statement, it can of course become more elaborate and less degenerate within triggers or functions.
MariaDB [test]> create table t1 (pk int primary key, i int) with system versioning; |
Query OK, 0 rows affected (0.15 sec) |
|
|
MariaDB [test]> replace into t1 values (1,10),(1,100),(1,1000); |
ERROR 1062 (23000): Duplicate entry '1-2038-01-19 05:14:07.999999' for key 'PRIMARY' |
That is, everything that removes a row which is represented by the non-"row end" part of PK more than once within a statement/function/trigger will fail.
Although, maybe in the case above the reason is actually different, not related to CURRENT_TIMESTAMP behavior as such, since it claims that it can't insert 2038-01-19 05:14:07.999999, which is just wrong. But even if it goes as expected, it will still attempt to insert (1,<current timestamp>) twice and fail.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Elena Stepanova [ elenst ] | Sergei Golubchik [ serg ] |
| Description |
_Note: Possibly it's the matter of documentation, but before we proceed with documenting it, it needs to be confirmed as intended behavior._
CURRENT_TIMESTAMP freezes its value upon the beginning of the statement/function/trigger execution. Given that the {{row end}} column is added automatically to the PK, it means that certain DML statements that work with non-versioned tables will become impossible with versioning. Here is basic example for a single statement, it can of course become more elaborate and less degenerate within triggers or functions. {code:sql} MariaDB [test]> create table t1 (pk int primary key, i int) with system versioning; Query OK, 0 rows affected (0.15 sec) MariaDB [test]> replace into t1 values (1,10),(1,100),(1,1000); ERROR 1062 (23000): Duplicate entry '1-2038-01-19 05:14:07.999999' for key 'PRIMARY' {code} That is, everything that removes a row which is represented by the non-"row end" part of PK more than once within a statement/function/trigger will fail. |
_Note: Possibly it's the matter of documentation, but before we proceed with documenting it, it needs to be confirmed as intended behavior._
CURRENT_TIMESTAMP freezes its value upon the beginning of the statement/function/trigger execution. Given that the {{row end}} column is added automatically to the PK, it means that certain DML statements that work with non-versioned tables will become impossible with versioning. Here is basic example for a single statement, it can of course become more elaborate and less degenerate within triggers or functions. {code:sql} MariaDB [test]> create table t1 (pk int primary key, i int) with system versioning; Query OK, 0 rows affected (0.15 sec) MariaDB [test]> replace into t1 values (1,10),(1,100),(1,1000); ERROR 1062 (23000): Duplicate entry '1-2038-01-19 05:14:07.999999' for key 'PRIMARY' {code} That is, everything that removes a row which is represented by the non-"row end" part of PK more than once within a statement/function/trigger will fail. Although, maybe in the case above the reason is actually different, not related to CURRENT_TIMESTAMP behavior as such, since it claims that it can't insert {{2038-01-19 05:14:07.999999}}, which is just wrong. But even if it goes as expected, it will still attempt to insert (1,<current timestamp>) twice and fail. |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Summary | Limitations that the row end as a part of PK imposes due to CURRENT_TIMESTAMP behavior | Limitations which the row end as a part of PK imposes due to CURRENT_TIMESTAMP behavior and otherwise |
| Assignee | Sergei Golubchik [ serg ] | Alexander Krizhanovsky [ krizhanovsky ] |
The first case (with REPLACE) is a bug, should be fixed. The second, with "key was too long" — that's a limitation, nothing we can do...
| Assignee | Alexander Krizhanovsky [ krizhanovsky ] | Eugene Kosov [ kevg ] |
In `tt-1.3.5` it works like this:
create table t1 (pk int primary key, i int) with system versioning; |
replace into t1 values (1,10),(1,100),(1,1000); |
select *, row_start, row_end from t1 for system_time all; |
pk i row_start row_end
|
1 1000 2018-02-14 14:22:04.358922 2038-01-19 06:14:07.999999
|
Relevant commit is https://github.com/tempesta-tech/mariadb/commit/d95c74d8280f93b73995935a5dcf2359ad375daa
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Status | Confirmed [ 10101 ] | In Review [ 10002 ] |
| Link |
This issue duplicates |
| Affects Version/s | 10.3.4 [ 22904 ] | |
| Affects Version/s | N/A [ 14700 ] |
| Fix Version/s | 10.3.5 [ 22905 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 84630 ] | MariaDB v4 [ 153458 ] |
Here is another kind of limitation which enforced addition of row end to the PK causes, also quite unexpected and without any obvious workaround:
) ENGINE=MyISAM;Adding system versioning to the table causes the error:
Same happens if versioning is added explicitly, through creating a table and a period, because even in this case row end is added to the PK, which is even stranger.