| Bug #22125 | Falcon: Double precision searches fail if index exists | ||
|---|---|---|---|
| Submitted: | 8 Sep 2006 19:39 | Modified: | 3 Dec 2007 14:24 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S2 (Serious) |
| Version: | 5.2 Debug | OS: | Linux (SUSE 10.0 / 64-bit) |
| Assigned to: | Christopher Powers | CPU Architecture: | Any |
[13 Sep 2006 14:05]
Hakan Küçükyılmaz
Reproduced on Linux 32-bit with change set 1.2287, 2006-09-12. Added test case falcon_bug_22125.test with slight modifications to 5.1-falcon tree. Regards, Hakan
[22 Nov 2006 7:24]
Calvin Sun
It is "almost" right: *** r/falcon_bug_22125.result 2006-09-13 17:19:28 +03:00 --- r/falcon_bug_22125.reject 2006-11-22 09:27:25 +03:00 *************** *** 28,36 **** f1 f2 f3 f4 -1.79769313486232e+308 -inf 0 -6 -1.79769313486232e+308 -max 0 -3 ! -1.79769313486232e+308 -nan -0 -5 -5.562684646268e-309 -min 0 -4 ! -0 -zero 0 -1 0 zero 0 1 5.562684646268e-309 min 0 4 1.79769313486232e+308 inf 0 6 --- 28,36 ---- f1 f2 f3 f4 -1.79769313486232e+308 -inf 0 -6 -1.79769313486232e+308 -max 0 -3 ! -1.79769313486232e+308 -nan 0 -5 -5.562684646268e-309 -min 0 -4 ! 0 -zero 0 -1 0 zero 0 1 5.562684646268e-309 min 0 4 1.79769313486232e+308 inf 0 6 *************** *** 38,44 **** 1.79769313486232e+308 nan 0 5 SELECT count(*) FROM t1 where f1 = 0; count(*) ! 2 SELECT count(*) FROM t1 where f1 < 1; count(*) 7 --- 38,44 ---- 1.79769313486232e+308 nan 0 5 SELECT count(*) FROM t1 where f1 = 0; count(*) ! 1 SELECT count(*) FROM t1 where f1 < 1; count(*) 7 ------------------------------------------------------- The real problem now is "-0" vs. "0". A simple test case: mysql> create table t7 (s1 double,s2 char(5),s3 double,s4 int) engine=falcon; Query OK, 0 rows affected (0.08 sec) mysql> insert into t7 values (0,'zero',0,1); Query OK, 1 row affected (0.00 sec) mysql> update t7 set s1 = -s1 where s2 = 'zero'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> create index it7 on t7 (s1); Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select count(*) from t7 where s1 = 0; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) If the index is created prior to the update, it works fine: mysql> create table t7 (s1 double,s2 char(5),s3 double,s4 int) engine=falcon; Query OK, 0 rows affected (0.06 sec) mysql> insert into t7 values (0,'zero',0,1); Query OK, 1 row affected (0.01 sec) mysql> create index it7 on t7 (s1); Query OK, 1 row affected (2.19 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> update t7 set s1 = -s1 where s2 = 'zero'; Query OK, 1 row affected (2.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select count(*) from t7 where s1 = 0; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
[10 Sep 2007 19:23]
Calvin Sun
not actively worked on, set back to verified.
[26 Oct 2007 5:15]
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/36399 ChangeSet@1.2652, 2007-10-26 00:15:02-05:00, chris@xeno.mysql.com +4 -0 Bug#22125 "Falcon: Double precision searches fail if index exists" Force DOUBLE values of -0 to 0.
[26 Oct 2007 5:22]
Christopher Powers
The internal representation of DOUBLE follows the IEEE Standard for Binary Floating-Point Arithmetic (IEEE 754). This means that -0 and 0 are computationally the same but different internally, so records with keys of type DOUBLE will be indexed separately for values of -0 and 0. To resolve the indexing issue, -0 is now coerced to 0, which is consistent with industry practice (e.g. Oracle) and with the MySQL documentation: "Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard."
[29 Nov 2007 23:51]
Hakan Küçükyılmaz
falcon_bug_22125.test is passing now.
[30 Nov 2007 20:43]
Bugs System
Pushed into 6.0.4-alpha
[3 Dec 2007 14:24]
MC Brown
A note has been added to the 6.0.4 changelog: Selecting information from a Falcon table using a DOUBLE column with an index would produce incorrect results.
Description: I create a table with an index and a double precision column. I insert some extreme values. I do two SELECT statements and get the wrong answers. The results would be correct if there was no index. How to repeat: mysql> create table t7 (s1 double,s2 char(5),s3 double,s4 int) engine=falcon; Query OK, 0 rows affected (0.01 sec) mysql> insert into t7 values (0,'zero',0,1); Query OK, 1 row affected (0.00 sec) mysql> insert into t7 values (1e308*2,'max',0,3); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t7 values (1e308*2,'min',0,4); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> update t7 set s1 = 1 / s1 where s2 = 'min'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update t7 set s1 = s1 / 999999999999999 where s2 = 'min'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t7 values (1e308*2,'nan',0,5); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t7 values (1e308*2,'inf',0,6); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t7 select -s1,concat('-',s2),s3,-s4 from t7; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> update t7 set s3 = -s3 where s2 = '-nan'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> create index it7 on t7 (s1); Query OK, 10 rows affected (0.03 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from t7 order by s1,s2,s3,s4; +------------------------+-------+------+------+ | s1 | s2 | s3 | s4 | +------------------------+-------+------+------+ | -1.79769313486232e+308 | -inf | 0 | -6 | | -1.79769313486232e+308 | -max | 0 | -3 | | -1.79769313486232e+308 | -nan | -0 | -5 | | -4.94065645841247e-324 | -min | 0 | -4 | | -0 | -zero | 0 | -1 | | 0 | zero | 0 | 1 | | 4.94065645841247e-324 | min | 0 | 4 | | 1.79769313486232e+308 | inf | 0 | 6 | | 1.79769313486232e+308 | max | 0 | 3 | | 1.79769313486232e+308 | nan | 0 | 5 | +------------------------+-------+------+------+ 10 rows in set (0.00 sec) mysql> select count(*) from t7 where s1 = 0; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t7 where s1 < 1; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)