MySQL well formed query doesn't retrieve any result using equal and between

Question

So, having these rows on table

## raw_material_costs ##
id    code      family     material min_size    max_size    value
651   100005    sleeve     SAE 1020 1.21        1.24        4.48882
659   100006    sleeve     SAE 1020 1.21        1.24        6.62878

Running the following query directly on the SQL tab doesn't return any of these rows. I want to get the row if the value (in this case is 1.21) is equal to min_size or is between min_size and max_size. (Tried to use the BETWEEN comparison before and got the same result)

SELECT * FROM (`raw_material_costs`) 
WHERE `raw_material_costs`.`family` =  'sleeve' 
AND `raw_material_costs`.`material` =  'SAE 1020' 
AND (
    ( `raw_material_costs`.`min_size` = '1.21' )
    OR ( `raw_material_costs`.`min_size` >= '1.21' AND `raw_material_costs`.`max_size` <=   '1.21' )
)

EDIT1: Forgot to mention the value used.

Fixed query (solution pointed by cjg[look below])

SELECT *
FROM (`raw_material_costs`)
WHERE `raw_material_costs`.`family` =  'sleeve'
AND
`raw_material_costs`.`material` =  'SAE 1020'
AND   (
(
CAST( `min_size` as DECIMAL(10,2) ) =
1.21
  )
OR     (
CAST( `min_size` as DECIMAL(10,2) ) >= '1.21'
AND CAST( `max_size` as
DECIMAL(10,2) ) <= '1.21'
)
)

You are comparing apples (numbers) to oranges (strings). `1.21` is anumber `'1.21'` is a string. Don't put single quotes around numbers - I wouldn't be surprised it the implicit casting that you are relying on is the cause of this (btw: the parantheses around the table name are totally useless) — , Feb 24 '14 at 17:28
For that matter, you don't need to put single quotes (or backticks) around table/column names, either. `WHERE raw_material_costs.family = 'sleeve'` is just fine and a lot more readable... — TypeIA, Feb 24 '14 at 17:29
Backticks are needed if the table or field name could be confused with a MySQL keyword. It doesn't _hurt_ to use backticks. Since there's only one table involved, I would ditch the `raw_material_costs.` prefix -- it's not necessary. — Phil Perry, Feb 24 '14 at 17:31
If you are constraining max_size to be <= 1.21, why would you expect to get any rows returned? Generally, you're going to have a "size" parameter that you want to match the row if min_size <= size <= max_size? — Phil Perry, Feb 24 '14 at 17:33
Yes, the "size" is 1.21, that's why it needs to be equal `min_size` OR between `min_size` and `max_size`. — forsureitsme, Feb 24 '14 at 17:39

score 1 · Answer 1 · answered Feb 24 '14 at 17:28

1

You're treating min_size and max_size as strings of text in your sql query, try removing the apostrophes from the min and max size:

You also have max_size filtered by 1.21, instead of 1.24, as pointed out by Gorkk in the comments.

SELECT * FROM (`raw_material_costs`) 
WHERE `raw_material_costs`.`family` =  'sleeve' 
AND `raw_material_costs`.`material` =  'SAE 1020' 
AND (
    ( `raw_material_costs`.`min_size` = 1.21 )
    OR ( `raw_material_costs`.`min_size` >= 1.21 AND `raw_material_costs`.`max_size` <=   1.24 )
)

answered Feb 24 '14 at 17:28

steinmas

398
3
9

1

An there's the fact he filters on `max_size <= 1.21` when his rows have `max_size = 1.24` – Gorkk Feb 24 '14 at 17:29
Ah, good catch. Completely missed that one. I'll edit my answer. – steinmas Feb 24 '14 at 17:29
Just ran it without the apostrophes and still no result. By the way, edited the answer to say that I have a value coming from the script to compare(1.21);."SELECT * FROM (`raw_material_costs`) WHERE `raw_material_costs`.`family` = 'sleeve' AND `raw_material_costs`.`material` = 'SAE 1020' AND ( ( `raw_material_costs`.`min_size` = 1.21 ) OR ( `raw_material_costs`.`min_size` >= 1.21 AND `raw_material_costs`.`max_size` <= 1.21 ) )" – forsureitsme Feb 24 '14 at 17:37

score 0 · Answer 2 · answered Feb 24 '14 at 17:29

0

Try like

SELECT * FROM 
`raw_material_costs`
WHERE `family` =  'sleeve' 
AND `material` =  'SAE 1020' 
AND `min_size` <= `max_size`

answered Feb 24 '14 at 17:29

Rahul

76,197
13
71
125

score 0 · Accepted Answer · edited May 23 '17 at 10:26

If you data type of the fields is float, there is very high probability that numbers matches, the floating point comparison might not be proper as there can be approximation. So even if you say its 1.21 it might be saved actually 1.20000001 or 1.9999999997 or so. To compare two floating point number, cast to decimal and compare.

CAST(`fields` as DECIMAL(10,2))

A similar question: MySQL floating point comparison issues

MySQL well formed query doesn't retrieve any result using equal and between

3 Answers3