Bug #42206 IFNULL control flow function returns a table instead of only the expression

• Submitted: 20 Jan 2009 7:20
• Modified: 20 Jan 2009 7:54
• Reporter: dahlia khan
• Status: Not a Bug
• Category: MySQL Server: Query Cache
• Severity: S1 (Critical)
• Version: 5.00
• OS: Any
• CPU Architecture: Any
• Tags: IFNULL control flow function

[20 Jan 2009 7:20] dahlia khan

Description:
mysql>SELECT IFNULL(1/0,10);
     > 10;
IFNULL(expr1,expr2) returns expr2 if expr1 is null expr1 otherwise.

This is what mentioned in the manual 

mysql> select ifnull(null,curdate());
+------------------------+
| ifnull(null,curdate()) |
+------------------------+
| 2009-01-20             |
+------------------------+
1 row in set (0.00 sec)

mysql>

But according to the manual it should be

mysql> select ifnull(null,curdate());
     > 2009-01-20

A total table is returned instead of only the expression. 

How to repeat:
mysql> select ifnull(null,curdate());
+------------------------+
| ifnull(null,curdate()) |
+------------------------+
| 2009-01-20             |
+------------------------+
1 row in set (0.00 sec)

[20 Jan 2009 7:24] dahlia khan

mysql> select datediff(select ifnull(null,curdate()),'2005-12-12'));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selec
t ifnull(null,curdate()),'2005-12-12'))' at line 1
--------this is not ok
this is caused due to "select ifnull(null,curdate())" portion of the query is returning a table;

[20 Jan 2009 7:54] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

"Table" in the output is just formatting in the mysql command line client. Real result is "2009-01-20". Look attention there is information message "1 row in set (0.00 sec)" which indicates it.