| 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: | |
| 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 |
[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
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.