CODEWITHSUNDEEP
mysqlsqldatabase
Bakly
Bakly

Reputation: 650

Query on Mysql View not Working properly

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)

Upvotes: 0

Views: 1809

Answers (2)

Sadikhasan
Sadikhasan

Reputation: 18598

Try to CAST

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

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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  |
+----+------+--------+------+-------+-------+-------+---------+----------+

Upvotes: 2

Related Questions