What's wrong with this MySQL SELECT statement?

[BigToque]

I have a table called "schedule" and that table has a field called "year". "year" is a float value.

All fields contains one of these entries:

2004.11
2004.12
2004.13
2004.21
2004.22
2004.23
2004.31
2004.32
2004.33

This is my statement:

SELECT * FROM `schedule` WHERE year = 2004.32

The query runs and is supposed to select all records from the 2004.32 season, but it doesn't select any data.

 

[Armitage]

Exact matches on floating point data can be dicey - the value may actually be stored as 2004.3199999999, which doesn't equal 2004.32 as you asked. Try to stay away from that kind of comparison with floats.

Either consider representing your data differently, or write your query like:

SELECT * FROM `schedule` WHERE year > 2004.315 and year < 2004.325

Personally, I'd take a closer look at your data types.

 

[BigToque]

Originally posted by: Armitage
Exact matches on floating point data can be dicey - the value may actually be stored as 2004.3199999999, which doesn't equal 2004.32 as you asked. Try to stay away from that kind of comparison with floats.

Either consider representing your data differently, or write your query like:

SELECT * FROM `schedule` WHERE year > 2004.315 and year < 2004.325

Personally, I'd take a closer look at your data types.

Hmm

The reason the fields are stored as floats is because it seems like the most simple method. This is for my hockey team and the seasons are broken down like this:

Spring League, Summer League, Winter League. Within each league there is preseason, regular season and playoffs. I'm not really sure of another way to sort the seasons.

Edit:

BTW, your suggestion worked.

 

[kamper]

Heh, I once had to test code where a float value was used like an enumeration, using direct comparisons like this 🙁

I also vote for a data type change, but, barring that, the range is your best bet. It can usually be a very, very small range.

By way of explanation, if you've ever tried to convert binary floating point numbers to decimal and vv, you'll have noticed that numbers that are defined in a finite number of digits in binary are frequently not so in decimal and the same the other way around. So 2004.32 apparently can't be accurately described in the number of bits available to your data type (if at all).

 

[kamper]

Originally posted by: Stefan
Spring League, Summer League, Winter League. Within each league there is preseason, regular season and playoffs. I'm not really sure of another way to sort the seasons.

Sounds like you're trying to squish three pieces of information into one field. Any reason you can't have 3 fields?

 

[BigToque]

Originally posted by: kamper

Originally posted by: Stefan
Spring League, Summer League, Winter League. Within each league there is preseason, regular season and playoffs. I'm not really sure of another way to sort the seasons.

Sounds like you're trying to squish three pieces of information into one field. Any reason you can't have 3 fields?

I guess I could just have year, league and season fields 🙂 lol

 

[Armitage]

Originally posted by: Stefan

Originally posted by: kamper

Originally posted by: Stefan
Spring League, Summer League, Winter League. Within each league there is preseason, regular season and playoffs. I'm not really sure of another way to sort the seasons.

Sounds like you're trying to squish three pieces of information into one field. Any reason you can't have 3 fields?

I guess I could just have year, league and season fields 🙂 lol

Yep, that's how I'd do it. Much easier and more flexible in the long run.
With your system, how do you get all of the summer league stats, regardless of year? Lots of parsing of the results - messy.

CREATE TABLE season (
year SMALLINT UNSIGNED NOT NULL,
league ENUM('spring', 'summer', 'winter') NOT NULL,
season ENUM('preseason', 'regular', 'playoffs') NOT NULL);

With something like that you just say
SELECT * FROM season WHERE league='summer';

 

[EagleKeeper]

floats are not hardly ever equal

Use two ints or a string for what you are trying to do.