MDEV-15001 no tests for system_versioning_innodb_algorithm_simple

Vers SQL: TRT fix getting TRX_ID by COMMIT_TS

Fixed wrong assumption that records are ordered by COMMIT_TS.
This is anyway a quick hack until tempesta-tech#314 is done.

See also FIXME and TODO in TR_table::query(MYSQL_TIME, bool).

Test: SEES case for trx_id.test [closes #456]

5 files changed, 183 insertions, 20 deletions

diff --git a/mysql-test/suite/versioning/r/commit_id.result b/mysql-test/suite/versioning/r/commit_id.result
index e2bd0f21b64..1f4c1344f7d 100644
--- a/mysql-test/suite/versioning/r/commit_id.result
+++ b/mysql-test/suite/versioning/r/commit_id.result
@@ -57,10 +57,12 @@ not vtq_trx_sees(@tx0, @tx1) as B,
 vtq_trx_sees_eq(@tx1, @tx1) as C,
 not vtq_trx_sees(@tx1, @tx1) as D,
 vtq_trx_sees(@tx2, 0) as E,
-vtq_trx_sees(0, @tx2) is null as F,
-vtq_trx_sees(-1, @tx2) as H;
-A	B	C	D	E	F	H
-1	1	1	1	1	1	1
+vtq_trx_sees(-1, @tx2) as F;
+A	B	C	D	E	F
+1	1	1	1	1	1
+select vtq_trx_sees(0, @tx2);
+vtq_trx_sees(0, @tx2)
+NULL
 set transaction isolation level read uncommitted;
 insert into t1 values ();
 select sys_trx_start from t1 where id = last_insert_id() into @tx3;
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result
index acccfc0a244..1b371ff9a34 100644
--- a/mysql-test/suite/versioning/r/trx_id.result
+++ b/mysql-test/suite/versioning/r/trx_id.result
@@ -65,4 +65,93 @@ alter table t1 drop column sys_start, drop column sys_end;
 select row_end = 18446744073709551615 as transaction_based from t1 for system_time all;
 transaction_based
 1
+# Simple vs SEES algorithms
+create or replace table t1 (
+x int,
+sys_start bigint(20) unsigned as row start invisible,
+sys_end bigint(20) unsigned as row end invisible,
+period for system_time (sys_start, sys_end)
+) with system versioning engine innodb;
+set transaction isolation level read committed;
+start transaction;
+insert into t1 values (1);
+connect  con1,localhost,root,,test;
+set transaction isolation level read committed;
+start transaction;
+insert into t1 values (2);
+connect  con2,localhost,root,,test;
+set transaction isolation level read committed;
+start transaction;
+insert into t1 values (3);
+commit;
+disconnect con2;
+connection default;
+set @ts1= sysdate(6);
+connection con1;
+commit;
+disconnect con1;
+connection default;
+set @ts2= sysdate(6);
+commit;
+set @ts3= sysdate(6);
+select sys_start from t1 where x = 1 into @trx_id1;
+select sys_start from t1 where x = 2 into @trx_id2;
+select sys_start from t1 where x = 3 into @trx_id3;
+select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3;
+@trx_id1 < @trx_id2	@trx_id2 < @trx_id3
+1	1
+select @ts1 < @ts2, @ts2 < @ts3;
+@ts1 < @ts2	@ts2 < @ts3
+1	1
+# MVCC is not resolved
+select * from t1 for system_time as of transaction @trx_id1;
+x
+1
+select * from t1 for system_time as of timestamp @ts1;
+x
+3
+select * from t1 for system_time as of transaction @trx_id2;
+x
+1
+2
+select * from t1 for system_time as of timestamp @ts2;
+x
+2
+3
+select * from t1 for system_time as of transaction @trx_id3;
+x
+1
+2
+3
+select * from t1 for system_time as of timestamp @ts3;
+x
+1
+2
+3
+set system_versioning_innodb_algorithm_simple= off;
+# MVCC is resolved
+select * from t1 for system_time as of transaction @trx_id1;
+x
+1
+2
+3
+select * from t1 for system_time as of timestamp @ts1;
+x
+3
+select * from t1 for system_time as of transaction @trx_id2;
+x
+2
+3
+select * from t1 for system_time as of timestamp @ts2;
+x
+2
+3
+select * from t1 for system_time as of transaction @trx_id3;
+x
+3
+select * from t1 for system_time as of timestamp @ts3;
+x
+1
+2
+3
 drop table t1;
diff --git a/mysql-test/suite/versioning/t/commit_id.test b/mysql-test/suite/versioning/t/commit_id.test
index 66a9db64e10..68d73416054 100644
--- a/mysql-test/suite/versioning/t/commit_id.test
+++ b/mysql-test/suite/versioning/t/commit_id.test
@@ -61,9 +61,9 @@ select
   vtq_trx_sees_eq(@tx1, @tx1) as C,
   not vtq_trx_sees(@tx1, @tx1) as D,
   vtq_trx_sees(@tx2, 0) as E,
-  vtq_trx_sees(0, @tx2) is null as F,
-  vtq_trx_sees(-1, @tx2) as H;
+  vtq_trx_sees(-1, @tx2) as F;
 
+select vtq_trx_sees(0, @tx2);
 
 # VTQ_ISO_LEVEL #
 
diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test
index bc0222b78f0..09d8690b1b7 100644
--- a/mysql-test/suite/versioning/t/trx_id.test
+++ b/mysql-test/suite/versioning/t/trx_id.test
@@ -67,4 +67,62 @@ insert into t1 values (1);
 alter table t1 drop column sys_start, drop column sys_end;
 select row_end = 18446744073709551615 as transaction_based from t1 for system_time all;
 
+--echo # Simple vs SEES algorithms
+create or replace table t1 (
+  x int,
+  sys_start bigint(20) unsigned as row start invisible,
+  sys_end bigint(20) unsigned as row end invisible,
+  period for system_time (sys_start, sys_end)
+) with system versioning engine innodb;
+
+set transaction isolation level read committed;
+start transaction;
+insert into t1 values (1);
+--connect (con1,localhost,root,,test)
+set transaction isolation level read committed;
+start transaction;
+insert into t1 values (2);
+--connect (con2,localhost,root,,test)
+set transaction isolation level read committed;
+start transaction;
+insert into t1 values (3);
+commit;
+--disconnect con2
+--connection default
+--sleep 0.01
+set @ts1= sysdate(6);
+--connection con1
+commit;
+--disconnect con1
+--connection default
+--sleep 0.01
+set @ts2= sysdate(6);
+commit;
+--sleep 0.01
+set @ts3= sysdate(6);
+
+select sys_start from t1 where x = 1 into @trx_id1;
+select sys_start from t1 where x = 2 into @trx_id2;
+select sys_start from t1 where x = 3 into @trx_id3;
+
+select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3;
+select @ts1 < @ts2, @ts2 < @ts3;
+
+--echo # MVCC is not resolved
+select * from t1 for system_time as of transaction @trx_id1;
+select * from t1 for system_time as of timestamp @ts1;
+select * from t1 for system_time as of transaction @trx_id2;
+select * from t1 for system_time as of timestamp @ts2;
+select * from t1 for system_time as of transaction @trx_id3;
+select * from t1 for system_time as of timestamp @ts3;
+
+set system_versioning_innodb_algorithm_simple= off;
+--echo # MVCC is resolved
+select * from t1 for system_time as of transaction @trx_id1;
+select * from t1 for system_time as of timestamp @ts1;
+select * from t1 for system_time as of transaction @trx_id2;
+select * from t1 for system_time as of timestamp @ts2;
+select * from t1 for system_time as of transaction @trx_id3;
+select * from t1 for system_time as of timestamp @ts3;
+
 drop table t1;
diff --git a/sql/table.cc b/sql/table.cc
index bb969b42073..4bc8cce7e31 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8692,27 +8692,41 @@ bool TR_table::query(MYSQL_TIME &commit_time, bool backwards)
                           1 /* use_record_cache */, true /* print_error */,
                           false /* disable_rr_cache */);
 
