numeric_string: float, real, decimal and money come back as %f strings (1e-10 becomes "0.000000") #498

With numeric strings on (conn.setUseNumericString(true) or per-query numeric_string: true), float, real, decimal, money and smallmoney values are read as a double and printed with %f: six decimal places, no exponent. 1e-10 reads as "0.000000" and a decimal(38,10) 1.0123456789 as "1.012346". The exception is a whole value inside the int64 range, which comes back as an integer ("709"). No error is raised. numeric, int and bigint are exact.

The option is documented as returning number columns as strings "to prevent exceeding max numeric for JS" (index.d.ts), so turning it on to stay exact loses precision on these types instead. node-mssql, for example, has turned numeric strings on for every msnodesqlv8 connection since 12.2.3 (April 2026) to keep bigint exact. bigint_as_native (5.2.0, #400) now covers bigint without numeric strings, but there is nothing equivalent for decimal.

Repro

'use strict'
const sql = require('msnodesqlv8')

const connStr = process.env.CONN_STR ||
  'Driver={ODBC Driver 17 for SQL Server};Server=localhost,1433;Database=master;Uid=sa;Pwd=<password>;TrustServerCertificate=yes;'

const query = `SELECT
  CAST(0.1234567890123 AS float)                 AS f1,
  CAST(1e-10 AS float)                           AS f2,
  CAST(0.12345678 AS real)                       AS r1,
  CAST(1.0123456789 AS decimal(38,10))           AS d1,
  CAST(12345678901234567890.12 AS decimal(38,2)) AS d2,
  CAST(0.0000001 AS decimal(10,7))               AS d3,
  CAST(922337203685477.5807 AS money)            AS m1,
  CAST(1.0123456789 AS numeric(38,10))           AS n1`

sql.open(connStr, (err, conn) => {
  if (err) throw err
  const run = (numericString, next) => {
    conn.setUseNumericString(numericString)
    conn.queryRaw(query, (err, res) => {
      if (err) throw err
      console.log(`numeric strings ${numericString ? 'on ' : 'off'}:`,
        res.meta.map((m, i) => `${m.name}=${JSON.stringify(res.rows[0][i])}`).join(' '))
      next()
    })
  }
  run(false, () => run(true, () => conn.close(() => {})))
})

Output with 5.6.0 and 6.0.0 (Node 24.22.1), the same with ODBC Driver 17 and Native Client 11:

numeric strings off: f1=0.1234567890123 f2=1e-10 r1=0.12345678359270096 d1=1.0123456789 d2=12345678901234567000 d3=1e-7 m1=922337203685477.6 n1=1.0123456789
numeric strings on : f1="0.123457" f2="0.000000" r1="0.123457" d1="1.012346" d2="12345678901234567168.000000" d3="0.000000" m1="922337203685477.625000" n1="1.0123456789"

Measured

Also measured with numeric strings on:

• CAST(1.5e300 AS float) reads as a 301-digit integer followed by .000000 (off: 1.5e+300).
• smallmoney 1.2345 reads as "1.234500" and money -0.0001 as "-0.000100". money has four decimal places, so %f only pads it; money loses digits through the double (the row above).
• Exact: CAST(12345678901234567.89 AS numeric(38,2)) gives "12345678901234567.89", bigint max gives "9223372036854775807", and a whole decimal(10,0) 709 gives "709".
• A whole decimal still goes through the double: CAST(9007199254740993 AS decimal(19,0)) gives "9007199254740992", where numeric(19,0) gives "9007199254740993".
• Prepared statements round numeric too. Through prepare/preparedQuery with numeric_string: true, numeric(38,10) 1.0123456789 gives "1.012346", and numeric(38,2) 12345678901234567.89 gives "12345678901234568".

Expected: a string that keeps the value. At a minimum, the same text String() gives for the number returned with numeric strings off ("1e-10", "1.0123456789"). Ideally, for decimal and money, the server's exact text, as numeric already gets.

Environment

• msnodesqlv8 2.7.0, 4.5.0 and 5.6.0: the float, real, decimal, money and smallmoney casts above, with setUseNumericString(true).
• msnodesqlv8 2.7.0, 3.1.0, 4.5.0, 5.2.1 and 5.6.0: float 1e-10, decimal(38,10), the numeric and bigint values and the 709 decimal, with per-query numeric_string: true.
• Node 18.20.8 (2.7.0), 20.20.2 (3.1.0, 5.2.1) and 24.22.1 (4.5.0, 5.6.0, 6.0.0). SQL Server 2022 Developer on Windows Server 2022.
• msnodesqlv8 5.6.0 and 6.0.0: the repro script above as written, the whole decimal(19,0) and the prepared statements.
• SQL Server Native Client 11.0 and ODBC Driver 17 for SQL Server. Results were the same on every version and driver tried. Not tried: Linux, macOS, ODBC Driver 18.

Cause (from reading the source)

The links are to v5.6.0. 6.0.0 has the same code, 47 lines further down odbc_statement_legacy.cpp (dispatch, get_data_decimal).

1. queryRaw runs on a StatementType::Legacy statement (odbc_connection.cpp#L340-L346). Under numeric strings its dispatch sends SQL_NUMERIC (and the integer types) to try_read_string, which is why they are exact. SQL_DECIMAL (decimal, money, smallmoney), SQL_REAL, SQL_FLOAT and SQL_DOUBLE always go to get_data_decimal, whatever _numericStringEnabled is set to.
2. get_data_decimal fetches the value as SQL_C_DOUBLE, so decimal and money are already rounded to a double at this point. A whole value inside the int64 range becomes a BigIntColumn and is printed as an integer (hence "709"). It has still been through the double, so a whole decimal(19,0) above 2^53 comes back rounded (measured above). Everything else becomes a NumberColumn, including whole values outside int64 such as 1.5e300.
3. NumberColumn::ToString calls Column::AsString<double>, which is std::to_wstring(value). C++11 to C++23 define to_wstring(double) as swprintf with "%f" (the addon builds as C++20): fixed notation, six decimal places, no exponent.

So decimal and money lose precision twice: first when they become doubles (...890.12 becomes ...168, .5807 becomes .625), then when %f drops everything after the sixth decimal place.

2.7.0 to 4.5.0 have the same chain in src/OdbcStatement.cpp and src/Column.h (v4.5.0: dispatch, get_data_decimal, to_wstring).

Prepared statements bind decimal, numeric, real and float result columns as double (bound_datum.cpp#L2409-L2414, dispatch_prepared, reserved_decimal). That is why numeric read through a prepared statement is rounded as well (measured above).

From the source only, not tested: the newer OdbcStatement has the same dispatch and the same std::to_wstring(v). queryRaw does not use it in 5.6.0.

sql_variant: d_variant dispatches again on the type that SQL_CA_SS_VARIANT_TYPE reports, so a float variant takes the same path (measured on 5.6.0 with SNAC 11: 2.25e-9 as the column's first row reads as "0.000000"). A decimal(5,2) variant 1.5 reads as the exact text "1.50", and a money variant 1.2345 as "1.2345", presumably because they report SQL_C_NUMERIC, which has the same value as SQL_NUMERIC. Later rows of a variant column have a separate problem (#497).

The "Numeric String Configuration" tests use only numeric, int and bigint values, so they do not reach this path.

Related

• Precision on large decimals is lost on select-query (DB contains correct values) #106: numeric_string was added for this issue. The original report was about a DECIMAL(21,7) column, which takes the same SQL_DECIMAL path as the decimal rows above. The '9223372036854775808.000000' result reported there for bigint while testing the fix (comment) looks like the same %f output. bigint and numeric are now read as text under numeric strings, but decimal, money, float and real still go through the double.
• ROADMAP.md L63 plans a useDecimalAsString option and says decimals already come back as strings under useNumericString. They do, but only in the form shown above.
• node-mssql cannot simply move to bigint_as_native: with numeric strings off, a sql_variant column that mixes types crashed the process in our tests (sql_variant: every row is read as the first row's base type (wrong values, failed reads, 0xC0000005) #497).

Suggested fix

Two code changes, plus tests. Either change helps on its own.

1. Print doubles without losing digits. The smallest change is in NumberColumn::ToString:

inline Napi::Object ToString(Napi::Env env) override {
  // same text as String(n) in JS: the shortest string that round-trips
  return Napi::Number::New(env, value).ToString().As<Napi::Object>();
}

The string then matches what String(n) gives for the number returned with numeric strings off today ("1e-10", "1.5e+300", "0.12345678359270096", "1.0123456789"). That fixes float and real; real still shows the float widened to a double, as it does with numeric strings off.

OdbcStatement::get_data_decimal (odbc_statement.cpp L776) has no napi env. std::to_chars(double) gives the shortest round-trip digits there, but not always in the same notation as String(n): -0.0001 becomes "-1e-04". Floating-point to_chars needs macOS 13.3 with Apple's libc++ and GCC 11 with libstdc++.

2. Read SQL_DECIMAL as text under numeric strings, as SQL_NUMERIC already is. Formatting cannot bring back digits the double never held (a decimal(38,2) with 20 integer digits, large money values), so these need the driver's text:

    case SQL_NUMERIC:
    case SQL_DECIMAL:
      if (_numericStringEnabled) {
        res = try_read_string(false, row_id, column);
      } else {
        res = get_data_decimal(row_id, column);
      }
      break;

    case SQL_REAL:
    case SQL_FLOAT:
    case SQL_DOUBLE:
      res = get_data_decimal(row_id, column);
      break;

The same change would go in odbc_statement.cpp L494-L506. Things to weigh:

• Decimals would keep their scale digits. The driver's text for a decimal(5,2) 1.5 is "1.50" (measured, through a sql_variant), so a whole decimal(5,2) 5 would read as "5.00" where it reads as "5" today.
• The driver leaves out the leading zero for values below 1 (measured: numeric(10,7) 0.0000001 already reads as ".0000001"). Number() parses it, but you may want to add the zero.
• Prepared statements bind these columns as double, which already rounds numeric there (measured above), so that path would also need a char binding under numeric strings.
• If you would rather not change what numeric_string returns for decimal in 5.x, the same change could sit behind the useDecimalAsString option the ROADMAP already plans.

For float and real, part 1 is better than the driver's text, which gives a float 17 significant digits: 2.25e-9 read as text came back as "2.2499999999999999E-9" (seen through a sql_variant, #497).

Tests: add decimal, money, smallmoney, float and real cases to "Numeric String Configuration" in test/params.test.js, including 1e-10 and a decimal(38,2) with 20 integer digits.