E0032 integer-division

Division of integer columns truncates toward zero.

What it reports

In PostgreSQL, SQLite and Redshift, dividing two integers performs integer division, truncating the fractional part toward zero (1 / 3 = 0). Usually a ratio or average was intended.

E0032 reports / when both operand types are known integers and at least one operand is a column or an aggregate. Two literal integers (such as 10 / 3) are not reported because constant integer division is often deliberate.

This rule is not reported for MySQL, where / produces a decimal result (MySQL uses DIV for integer division).

Example

CREATE TABLE orders (id INT PRIMARY KEY, qty INT, items INT);

SELECT qty / items AS ratio FROM orders;

warning[E0032]: Division of integer columns truncates toward zero: integer / integer produces an integer
  --> q.sql:1:8
    |
  1 | SELECT qty / items AS ratio FROM orders;
    |        ^^^
    = help: Cast an operand to NUMERIC or FLOAT if a ratio or average is meant: CAST(x AS NUMERIC) / y

Fixed by casting an operand to numeric:

SELECT CAST(qty AS NUMERIC) / items AS ratio FROM orders;
-- or in PostgreSQL:
SELECT qty::numeric / items AS ratio FROM orders;

Notes

• Operands explicitly cast or multiplied by float (such as 1.0 * qty / items or qty::numeric / items) produce a non-integer type and are not reported.
• An integer division inside a cast (such as (qty / items)::numeric) is reported because the truncation happens before the outer cast.
• In MySQL, / returns a decimal and integer division is spelled DIV, so MySQL queries are not reported.

Configuring

This rule is off by default (pedantic category). To enable it:

# sqlsift.toml
[rules]
integer-division = "warn"   # or "error"

Or enable all pedantic rules:

# sqlsift.toml
[categories]
pedantic = "warn"

Or on the command line: -W integer-division, or -W pedantic. For a single line: -- sqlsift:disable E0032.