Overflow and Precision Loss

Concepts covered: Sums & Averages, Counting, Numeric Precision, Type Casting

The question rarely says overflow. It arrives as "the revenue total differs between 2 runs of the same query", "finance says the daily sum is 3 cents off", or "a job that has run for a year failed last night with an arithmetic error". Each is a type question, and the first move is to ask what type the aggregate returns in this engine, because the result type is where the ceiling sits.

The result type sets the ceiling

SQL Server is where billions of rows bite first: SUM over an INT column returns INT, and COUNT(*) returns INT as well, so a plain count over a large fact table fails with an overflow error. COUNT_BIG returns BIGINT and is the function to name. Spark is the dangerous one, because its failure is silent. With ANSI mode off, the default through Spark 3.x, a BIGINT sum that passes its limit wraps around and the dashboard shows negative revenue. Spark 4.0 turns ANSI mode on by default, which converts that wrap into an error.

• 2,147,483,647

Overflow happens before the SUM

The aggregate's type is half the story. In SUM(quantity * unit_price_minor) the multiplication runs per row, in the operands' type. The product of 2 INT columns is an INT, so a single line of 30,000 units at 90,000 minor units, a product of 2.7 billion, overflows before the sum ever sees it. Casting the SUM's result changes nothing; the widening has to reach an operand.

-- Overflows per row: INT * INT
SELECT SUM(quantity * unit_price_minor) AS gross_minor
FROM order_lines;

-- Too late: the product overflowed before the cast
SELECT CAST(SUM(quantity * unit_price_minor) AS BIGINT) AS gross_minor
FROM order_lines;

-- Widen an operand, and count with COUNT_BIG
SELECT SUM(CAST(quantity AS BIGINT) * unit_price_minor) AS gross_minor,
       COUNT_BIG(*) AS line_count
FROM order_lines;

Floats give 2 answers to 1 question

Floating-point addition is not associative: (a + b) + c can differ from a + (b + c) in the last bits. A distributed engine adds partial aggregates in whatever order its tasks finish, so a DOUBLE total over 2.4 billion amounts can change in its trailing digits between 2 runs on identical data. That's the "differs between runs" question, and the cause sits in the column's type: DOUBLE can't represent 0.10 exactly, and its rounding depends on the order of the additions.

Exact money takes 1 of 2 forms: integer minor units, where BIGINT cents reach about 92 quadrillion dollars, or DECIMAL with an explicit scale. Either way the currency lives in its own column, so nothing sums yen into dollars, and either way the total is identical under any summation order.

Decimal arithmetic quietly drops scale

DECIMAL stays exact until an expression's result type outgrows 38 digits. Multiplying DECIMAL(p1,s1) by DECIMAL(p2,s2) yields precision p1 + p2 + 1 and scale s1 + s2. Past 38 digits, SQL Server cuts the scale, keeping at least 6 places, and Spark does the same when spark.sql.decimalOperations.allowPrecisionLoss is on, which it is by default. Convert DECIMAL(38,10) amounts with DECIMAL(38,10) rates and every product lands at scale 6, rounded row by row before the sum adds the rounding up. In Spark with ANSI mode off, a decimal product that overflows becomes NULL, which SUM then skips, and a SUM that overflows its own precision returns NULL. Neither raises an error.

The follow-up that tests this: "So would you declare everything DECIMAL(38) to be safe?" The strong answer declines, with the reason. Storage cost aside, a wide declaration pushes every product past 38 digits sooner, which triggers exactly the scale reduction above. Size precision to the domain, DECIMAL(20,4) for amounts and a scale that matches the rate source for FX, and widen deliberately where a product needs it.

About this lesson

This section is part of the Type Casting: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.