skip to content

In SQL, what does MOD(-7, 3) return, and why does WHERE MOD(x, 2) = 1 miss some odd numbers?

level: middleimportance: should knowfreq 38%

answer

  1. the remainder follows one operand's sign
  2. dividend, not divisor
  3. negative odd numbers give -1
  4. wrap it to stay non-negative

basics

~20 s

MOD(-7, 3) returns -1 because the remainder takes the sign of the dividend on the common engines. A negative odd x gives MOD(x, 2) = -1, so the test misses it; write MOD(x, 2) <> 0 instead.

solid answer

~40 s

On PostgreSQL, MySQL, SQL Server, Oracle and SQLite the remainder takes the sign of the dividend, because it pairs with a quotient truncated toward zero: -7 / 3 truncates to -2, and -7 - (3 * -2) is -1. So `MOD(-3, 2)` is -1, and `MOD(x, 2) = 1` silently skips negative odd numbers; `MOD(x, 2) <> 0` catches them all. The spelling varies: SQL Server uses the `%` operator and Oracle uses `MOD`. For a result always in 0 to n-1, wrap it: `MOD(MOD(x, n) + n, n)`. A divisor of 0 also varies: an error on PostgreSQL and SQL Server, `NULL` on MySQL and SQLite, and the dividend on Oracle.

code

sql · 3 lines
sql
SELECT mod(-7, 3)             AS negative_dividend,  -- -1
       mod(7, -3)             AS negative_divisor,   -- 1
       mod(mod(-7, 3) + 3, 3) AS wrapped;            -- 2

go deeper

for a junior

Recall that MOD or the % operator returns a remainder, and use it for an even-or-odd check. Know to test with a non-zero comparison so that negative numbers are included.

for a middle

Derive the sign from the quotient truncated toward zero and predict all four sign combinations. Name the spelling differences and the way a zero divisor varies between engines.

for a senior

Look for skipped rows when a signed hash feeds a bucket or shard number, since nothing errors. Prefer the wrapped form and explain why the minimum integer makes taking ABS before the remainder unsafe.

for a principal

Where bucket numbers drive partitioning or batch workers, make the wrapped remainder the shared helper and test it with negative and minimum values, because a mistake here loses rows quietly rather than failing.

## What MOD computes `MOD(x, y)` returns the **remainder** of dividing `x` by `y`. The **dividend** is `x`, the number being divided, and the **divisor** is `y`. A remainder is not unique until you choose what quotient it belongs to, and on the common SQL engines that quotient is the one truncated toward zero. The relationship is: ``` x = y * TRUNC(x / y) + MOD(x, y) ``` For `MOD(-7, 3)`: `-7 / 3` is -2.33, truncating to -2, and `-7 - (3 * -2)` is -1. The consequence is the rule to remember: **the result takes the sign of the dividend**, and the divisor's sign has no effect on it. | Expression | Result | |---|---| | `MOD(7, 3)` | 1 | | `MOD(-7, 3)` | -1 | | `MOD(7, -3)` | 1 | | `MOD(-7, -3)` | -1 | Some programming languages and the usual mathematical convention would answer 2 for `MOD(-7, 3)`, because they pair the remainder with a quotient rounded down. Candidates who carry that habit into SQL predict the wrong number, and a wrong prediction here does not raise an error. A quick way to predict any case by hand has two steps: 1. Take the remainder of the two magnitudes: 7 and 3 leave 1. 2. Copy the sign of the dividend onto it: the dividend is -7, so the answer is -1. ## Spelling and the zero divisor The function is widely available, but neither its name nor its behaviour on zero is uniform. | Engine | Spelling | `7 mod 0` | |---|---|---| | PostgreSQL | `MOD(x, y)` or `x % y` | error | | MySQL | `MOD(x, y)`, `x % y` or `x MOD y` | `NULL` | | SQL Server | `x % y` only | error | | Oracle | `MOD(x, y)` only | returns `x`, here 7 | | SQLite | `x % y`, plus `mod` where math functions are built in | `NULL` | PostgreSQL offers `mod` and `%` for `smallint`, `integer`, `bigint` and `numeric`, but not for `double precision`, so a float operand needs a cast first. On a decimal value the result can be fractional: `MOD(7.5, 2)` is 1.5. A `NULL` operand gives `NULL`. ## Three queries the sign breaks 1. **The odd test.** `WHERE MOD(x, 2) = 1` looks complete and misses -1, -3 and -5. `WHERE MOD(x, 2) <> 0` is correct for every integer. The even test, `MOD(x, 2) = 0`, was never affected, and the odd test passes any test run on positive data, so the bug tends to survive until a negative value arrives. 2. **Buckets and shards.** `MOD(hash_value, 8)` on a signed hash returns -7 to 7, not 0 to 7. Rows with a negative bucket number match no worker that loops from 0 to 7, and they are skipped without an error. 3. **Cycling calendars and rotas.** Offsets computed by subtraction can go negative, such as "how many days before this date", and `MOD` then returns a negative position instead of a place in the cycle. The repair is to wrap the remainder into the range you wanted: - `MOD(MOD(x, n) + n, n)` gives the mathematical result for a positive `n`, always from 0 to n-1. For -7 and 3 it computes -1, then 2, then 2. - `ABS(MOD(x, n))` is also in range and shorter, but it folds -1 and 1 into the same value, so the bucket spread changes and buckets are no longer in cycle order. That is acceptable for sharding and wrong for a calendar. ## ABS, and the one input it cannot handle `ABS(x)` returns the magnitude of `x`, and `NULL` for `NULL`. Its everyday use is measuring a difference regardless of direction, as in `WHERE ABS(report_total - ledger_total) >= 0.01`, which lists the rows where two figures disagree by a cent or more. It has one edge. A signed integer type holds one more negative value than positive, so the minimum has no positive counterpart. On a PostgreSQL `integer` holding -2147483648, `ABS` raises an "integer out of range" error, and SQLite raises an overflow error at its 64-bit minimum. So `MOD(ABS(hash_value), 8)` is exposed on that single value, and it can run for months before a hash produces it. Computing the remainder first, as both forms above do, keeps the value small and avoids the problem.

  • How do you make a remainder land in 0 to n-1 when x can be negative?
    Add the divisor and take the remainder again: `MOD(MOD(x, n) + n, n)`. For x = -7 and n = 3 the inner remainder is -1, adding 3 gives 2, and the outer remainder is 2. On a positive x, the second step simply undoes the added n, so the same expression is safe for every input.
  • What happens if the divisor of MOD is 0?
    It depends on the engine, so do not assume. PostgreSQL and SQL Server raise a division-by-zero error, MySQL and SQLite return `NULL`, and Oracle's `MOD(7, 0)` returns 7, the dividend. A shared query should keep the divisor from being zero rather than rely on any one of these.
  • Why can MOD(ABS(x), 8) fail on an INTEGER column in PostgreSQL?
    A 32-bit signed integer ranges from -2147483648 to 2147483647, so the minimum has no positive counterpart and `ABS` raises an out-of-range error on it. Taking the remainder first avoids that, as in `ABS(MOD(x, 8))` or `MOD(MOD(x, 8) + 8, 8)`, because the intermediate value is always small.

  • MOD(-7, 3) is 2 because a remainder is never negative.
  • The divisor's sign decides the result, so MOD(7, -3) is -1.
  • MOD(x, 2) = 1 catches every odd number.
  • MOD with a zero divisor returns NULL on every engine.
  • MOD is a function name that every SQL engine accepts.
  • ABS can never fail, because it only removes a sign.