[MDEV-31137] UUID type is never used for user variables Created: 2023-04-27  Updated: 2023-05-30  Resolved: 2023-05-30

Status: Closed
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: uuid

Issue Links:
Relates
relates to MDEV-4958 Adding datatype UUID Closed

 Description   

The UUID() function correctly returns a value of type UUID. We can easily verify this with CREATE TABLE x SELECT UUID(); and SHOW CREATE TABLE x;.

However, the value becomes VARCHAR if we try to assign it to a user variable:

MariaDB [(none)]> SET @uuid := UUID();
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> SELECT VARIABLE_TYPE FROM information_schema.user_variables WHERE VARIABLE_NAME = 'uuid'
 
;
 
+---------------+
 
| VARIABLE_TYPE |
 
+---------------+
 
| VARCHAR       |
 
+---------------+
 
1 row in set (0.000 sec)

The same happens if we try to cast the type explicitly:

MariaDB [(none)]> SET @uuid := CAST(UUID() AS UUID);
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> SELECT VARIABLE_TYPE FROM information_schema.user_variables WHERE VARIABLE_NAME = 'uuid';
 
+---------------+
 
| VARIABLE_TYPE |
 
+---------------+
 
| VARCHAR       |
 
+---------------+
 
1 row in set (0.000 sec)

This can lead to incorrect results:

-- with dashes:
 
MariaDB [(none)]> SELECT @uuid = 'fb025246-e48d-11ed-b010-56000464509c';
 
+------------------------------------------------+
 
| @uuid = 'fb025246-e48d-11ed-b010-56000464509c' |
 
+------------------------------------------------+
 
|                                              1 |
 
+------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
-- with no dashes:
 
MariaDB [(none)]> SELECT @uuid = 'fb025246e48d11edb01056000464509c';
 
+--------------------------------------------+
 
| @uuid = 'fb025246e48d11edb01056000464509c' |
 
+--------------------------------------------+
 
|                                          0 |
 
+--------------------------------------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Alexander Barkov [ 2023-04-28 ]

User variables support only four data types:

  • VARCHAR
  • INT
  • DOUBLE
  • DECIMAL

The same problem is repeatable with any other data types:

MariaDB [test]> SET @date=CURRENT_DATE();
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> SELECT VARIABLE_TYPE FROM information_schema.user_variables WHERE VARIABLE_NAME = 'date';
 
+---------------+
 
| VARIABLE_TYPE |
 
+---------------+
 
| VARCHAR       |
 
+---------------+
 
1 row in set (0.001 sec)

Notice, DATE was converted to VARCHAR.

Supporting precise data types in user variables is currently not on our todo.

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