[MDEV-16010] Too many rows with AS OF point_in_the_past_or_NULL Created: 2018-04-24  Updated: 2018-05-12  Resolved: 2018-05-12

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
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.

 Comments   
Comment by Alexander Barkov [ 2018-04-24 ]

If I use a point in time in the future:

select * from t1 for system_time as of TIMESTAMP'2020-01-01 00:00:00';

it returns one row as expected:

+------+---------------+----------------------+
 
| x    | sys_trx_start | sys_trx_end          |
 
+------+---------------+----------------------+
 
|    2 |         26349 | 18446744073709551615 |
 
+------+---------------+----------------------+

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