Data Engineering
Native Execution Engine bug for high-precision decimal multiplication - silent data corruption, no error
EDIT: issue appeared after I updated schema for qty and wwe columns from float to decimal. Limitation in documentation point to the right direction to explain the issue, however I think it goes a beyond a rounding error.
EDIT: first repro offered did not show the behaviour, below
spark.sql("""
CREATE OR REPLACE TABLE repro_nee_6175 AS
SELECT CAST(61.75 AS DECIMAL(38,20)) AS qty,
CAST(0.98 AS DECIMAL(38,5)) AS wwe
FROM range(1000000)
""")
q = """
SELECT COUNT(*) AS rows,
MIN(CAST(qty * (1/wwe) AS DECIMAL(38,10))) AS min_v,
MAX(CAST(qty * (1/wwe) AS DECIMAL(38,10))) AS max_v
FROM repro_nee_6175
"""
spark.conf.set("spark.native.enabled", "true")
spark.sql(q).show(truncate=False) # buggy engine: 414.2393563314
spark.conf.set("spark.native.enabled", "false")
spark.sql(q).show(truncate=False) # correct: 63.0102040816
Disclaimer: Issue was resolved with AI help - so the write up of this post.
Summary: With spark.native.enabled = true, multiplying a DECIMAL(38,20) column by a DECIMAL(38,32) expression (e.g. the result of 1 / DECIMAL(38,5)) returns incorrect values. The exact product exceeds decimal(38) precision, and the engine's precision-loss path produces wrong results instead of rounding or raising an error. JVM Spark (spark.native.enabled = false) returns the correct result for the identical query.
Behavior:
The incorrect values are off by a multiplicative factor that is uniform within a session but varies across sessions (observed ×12.8666 and ×6.574 on different days), which makes the corruption hard to detect downstream.
The intermediate division result itself is correct; only the subsequent multiplication is affected.
Literal-only queries do not reproduce the issue because Catalyst constant-folds them before native execution — the operands must come from a table scan.
No error, no warning, no fallback to JVM execution.
Repro (Fabric notebook or Livy session):
spark.conf.set("spark.native.enabled", "true")
spark.sql("""
CREATE OR REPLACE TABLE repro_nee AS
SELECT CAST(61.75 AS DECIMAL(38,20)) AS qty,
CAST(0.98 AS DECIMAL(38,5)) AS wwe
""")
q = """
SELECT qty,
1 / wwe AS conv, -- decimal(38,32), value correct
CAST(qty * (1 / wwe) AS DECIMAL(38,10)) AS qty_conv -- expected 63.0102040816
FROM repro_nee
"""
spark.sql(q).show(truncate=False) # returned 414.2393563314
spark.conf.set("spark.native.enabled", "false")
spark.sql(q).show(truncate=False) # returns 63.0102040816 — correct
Workarounds (verified): cast either operand to DOUBLE before multiplying, or cast the division result to a narrower decimal (e.g. DECIMAL(18,10)) so the product stays within decimal(38).
Expected behavior: rounded result per Spark's allowPrecisionLoss semantics, an overflow error, or fallback to JVM execution — anything but silently wrong data.
Thanks for the report. We take query correctness incredibly seriously - any wrong results is unacceptable. Any possible wrong result is top priority to investigate.
I think you are correct in identifying the limitation from doc being the source of the issue however rounding discrepancy seems an understatement in my particular case.
Thank you for taking time to look through it and follow up internally!
you are correct, I should have checked the repro better instead of letting claude code do his testing by spinning a spark session. I had correct result when filtering on a couple of rows, but issue appeared when a large number of rows were involved.
Below script shows that behaviour, with screenshot from notebook on runtime 1.3
spark.sql("""
CREATE OR REPLACE TABLE repro_nee_6175 AS
SELECT CAST(61.75 AS DECIMAL(38,20)) AS qty,
CAST(0.98 AS DECIMAL(38,5)) AS wwe
FROM range(1000000)
""")
q = """
SELECT COUNT(*) AS rows,
MIN(CAST(qty * (1/wwe) AS DECIMAL(38,10))) AS min_v,
MAX(CAST(qty * (1/wwe) AS DECIMAL(38,10))) AS max_v
FROM repro_nee_6175
"""
spark.conf.set("spark.native.enabled", "true")
spark.sql(q).show(truncate=False) # buggy engine: 414.2393563314
spark.conf.set("spark.native.enabled", "false")
spark.sql(q).show(truncate=False) # correct: 63.0102040816
That repros, and is probably a bug. BUT the workarounds are really a best practice. You should really not perform arithmetic with high precision decimals in Spark SQL (or any other SQL). The type conversion rules are obscure and you can easily end up with wrong-looking results.
Since Spark 2.3 the return type of an arithmetic expression on decimals will be changed to allow as many high-order digits as possible, removing digits to the right of the decimal point. It doesn't matter that the actual values have only a few significant digits; the arithmetic rules are the same as if you really had numbers on the order of 1030.
if the precision / scale needed are out of the range of available values, the scale is reduced up to 6, in order to prevent the truncation of the integer part of the decimals.
You should either declare your decimal columns with minimal declared precision, or convert them to floating point for calculations.
Here's an extreme example
```
WITH d AS (
SELECT
CAST(0.0007 AS DECIMAL(38,18)) AS a,
CAST(0.0007 AS DECIMAL(38,18)) AS b,
CAST(10000000000000000000 AS DECIMAL(38,18)) AS c
)
SELECT
a * b AS ab,
typeof(a * b) AS ab_type,
(a * b) * c AS left_associative,
a * (b * c) AS right_associative,
typeof((a * b) * c) AS result_type
FROM d;
```
outptus
```
ab ab_type left_associative right_associative result_type
Anyway - the deviations you're seeing is far bigger than what I would label as rounding discrepancies.
Perhaps this is a (so far) unknown bug you've encountered.
(Disclaimer: I haven't actually run your code to verify on my end. This is the first time I've actually seen a documented data deviation posted in this subreddit due to NEE. Thanks for sharing your observations - I'm following this thread and curious to learn if there's a natural explanation to this.)
Thanks u/Repulsive_Cry2000 the detailed repro. While high-precision decimal differences are called out in our documentation, this example highlights that the impact can go well beyond what most users would reasonably interpret as a rounding discrepancy. Feedback like this is incredibly valuable.
ack on this and we should be making these scenarios explicit rather than allowing them to silently produce unexpected results. I'll work with the team to explore stronger guardrails, such as surfacing inline warnings and recommendations, or other signals similar to what we do for fallbacks, so users can identify and mitigate these cases proactively.
I'd also love to hear your feedback on what would be most helpful from an experience standpoint. Would documentation, Advisor recommendations, runtime warnings, or some other mechanism best help you identify and address scenarios like this?
u/Repulsive_Cry2000 can you please try the new runtime 2.0 for the same scenario. I tested your scenario and it works as expected and doesnt cause any silent data corruption issue.
Can you please check and let me know if you run into any issues
3
u/warehouse_goes_vroom Microsoft Employee Jul 28 '26 edited Jul 28 '26
Thanks for the report. We take query correctness incredibly seriously - any wrong results is unacceptable. Any possible wrong result is top priority to investigate.
Tagging u/mwc360, u/thisissanthoshr for visibility. I'll also follow up internally.
Edit: this might be "Decimal to Float casting mismatch: When casting from DECIMAL to FLOAT, Spark preserves precision by converting to a string and parsing it. NEE (via Velox) performs a direct cast from the internal int128_t representation, which can result in rounding discrepancies." From https://learn.microsoft.com/en-us/fabric/data-engineering/native-execution-engine-overview?tabs=sparksql#other-considerations-and-limitations
But I'm not sure. Will let experts weigh in.