Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.3
-
Fix Version/s: 10.3.7
-
Component/s: Versioned Tables
-
Labels: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.