CODEWITHSUNDEEP
mysqlsql
Laurentiu L.
Laurentiu L.

Reputation: 6686

Unexpected result when combining IFNULL with TRUNCATE

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

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

Why is that?

Breaking it apart works as expected and described in the TRUNCATE function documentation and IFNULL docs :

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 nesting them?

Upvotes: 4

Views: 283

Answers (2)

aergistal
aergistal

Reputation: 31209

The type of TRUNCATE(NULL,n) is DOUBLE. This can be seen by running mysql with the --column-type parameter:

$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 documentation page:

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

Therefore your result is 0.000, the 0 as DOUBLE truncated to 3 decimal places.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your expectation is wrong. TRUNCATE(NULL, 3) is going to return a decimal value with three decimal places. Although the value is NULL, NULL has a type associated with it. The type is integer by default. But this is not a default situation.

So, 0 is converted to a decimal with three decimal places.

EDIT:

To understand what I mean, consider this code:

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

describe t;

You will see that the column has a precision of "3". The NULL value is not typeless. You can see this on SQL Fiddle.

Upvotes: 1

Related Questions