SQL comparison of integer columns against float numbers is not correct

[GoogleCodeExporter]

sorry I'm in a hurry, H2 Implementation-Version: 1.2.132,
code to reproduce the problem:

 CREATE TABLE testcase(num integer);

 insert into testcase(num)values(1);

 commit;

 -- the following select should return the previously inserted row but it
doesn't
 select * from testcase where num >0.5

Original issue reported on code.google.com by robert.s...@googlemail.com on 25 Mar 2010 at 5:53

[GoogleCodeExporter]

The following workaround may work in some cases. However, in my case the query 
is
generated inside a library and can not be changed.

I had a first look at the code and saw that org.h2.value.Value#compareTo usually
should take care of different types.

WORKAROUND:

 -- multiply integer colum vlues with 1.0 in order to force type conversion
 select * from testcase where 0.5<=num*1.0

Original comment by robert.s...@googlemail.com on 26 Mar 2010 at 9:19

[GoogleCodeExporter]

Your are right, this is a bug. The problem is that H2 tries to convert the 
parameter
/ constant to the column type (if it's a comparison with a column). This caused
problems in the past. I think the only solution is to convert when running the 
query,
when actually comparing the values. It's a tiny bit slower if the data types 
don't
match, but it shouldn't affect performance in the normal case. And it's the only
solution (I know) that always works.

Original comment by thomas.t...@gmail.com on 26 Mar 2010 at 5:38

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.2.133

Original comment by thomas.t...@gmail.com on 10 Apr 2010 at 8:45

• Changed state: Fixed