Wednesday, November 2, 2011

Floating-Point Number Equality Comparison in Oracle, Sybase / SQL Server, Java and JMS

As you know that computer can't store all floating-point numbers exactly primarily due to memory constraint. A double value such as 0.1 is actually represented as 0.1000000000000000055511151231257827021181583404541015625 in computer memory. Due to rounding errors, your equality comparison on floating-point numbers will probably fail.
In this topic I will show how SQL and Java store exact floating-point numbers (or just decimal numbers), and how floating-point literals are represented.

SQL99 uses FLOAT, REAL and DOUBLE PRECISION for approximate floating-point numbers, and NUMERIC(p,s) / DECIMAL (p,s) for exact floating-point numbers. For floating-point literals, SQL99 stores them as approximate floating-point types if they use the E notation (scientific notation); otherwise as the exact floating-point type if the literals meet the precision and scale of the exact types.
Because the NUMERIC(p,s) / DECIMAL (p,s) stores values without loss of precision for your decimal numbers, it should be used if you want to compare the equality between a floating-point column (in DB) or variable (in Java) to a decimal literal such as 0.1.
Different database vendors may have a bit different variants from the above specification.

Oracle 10g uses NUMBER(p,s) for both floating-point types in addition to its BINARY_FLOAT, BINARY_DOUBLE and FLOAT(p) for approximate types. Make sure you always specify the precision (p) for exact types.
If a floating-point is sufficed by f  or F (for BINARY_FLOAT), or d or D (for BINARY_DOUBLE), it is of approximate types; otherwise of the exact NUMBER(p,s) type.

Sybase ESA 15 and MS SQl Server 2008 both comply with the above SQL99 standard very well. And both also have MONEY(SMALLMONEY) for exact float-point numbers.

Java has BigDecimal to store decimal values exactly. For example new BigDecimal("0.1")  stores 0.1 exactly.

JMS's message selector is a string whose syntax is based on a subset of SQL92. Unfortunately JMS doesn't support exact floating-point numbers and restricts its exact numeric literal only to those without a decimal.

No comments:

Post a Comment