In SQL, what does MOD(-7, 3) return, and why does WHERE MOD(x, 2) = 1 miss some odd numbers?
answer
- the remainder follows one operand's sign
- dividend, not divisor
- negative odd numbers give -1
- wrap it to stay non-negative
basics
~20 sMOD(-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 sOn 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 linesSELECT mod(-7, 3) AS negative_dividend, -- -1
mod(7, -3) AS negative_divisor, -- 1
mod(mod(-7, 3) + 3, 3) AS wrapped; -- 2go deeper
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.
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.
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.
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.