Bug #34385 IFNULL change result DataType
Submitted: 7 Feb 2008 17:40 Modified: 10 Feb 2008 4:50
Reporter: Mathieu Tremblay Email Updates:
Status: Verified 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
Triage: Triaged: D3 (Medium)

[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 "";