Field query problem

Hi,


if fields are defined as double/float, they dont allow <= >= !=





Another issue is, that complex queries with pre-calculated fields can not be saved or entered as a query like in the chemical terms filter, which is pretty inconvinient. So if I have lots of repaeting queries, I have a problem, and the chemicla terms filter only calculates on the fly. And fields can have the same name as a property like logP().





Tobias

> if fields are defined as double/float,


> they dont allow <= >= !=





Yes, this is by design. Equality operations are not safe for floating point numbers, particularly when numbers are comning from different systems (e.g. a database and Java). Although 2 numbers might both display as 1.234556789 differences in precission can mean that they are not actually equal.


The "best" way to do equality with floating point numbers is to provide a range.


eg. 0.999999 < x < 1.000001 is a much safer way of testing for the value 1.0000000





This is why equality operators are supported for Integers, but not for floating point numbers.








> Another issue is, that complex queries with


> pre-calculated fields can not be saved





Our planned solution for this is to allow queries to be stored so that they can be re-run. But this isn't yet implemented.

tdudgeon wrote:

> if fields are defined as double/float, > they dont allow <= >= != Yes, this is by design. Equality operations are not safe for floating point numbers, particularly when numbers are comning from different systems (e.g. a database and Java). Although 2 numbers might both display as 1.234556789 differences in precission can mean that they are not actually equal. The "best" way to do equality with floating point numbers is to provide a range. eg. 0.999999 < x < 1.000001 is a much safer way of testing for the value 1.0000000 This is why equality operators are supported for Integers, but not for floating point numbers.

Hi,


this sounds like: our Mercedes XLS can reach 300 km/h but we limited the machine speed to 50 km/h because its much more save. I think all other DBs allow such queries and why should I introduce so much hazzle (I have to pre-sort each field by hand to find the maximum or minimum value and then define a range)? If Lipinskis says all below 5.0 then how people did it in the past? This is pure mathematics and power should be give to the user and not the design.


Tobias

Its only equality oeprators that are not sensible with floating point numbers.


Searching for a logP LESS THAN 1.23456789 is sensible, safe, and allowed in IJC.


Searching for a logP EQUAL TO 1.23456789 is not safe (and probably not sensible), and is not allowed. Because of precission differences (not to mention the inherrant error in a logP prediction), the most sensible way to run this sort of query is to specify it as a range query (e.g 4.9 < logP < 5.1), using what ever range is appropriate. Again, this is supported by IJC.

Hi Tim


I know what you want to say and I agree on the floating point stuff, but I would say all statistics packages we have allow operations with floating points, and if I say: please select all values less 5.0 - the 5.00001 has to stay outside - because its defined in that way. There is a nice description of the IEEE Standard for Double Precision Floating Point numbers from Chip Pearson http://www.cpearson.com/excel/rounding.htm and as long nobody else complains, im fine with the ranges - not :-)


Tobias