MSSQL: out-of-range `datetime2` value makes `MIN()` return NULL silently
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
SQL Server's `datetime2` range starts at year 0001. Arrow's timestamp
representation (int64 nanoseconds) starts around 1677. A single row holding a
date in between — perfectly legal in the source — produces two different
failures depending on the query:
1. **A hard error** on a rollup:
`Database Execution Error: The conversion of a datetime2 data type to a
datetime data type resulted in an out-of-range value.`
2. **A silent wrong answer** on an aggregate: `SELECT MIN(d)` returns **`NULL`**,
with no error and no warning, even though the column has non-null values in
every row.
The second is the serious one. The query succeeds, returns the right *shape*,
and the value is simply wrong — so nothing downstream can detect it. Any
row-count or schema-based health check passes while the data is incorrect.
Such rows are easy to acquire accidentally: a bad ETL default, a `0001-01-01`
sentinel, or a mis-parsed date all land in range for `datetime2`.
**To Reproduce**
Steps to reproduce the behavior:
1. Point Cube at a **SQL Server** data source (`CUBEJS_DB_TYPE=mssql`) with the
schema below. It contains one in-range row and one row dated `0202-01-01`.
2. Connect to the SQL API and run:
```sql
-- 1. Hard error: "The conversion of a datetime2 data type to a datetime data type
-- resulted in an out-of-range value."
SELECT DATE_TRUNC('year', d) y, COUNT(*) n FROM OutOfRange GROUP BY 1 ORDER BY 1;
-- 2. SILENT: returns lo = NULL, no error, despite both rows having a non-null d
SELECT MIN(d) lo FROM OutOfRange;
```
3. Statement 2 returns `NULL`. The expected value is `0202-01-01`, or failing
that, an error.
**Expected behavior**
Either:
- the out-of-range value is surfaced as an error consistently (as it already is
in statement 1), or
- it is handled/clamped and `MIN()` returns a correct value.
What should not happen is one query erroring and another silently returning
`NULL` for the same underlying data. Silent `NULL` is worse than a failure
because it cannot be detected downstream.
**Screenshots**
N/A.
**Minimally reproducible Cube Schema**
```javascript
cube(`OutOfRange`, {
sql: `
select 1 as id, CAST('0202-01-01' AS DATETIME2) as d
UNION ALL
select 2 as id, CAST('2024-01-01' AS DATETIME2) as d
`,
measures: {
count: { type: `count` },
},
dimensions: {
id: { sql: `id`, type: `number`, primaryKey: true, shown: true },
d: { sql: `d`, type: `time` },
},
});
```
**Version:**
1.7.12 (`cubejs/cube:v1.7.12`)
**Additional context**
- Reproduced against the schema above with **no `cube.js` file** (stock
configuration).
- The two symptoms share one root cause but surface in different layers, which
is why they are filed together: statement 1 fails in the MSSQL driver's
`datetime2 → datetime` conversion, statement 2 appears to fail when the value
is materialised into Arrow.
- Detecting this in a test suite requires a **value** assertion; row-count and
schema assertions both pass while the defect is live, because `MIN()` always
returns exactly one row.
- A related caching note, in case it confuses reproduction: once `MIN()` has
returned `NULL` for this query, the `NULL` is served from Cube's result cache
under the same query key even after the offending row is deleted. Removing the
row does not immediately restore the correct value, which can make the bug
look intermittent.
Contributor guide
Research direction
Start with the MSSQL driver's datetime2-to-datetime conversion and the Arrow materialization path described in the issue. Reproduce the supplied schema and queries, then add a regression test with a value assertion showing that MIN(d) returns 0202-01-01 or reports an error rather than NULL.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100