mysql decimal query issue

Question

First off I am running mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

Sorry for the formatting but I don't have the ability to copy it so I have to type it in.

the two fields in question are the following

id int(11)
startj1950 decimal(38,6)



mysql> select id,startj1950 from store where id = 32513;

+------------------+
| id       | startj1950           |
+----------|----------------------|
|32513     | 1912181654.500000    |
-----------------------------------

mysql> select id from store where startj1950=1912181654.500000;
Empty set

So I figured I was messing up somehow in the copy so I would let mysql get the value for me.

mysql> select id from store where startj1950=(select startj1950 from store where id=32513);
Empty set <br/>

But it works for other results

mysql> select id,startj1950 from store where id = 32513;

+------------------+
| id       | startj1950           |
+----------|----------------------|
|18675     | 1907365784.570000    |
-----------------------------------

mysql> select id from store where startj1950=1907365784.570000;

+----------+
| id       |
+----------|
|18675     |
------------

mysql> select id from store where startj1950=(select startj1950 from store where  id=18675);<br/>

+----------+
| id       |
+----------|
|18675     |
------------

I figure I either have hit a mysql bug or I am misunderstanding some sort of concept. Thanks in advance for the help.

Answer 1

decimal (38, 6) will be a floating-point type, with all the difficulties associated with that type. In this particular case, it means that some of the values displayed are not exact values. The size of that column leads me to suspect the underlying type is already double-precision, so to enquire on this column you need to do something like...

SELECT *
    FROM store
    WHERE startj1950 BETWEEN somevalue - @delta AND somevalue + @delta;

and fiddle with the value of delta until you get the desired results, or redesign the column so that you can use INTEGER or CHAR types.