Bug #106298 Unexpected outputs from GTID_SUBSET function
Submitted: 27 Jan 2022 1:00 Modified: 15 Feb 2022 14:34
Reporter: Yu Liang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.27, 5.7.37 OS:Ubuntu (Ubuntu 20.04.3 LTS)
Assigned to: CPU Architecture:x86 (Intel(R) Core(TM) i7-10700 CPU @ 2.90GHz)
Tags: GTID, GTID_SUBSET

[27 Jan 2022 1:00] Yu Liang
Description:
mysql> CREATE TABLE v0(c1 INT);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
Query OK, 1 row affected (0.02 sec)

mysql> SELECT GTID_SUBSET ( c1, c1 ) FROM v0;
+------------------------+
| GTID_SUBSET ( c1, c1 ) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

-- Unexpected. Should be NULL. 

mysql> SELECT GTID_SUBSET ( NULL, NULL ) FROM v0;
+----------------------------+
| GTID_SUBSET ( NULL, NULL ) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)

-- Expected. 

------------------------------------------------

In the query shown above, we create a table v0 with integer column c1. We insert a NULL value into the table. And then we call function GTID_SUBSET using column c1 as the parameters. According to the documentation,  function GTID_SUBSET should only accept global transaction identifiers (a string) as inputs. If the provided parameters are table columns or NULL values, we expect the function to return NULL. However, the GTID_SUBSET function returns 1 (True) in the execution. 

We are not sure whether this behavior is expected or not. Looking forward to further information of what is going on in this query. 

Thanks.  :-)

We are using the latest MySQL server version 8.0.27 to produce these query outputs. The document of function GTID_SUBSET is in link: https://dev.mysql.com/doc/refman/8.0/en/gtid-functions.html#function_gtid-subset. 

How to repeat:
In MySQL server version 8.0.27, use the following command: 

mysql> CREATE TABLE v0(c1 INT);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
Query OK, 1 row affected (0.02 sec)

mysql> SELECT GTID_SUBSET ( c1, c1 ) FROM v0;
+------------------------+
| GTID_SUBSET ( c1, c1 ) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

-- Unexpected. Should be NULL. 

mysql> SELECT GTID_SUBSET ( NULL, NULL ) FROM v0;
+----------------------------+
| GTID_SUBSET ( NULL, NULL ) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)

-- Expected. 

Suggested fix:
When given parameters that are not global transaction identifiers, GTID_SUBSET function should always return NULL as results. 

mysql> CREATE TABLE v0(c1 INT);
mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
mysql> SELECT GTID_SUBSET ( c1, c1 ) FROM v0;
-- Should return NULL as result. 
mysql> SELECT GTID_SUBSET ( NULL, NULL ) FROM v0;
-- Should return NULL as result.
[27 Jan 2022 5:56] MySQL Verification Team
Hello Yu Liang,

Thank you for the report and test case.

regards,
Umesh
[15 Feb 2022 14:34] Jon Stephens
Documented fix as follows in the MySQL 8.0.30 changelog:

    In some cases, when arguments other than global transaction IDs
    were passed to GTID_SUBSET(), the function returned values other
    than the expected NULL.

Closed.