| Bug #9840 | string to number compare fails | ||
|---|---|---|---|
| Submitted: | 12 Apr 2005 11:50 | Modified: | 13 Apr 2005 13:41 |
| Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.11 | OS: | FreeBSD (freebsd/linux) |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[12 Apr 2005 12:07]
Martin Friebe
Another way to show the problem, And i am not sure , if the values in the table, are expected like this create table tbl1 select '1052136747946823684'-0.0 , 1052136747946823684-0.0; show create table tbl1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl1 | CREATE TABLE `tbl1` ( `'1052136747946823684'-0.0` double NOT NULL default '0', `1052136747946823684-0.0` double(18,1) NOT NULL default '0.0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ select * from tbl1; +---------------------------+-------------------------+ | '1052136747946823684'-0.0 | 1052136747946823684-0.0 | +---------------------------+-------------------------+ | 1.05213674794682e+18 | 100000000000000000.0 | +---------------------------+-------------------------+ 1 row in set (0.00 sec)
[12 Apr 2005 15:53]
Geert Vanderkelen
Hi Martin, Post 1) I could not reproduce it on the official binaries provided by MySQL AB. Are you using these or you compile it yourself? All examples gave expected results here. Post2) CREATE TABLE ... SELECT makes a column double(18,1), not sure where that is documented, sitll looking :) Try SHOW WARNINGS after that stament. Regards, Geert
[12 Apr 2005 16:12]
Martin Friebe
Ok, I get the warning for the 2nd example. With regards to the first relevant parts ov the show variables: | version | 4.1.11-log | | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (April 1, 2005) | | version_comment | FreeBSD port: mysql-server-4.1.11 | | version_compile_machine | i386 | | version_compile_os | portbld-freebsd4.11 | | wait_timeout | 28800 | /usr/local/libexec/mysqld -V /usr/local/libexec/mysqld Ver 4.1.11-log for portbld-freebsd4.11 on i386 (FreeBSD port: mysql-server-4.1.11) This is compiled from the freebsd port system (and I have tested against 4.1.11 4.1.10a 4.1.7 and 4.0.22) make install WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes using the mysql command line client. I will see if i can find more gcc -v Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] anything else I can add? i will see and buld without optimization
[12 Apr 2005 17:31]
Martin Friebe
I have checked on one of our companies lifeservers: freebsd 5.3 mysql 4.1.10a cc -v Using built-in specs. Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 3.4.2 [FreeBSD] 20040728 the mysql 4.1.11 server is on freebsd 4.11 uname -a FreeBSD m.com 4.11-STABLE FreeBSD 4.11-STABLE #3: Thu Jan 20 13:07:04 GMT 2005 root@m.com:/usr/obj/usr/src/sys/KERNEL i386 with cc -v Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] all mysqd are build from ports, with the options allready specified, but it also happens. I will add a debug-trace file. but it doesnt look promising to me Across all the servers I tested, I found the below to have the same result. It looks like the last 2 bits get lost. (except for 4000E3AA102714, which doesnt fit the pattern) I dont know, if this is a mysql, or a freebsd issue, but it is consistent. select a, hex(a), conv(a,10,10) = a from (select 18015376320243458 a union select 18015376320243459 union select 18015376320243460 union select 18015376320243461 union select 18015376320243462 union select 18015376320243463 union select 18015376320243464 union select 18015376320243465 union select 18015376320243466 union select 18015376320243467 union select 18015376320243468 union select 18015376320243469 union select 18015376320243470 union select 18015376320243471 union select 18015376320243472 union select 18015376320243473 union select 18015376320243474 union select 18015376320243475 union select 18015376320243476 union select 18015376320243477 union select 18015376320243478 union select 18015376320243479 union select 18015376320243480 union select 18015376320243481 union select 18015376320243482 union select 18015376320243483 union select 18015376320243484 union select 18015376320243485 union select 18015376320243486 ) t; +-------------------+----------------+-------------------+ | a | hex(a) | conv(a,10,10) = a | +-------------------+----------------+-------------------+ | 18015376320243458 | 4000E3AA102702 | 0 | | 18015376320243459 | 4000E3AA102703 | 0 | | 18015376320243460 | 4000E3AA102704 | 1 | | 18015376320243461 | 4000E3AA102705 | 0 | | 18015376320243462 | 4000E3AA102706 | 0 | | 18015376320243463 | 4000E3AA102707 | 0 | | 18015376320243464 | 4000E3AA102708 | 1 | | 18015376320243465 | 4000E3AA102709 | 0 | | 18015376320243466 | 4000E3AA10270A | 0 | | 18015376320243467 | 4000E3AA10270B | 0 | | 18015376320243468 | 4000E3AA10270C | 1 | | 18015376320243469 | 4000E3AA10270D | 0 | | 18015376320243470 | 4000E3AA10270E | 0 | | 18015376320243471 | 4000E3AA10270F | 0 | | 18015376320243472 | 4000E3AA102710 | 1 | | 18015376320243473 | 4000E3AA102711 | 0 | | 18015376320243474 | 4000E3AA102712 | 0 | | 18015376320243475 | 4000E3AA102713 | 0 | | 18015376320243476 | 4000E3AA102714 | 0 | | 18015376320243477 | 4000E3AA102715 | 0 | | 18015376320243478 | 4000E3AA102716 | 0 | | 18015376320243479 | 4000E3AA102717 | 0 | | 18015376320243480 | 4000E3AA102718 | 1 | | 18015376320243481 | 4000E3AA102719 | 0 | | 18015376320243482 | 4000E3AA10271A | 0 | | 18015376320243483 | 4000E3AA10271B | 0 | | 18015376320243484 | 4000E3AA10271C | 1 | | 18015376320243485 | 4000E3AA10271D | 0 | | 18015376320243486 | 4000E3AA10271E | 0 | +-------------------+----------------+-------------------+ The following is the result, when adding 0.0 to the bigint side (strange), I can add anything of the following 0.0 or 0.000000 or even 0.9, the result below does not change select a, hex(a), conv(a,10,10) = a , conv(a,10,10) = a+0.00 from (select 18015376320243458 a union select 18015376320243459 union select 18015376320243460 union select 18015376320243461 union select 18015376320243462 union select 18015376320243463 union select 18015376320243464 union select 18015376320243465 union select 18015376320243466 union select 18015376320243467 union select 18015376320243468 union select 18015376320243469 union select 18015376320243470 union select 18015376320243471 union select 18015376320243472 union select 18015376320243473 union select 18015376320243474 union select 18015376320243475 union select 18015376320243476 union select 18015376320243477 union select 18015376320243478 union select 18015376320243479 union select 18015376320243480 union select 18015376320243481 union select 18015376320243482 union select 18015376320243483 union select 18015376320243484 union select 18015376320243485 union select 18015376320243486 ) t; +-------------------+----------------+-------------------+------------------------+ | a | hex(a) | conv(a,10,10) = a | conv(a,10,10) = a+0.00 | +-------------------+----------------+-------------------+------------------------+ | 18015376320243458 | 4000E3AA102702 | 0 | 1 | | 18015376320243459 | 4000E3AA102703 | 0 | 0 | | 18015376320243460 | 4000E3AA102704 | 1 | 1 | | 18015376320243461 | 4000E3AA102705 | 0 | 1 | | 18015376320243462 | 4000E3AA102706 | 0 | 1 | | 18015376320243463 | 4000E3AA102707 | 0 | 1 | | 18015376320243464 | 4000E3AA102708 | 1 | 1 | | 18015376320243465 | 4000E3AA102709 | 0 | 1 | | 18015376320243466 | 4000E3AA10270A | 0 | 1 | | 18015376320243467 | 4000E3AA10270B | 0 | 1 | | 18015376320243468 | 4000E3AA10270C | 1 | 1 | | 18015376320243469 | 4000E3AA10270D | 0 | 1 | | 18015376320243470 | 4000E3AA10270E | 0 | 1 | | 18015376320243471 | 4000E3AA10270F | 0 | 1 | | 18015376320243472 | 4000E3AA102710 | 1 | 1 | | 18015376320243473 | 4000E3AA102711 | 0 | 0 | | 18015376320243474 | 4000E3AA102712 | 0 | 0 | | 18015376320243475 | 4000E3AA102713 | 0 | 1 | | 18015376320243476 | 4000E3AA102714 | 0 | 0 | | 18015376320243477 | 4000E3AA102715 | 0 | 0 | | 18015376320243478 | 4000E3AA102716 | 0 | 1 | | 18015376320243479 | 4000E3AA102717 | 0 | 1 | | 18015376320243480 | 4000E3AA102718 | 1 | 1 | | 18015376320243481 | 4000E3AA102719 | 0 | 1 | | 18015376320243482 | 4000E3AA10271A | 0 | 1 | | 18015376320243483 | 4000E3AA10271B | 0 | 1 | | 18015376320243484 | 4000E3AA10271C | 1 | 1 | | 18015376320243485 | 4000E3AA10271D | 0 | 1 | | 18015376320243486 | 4000E3AA10271E | 0 | 1 | +-------------------+----------------+-------------------+------------------------+
[12 Apr 2005 17:35]
Martin Friebe
trace
Attachment: mysqld.trace.gz (application/x-tar, text), 29.32 KiB.
[12 Apr 2005 17:42]
Martin Friebe
If I build teh mysql (4.1.11) without specifing the OPTIMIZED on the make line, the results for cast(a,10,10)=a are the same as for cast(a,10,10)=a+0.0 So I get viewer, but still some lines wrong
[12 Apr 2005 18:05]
Martin Friebe
I am also able to see under linux, however not sure what kind of installation, as this server had been installed by a 3rd party /usr/sbin/mysqld -V /usr/sbin/mysqld Ver 4.0.15-standard for pc-linux on i686 I tested several of the values above set @a = 18015376320243450; select conv(@a,10,10)=@a , conv(@a,10,10)=@a + 0.0; set @a = @a+1; the +0.0 returns true for all the values (tested about 40), the simple compare returns true on almost every 4th value. ###### In addition (by accident) I found this, and reproduced it on ALL of the servers: select 18015376320243473 = 18015376320243474 +0.0; +--------------------------------------------+ | 18015376320243473 = 18015376320243474 +0.0 | +--------------------------------------------+ | 1 | +--------------------------------------------+ 1 row in set (0.00 sec) the values are clearly different. mysql> select 18015376320243473 = 18015376320243474 ; +---------------------------------------+ | 18015376320243473 = 18015376320243474 | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.00 sec)
[13 Apr 2005 8:58]
Geert Vanderkelen
Hi Martin, Before going on, because I think this more a BSD/compilter issue, check following please: - http://dev.mysql.com/doc/mysql/en/freebsd.html - try the binaries provided for FreeBSD: http://dev.mysql.com/downloads/mysql/4.1.html - note that the float calculation has been improved in version 5.0 "select a, hex(a), conv(a,10,10) = a , conv(a,10,10) = a+0.00" gives all true on the MySQL 4.1 -- Generally Available (GA) release (Linux btw). Regards, Geert
[13 Apr 2005 11:03]
Sergei Golubchik
Martin, you mix different issues here, many of them are not bugs. See below: 1. select 18015376320243473 = 18015376320243474 +0.0; vs. select 18015376320243473 = 18015376320243474; and select a, hex(a), conv(a,10,10) This is not a bug. double has only 53 bits for mantissa (/usr/include/float.h) and ulonglong has 64 bits. Thus, two integers that are not equal (if compared as integers) may be equal when converted to double, because of this precision loss. 2. select '18015376320243459' =18015376320243459 and cast(a,10,10)=a vs. cast(a,10,10)=a+0.0 This is not a bug. Floating point computations are done in the FPU, and FPU registers are 80 bit, while double is 64 bit. That is, computations are done with the excess precision. As an optimization, gcc can reuse the result of the previous computations in the FPU without storing it in RAM as a double and loading into FPU again. As a result of this optimization, one of the operands of = may have excess precision while other does not, and they will not compare equal. See 'info gcc' for -ffloat-store In general (this concerns both 1 adn 2) it's a bad idea to compare floating point numbers with =. Floating point numbers are inherently inexact, and should always be compared as abs(f1 - f2) < epsilon. 3. create table tbl1 select '1052136747946823684'-0.0 , 1052136747946823684-0.0; This looks like a bug. Could you submit it separately, please ?
[13 Apr 2005 11:15]
Martin Friebe
I agree that any of the +0.0 are not bugs, and documenent. the create table issue is allready reported as seperate bug. (Sorry, for having mixed them in here) All the rest (below), if it can be reproduced, on anything ales but the systems I have (uptodate freebsd port build (with various compilers and freebsd versions), and outdated linux rpm (not sure of the orign of the rpm)). I will see, what happens if I download the precompiled version the only bug left here would be: select '18015376320243459' =18015376320243459; apparently, the conversion string to float, and integer to float are 2 different operations. I am not sure if they should be, and if so , that should be more explicitly documented, as it affects string to number comparisation in an unexpected way. This is that any computation with float is not exact. But on the same server, executing 2 times the same float expression, will give 2 times the same result. for example float_const1 + float_const2 = float_const1 + float_const2 for any 2 values, even if mixed with bigint, as long as both of the constant are the same on both sides. Therefore, it is crucial to know, if "string to float" and "integer to float" should be considered the same operation. If so we have a bug, if not all is ok, but I would urge to document more explicit, as the reults, might be unexpected, (workarounds are easy and availabele with cast())
[13 Apr 2005 13:41]
Sergei Golubchik
Whether "string to float" and "integer to float" should be considered the same operation...
I don't think they should. Basically
select '18015376320243459' =18015376320243459;
computes
(((((((((((((1.0*10+8.0)*10*10+1.0)*10+5.0)*10+3.0)*10+7.0)*10+6.0)*10+3.0)*10
+2.0)*10*10+2.0)*10+4.0)*10+3.0)*10+4.0)*10+5.0)*10+9.0 ==
(double)18015376320243459
the left part is all calculated using floating point numbers, so it will not necessarily be
*exact* value of the right part.
[13 Apr 2005 14:03]
Martin Friebe
Thanks, for the excellent explanation. Yes fully agree that it is not a bug, with the given background. Reviewed the doc, and I will add a feature request, to point out the hidden calculation in this conversion, aswell as linking from certain pages. Thanks
[5 May 2006 21:33]
Paul DuBois
I've updated these sections with additional information about type conversion and floating-point comparisons: http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html I've also added a note about how master/slave differences can arise from floating-point operations: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html See also Bug#9871.
Description: A Statement like select '1' = 1; returns true. According to the documentation, this is compared as floating point numbers. The same statement, fails if the value is from the bigint range (not sure if it is possible to find values from the int range). Both values should be converted into the same floating point number, so the result should always be true select '18015376320243459' =18015376320243459; +----------------------------------------+ | '18015376320243459' =18015376320243459 | +----------------------------------------+ | 0 | +----------------------------------------+ #but select '1052136747946823684' = 1052136747946823684; +---------------------------------------------+ | '1052136747946823684' = 1052136747946823684 | +---------------------------------------------+ | 1 | +---------------------------------------------+ #This is especially annoying, for result of conv(). select conv('004000E3AA102703',16,10), conv('004000E3AA102703',16,10) = 18015376320243459; +--------------------------------+----------------------------------------------------+ | conv('004000E3AA102703',16,10) | conv('004000E3AA102703',16,10) = 18015376320243459 | +--------------------------------+----------------------------------------------------+ | 18015376320243459 | 0 | +--------------------------------+----------------------------------------------------+ How to repeat: select '18015376320243459' =18015376320243459; Suggested fix: -