Hi all,

I have this very odd problem I can't explain so I'm hoping one of you
can. I'll try to keep this post as short and simple as possible:

I have a table named rt_hour with this table lay-out:
+-----------+------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+-------------------+-------+
| server_id | int(11) | NO | PRI | | |
| date | timestamp | NO | PRI | CURRENT_TIMESTAMP | |
| rt | float(6,2) | NO | | | |
+-----------+------------+------+-----+-------------------+-------+

This table contains some records, including this one:
+-----------+---------------------+------+
| server_id | date | rt |
+-----------+---------------------+------+
| 9 | 2008-02-03 17:30:00 | 0.78 |
+-----------+---------------------+------+

Running this query shows me that record:
SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
17:30:00');

However, when I run this query, it returns nothing!
SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
17:30:00' AND rt = 0.78);

If you know how this happens, please let me know. Is this a bug in my
MySQL version (5.0.32, running on i468 Linux) or is there a problem with
the table lay-out?

Kind Regards,
Vincent van Scherpenseel

On Thu, 07 Feb 2008 11:53:17 +0100, Vincent van Scherpenseel
<please.reply@newsgroup.invalid> wrote:

> Hi all,
>
> I have this very odd problem I can't explain so I'm hoping one of you
> can. I'll try to keep this post as short and simple as possible:
>
> I have a table named rt_hour with this table lay-out:
> +-----------+------------+------+-----+-------------------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+------------+------+-----+-------------------+-------+
> | server_id | int(11) | NO | PRI | | |
> | date | timestamp | NO | PRI | CURRENT_TIMESTAMP | |
> | rt | float(6,2) | NO | | | |
> +-----------+------------+------+-----+-------------------+-------+
>
> This table contains some records, including this one:
> +-----------+---------------------+------+
> | server_id | date | rt |
> +-----------+---------------------+------+
> | 9 | 2008-02-03 17:30:00 | 0.78 |
> +-----------+---------------------+------+
>
> Running this query shows me that record:
> SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
> 17:30:00');
>
> However, when I run this query, it returns nothing!
> SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
> 17:30:00' AND rt = 0.78);
>
> If you know how this happens, please let me know. Is this a bug in my
> MySQL version (5.0.32, running on i468 Linux) or is there a problem with
> the table lay-out?


Floating point precision problem. Decide the precision you need (I'd say
0.005):
SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
17:30:00' AND rt BETWEEN 0.78 - 0.005 AND 0.78 + 0.005);

.... which is why I prefer working with integers.
--
Rik Wasmus

Rik Wasmus wrote:
> <snip>
> Floating point precision problem. Decide the precision you need (I'd say
> 0.005):
> SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
> 17:30:00' AND rt BETWEEN 0.78 - 0.005 AND 0.78 + 0.005);
>
> ... which is why I prefer working with integers.

Ah, that makes sense - Does the DECIMAL datatype also has this
precision problem, or is that more like an integer?

Thank you for your time,
Vincent van Scherpenseel

"Vincent van Scherpenseel" <please.reply@newsgroup.invalid> schreef in
bericht news:47aaea20$0$32516$bf4948fe@news.tele2.nl...
> Rik Wasmus wrote:
>> <snip>
>> Floating point precision problem. Decide the precision you need (I'd say
>> 0.005):
>> SELECT * FROM rt_hour WHERE (server_id = 9 AND date = '2008-02-03
>> 17:30:00' AND rt BETWEEN 0.78 - 0.005 AND 0.78 + 0.005);
>>
>> ... which is why I prefer working with integers.
>
> Ah, that makes sense - Does the DECIMAL datatype also has this
> precision problem, or is that more like an integer?
>
> Thank you for your time,
> Vincent van Scherpenseel


http://dev.mysql.com/doc/refman/5.0/...ric-types.html
The DECIMAL and NUMERIC data types are used to store exact numeric data
values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to
store values for which it is important to preserve exact precision, for
example with monetary data.
As of MySQL 5.0.3, DECIMAL and NUMERIC values are stored in binary format.
Previously, they were stored as strings, with one character used for each
digit of the value, the .....


so, if you change the float(6,2) to decimal, your problem should also been
solved.


--
Luuk

Luuk wrote:

> so, if you change the float(6,2) to decimal, your problem should also been
> solved.

Thank you very much

- Vincent van Scherpenseel