Query on Mysql View not Working properly

Question

I run a very simple query on a view it works with one value and doesn't work with all others. i'm trying to select rows from the view with different index value only index 1.5 return results

The following is a sample of the view table

I have this View easylens

select * from easylens

+---+----+------+----+-----+-----+-----+-------+--------+
|id |type|design|name|brand|index|color|coating|material|
+---+----+------+----+-----+-----+-----+-------+--------+
| 1 | sv |aase  |nel |hoya |  1.5|292  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 2 | sv |base  |tel |zeri |  1.5|293  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 3 | sv |case  |fel |essi |  1.5|294  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 4 | sv |dase  |gel |hoya |  1.6|293  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 5 | sv |fase  |rel |essi |  1.6|293  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 6 | sv |gase  |mel |hoya |  1.6|292  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+

when I run

select * from easylens where `index`=1.5

I get

+---+----+------+----+-----+-----+-----+-------+--------+
|id |type|design|name|brand|index|color|coating|material|
+---+----+------+----+-----+-----+-----+-------+--------+
| 1 | sv |aase  |nel |hoya |  1.5|292  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 2 | sv |base  |tel |zeri |  1.5|293  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+
| 3 | sv |case  |fel |essi |  1.5|294  |ar     |plastic |
+---+----+------+----+-----+-----+-----+-------+--------+

but when I run

select * from easylens where `index`=1.6

I get

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0002 sec)

Change it to DECIMAL - or it's probably sufficient to define precision and scale in FLOAT - but I'm not sure why — Strawberry, Sep 10 '14 at 11:00
Check this post may helpful http://stackoverflow.com/questions/2567434/mysql-floating-point-comparison-issues — Sadikhasan, Sep 10 '14 at 11:03
additional http://stackoverflow.com/questions/1302243/selecting-a-float-in-mysql — Mikhail Timofeev, Sep 10 '14 at 11:05

score 2 · Accepted Answer · answered Sep 10 '14 at 11:09

The perils of float (with undefined precision and scale)

SELECT * FROM easylens WHERE `index` = 1.6000000238418578;
+----+------+--------+------+-------+-------+-------+---------+----------+
| id | type | design | name | brand | index | color | coating | material |
+----+------+--------+------+-------+-------+-------+---------+----------+
|  4 | sv   | dase   | gel  | hoya  |   1.6 |   293 | ar      | plastic  |
|  5 | sv   | fase   | rel  | essi  |   1.6 |   293 | ar      | plastic  |
|  6 | sv   | gase   | mel  | hoya  |   1.6 |   292 | ar      | plastic  |
+----+------+--------+------+-------+-------+-------+---------+----------+

score 1 · Answer 2 · answered Sep 10 '14 at 11:12

1

Try to CAST

SELECT * FROM easylens WHERE CAST(`index` as DECIMAL) = CAST(1.6 as DECIMAL);

answered Sep 10 '14 at 11:12

Sadikhasan

18,365
21
80
122

it is not about the casting as much as it is about specifying length Decimal(M,D) float(M,D) didn't work too – Bakly Sep 10 '14 at 11:45

Query on Mysql View not Working properly

2 Answers2