MySQL Bugs: #62125: testing STORED FUNCTION result for NULL incorrectly yields 1292 warning

Bug #62125	testing STORED FUNCTION result for NULL incorrectly yields 1292 warning
Submitted:	9 Aug 2011 11:43	Modified:	28 Jan 2012 15:16
Reporter:	Paul Lucassen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.14, 5.5.16, 5.6.3-m6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
When the result of a Stored Function returning a non-integer type is evaluated for NULL, an incorrect type warning (1292) is generated. This warning cannot be correct as the test for NULL should work for any type.
In 5.1 this worked correctly. Stored Procedures not handling 1292 fail in 5.5 because of this incorrect warning.

The issue does not arise if the result of the Stored Function is embedded in an expression such as CONCAT(some_string_result()). In this way a workaround could be constructed.

How to repeat:
The following simple test show how to repeat this.

DELIMITER //
//
DROP FUNCTION IF EXISTS some_string_returner
//
CREATE FUNCTION some_string_returner( )
RETURNS VARCHAR(1) CHARSET "utf8"
BEGIN
  RETURN 'X';
END;
//
DELIMITER ;

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27824
Server version: 5.5.14-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select some_string_returner() IS NULL;
+--------------------------------+
| some_string_returner() IS NULL |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set, 1 warning (0,00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'X' |
+---------+------+----------------------------------------+
1 row in set (0,00 sec)

mysql> select "X" IS NULL;
+-------------+
| "X" IS NULL |
+-------------+
|           0 |
+-------------+
1 row in set (0,00 sec)

mysql> select CONCAT("X") IS NULL;
+---------------------+
| CONCAT("X") IS NULL |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0,00 sec)

mysql> select CONCAT(some_string_returner()) IS NULL;
+----------------------------------------+
| CONCAT(some_string_returner()) IS NULL |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0,00 sec)

I cannot reproduce with mySQL 5.5.15 on Windows (64 bit server):

SELECT VERSION(); ---5-5-15
USE test;

DELIMITER //

DROP FUNCTION IF EXISTS some_string_returner //

CREATE FUNCTION some_string_returner( )
RETURNS VARCHAR(1) CHARSET "utf8"
BEGIN
  RETURN 'X';
END;
//

DELIMITER ;

SELECT some_string_returner(); --returns 'X'

SHOW WARNINGS; -- empty set

Peter
(not a MySQL person)

Thank you for the bug report. Verified just as described with current mysql-5.5 from bzr:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER //
mysql> DROP FUNCTION IF EXISTS some_string_returner
    -> //
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE FUNCTION some_string_returner( )
    -> RETURNS VARCHAR(1) CHARSET "utf8"
    -> BEGIN
    ->   RETURN 'X';
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select some_string_returner() IS NULL;
+--------------------------------+
| some_string_returner() IS NULL |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect INTEGER value: 'X'
1 row in set (0.00 sec)

Sorry .. my mistake!

Also reproducible here!

This bug also present in 5.6.3-m6.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6. Documented in the 5.6.5 changelog as follows:

        When the result of a stored function returning a non-integer
        type was evaluated for NULL, an incorrect type warning (Warning
        1292 -Truncated incorrect INTEGER value-) is generated, although
        such a test for NULL should work with any type. This can cause
        stored routines not handling the warning correctly to fail.

        The issue could be worked around by wrapping the result in an
        expression, using a function such as CONCAT().

Closed.