[MDEV-15412] For any non-existing transaction ID, AS OF provides the current table contents without a warning Created: 2018-02-24  Updated: 2019-03-29  Resolved: 2019-03-29

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.3.14, 10.4.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

bb-10.3-release 9b59f78d16fb000

Issue Links:
Duplicate
is duplicated by MDEV-16096 system versioning by trx id doesn't s... Closed

 Description   

I couldn't find it in the current tests, so don't know if it's intentional.

create or replace table t1 (i int, s bigint unsigned as row start, e bigint unsigned as row end, period for system_time(s,e)) with system versioning;
 
insert into t1 (i) values (1),(2);
 
update t1 set i = 3;


MariaDB [test]> select * from t1 for system_time all;
 
+------+-----+----------------------+
 
| i    | s   | e                    |
 
+------+-----+----------------------+
 
|    3 | 229 | 18446744073709551615 |
 
|    3 | 229 | 18446744073709551615 |
 
|    1 | 222 |                  229 |
 
|    2 | 222 |                  229 |
 
+------+-----+----------------------+
 
4 rows in set (0.00 sec)

Existing transaction, old snapshot - OK

 
MariaDB [test]> select * from t1 for system_time as of transaction 222;
 
+------+-----+-----+
 
| i    | s   | e   |
 
+------+-----+-----+
 
|    1 | 222 | 229 |
 
|    2 | 222 | 229 |
 
+------+-----+-----+
 
2 rows in set (0.00 sec)

Existing transaction, current snapshot - OK

 
MariaDB [test]> select * from t1 for system_time as of transaction 229;
 
+------+-----+----------------------+
 
| i    | s   | e                    |
 
+------+-----+----------------------+
 
|    3 | 229 | 18446744073709551615 |
 
|    3 | 229 | 18446744073709551615 |
 
+------+-----+----------------------+
 
2 rows in set (0.00 sec)

Non existing transaction (greater than latest) -- questionable

 
MariaDB [test]> select * from t1 for system_time as of transaction 230;
 
+------+-----+----------------------+
 
| i    | s   | e                    |
 
+------+-----+----------------------+
 
|    3 | 229 | 18446744073709551615 |
 
|    3 | 229 | 18446744073709551615 |
 
+------+-----+----------------------+
 
2 rows in set (0.00 sec)

Non existing transaction (less than minimal) -- not good

 
MariaDB [test]> select * from t1 for system_time as of transaction 1;
 
+------+-----+----------------------+
 
| i    | s   | e                    |
 
+------+-----+----------------------+
 
|    3 | 229 | 18446744073709551615 |
 
|    3 | 229 | 18446744073709551615 |
 
+------+-----+----------------------+
 
2 rows in set (0.00 sec)

Non existing transaction (intermediate value) -- not good

 
MariaDB [test]> select * from t1 for system_time as of transaction 227;
 
+------+-----+----------------------+
 
| i    | s   | e                    |
 
+------+-----+----------------------+
 
|    3 | 229 | 18446744073709551615 |
 
|    3 | 229 | 18446744073709551615 |
 
+------+-----+----------------------+
 
2 rows in set (0.00 sec)



 Comments   
Comment by Eugene Kosov (Inactive) [ 2018-03-29 ]

Question is about TR_table::query_sees() algorithm.

Here is my opinion. as of transaction 1 will see nothing. as of transaction big_number will see current rows. And here is the patch:

diff --git a/sql/table.cc b/sql/table.cc
 
index 4f90d429ce5..d235f37f312 100644
 
--- a/sql/table.cc
 
+++ b/sql/table.cc
 
@@ -8751,11 +8751,18 @@ bool TR_table::query_sees(bool &result, ulonglong trx_id1, ulonglong trx_id0,
 
 
   if (!commit_id1)
 
   {
 
-    if (!query(trx_id1))
 
-      return true;
 
-
 
-    commit_id1= (*this)[FLD_COMMIT_ID]->val_int();
 
-    iso_level1= iso_level();
 
+    if (query(trx_id1))
 
+    {
 
+      commit_id1= (*this)[FLD_COMMIT_ID]->val_int();
 
+      iso_level1= iso_level();
 
+    }
 
+    else
 
+    {
 
+      // User specified some random transaction id.
 
+      // Make it instant and use default isolation level for computation.
 
+      commit_id1= trx_id1;
 
+      iso_level1= static_cast<enum_tx_isolation>(thd->variables.tx_isolation);
 
+    }
 
   }
 
 
   if (!commit_id0)

Alexey(midenok) has different opinion. He says as of unknown_trx_id should be an error.

We don't know how it should work and ask you what is the correct behaviour.

Comment by Aleksey Midenkov [ 2018-07-27 ]

TRX_ID is something material. If there was a transaction, there was some TRX_ID. But unknown TRX_ID means there was no such transaction. We must not use unknown TRX_ID therefore and cannot link it to some point in time.

Comment by Sergei Golubchik [ 2018-11-12 ]

I tend to agree with midenok. A transaction id means there was a transaction with this id. If there was no such a transaction, you cannot show a view as of nonexistent transaction.

Generated at Thu Feb 08 08:21:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.