Bug #39049 Wrong warning when comparing some decimal values to NULL
Submitted: 26 Aug 2008 16:18 Modified: 10 Dec 2008 12:18
Reporter: Matthias Leich Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[26 Aug 2008 16:18] Matthias Leich
Description:
There is some strange effect when comparing
user variables or columns of type decimal
with NULL:
SET @my_var = 1111111111111111111.111111111111111111111111111111;
DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 ENGINE = MEMORY AS SELECT @my_var;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `@my_var` decimal(65,30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
SELECT @my_var IS NULL;
@my_var IS NULL
0
SELECT @my_var IS NULL FROM t1;
@my_var IS NULL
0
   <-- The results above look ok.
SET @my_var = 11111111111111111111.1;
DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 ENGINE = MEMORY AS SELECT @my_var;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `@my_var` decimal(65,30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
SELECT @my_var IS NULL;
@my_var IS NULL
0
Warnings:
Error	1292	Truncated incorrect DECIMAL value: ''
   <--- Why do we get this warning?
        NULL and 11111111111111111111.1 are legal values.
        IMHO
        - There is no need to truncate anything.
        - Any conversion with floating point math libs
          should be also not involved.
SELECT @my_var IS NULL FROM t1;
@my_var IS NULL
0
Warnings:
Error	1292	Truncated incorrect DECIMAL value: ''
   <--- Same problem as above.

Experiments show that the suspicious effect comes
up when the number of digits before '.' exceeds 19.

My environment:
---------------
trees: mysql-5.0/5.1/6.0-bugteam
last changesets: ~2008-08-26
build via BUILD/compile-pentium-debug-max
hardware: Intel Core2Duo (64 Bit)
OS: Linux OpenSuSE 10.3

How to repeat:
Please run the statements above.
[10 Dec 2008 12:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61202

2721 Alexey Botchkov	2008-12-10
      Bug#39049      Wrong warning when comparing some decimal values to NULL
              the Item::update_null_value() implementation calls val_int(),
              that can fail if a decimal result of an item is bigger than any
              possible integer.
              update_null_value() now calls val_decimal() for DECIMAL_RESULT
      
      per-file messages:
        sql/item.cc
      Bug#39049      Wrong warning when comparing some decimal values to NULL
              update_null_value() implemented
        sql/item.h
      Bug#39049      Wrong warning when comparing some decimal values to NULL
              declaration of the update_null_value() changed
[10 Dec 2008 12:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61204

2722 Alexey Botchkov	2008-12-10
      Bug#39049      Wrong warning when comparing some decimal values to NULL
      
      per-file messages:
        mysql-test/r/type_newdecimal.result
      Bug#39049      Wrong warning when comparing some decimal values to NULL
              test result
        mysql-test/t/type_newdecimal.test
      Bug#39049      Wrong warning when comparing some decimal values to NULL
              test case