MySQL Bugs: #34385: IFNULL change result DataType

Bug #34385	IFNULL change result DataType
Submitted:	7 Feb 2008 17:40	Modified:	23 Feb 2021 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

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;

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;

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.

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

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  |
+---------------------+-------------+

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.