Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16010

Too many rows with AS OF point_in_the_past_or_NULL

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            midenok Aleksey Midenkov
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: