| Bug #68688 | IFNULL of SET & ENUM less sensitive than IF to numeric context | ||
|---|---|---|---|
| Submitted: | 16 Mar 2013 0:45 | Modified: | 19 Mar 2013 2:46 |
| Reporter: | Programmer Old | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.5.8-log | OS: | Windows (Vista) |
| Assigned to: | CPU Architecture: | Any | |
[16 Mar 2013 16:10]
MySQL Verification Team
It does seem inconsistent. A workaround is this, all queries return the same: select m,if(m is not null, m, 21) + 1 from v; select m,ifnull(m+0, 21) + 1 from v; select m,coalesce(m+0 ,21) + 1 from v;
[19 Mar 2013 2:46]
Programmer Old
Do not ignore the complete lack of warning that the character-string form of the SET was turned to 0, in contrast to <code>'a,b' + 1</code>.
Description: IF and CASE pass their numeric context to their arguments for SET & ENUM to conform to, but IFNULL not: mysql> select m,if( m is not null, m, 21) + 1 from v; +------+-------------------------------+ | m | if( m is not null, m, 21) + 1 | +------+-------------------------------+ | a | 2 | | b | 3 | | a | 2 | | b | 3 | | a,b | 4 | | NULL | 22 | +------+-------------------------------+ 6 rows in set (0.00 sec) mysql> select m,ifnull( m, 21) + 1 from v; +------+--------------------+ | m | ifnull( m, 21) + 1 | +------+--------------------+ | a | 1 | | b | 1 | | a | 1 | | b | 1 | | a,b | 1 | | NULL | 22 | +------+--------------------+ 6 rows in set (0.00 sec) Furthermore, although the strings are converted to 0, there is no warning of that. How to repeat: create temporary table v (m set ('a','b')); insert into v value (1),(2),('a'),('b'),('b,a'),(null); select m,if( m is not null, m, 21) + 1 from v; select m,ifnull( m, 21) + 1 from v; select m, 'a,b' + 1 from v; -- for comparison Suggested fix: Make IFNULL sensitive to its numeric context when its argument is SET or ENUM