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, Q3/2026 Server Development

    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

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 15d
                  15d
                  Remaining:
                  Time Spent - 14d 7h Remaining Estimate - 1h
                  1h
                  Logged:
                  Time Spent - 14d 7h Remaining Estimate - 1h
                  14d 7h

                  Git Integration

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