Bug #47049 Boundaries are included in result when using a float in a range (< or >)

• Submitted: 1 Sep 2009 17:10
• Modified: 1 Sep 2009 22:03
• Reporter: Sheeri Cabral (Candidate Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.35, 4.1, 5.0, 5.1, 5.4 bzr
• OS: Any (SunOS 5.10 Generic_138889-02 i86pc i386 i86pc)
• CPU Architecture: Any
• Tags: <, >, boundary, FLOAT, qc, range

[1 Sep 2009 17:10] Sheeri Cabral

Description:
When you use > or < the boundary conditions should not be included in the result.  This does not work with FLOAT values with 2 or more digits of precision:

How to repeat:
CREATE TABLE `float_test` (
  `floatnum` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO float_test (floatnum) VALUES (0),(1.0),(0.1),(0.01),(0.001),(0.0001),(0.00001);

insert into float_test (floatnum) SELECT floatnum*-1 from float_test;

The following queries work as expected, and don't include the boundaries:

select * from float_test where floatnum > 0 and floatnum < 1.0;
(ie works as expected, doesn't include boundaries 0 and 1.0)

select * from float_test where floatnum > -1.0 and floatnum < 0;
select * from float_test where floatnum > 0 and floatnum < 0.1;
select * from float_test where floatnum > -0.1 and floatnum < 0;

The following, however, start to include the boundary with 2 or more digits of precision
select * from float_test where floatnum > 0 and floatnum < 0.01;
ie, 
+----------+
| floatnum |
+----------+
|     0.01 | 
|    0.001 | 
|   0.0001 | 
|    1e-05 | 
+----------+
4 rows in set (0.00 sec)

select * from float_test where floatnum > -0.01 and floatnum < 0;
select * from float_test where floatnum > 0 and floatnum < 0.001;
select * from float_test where floatnum > -0.001 and floatnum < 0;
select * from float_test where floatnum > 0 and floatnum < 0.0001;
select * from float_test where floatnum > -0.0001 and floatnum < 0;

select * from float_test where floatnum > -0.01 and floatnum < 0.01;
(this contains both boundary values, -0.01 and 0.01)

Suggested fix:
don't include boundary conditions in the result when using < or >, only when using =, >= and <=.

[1 Sep 2009 20:33] Sveta Smirnova

Thank you for the report.

Verified as described.

Really this can be considered as not a bug, because float values are not expected to be precise, but this case with boundaries can be fixed.

[1 Sep 2009 20:33] Sveta Smirnova

Workaround: use DECIMAL

[1 Sep 2009 22:03] Sergei Golubchik

not a bug. see http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html