Bug #79116 IFNULL returns expr1 with appending decimals when expr2 is w/ decimal

• Submitted: 4 Nov 2015 8:56
• Modified: 4 Nov 2015 13:34
• Reporter: Su Dylan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8,5.6.24,5.5.43,5.1.73
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[4 Nov 2015 8:56] Su Dylan

Description:
Output:
=======
mysql> select ifnull(2,1.000);
+-----------------+
| ifnull(2,1.000) |
+-----------------+
|           2.000 |
+-----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
2 is expected, not 2.000

How to repeat:
select ifnull(2,1.000);

Suggested fix:
select ifnull(2,1.000) returns 2

[4 Nov 2015 12:17] MySQL Verification Team

[arhimed@gedora sandboxes]$ ./use_all "source /home/arhimed/sandboxes/b79116.sql "
@@version
5.1.73
ifnull(2,1.000) ifnull(2,1)     ifnull(2,"1")
2.000   2       2

@@version
5.5.43-log
ifnull(2,1.000) ifnull(2,1)     ifnull(2,"1")
2.000   2       2

@@version
5.6.24
ifnull(2,1.000) ifnull(2,1)     ifnull(2,"1")
2.000   2       2

@@version
5.7.7-rc
ifnull(2,1.000) ifnull(2,1)     ifnull(2,"1")
2.000   2       2

[arhimed@gedora msb_5_7_7]$ ./use --column-type-info -r -e "select ifnull(2,1.000), ifnull(2,1), ifnull(2,"1");"
Field   1:  `ifnull(2,1.000)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     6
Max_length: 5
Decimals:   3
Flags:      NOT_NULL BINARY NUM

Field   2:  `ifnull(2,1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   3:  `ifnull(2,1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+-----------------+-------------+-------------+
| ifnull(2,1.000) | ifnull(2,1) | ifnull(2,1) |
+-----------------+-------------+-------------+
|           2.000 |           2 |           2 |
+-----------------+-------------+-------------+

[4 Nov 2015 13:34] MySQL Verification Team

Hi,

Thanks for submitting bug.

This is expected behavior and it is same throughout MySQL versions.

if you look at ifnull documentation
for e.g.: http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull

you can see that 

"The default result value of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER."

kind regards
Bogdan Kecman