sql_variant: every row is read as the first row's base type (wrong values, failed reads, 0xC0000005) #497

Within one result set, queryRaw (and query, which wraps it) reads every value in a sql_variant column as the base type of the column's first row. A later value of another base type comes back truncated or as another type with no error, or cannot be read at all. Up to 5.6.0 a value that cannot be read just goes missing from rows, again with no error. 6.0.0 reports an ODBC error for it instead (22018 or 07006). For example, this returns 7 and 1 instead of 7 and 1.5 on every version we tried:

select v from (values (1, cast(cast(7 as int) as sql_variant)), (2, cast(cast(1.5 as decimal(5,2)) as sql_variant))) t(i, v) order by i

When a value that cannot be read is followed by another cell, the Node process dies with an access violation (0xC0000005), on 6.0.0 too. That includes the usual way of reading a variant column next to a key: select i, v over int 7 then nvarchar 'abc' crashes, where select v returns [7] (5.6.0) or the 22018 error (6.0.0). With numeric strings off, every value we tried reads correctly when it is in the first row.

The crash and the NULL-first case below behave the same on every version we tried (2.7.0, 3.1.0, 4.5.0, 5.2.1, 5.6.0, 6.0.0) with both Native Client 11 and ODBC Driver 17. From the source, the code that reads a variant has not changed in 6.0.0.

Repro

'use strict'
// node variant-first-row.js          prints the cases that return wrong data
// node variant-first-row.js crash    runs the case that kills the process (kept apart for that reason)
const sql = require('msnodesqlv8')

const connStr = 'Driver={ODBC Driver 17 for SQL Server};Server=...;Database=master;Uid=...;Pwd=...;'

// One sql_variant column; ORDER BY i fixes which value comes first.
const variantColumn = (...values) =>
  'select v from (values ' +
  values.map((x, i) => `(${i + 1}, cast(${x} as sql_variant))`).join(', ') +
  ') t(i, v) order by i'

const cases = {
  'int, then decimal': variantColumn('cast(7 as int)', 'cast(1.5 as decimal(5,2))'),
  'int, then nvarchar': variantColumn('cast(7 as int)', "cast(N'abc' as nvarchar(10))"),
  'datetime, then int': variantColumn("cast('2020-01-02' as datetime)", 'cast(7 as int)'),
  'NULL, then int, nvarchar, numeric': variantColumn('null', '1', "N'abc'", '1.5')
}
const crash = variantColumn('1', "N'abc'", '1.5', 'cast(9223372036854775807 as bigint)', '2.25e-9')

// numeric_string is false by default and is set here to be explicit.
// bigint_as_native (5.2+) only changes how a correctly read bigint is returned.
const run = (conn, query) => new Promise(resolve => {
  conn.queryRaw({ query_str: query, numeric_string: false, bigint_as_native: true },
    (err, res) => resolve(err || res.rows.map(r => r[0])))
})

sql.open(connStr, async (err, conn) => {
  if (err) throw err
  if (process.argv[2] === 'crash') {
    console.log(await run(conn, crash))
  } else {
    for (const [name, query] of Object.entries(cases)) {
      console.log(name, await run(conn, query))
    }
  }
  conn.close(() => {})
})

Up to 5.6.0, apart from the crash, no error reaches the callback and the process exits with 0. The script was run as written on 5.6.0 and 6.0.0 (Node 24.22.1), with ODBC Driver 17 and with Native Client 11, and printed exactly this. 6.0.0 differs only where the table says. The last two cases were also run on every version and driver under Environment, and gave the same result each time: with these options and with connection defaults on 2.7.0 to 5.6.0, and with these options on 6.0.0.

The crash is not specific to that mix. On 5.6.0 and 6.0.0, with both drivers and these options, the process also died for select i, v over int 7 then nvarchar 'abc', and for select v over int 7, nvarchar 'abc', int 8.

With numeric_string: true, columns whose first row is int, bigint, decimal, nvarchar or NULL return every later value as a string, datetime and bit included ('2020-01-02 00:00:00.000', '1'). Columns whose first row is float, datetime or bit still lose rows or return wrong values (float, then bigint max: '-9223372036854775808'). A float there also reads as '0.000000', even in the first row; that is a separate formatting problem, #498.

Environment

• msnodesqlv8 2.7.0 (Node 18.20.8), 3.1.0 (Node 20.20.2), 4.5.0 (Node 24.22.1), 5.2.1 (Node 20.20.2), 5.6.0 (Node 24.22.1), 6.0.0 (Node 24.22.1)
• SQL Server 2022 Developer (16.x) on Windows Server 2022
• SQL Server Native Client 11.0 and ODBC Driver 17 for SQL Server
• Not tried on Linux or macOS

Cause (from reading the source at v5.6.0)

