sam February 2016

greatest() function in mysql returns blob

I am trying to use the greatest function of mysql but if i am trying to add one column from the sub queries it is returning me the blob value , I tried using mysql workbench, i also tried from phpmyadmin but the results are same.

Below is my sql query :

select greatest(pm10index,pm2index,noindex,so2index,o3index,nh3index,coindex)
from table ;

Here the values are fetched from some sub queries.

The issue occurs when i add pm10index column in the function , If this particular column pm10index is removed from the function it returns the highest value correctly .

Below is the value for pm10index

pm10,
       (CASE 
       when pm10 = 0   then 0
       when pm10 <=50  then pm10
       when pm10 >50  and   pm10 <=100 then pm10 
       when pm10 >100 and   pm10 <=250 then 100+(pm10-100)*0.666
       when pm10 >250 and   pm10 <=350 then 200+(pm10-250)
       when pm10 >350 and   pm10 <=430 then 300+(pm10-350)*1.25
       when pm10 >430 then  400+(pm10-430)*1.25
       END) pm10index

pm10 is defined as varchar datatype , while all other fields are also defined as varchar

It will be great if you can help me here . Thanks in advance .

Answers


symcbean February 2016

Below is the value for pm10index ... pm10 is defined as varchar datatype

There is no varchar (or any type of string) in the code you provided. Even if you did cast the data to a varchar, your going to end up with odd results (try select greatest('2','100');).

While MySQL will do its best to compare strings and numbers, sooner or later its going to break. Unless you have at least consistent data types (and really it should be correct data types) your code cannot be relied upon.

Post Status

Asked in February 2016
Viewed 1,413 times
Voted 11
Answered 1 times

Search




Leave an answer