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

Too many rows with AS OF point_in_the_past_or_NULL

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

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov added a comment - - edited

          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 |
          		 
          +------+---------------+----------------------+

          bar Alexander Barkov added a comment - - edited 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 | +------+---------------+----------------------+
          serg Sergei Golubchik made changes -
          Field Original Value New Value
          Assignee Alexander Krizhanovsky [ krizhanovsky ]
          serg Sergei Golubchik made changes -
          Affects Version/s 10.3 [ 22126 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.3 [ 22126 ]
          serg Sergei Golubchik made changes -
          Component/s System versioning [ 14303 ]
          krizhanovsky Alexander Krizhanovsky made changes -
          Assignee Alexander Krizhanovsky [ krizhanovsky ] Aleksey Midenkov [ midenok ]
          midenok Aleksey Midenkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          midenok Aleksey Midenkov made changes -
          Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.3.7 [ 23005 ]
          Fix Version/s 10.3 [ 22126 ]
          Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 86764 ] MariaDB v4 [ 154243 ]

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.