[#MDEV-31882] IFNULL('a', UUID()) returns an UUID

Comment by Claudio Friizziero [ 2023-08-09 ]

also MariaDB 10.11.4 is affected

Comment by Sergei Golubchik [ 2023-08-11 ]

Cf.

MariaDB [test]> select ifnull("a", GEOMFROMTEXT('POINT(1 1)'));

+-----------------------------------------+

| ifnull("a", GEOMFROMTEXT('POINT(1 1)')) |

+-----------------------------------------+

| a                                       |

+-----------------------------------------+

1 row in set (0.001 sec)

Comment by Alexander Barkov [ 2023-08-16 ]

INET6 demonstrates similar behavior:

MariaDB [test]> SELECT IFNULL('a', CAST('::' AS INET6));

+----------------------------------+

| IFNULL('a', CAST('::' AS INET6)) |

+----------------------------------+

| ::                               |

+----------------------------------+

1 row in set, 1 warning (0.001 sec)

MariaDB [test]> SHOW WARNINGS;

+---------+------+----------------------------+

| Level   | Code | Message                    |

+---------+------+----------------------------+

| Warning | 1292 | Incorrect inet6 value: 'a' |

+---------+------+----------------------------+

1 row in set (0.001 sec)

Comment by Alexander Barkov [ 2023-08-16 ]

UUID and INET6 are implemented as strong data types (stronger than VARCHAR).
The data type of a hybrid function like IFNULL, COALESCE is evaluated as:

VARCHAR + UUID -> UUID
VARCHAR + INET6 -> INET6

SELECT IFNULL('a', UUID());

is actually evaluated as:

SELECT IFNULL(CAST('a' AS UUID), UUID());

The result of CAST is NULL, because 'a' is not a valid UUID value.
A warning is issued, and the function result is evaluated from the second argument.

Comment by Alexander Barkov [ 2023-08-16 ]

During a discussion with serg, we had an idea that we could change the implicit CAST to convert wrong UUID values to zero UUID instead of NULL. However, this needs further studies.

[MDEV-31882] IFNULL('a', UUID()) returns an UUID Created: 2023-08-09 Updated: 2023-11-28
Status:	Confirmed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.7
Fix Version/s:	10.11, 11.0, 11.1

[MDEV-31882] IFNULL('a', UUID()) returns an UUID Created: 2023-08-09 Updated: 2023-11-28