MySQL numeric data types select problem

[barns101]

I have a field set up as follows:

enum('0.00', '0.50', '1.00', '1.50', '2.00', '2.50', '3.00', '3.50', '4.00', '4.50')

I'm getting differing results when selecting records using the less than or equal operator.

SELECT * FROM `table` WHERE `min_return` <= 2.128
-> 24

But

SELECT * FROM `table` WHERE `min_return` <= '2.128'
-> 34

I realise it's something to do with how numbers are treated or compared and I've looked at the manual page for comparison operators but can't find a solution. I looked at cast() but it mainly deals with integers as far as I can see.

Can anyone shed some light on it? Thanks in advance.

[SteveLetwin]

This is really more of a problem with enum than numeric comparisons.

I made a table which contains a similar enum to yours.

mysql> describe enumtry;
_
+-------+-------------------------------------------------------------------------------+------+-----+---------+-------+
¦ Field ¦ Type ________________________________________________________________________ ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+-------+-------------------------------------------------------------------------------+------+-----+---------+-------+
¦ thing ¦ enum('0.00','9.99','0.50','0.99','1.00','1.50','2.00','2.50','3.00','200.00') ¦ YES_ ¦ ___ ¦ NULL __ ¦ _____ ¦
+-------+-------------------------------------------------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Then I ran a query comparing it to different values.

mysql> select thing as e, cast(thing as unsigned) as i, thing <= '1.00' as "'1.00'",thing <= 1.00 as "1.00" ,thing <= '1' as "'1'", thing <= 1_as "1", thing <= '1.58' as "'1.58'", thing <= 1.58 as "1.58", thing <= '10.58' as "'10.58'", thing <= 10.58 as "10.58", thing <= '1.a' as "'1.z'" from enumtry order by thing;
+--------+------+--------+------+------+------+--------+------+---------+-------+-------+
¦ e ____ ¦ i __ ¦ '1.00' ¦ 1.00 ¦ '1'_ ¦ 1 __ ¦ '1.58' ¦ 1.58 ¦ '10.58' ¦ 10.58 ¦ '1.z' ¦
+--------+------+--------+------+------+------+--------+------+---------+-------+-------+
¦ ______ ¦ __ 0 ¦ ____ 1 ¦ __ 1 ¦ __ 1 ¦ __ 1 ¦ ____ 1 ¦ __ 1 ¦ _____ 1 ¦ ___ 1 ¦ ___ 1 ¦
¦ 0.00 _ ¦ __ 1 ¦ ____ 1 ¦ __ 1 ¦ __ 1 ¦ __ 1 ¦ ____ 1 ¦ __ 1 ¦ _____ 1 ¦ ___ 1 ¦ ___ 1 ¦
¦ 9.99 _ ¦ __ 2 ¦ ____ 0 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 0 ¦ __ 0 ¦ _____ 0 ¦ ___ 1 ¦ ___ 0 ¦
¦ 0.50 _ ¦ __ 3 ¦ ____ 1 ¦ __ 0 ¦ __ 1 ¦ __ 0 ¦ ____ 1 ¦ __ 0 ¦ _____ 1 ¦ ___ 1 ¦ ___ 1 ¦
¦ 0.99 _ ¦ __ 4 ¦ ____ 1 ¦ __ 0 ¦ __ 1 ¦ __ 0 ¦ ____ 1 ¦ __ 0 ¦ _____ 1 ¦ ___ 1 ¦ ___ 1 ¦
¦ 1.00 _ ¦ __ 5 ¦ ____ 1 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 1 ¦ __ 0 ¦ _____ 1 ¦ ___ 1 ¦ ___ 1 ¦
¦ 1.50 _ ¦ __ 6 ¦ ____ 0 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 1 ¦ __ 0 ¦ _____ 1 ¦ ___ 1 ¦ ___ 1 ¦
¦ 2.00 _ ¦ __ 7 ¦ ____ 0 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 0 ¦ __ 0 ¦ _____ 0 ¦ ___ 1 ¦ ___ 0 ¦
¦ 2.50 _ ¦ __ 8 ¦ ____ 0 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 0 ¦ __ 0 ¦ _____ 0 ¦ ___ 1 ¦ ___ 0 ¦
¦ 3.00 _ ¦ __ 9 ¦ ____ 0 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 0 ¦ __ 0 ¦ _____ 0 ¦ ___ 1 ¦ ___ 0 ¦
¦ 200.00 ¦ _ 10 ¦ ____ 0 ¦ __ 0 ¦ __ 0 ¦ __ 0 ¦ ____ 0 ¦ __ 0 ¦ _____ 0 ¦ ___ 1 ¦ ___ 0 ¦
+--------+------+--------+------+------+------+--------+------+---------+-------+-------+
11 rows in set (0.00 sec)

The problem is that an enum is sorta like a table, in that for each value there's a numeric index (i in the table above) and there's the string value associated with it (e in the table above). So for you application you might want to rethink using enums at all, because they are strings with associated indexes, not normal numbers.

[edited by: SteveLetwin at 8:44 pm (utc) on Aug. 23, 2007]

[barns101]

Thanks for your reply, Steve. I'll test the application using a decimal(3,2) data type instead of enums.