Bug #44481 cast/convert function not working correctly with NULL value

• Submitted: 26 Apr 2009 17:48
• Modified: 27 Apr 2009 6:18
• Reporter: Piotr Kroczynski
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: Ver 14.12 Distrib 5.0.75
• OS: Linux (Kubuntu 9.04)
• CPU Architecture: Any
• Tags: cast, convert

[26 Apr 2009 17:48] Piotr Kroczynski

Description:
When I'm casting NULL value as signed integer or unsigned integer i get NULL.

mysql> select cast(NULL as signed integer);
+------------------------------+
| cast(NULL as signed integer) |
+------------------------------+
|                         NULL |
+------------------------------+
1 row in set (0.00 sec)

mysql> select cast(NULL + 1 as signed integer);
+----------------------------------+
| cast(NULL + 1 as signed integer) |
+----------------------------------+
|                             NULL |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select convert(NULL + 1 , signed integer);
+------------------------------------+
| convert(NULL + 1 , signed integer) |
+------------------------------------+
|                               NULL |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select convert(NULL , signed integer);
+--------------------------------+
| convert(NULL , signed integer) |
+--------------------------------+
|                           NULL |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select convert(NULL , signed);
+------------------------+
| convert(NULl , signed) |
+------------------------+
|                   NULL |
+------------------------+
1 row in set (0.00 sec)
 

How to repeat:
mysql> select convert(NULL , signed);
mysql> select convert(NULL , signed integer);
mysql> select convert(NULL + 1 , signed integer);
mysql> select cast(NULL + 1 as signed integer);
mysql> select cast(NULL as signed integer);

[26 Apr 2009 18:06] Valeriy Kravchuk

Why do you think there is a bug here? NULL is NULL, whatever type it is. And if NULL is an operand in expression, the result is usually NULL. This is expected.

[26 Apr 2009 19:48] Piotr Kroczynski

Maybe I'm thinking wrong way but for example i want to get something like that:

SELECT CAST(MAX(field) AS UNSIGNED) + 1 AS next_number FROM table; 

For empty table I'm expecting to get 1 as next_number. Maybe my thinking is wrong. I read this earlier today: http://bugs.mysql.com/bug.php?id=2219

[27 Apr 2009 4:10] Valeriy Kravchuk

Current behavior is intended. You can use functions in your expression to check for NULL and return the result you need for this case. Check http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull.

[27 Apr 2009 6:18] Piotr Kroczynski

Ok, my mistake. Didn't know it is intended. Thank for link :) Regards