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.