[MDEV-16010] Too many rows with AS OF point_in_the_past_or_NULL - Jira

XML

Word

Printable

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.