ver: ejb3-RC9

Hi all,
I'm doing simple addition and subtraction formulas in my JPA-QL's/EJB-QL's in the where clause of queries.

Example: select node from Balances node where (node.totalPrice - node.tax - node.retailPrice != 0)

All the properties are created from Hibernate/EJB3 from the BigDecimal java class, and on the database side it looks like it is creating the appropriate fields (i.e. 19,2 decimal fields by default).

My problem comes from inconsistent results from the same JPA QL. When trying to run the above query on Hypersonic, it works correctly/as-expected. When running on Mysql 4.1, run into floating-point equivelant miscalculations (i.e. different results than on Hypersonic).

Is there any way to alleviate this, or should I resort to evaluating all of the nodes as java objects for these calculations instead of database-side?

thanks,
-D

Somewhat resolved - Mysql doesn't handle decimal precision calculations until => 5.0.3

However, this makes me feel like you do build your application to a specific database to make sure these types of query-level mathematical calculations are done correctly.

Any comments on this?

thanks,
-D

Well at some point you database is supposed to deal with ANSI SQL in a correct fashion