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

Wrong result when selecting from precise-versioned table

    XMLWordPrintable

Details

    • Can result in unexpected behaviour
    • Q3/2026 Server Maintenance

    Description

      Reproduce

      --source include/have_innodb.inc
       
      create or replace table t1 (
        x int primary key,
        sys_trx_start bigint(20) unsigned as row start invisible,
        sys_trx_end bigint(20) unsigned as row end invisible,
        period for system_time (sys_trx_start, sys_trx_end)
      ) with system versioning engine innodb;
       
      create or replace table t2 (
        x int primary key,
        sys_trx_start timestamp(6) as row start invisible,
        sys_trx_end timestamp(6) as row end invisible,
        period for system_time (sys_trx_start, sys_trx_end)
      ) with system versioning engine innodb;
       
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      insert into t1 values (1);
      insert into t2 values (1);
      set timestamp= unix_timestamp('2000-01-01 00:00:10');
      delete from t1;
      delete from t2;
      set timestamp= unix_timestamp('2000-01-01 00:00:20');
      insert into t1 values (2);
      insert into t2 values (2);
       
      # Bad result
      select * from t1 for system_time as of '2000-01-01 00:00:09';
      select * from t1 for system_time as of '2000-01-01 00:00:10';
       
      # Good result
      select * from t2 for system_time as of '2000-01-01 00:00:09';
      select * from t2 for system_time as of '2000-01-01 00:00:10';
       
      drop tables t1, t2;
      

      Result

      select * from t1 for system_time as of '2000-01-01 00:00:09';
      x
      2
      select * from t1 for system_time as of '2000-01-01 00:00:10';
      x
      2
      select * from t2 for system_time as of '2000-01-01 00:00:09';
      x
      1
      select * from t2 for system_time as of '2000-01-01 00:00:10';
      x
      

      Expected

      Result between timestamp and trx-id versioning must be same:

      select * from t1 for system_time as of '2000-01-01 00:00:09';
      x
      1
      select * from t1 for system_time as of '2000-01-01 00:00:10';
      x
      

      Attachments

        Activity

          People

            midenok Aleksey Midenkov
            midenok Aleksey Midenkov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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