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:Microsoft Windows (XP, Vista)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[25 Nov 2013 13:44] Programmer Old
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
[25 Nov 2013 17:49] Sveta Smirnova
Thank you for the report.

Which results do you expect?
[25 Nov 2013 20:58] Programmer Old
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.
[28 Nov 2013 17:01] Sveta Smirnova
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.