SQLite div_i (integer division //) produces wrong results when |dividend| < |divisor|

[queelius]

Bug Description

The SQLite implementation of integer division (// operator) in std.sql.prql produces incorrect results when the absolute value of the dividend is less than the absolute value of the divisor (i.e., when the correct result should be 0).

Reproduction

from t | select { x = a // b }

Compiled with --target sql.sqlite, this generates:

SELECT
  ROUND(ABS(a / b) - 0.5) * SIGN(a) * SIGN(b) AS x
FROM
  t

Wrong Results (verified in SQLite)

a	b	Formula result	Expected
1	2	-1	0
-1	2	1	0
3	7	-1	0
-3	7	1	0

The bug affects all integer division cases where |a| < |b|.

Root Cause

The formula ROUND(ABS(a / b) - 0.5) is used to emulate FLOOR(ABS(a / b)) since SQLite lacks a FLOOR function. However, when a and b are integers, SQLite's / performs integer division (truncation towards zero), so a / b = 0 when |a| < |b|. Then:

• ABS(0) - 0.5 = -0.5
• ROUND(-0.5) = -1 (SQLite rounds away from zero)

This gives -1 instead of the expected 0.

Suggested Fix

Replace the SQLite div_i formula in prqlc/prqlc/src/sql/std.sql.prql:

# Before (broken):
let div_i = l r -> s"ROUND(ABS({l:11} / {r:12}) - 0.5) * SIGN({l:0}) * SIGN({r:0})"

# After (correct):
let div_i = l r -> s"CAST(ABS({l:11} * 1.0 / {r:12}) AS INTEGER) * SIGN({l:0}) * SIGN({r:0})"

The fix forces float division with * 1.0, then uses CAST(... AS INTEGER) which truncates towards zero in SQLite -- exactly the semantics needed for integer division.

Environment

• PRQL version: 0.13.12 (main branch)
• Affected dialect: SQLite only

[prql-bot]

Thanks for reporting this! I was able to reproduce the issue and confirmed the root cause: when |a| < |b|, SQLite's integer division truncates a / b to 0, then ROUND(ABS(0) - 0.5) = ROUND(-0.5) = -1 due to SQLite rounding away from zero.

I've opened #5739 with a fix using the approach you suggested (CAST(ABS(a * 1.0 / b) AS INTEGER) to force float division and truncate correctly). A maintainer will review it shortly.