Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
I create and populate a versioned trx-id based table, then I delete one record immediately:
create or replace table t1 ( |
x int, |
sys_trx_start bigint unsigned as row start, |
sys_trx_end bigint unsigned as row end, |
period for system_time (sys_trx_start, sys_trx_end) |
) with system versioning engine innodb; |
insert into t1 (x) values (1); |
insert into t1 (x) values (2); |
commit; |
delete from t1 where x=1; |
commit; |
Now I query the table:
select * from t1; |
+------+---------------+----------------------+
|
| x | sys_trx_start | sys_trx_end |
|
+------+---------------+----------------------+
|
| 2 | 26349 | 18446744073709551615 |
|
+------+---------------+----------------------+
|
Looks good. The deleted record is not seen.
Now I query the table using a point in the past, before the table was created:
select * from t1 for system_time as of TIMESTAMP'2018-01-01 00:00:00'; |
+------+---------------+----------------------+
|
| x | sys_trx_start | sys_trx_end |
|
+------+---------------+----------------------+
|
| 1 | 26346 | 26352 |
|
| 2 | 26349 | 18446744073709551615 |
|
+------+---------------+----------------------+
|
It returned both records. Looks wrong. I'd expect an empty set.
Now I query the table using NULL as the point in time:
select * from t1 for system_time as of NULL; |
+------+---------------+----------------------+
|
| x | sys_trx_start | sys_trx_end |
|
+------+---------------+----------------------+
|
| 1 | 26346 | 26352 |
|
| 2 | 26349 | 18446744073709551615 |
|
+------+---------------+----------------------+
|
It returned both records. Looks wrong. I'd expect an empty set again.