Bug #34385 IFNULL change result DataType
Submitted: 7 Feb 2008 17:40 Modified: 23 Feb 8:10
Reporter: Mathieu Tremblay Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.45, 5.0, 5.1, 6.0 BK OS:Any (Linux, Windows XP)
Assigned to: CPU Architecture:Any

[7 Feb 2008 17:40] Mathieu Tremblay
Description:
MySQL Function IFNULL change result Datatype.

Wwhen using IFNULL function on a UNSIGNED DataType the function convert the datatype to Decimal(10,0).

See How to repeat.
 
_______________________________________________________
Field   |Type	          |Null	  |Key  |Default |Extra
Number  |int(10) unsigned |NO     |     |0	
OK      |int(10) unsigned |YES    |     |	
Error   |decimal(10,0)    |NO     |     |0
_______________________________________________________

How to repeat:
DROP VIEW IF EXISTS `testview`;
DROP TABLE IF EXISTS `testtable`;

CREATE TABLE `testtable` (
  `Number` int(10) unsigned NOT NULL auto_increment,
  `Error` int(10) unsigned default NULL,
  PRIMARY KEY  (`Number`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

ALTER TABLE `testtable` DISABLE KEYS;
INSERT INTO `testtable` (`Number`,`Error`) VALUES
 (1,NULL),
 (2,1),
 (3,2);
ALTER TABLE `testtable` ENABLE KEYS;

CREATE OR REPLACE VIEW `TestView` AS
SELECT Number,Error AS OK,IFNULL(Error,1) AS Error
FROM TestTable;

DESC Testtable;
DESC Testview;
[8 Feb 2008 9:04] Sveta Smirnova
Thank you for the report.

Verified as described.

According to http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_ifnull:

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

So it can be REAL for UNSIGNED. But question is why VIEW treats this value as DECIMAL

Workaround:

CREATE OR REPLACE VIEW `testview` AS
SELECT Number,Error AS OK,CAST(IFNULL(Error,1) as UNSIGNED) AS Error
FROM testtable;
[8 Feb 2008 17:59] Mathieu Tremblay
1)CAST(IFNULL(Error,1) as UNSIGNED) AS Error is not a good workaround.

Not a workaround because its transform a 4 bytes integer to a 8 bytes bigint and that not the same thing when expected 4 bytes integer.

2)The result of the query "SELECT IFNULL(Error,1)AS Error FROM TestTable" is treats as DECIMAL like the VIEW even if the Error column is int(10).

So that is not only a VIEW problem.
[10 Feb 2008 4:50] Mathieu Tremblay
If modify the creation of the VIEW as Folow
CREATE OR REPLACE VIEW `TestView` AS
SELECT Number,Error AS OK,IFNULL(Error,"") AS Error
FROM TestTable;

column "Error" DataType is varbinary(11). Just because of the "";
[22 Feb 23:12] Daniel Zeller
Still an issue in 5.7.33 and 8.0.23.

Another example:

SELECT
IFNULL(TIMESTAMP(NULL),date(now())) as validUntil,
IFNULL(NULL,date(now())) as validUntil2

Result:

+---------------------+-------------+
| validUntil          | validUntil2 |
+---------------------+-------------+
| 2021-02-20 00:00:00 | 2021-02-20  |
+---------------------+-------------+
[23 Feb 8:10] Roy Lyseng
Posted by developer:
 
This does not look like a problem in current 8.0:

CREATE TABLE t1 (
  Number int unsigned NOT NULL auto_increment,
  Error int unsigned default NULL,
  PRIMARY KEY(Number)
);

INSERT INTO t1(Number, Error) VALUES (1,NULL), (2,1), (3,2);

CREATE VIEW v1 AS
SELECT Number, Error AS OK, IFNULL(Error,1) AS Error
FROM t1;

DESC t1;

| Number | int unsigned | NO   | PRI | NULL    | auto_increment |
| Error  | int unsigned | YES  |     | NULL    |                |

DESC v1;

| Number | int unsigned | NO   |     | 0       |       |
| OK     | int unsigned | YES  |     | NULL    |       |
| Error  | bigint       | NO   |     | 0       |       |

The value 1 is a signed integer, thus the type of the expression IFNULL(Error,1) is also signed.