CODEWITHSUNDEEP
mysql
Kaare
Kaare

Reputation: 531

mySQL returns empty even though it shouldn't?

am a total mySQL newbie, but I have no idea how to search for the answer to this, so that's why I'm bringing it here:

            DESCRIBE rParam;
            +----------------+---------------+------+-----+---------+-------+
            | Field          | Type          | Null | Key | Default | Extra |
            +----------------+---------------+------+-----+---------+-------+
            | p              | float         | YES  |     | NULL    |       |
            | d              | float         | YES  |     | NULL    |       |
            | LTP            | float         | YES  |     | NULL    |       |
            | LTD            | float         | YES  |     | NULL    |       |
            | alpha          | float         | YES  |     | NULL    |       |
            | N              | smallint(6)   | YES  |     | NULL    |       |
            | g              | float         | YES  |     | NULL    |       |
            | a              | float         | YES  |     | NULL    |       |
            | seed           | float         | YES  |     | NULL    |       |
            | startingWeight | float         | YES  |     | NULL    |       |
            | path           | varchar(1000) | YES  | UNI | NULL    |       |
            | type           | varchar(100)  | YES  |     | NULL    |       |
            +----------------+---------------+------+-----+---------+-------+
            12 rows in set (0.00 sec)


            SELECT p FROM rParam GROUP BY p;

            +--------+
            | p      |
            +--------+
            |      0 |
            |  0.001 |
            |  0.002 |
            |  0.003 |
            |  0.004 |
            |  0.005 |
            | 0.0075 |
            |  0.008 |
            |   0.01 |
            |  0.012 |
            | 0.0125 |
            |  0.014 |
            |  0.015 |
            |   0.02 |
            |  0.025 |
            |   0.03 |
            |  0.035 |
            |   0.04 |
            |   0.05 |
            |    0.1 |
            |    0.2 |
            |    0.3 |
            |    0.4 |
            |    0.5 |
            |    0.6 |
            |    0.7 |
            +--------+
            26 rows in set (0.00 sec)

I can get the results for one type of query:

            SELECT p FROM rParam WHERE p=0.5 GROUP BY p;
            +------+
            | p    |
            +------+
            |  0.5 |
            +------+
            1 row in set (0.00 sec)

However, changing the p-value that I'm asking for:

            SELECT p FROM rParam WHERE p=0.6 GROUP BY p;
            Empty set (0.00 sec)

but we can clearly see from the first output that there are rows for which p=0.6? This is a problem for most of the p-values - why does mySQL return the empty set?

Upvotes: 0

Views: 141

Answers (2)

Hubert Schölnast
Hubert Schölnast

Reputation: 8497

The value 0.6 can not be stored exactly in a float, but 0.5 can. Float numbers are stored binary. 1/2 can be stored exaclty, also 1/4 and 3/4, 1/8, 3/8, 5/8 and 7/8. But 0.6 can not be written as a fraction with a power of 2 on the denominator. So it can not be stored exactly in binary formats.

Upvotes: 2

knittl
knittl

Reputation: 265171

This sounds like an rounding error. Floating point numbers cannot always be represented exactly in binary form. Try applying some epsilon:

SELECT DISTINCT p
FROM rParam
WHERE ABS(p - 0.6) < 0.00001

If you want exact fractional numbers, use DECIMAL instead of FLOAT as column data type.

ps. Probably better to use SELECT DISTINCT instead of a GROUP BY.

Upvotes: 5

Related Questions