Bug #65976 SELECT IFNULL(NULL, ~1) returns incorrect value

• Submitted: 21 Jul 2012 18:11
• Modified: 15 Oct 2013 18:31
• Reporter: Jon Dufresne
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.25a, 5.5.26
• OS: Any
• CPU Architecture: Any

[21 Jul 2012 18:11] Jon Dufresne

Description:
It appears that some bug, (integer overflow? Bad type casting?) is causing the expression 'SELECT IFNULL(NULL, ~1)' to return the incorrect value. Observe the output:

mysql> SELECT ~1, IFNULL(NULL, ~1), IFNULL(NULL, ~1) = ~1;
+----------------------+------------------+-----------------------+
| ~1                   | IFNULL(NULL, ~1) | IFNULL(NULL, ~1) = ~1 |
+----------------------+------------------+-----------------------+
| 18446744073709551614 |               -2 |                     0 |
+----------------------+------------------+-----------------------+
1 row in set (0.00 sec)

I expect the second value to be identical to the first and the comparison should reflect that.

I am using the MySQL packaged by Fedora 16.

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.25a, for Linux (x86_64) using readline 5.1

How to repeat:
To reproduce, simply run the query

mysql> SELECT IFNULL(NULL, ~1);

[21 Jul 2012 19:16] Valeriy Kravchuk

Thank you for the problem report.

[21 Jul 2012 19:21] Peter Laursen

I get same results with MySQL 5.1.63 and 5.5.23 on Windows 64 bit (64 bit server). I also think 1st and 2nd results should be the same.

Peter
(not a MySQL/Oracle person)

[21 Jul 2012 19:24] Peter Laursen

.. and also same on 5.0.96

[23 Jul 2012 17:27] Davi Arnaut

Quoting the manual: "The default result value of IFNULL(expr1,expr2) is the more 'general' of the two expressions, in the order STRING, REAL, or INTEGER."

mysql> SELECT CAST(~1 AS SIGNED INTEGER);
+----------------------------+
| CAST(~1 AS SIGNED INTEGER) |
+----------------------------+
|                         -2 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(18446744073709551614 AS SIGNED INTEGER);
+----------------------------------------------+
| CAST(18446744073709551614 AS SIGNED INTEGER) |
+----------------------------------------------+
|                                           -2 |
+----------------------------------------------+
1 row in set (0.00 sec)

[15 Oct 2013 18:31] Paul DuBois

Noted in 5.7.3 changelog.

With a NULL literal in a particular parameter position, IFNULL(), 
COALESCE(), and CASE returned a signed value when they should return
an unsigned value.

[4 Dec 2013 10:48] Laurynas Biveinis

mysql-server$ bzr log -r 6684
------------------------------------------------------------
revno: 6684
committer: Norvald H. Ryeng <[e-mail]>
branch nick: mysql-trunk-14359340
timestamp: Mon 2013-10-14 15:00:32 +0200
message:
  Bug#14359340 SELECT IFNULL(NULL, ~1) RETURNS INCORRECT VALUE
  
  Problem: IFNULL, COALESCE and CASE return signed values when they
  should return unsigned values if there is a NULL literal in a
  particular parameter position.
  
  Item_func_ifnull, Item_func_coalesce and Item_func_case all use
  agg_result_type() to find a data type that covers all argument types,
  and this function correctly ignores NULL literals. However, signedness
  is not computed the same way, so each item class has its own
  implementation, and those don't ignore NULL literals.
  
  Fix: Compute signedness of the combined data type in
  agg_result_type().