FireDAC Bug: FLOAT Field Bound as Decimal(22,20) When Using MapRules for BCD → Double (Affects Update WHERE Clause)

[Ron Schuster 0]

Delphi Version: 12.2 Athens
Database: SQL Server
Driver: FireDAC MSSQL
Query Type: Live TFDQuery, UpdateOptions.UpdateMode := upWhereAll
Context: Migrating from BDE with many legacy TFloatField components

Problem Summary

When FireDAC’s FormatOptions.MapRules are configured to map BCD and FMTBCD fields to Double, FireDAC mishandles actual FLOAT database fields during auto-generated UPDATE statements.

Specifically, it generates a WHERE clause where the FLOAT field is incorrectly bound as decimal(22,20) — causing loss of precision and failed row matching.
Reproduction Conditions

    SQL Server table contains:

pop_density FLOAT NULL

Delphi TFDQuery includes:

    A persistent TFloatField bound to this column.

    UpdateOptions.UpdateMode := upWhereAll

FormatOptions.MapRules contains:

MapRules.Add(dtBCD, dtDouble);
MapRules.Add(dtFmtBCD, dtDouble);

(This is necessary to preserve compatibility with hundreds of legacy TFloatField components from a BDE application.)

The float value in the database is something like 1.23456789123E-10.

FireDAC generates the following:

    exec sp_executesql N'
      UPDATE dbo.unit
      SET ...
      WHERE ... AND pop_density = @P5',
      N'@P5 decimal(22,20)', 123456789123

    The float value is bound as a decimal, stripping both the decimal point and exponent — resulting in no match.

Observations

    This does not occur in a small test app unless MapRules are set.

    OldValue of the field is correct and contains the scientific notation.

    Field class is TFloatField.

    Problem only arises when MapRules include rules to map to dtDouble.

Workarounds

    Use UpdateOptions.UpdateMode := upWhereChanged to avoid the float field being used in the WHERE clause. This breaks the "another user changed the row" functionality.

    Avoid setting MapRules globally. Would require upgrading every persistent TFloatField.
    Manually override update SQL via TFDUpdateSQL. High effort. Legacy applications contain a large number of live queries using persistent TFields. 

Why This Matters

This is a blocking issue for anyone migrating a BDE-based application where all numeric fields were defined as TFloatField (common BDE behavior). Using FireDAC’s MapRules to redirect BCD types to Double is a clean workaround — but this side effect breaks updates on real FLOAT columns. Problem has also been observed on database fields defined as NUMERIC.

Conclusion

FireDAC's parameter binding logic incorrectly applies decimal formatting to native SQL Server FLOAT fields and sometimes NUMERIC fields when MapRules redirect BCD/FMTBCD to Double — even if the persistent field is explicitly a TFloatField.

Would appreciate any insights or confirmation from others facing this during BDE → FireDAC migration.

 

EDIT: 

The issue only seems to occur when the value in the float field uses a negative exponent greater than 6. For example, the problem occurs when the float = 1E-07, but not 1E-06.

I have attached a small test program that demonstrates the problem, with a SQL file to build the test table and add a couple of data rows. If I modify any value in the row containing 1E-07 in the pop_density column, I get the error: [FireDAC][DApt]-400. Update command updated [0] instead of [1] record.

test.zip

Edited Wednesday at 03:26 PM by Ron Schuster
Test program added