queryRaw runs on OdbcStatementLegacy (odbc_connection.cpp L346). fetch_read dispatches each cell on the column definition shared by the whole result set (L253-L259). dispatch sends SQL_SS_VARIANT to d_variant (L1333-L1335), which gets the row's base type (SQL_CA_SS_VARIANT_TYPE) and writes it into that shared definition (L1456-L1459):

auto& definition = _resultset->get_meta_data(static_cast<int>(column));
definition.dataType = static_cast<SQLSMALLINT>(variant_type);
const auto res = dispatch(definition.dataType, row_id, column);

From the second row on, the column no longer looks like a variant, so d_variant is not called again for that result set. Every later value goes to the first row's reader (SQL_C_SLONG, SQL_C_SBIGINT, SQL_C_DOUBLE, SQL_C_TIMESTAMP, SQL_C_BIT, or string). We assume the type reported for a NULL is one dispatch doesn't handle, so a NULL first row sends everything to the default string case. With numeric strings on, dispatch reads int, bigint and numeric through try_read_string, which takes any value, but the float, datetime and bit readers ignore the option. That is why numeric strings hide the problem for some first-row types only.

The same overwrite is in 4.5.0 (L1117-L1120) and 2.7.0 (L1015-L1018). It dates from the variant decoding added for #10 in May 2016 (v0.1.44 OdbcConnection.cpp L371-L374). In 6.0.0, d_variant (L1520-L1523), dispatch and the row-building code below are unchanged. fetch_read now keeps a fetch's diagnostics (#494), which is presumably why 6.0.0 reports the failed reads above as errors.

Possible fix

Dispatch each row on its own base type, and leave the column as SQL_SS_VARIANT so the next row is resolved again. In OdbcStatementLegacy::d_variant, in place of L1456-L1460:

  // dispatch this row by its own base type; the column stays SQL_SS_VARIANT,
  // so the next row is resolved again
  return dispatch(static_cast<SQLSMALLINT>(variant_type), row_id, column);

OdbcStatement::d_variant already does this on a copy of the definition (odbc_statement.cpp L853-L859), but queries don't go through that class. The cost is one zero-length SQLGetData and one SQLColAttribute per variant cell, which the first row already pays. This should also fix NULL-first and string-first columns. We have not built or tested it.

The variant tests in test/params.test.js (L801-L858) each use one row. A column with several rows of mixed types would cover this.

Why rows go missing or the process crashes (from source; not confirmed with a debugger or a dump)

Some later reads convert lossily and succeed (1.5 as SQL_C_SLONG gives 1). Others fail ('abc' or bigint max as SQL_C_SLONG, a number as SQL_C_TIMESTAMP). A failed cell only breaks fetch_read's column loop. The next fetch sets res = true again, or hits SQL_NO_DATA and returns true (L237-L241), so up to 5.6.0 the error is dropped and the row gets no cell. (Only a failure on the last row of a full fetch batch would leave res false; our result sets were shorter than that.) 6.0.0 keeps the diagnostics on those paths and reports the error.

ResultSet::add_column grows _rows to row_id + 1 when it stores a cell (result_set.cpp L17-L26), so a failed last row just goes missing. A failed row followed by a successful one is left as an empty vector. fromQueryResult calls get_column(row_id, c)->ToValue(env) on every row (js_object_mapper.cpp L81-L94), and get_column checks row_id but then indexes the empty row (result_set.cpp L9-L15). A column before the variant creates the row too, and leaves the variant's slot as an empty shared_ptr, so with select i, v even a failed last row is dereferenced. That fits every case we measured: the process died whenever a failed cell left a gap that another stored cell made visible, whether a later row (int 7, nvarchar 'abc', int 8 with select v) or an earlier column of the same row (select i, v). When the failed cell was the last thing stored, the row just went missing. 4.5.0 builds rows the same way (L194, ResultSet.cpp L77-L85).

Whatever the variant fix, a failed cell read should not be fatal. 6.0.0 reports the error when nothing else was stored in or after the failed cell's row, but still crashes otherwise. get_column or fromQueryResult could return null or an error for a short row or an empty cell, instead of dereferencing it.

Separate, smaller: bigint max comes back negative

The -9223372036854776000 in the matrix ('-9223372036854775808' with numeric strings) is bigint max read through get_data_decimal. We think its range check (L1878-L1882) lets 2^63 through: with MSVC, long double is double, so the INT64_MAX bound rounds to 2^63, and the out-of-range cast to int64_t then gives INT64_MIN on x64. A bound that is exact as a double (v2 < 9223372036854775808.0) would avoid it.

Context

We found this through tediousjs/node-mssql. The obvious case is an EAV-style table with values of several types in one sql_variant column, and a native crash takes down the whole process. node-mssql turns numeric strings on, which hides much of this, but float-, datetime- and bit-first columns still lose rows.

Related: #10 added variant decoding and closed with a request to open a new issue for further variant problems. #389 and #382 are also access-violation crashes but look unrelated. We found no existing report of this problem.