[MDEV-15412] For any non-existing transaction ID, AS OF provides the current table contents without a warning - Jira

Elena Stepanova created issue - 2018-02-24 19:10

Elena Stepanova made changes - 2018-02-24 19:11

Field	Original Value	New Value
Description	{code:sql} 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; {code} {code:sql} 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) {code} {code:sql\|title=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) {code} {code:sql\|title=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) {code} {code:sql\|title=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) {code} {code:sql\|title=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) {code}	I couldn't find it in the current tests, so don't know if it's intentional. {code:sql} 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; {code} {code:sql} 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) {code} {code:sql\|title=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) {code} {code:sql\|title=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) {code} {code:sql\|title=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) {code} {code:sql\|title=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) {code}

Elena Stepanova made changes - 2018-02-24 19:12

Description

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

{code:sql}
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;
{code}
{code:sql}
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)
{code}

{code:sql|title=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)
{code}
{code:sql|title=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)
{code}

{code:sql|title=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)
{code}

{code:sql|title=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)
{code}

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

{code:sql}
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;
{code}
{code:sql}
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)
{code}

{code:sql|title=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)
{code}
{code:sql|title=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)
{code}

{code:sql|title=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)
{code}

{code:sql|title=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)
{code}

{code:sql|title=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)
{code}

Eugene Kosov (Inactive) made changes - 2018-03-28 13:53

Assignee

Sergei Golubchik [ serg ]

Eugene Kosov [ kevg ]

Eugene Kosov (Inactive) made changes - 2018-03-28 13:55

Status

Open [ 1 ]

In Progress [ 3 ]

Eugene Kosov (Inactive) added a comment - 2018-03-29 16:42

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.

Eugene Kosov (Inactive) added a comment - 2018-03-29 16:42 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.

Axel Schwenke made changes - 2018-05-07 10:54

Link

This issue is duplicated by ~~MDEV-16096~~ [ ~~MDEV-16096~~ ]

Eugene Kosov (Inactive) made changes - 2018-06-19 07:43

Assignee

Eugene Kosov [ kevg ]

Sergei Golubchik [ serg ]

Aleksey Midenkov added a comment - 2018-07-27 22:05

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.

Aleksey Midenkov added a comment - 2018-07-27 22:05 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.

Aleksey Midenkov made changes - 2018-07-27 22:13

Assignee

Sergei Golubchik [ serg ]

Aleksey Midenok [ midenok ]

Aleksey Midenkov made changes - 2018-07-27 22:13

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Aleksey Midenkov made changes - 2018-07-30 12:37

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2018-07-30 16:55

Assignee	Aleksey Midenok [ midenok ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik added a comment - 2018-11-12 12:53

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.

Sergei Golubchik added a comment - 2018-11-12 12:53 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.

Sergei Golubchik made changes - 2018-11-12 12:53

Assignee	Sergei Golubchik [ serg ]	Aleksey Midenok [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2018-12-21 10:15

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2018-12-25 08:33

Assignee	Aleksey Midenok [ midenok ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2019-03-26 16:40

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2019-03-29 11:51

Fix Version/s		10.3.14 [ 23216 ]
Fix Version/s		10.4.4 [ 23310 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 85743 ]

MariaDB v4 [ 153863 ]

MariaDB Server

For any non-existing transaction ID, AS OF provides the current table contents without a warning

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration