Unexpected result when combining IFNULL with TRUNCATE

Question

The following query returns 0.000 when I expected it to return 0.

SELECT IFNULL(TRUNCATE(NULL, 3), 0) FROM DUAL

Why is this?

A break in its operation is expected and described in the documentation of the TRUNCATE and IFNULL docs functions :

SELECT TRUNCATE(NULL, 3) FROM DUAL

returns null.

SELECT IFNULL(null, 0) FROM DUAL

this returns 0. So why do I get 0.000 when they are nested?

Answer 1

Type TRUNCATE(NULL,n)- DOUBLE. This can be seen by running mysqlwith the parameter --column-type:

$mysql -u root --column-type testdb

mysql> SELECT(TRUNCATE(NULL,3));
Field   1:  `(TRUNCATE(NULL,3))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   3
Flags:      BINARY NUM 


+--------------------+
| (TRUNCATE(NULL,3)) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0,00 sec)

According to the IFNULL page :

IFNULL (expr1, expr2) "" STRING, REAL INTEGER

, 0.000, 0, DOUBLE .

Answer 2

. TRUNCATE(NULL, 3) . NULL, NULL . . .

, 0 .

EDIT:

, , :

create table t as
    select truncate(NULL, 3) as x;

describe t;

, "3". NULL . SQL Fiddle.