Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

cfqueryparam not working with CF_SQL_FLOAT for negative exponent numbers with MSSQL

Description

There is a bug in handling of CF_SQL_FLOAT with negative exponent numbers, e.g. 0.00000001 (i.e. 1E-08).

This bug is a show stopper for us converting our codebase from ACF to Lucee.

Running SQL Profiler shows that a FLOAT value of 0.00000001 is being cast to 9.9999999392252903e-009, which is resulting in an incorrect query where clause.

If the parameter is specified as CF_SQL_NUMERIC it works correctly.

I am using the MS SQL Driver v7.2.2 (com.lucee.mssql).

The database column in the MSSQL Server database is defined as a float column.

As a simple exampe the following code highlights the issue:

<cfscript> qQuery = QueryExecute("SELECT 1 WHERE 1E-8 = :FloatingPoint", { FloatingPoint = { cfsqltype="CF_SQL_FLOAT", value="0.00000001"} }); writeDump(qQuery); // INCORRECTLY RETURNS ZERO ROWS qQuery = QueryExecute("SELECT 1 WHERE 1E-8 = :FloatingPoint", { FloatingPoint = { cfsqltype="CF_SQL_NUMERIC", value="0.00000001"} }); writeDump(qQuery); // CORRECTLY RETURNS 1 ROW </cfscript>

 

Attachments

1
  • 26 Jul 2024, 10:18 am

Activity

Show:

Zac Spitzer 26 July 2024 at 10:34

Ahh, good old floating point fun

0.0000000099999999392252903 != . 0.00000001

Zac Spitzer 26 July 2024 at 10:13
Edited

I’ve refactored the test a bit and added mysql and qoq variants, only the mssql with FLOAT

I’ve enabled all the other tests and only disabled the failing one

https://github.com/zspitzer/Lucee/commit/7d60b0651c1202970e3149cbacb9a3273037cb62

 

image-20240726-101840.png

Pothys - MitrahSoft 24 March 2020 at 15:12

I added a test case for this ticket.

Pull request: https://github.com/lucee/Lucee/pull/899

Details

Assignee

Reporter

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Sprint

Affects versions

Priority

Created 22 August 2019 at 05:53
Updated 18 March 2025 at 21:54

Flag notifications