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.

It looks to me as if you are selecting two different things - one statement uses a quoted number, and selects by the value in the field the second example selects by the implicit index (key) of your enumerated list where:
0->'0.00', 1->'0.50',2->'1.00', 3->'1.50', etc.

I'm just guessing that if you want to select by value, keep the selection criteria quoted.

I hate MySQL. I just gotta learn to love it.

The quoted one most likely compares alphabetically.

Thank you both for your replies.

The quoted one most likely compares alphabetically.

That sounds about right. So how do I compare them numerically?

Larger numbers, such as 14.951, behave differently. The quoted query returns fewer results than the none-quoted one, which is the opposite of the problem when using a smaller number such as 2.128:

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

But

SELECT * FROM `table` WHERE `min_return` <= '14.951'
-> 30

So neither adding quotes nor leaving them out can be trusted return an accurate number of results because it differs depending on the size of the number.

From what I know, the enum data type always consists of strings, so you'd most likely have to do an explicit conversion of each entry into a number before comparing.

I though that it'd be something to do with the data type being a string somewhere along the line!

Thanks again.

Back to the manual :gag:

I hate MySQL. I just gotta learn to love it.

I share your pain:(.

you might get it to work using a cast function

SELECT * FROM `table` WHERE cast( `min_return` as float ) <= 2.128

I haven't tried it so it still might pick up the ordinal value rather than the string

...
0->'0.00', 1->'0.50',2->'1.00', 3->'1.50', etc...

not quite right

0->'', 1->'0.00', 2->'0.50', 3->'1.00', 4->'1.50', etc

any string which isn't in the list evaluates to a null string on an insert operation and null strings have a zero enumeration value

well according to my manual anyway

Thanks esme, I had a smilar idea, but cast() only takes the following types: BINARY[(N)], CHAR[(N)], DATE, DATETIME, DECIMAL, SIGNED [INTEGER], TIME, UNSIGNED [INTEGER].

I think I'm going to change the enum to decimal(3,2) and see how that goes.

If all else fails, multiply the enum values by 10 (or how ever many D.P. you want) and use whole numbers instead.

If it want a float result, divide it by 10.

If all else fails, multiply the enum values by 10 (or how ever many D.P. you want) and use whole numbers instead.

If it want a float result, divide it by 10.

hmm not 100% sure about that, the manual says if you want the ordinal value of the enum variable rather than the string then perform a math op on it like adding zero, so multiplying by 10 may just return 10 times the ordinal value

... I'm going to have to check this, back soon

just done a quick test and multiplying by 10 does seem to pick up the string value and convert it to a number

good one John, I didn't know that :)