Why ifnull() return DECIMAL instead of BIGINT in MariaDB 10.1+?

Question

Why ifnull() return DECIMAL instead of BIGINT in MariaDB 10.1?

For example:

Query:

select a, ifnull(b, 1) from table;

10.0.22-MariaDB:

ifnull(b, 1) type is BIGINT

10.1.37-MariaDB:

ifnull(b, 1) type is DECIMAL

Moreover, in both versions the return type of this query is the same:

select 1; //type is BIGINT

Why ifnull() convert BIGINT to DECIMAL?

what is the actual data type of column 'b' in 'table'? – Hartmut Holzgraefe Apr 02 '19 at 19:36 — Hartmut Holzgraefe, Apr 02 '19 at 19:36
@HartmutHolzgraefe TINYINT UNSIGNED – Songe Apr 03 '19 at 12:15 — Songe, Apr 03 '19 at 12:15

score 2 · Accepted Answer · answered Apr 02 '19 at 19:31

I can't reproduce the problem, see the example:

MariaDB [test]> SELECT VERSION();
Field   1:  `VERSION()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     72
Max_length: 24
Decimals:   31
Flags:      NOT_NULL 

+-----------------+
| VERSION()       |
+-----------------+
| 10.1.38-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test]> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE IF NOT EXISTS `test` (
    ->   `bigint` BIGINT,
    ->   `decimal` DECIMAL(5, 2)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO `test`
    ->   (`bigint`, `decimal`)
    -> VALUES
    ->   (NULL, NULL);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SELECT 1;
Field   1:  `1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

MariaDB [test]> SELECT
    ->   IFNULL(`bigint`, 1) `bigint`,
    ->   IFNULL(`decimal`, 1) `decimal`
    -> FROM
    ->   `test`;
Field   1:  `bigint`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

Field   2:  `decimal`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     7
Max_length: 4
Decimals:   2
Flags:      NOT_NULL BINARY NUM 

+--------+---------+
| bigint | decimal |
+--------+---------+
|      1 |    1.00 |
+--------+---------+
1 row in set (0.00 sec)

@Songe: See [MDEV-10171: Wrong field type for COALESCE with unsigned int](https://jira.mariadb.org/browse/MDEV-10171). — wchiquito, Apr 04 '19 at 04:48
@RickJames: `$ mysql --column-type-info`, see [mysql Command-line Client](https://mariadb.com/kb/en/library/mysql-command-line-client/). — wchiquito, May 09 '19 at 12:51

score 0 · Answer 2 · answered Apr 02 '19 at 11:52

The default return type will result from comparasion of the both type values passed to the function, there is some order of the returned types: integer before bigint

TYPE (X,Y) = IFNULL(type X , type Y).

where the decimal is more general than bigin in your case.

I suppose there are some changes regarding the both version of Mysql as of how to handle the default return type.

Why ifnull() return DECIMAL instead of BIGINT in MariaDB 10.1+?

2 Answers2