-  // With PK by transaction_id the records are ordered by PK
+  // With PK by transaction_id the records are ordered by PK, so we have to
+  // scan TRT fully and collect min (backwards == true)
+  // or max (backwards == false) stats.
   bool found= false;
+  MYSQL_TIME found_ts;
   while (!(error= info.read_record()) && !thd->killed && !thd->is_error())
   {
-    if (select->skip_record(thd) > 0)
+    int res= select->skip_record(thd);
+    if (res > 0)
     {
-      if (backwards)
-        return true;
-      found= true;
-      // TODO: (performance) make ORDER DESC and break after first found.
-      // Otherwise it is O(n) scan (+copy)!
-      store_record(table, record[1]);
+      MYSQL_TIME commit_ts;
+      if ((*this)[FLD_COMMIT_TS]->get_date(&commit_ts, 0))
+      {
+        found= false;
+        break;
+      }
+      int c;
+      if (!found || ((c= my_time_compare(&commit_ts, &found_ts)) &&
+        (backwards ? c < 0 : c > 0)))
+      {
+        found_ts= commit_ts;
+        found= true;
+        // TODO: (performance) make ORDER DESC and break after first found.
+        // Otherwise it is O(n) scan (+copy)!
+        store_record(table, record[1]);
+      }
     }
-    else
+    else if (res < 0)
     {
-      if (found)
-        restore_record(table, record[1]);
-      if (!backwards)
-        break;
+      found= false;
+      break;
     }
-  }
+ }
+  if (found)
+    restore_record(table, record[1]);
   return found;
 }
 #undef newx