High-precision DECIMAL/NUMERIC values can be stored as a different number on PostgreSQL, SQL Server, MySQL and CockroachDB #4172

Problem: on PostgreSQL, SQL Server, MySQL and CockroachDB, a value with more significant digits than a double holds (about 15–17) can be stored as a different number when written to a wide DECIMAL / NUMERIC column. Wheels maps those columns to cf_sql_decimal / cf_sql_numeric, and on Lucee and BoxLang both binds pass the value through a double.

Measured by inserting 123456789012345678901234567890 into a NUMERIC(38,0) / DECIMAL(38,0) column with Wheels' mapped type, then reading it back as text:

The same value bound as cf_sql_varchar is stored exactly on SQL Server, MySQL and CockroachDB. PostgreSQL rejects a varchar bind into a numeric column ("column is of type numeric but expression is of type character varying") unless it's cast or bound as cf_sql_other. Adobe ColdFusion hasn't been measured.

This affects any high-precision decimal, not only whole numbers: for example a DECIMAL(38,10) value with more than about 16 significant digits. Typical money and quantity values fit in a double exactly and are unaffected.

The Oracle side of the same engine behaviour is #4162. Oracle can take a text bind for integers beyond 64 bits; these adapters need per-database handling:

• text (cf_sql_varchar) on SQL Server, MySQL and CockroachDB;
• cf_sql_other or an explicit cast on PostgreSQL.

Proposal: for a decimal / numeric param whose canonical digit string has more significant digits than a double holds exactly, bind it in a form each database converts exactly. Leave every other value as it is. Measure Adobe first.

Tests: a round-trip of a 30-digit integer and of a high-precision fraction into a NUMERIC(38,x) column, plus an equality WHERE, on each of the four databases and every engine.