MySQL Bugs: #71002: IFNULL of plain function parameter always latter argument

Bug #71002	IFNULL of plain function parameter always latter argument
Submitted:	25 Nov 2013 13:44	Modified:	28 Nov 2013 17:01
Reporter:	Programmer Old	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.8-log, 5.6.16, 5.5.35, 5.1.71	OS:	Windows (XP, Vista)
Assigned to:		CPU Architecture:	Any

Description:
Within RETURN I enclose bare function-parameter in IFNULL, with outcome that latter argument is always the value:

mysql> DROP FUNCTION IF EXISTS wronger;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION wronger(eld SET ('withdrawn','misaddress','away'), away BOOL)
    -> RETURNS SET ('withdrawn','misaddress','away')
    -> COMMENT 'from table to alsoa "away" (BOOL)'
    -> DETERMINISTIC NO SQL
    -> RETURN IFNULL(eld + 0, 0) | IFNULL(away << 2, 0);
Query OK, 0 rows affected (0.00 sec)

mysql> select wronger(3,1),wronger(null,1),wronger(3,null);
+---------------------------+-----------------+----------------------+
| wronger(3,1)              | wronger(null,1) | wronger(3,null)      |
+---------------------------+-----------------+----------------------+
| withdrawn,misaddress,away | away            | withdrawn,misaddress |
+---------------------------+-----------------+----------------------+
1 row in set (0.00 sec)

mysql> DROP FUNCTION IF EXISTS wronger;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION wronger(eld SET ('withdrawn','misaddress','away'), away BOOL)
    -> RETURNS SET ('withdrawn','misaddress','away')
    -> COMMENT 'from table to alsoa "away" (BOOL)'
    -> DETERMINISTIC NO SQL
    -> RETURN IFNULL(eld, 0) | IFNULL(away << 2, 0);
Query OK, 0 rows affected (0.00 sec)

mysql> select wronger(3,1),wronger(null,1),wronger(3,null);
+--------------+-----------------+-----------------+
| wronger(3,1) | wronger(null,1) | wronger(3,null) |
+--------------+-----------------+-----------------+
| away         | away            |                 |
+--------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.8-log |
+-----------+

For all I know other things affect it: whether IFNULL s latter argument is constant, what the function-comment is, ....

How to repeat:
As above

Suggested fix:
Treatment of IFNULL s arguments within RETURN the same whether bare variable or expression

Thank you for the report.

Which results do you expect?

The result with argument "eld + 0" is the expected. But it comes to me that maybe this is a case that I already reported, #68688, where IFNULL s context is not passed to its arguments (although IF s context is), and the SET is interpreted not in number form but string.

Combined with MySQL s too great willingness to convert character string to 0, quite unintuitive.

Thank you for the feedback.

Verified as described. This can be duplicate of another bug, but I leave it as a separate one for now.