fengqiaoqu · Oct 26, 2026 · pullfrog · Oct 26, 2026
diff --git a/engine/sqlite/src/sqlite.rs b/engine/sqlite/src/sqlite.rs
@@ -1264,8 +1264,21 @@ impl Analyzer<'_> {
             }
             "/" | "%" => {
                 // A zero divisor gives NULL; a literal divisor is one the statement rules out.
+                // `%` casts both operands to INTEGER first
+                // (https://sqlite.org/lang_expr.html#binaryops), so a fractional literal that
+                // truncates to 0 (0.5, 0.99, 1e-3) is a zero divisor too. `/` keeps the float.
                 let (l, r) = (arg(&n.left), arg(&n.right));
-                let literal_divisor = n.right.as_deref().is_some_and(|d| matches!(d.op.as_str(), "integer" | "float") && d.value.as_deref().and_then(|v| v.parse::<f64>().ok()).is_some_and(|v| v != 0.0));
+                let modulo = n.op == "%";
+                let literal_divisor = n.right.as_deref().is_some_and(|d| {
+                    matches!(d.op.as_str(), "integer" | "float")
+                        && d.value.as_deref().and_then(|v| v.parse::<f64>().ok()).is_some_and(|v| {
+                            if modulo {
+                                v.trunc() != 0.0
+                            } else {
+                                v != 0.0
+                            }
+                        })
+                });
                 Fact { ty: arithmetic(&l.ty, &r.ty), non_null: l.non_null && r.non_null && literal_divisor }
             }
             "||" => {
@@ -4591,6 +4604,25 @@ mod tests {
         assert_eq!(odd(&Extras { total: vec![TotalFunction { name: "JSON_OBJECT".into(), arity: 3, returns: Affinity::Text }], host: None }), 0);
     }
 
+    #[test]
+    fn a_modulo_by_a_fractional_literal_under_one_is_nullable() {
+        // SQLite casts both `%` operands to INTEGER, so 0.5 truncates to 0 and the remainder is
+        // NULL on every row. `/ 0.5` stays non-null: division keeps the float.
+        let schema = vec!["create table items (id integer primary key, qty integer not null)".to_string()];
+        let typed = |sql: &str| {
+            let a = analyze_ddl(sql, &schema, &Extras::default()).analysis;
+            assert!(a.diagnostics.is_empty(), "{sql}: {:?}", a.diagnostics);
+            col(&a, "r")
+        };
+        assert_eq!(typed("select qty % 0.5 as r from items"), ("real".into(), true));
+        assert_eq!(typed("select qty % 0.99 as r from items"), ("real".into(), true));
+        assert_eq!(typed("select qty % 1e-3 as r from items"), ("real".into(), true));
+        assert_eq!(typed("select qty % 2.5 as r from items"), ("real".into(), false));
+        assert_eq!(typed("select qty % 2 as r from items"), ("integer".into(), false));
+        assert_eq!(typed("select qty / 0.5 as r from items"), ("real".into(), false));
+        assert_eq!(typed("select qty / 0 as r from items"), ("integer".into(), true));
+    }
+
     #[test]
     fn sqlite_only_syntax_parses() {
         let a = analysis("select id from users limit :n, :m");

diff --git a/oracle/cases/sqlite_modulo_divisor/dialect b/oracle/cases/sqlite_modulo_divisor/dialect
@@ -0,0 +1 @@
+sqlite
diff --git a/oracle/cases/sqlite_modulo_divisor/divide_half.sql b/oracle/cases/sqlite_modulo_divisor/divide_half.sql
@@ -0,0 +1 @@
+select qty / 0.5 as r from items
diff --git a/oracle/cases/sqlite_modulo_divisor/expected.txt b/oracle/cases/sqlite_modulo_divisor/expected.txt
@@ -0,0 +1,6 @@
+divide_half.sql: many () -> { r: real }
+half.sql: many () -> { r: real? }
+half_almost.sql: many () -> { r: real? }
+milli.sql: many () -> { r: real? }
+two.sql: many () -> { r: integer }
+two_half.sql: many () -> { r: real }
diff --git a/oracle/cases/sqlite_modulo_divisor/half.sql b/oracle/cases/sqlite_modulo_divisor/half.sql
@@ -0,0 +1 @@
+select qty % 0.5 as r from items
diff --git a/oracle/cases/sqlite_modulo_divisor/half_almost.sql b/oracle/cases/sqlite_modulo_divisor/half_almost.sql
@@ -0,0 +1 @@
+select qty % 0.99 as r from items
diff --git a/oracle/cases/sqlite_modulo_divisor/milli.sql b/oracle/cases/sqlite_modulo_divisor/milli.sql
@@ -0,0 +1 @@
+select qty % 1e-3 as r from items
diff --git a/oracle/cases/sqlite_modulo_divisor/setup.sql b/oracle/cases/sqlite_modulo_divisor/setup.sql
@@ -0,0 +1,5 @@
+-- `%` casts both operands to INTEGER before the remainder
+-- (https://sqlite.org/lang_expr.html#binaryops). A fractional literal that truncates to 0 is a
+-- zero divisor and returns NULL on every row; `/` keeps the float and stays non-null.
+create table items (id integer primary key, qty integer not null);
+insert into items values (1, 5), (2, 7), (3, 0);
diff --git a/oracle/cases/sqlite_modulo_divisor/two.sql b/oracle/cases/sqlite_modulo_divisor/two.sql
@@ -0,0 +1 @@
+select qty % 2 as r from items
diff --git a/oracle/cases/sqlite_modulo_divisor/two_half.sql b/oracle/cases/sqlite_modulo_divisor/two_half.sql
@@ -0,0 +1 @@
+select qty % 2.5 as r from items
diff --git a/packages/sqlite/engine.wasm b/packages/sqlite/engine.wasm
diff --git a/site/content/docs/sqlite.mdx b/site/content/docs/sqlite.mdx
@@ -455,7 +455,7 @@ where o.ships_on is not null
 
 **Expressions**
 
-Reylo's table of SQLite's functions says which are non-null over non-null inputs: `length`, `coalesce`, `||` and arithmetic are; division by a column is not, since dividing by zero returns `NULL`. Arithmetic on a `REAL` with no answer is the one exception ([Limits](#limits)).
+Reylo's table of SQLite's functions says which are non-null over non-null inputs: `length`, `coalesce`, `||` and arithmetic are; division by a column is not, since dividing by zero returns `NULL`. `%` by a fractional literal under 1 is not either: SQLite casts both operands to INTEGER first, so `0.5` truncates to `0` and the remainder is `NULL` on every row. Arithmetic on a `REAL` with no answer is the one exception ([Limits](#limits)).
-Reylo's table of SQLite's functions says which are non-null over non-null inputs: `length`, `coalesce`, `||` and arithmetic are; division by a column is not, since dividing by zero returns `NULL`. `%` by a fractional literal under 1 is not either: SQLite casts both operands to INTEGER first, so `0.5` truncates to `0` and the remainder is `NULL` on every row. Arithmetic on a `REAL` with no answer is the one exception ([Limits](#limits)).
+Reylo's table of SQLite's functions says which are non-null over non-null inputs: `length`, `coalesce`, `||` and arithmetic are; division by a column is not, since dividing by zero returns `NULL`. Nor is `%` by a fractional literal under 1: SQLite casts both operands to INTEGER first, so `0.5` truncates to `0` and the remainder is `NULL` on every row. Arithmetic on a `REAL` with no answer is the one exception ([Limits](#limits)).
-Reylo's table of SQLite's functions says which are non-null over non-null inputs: `length`, `coalesce`, `||` and arithmetic are; division by a column is not, since dividing by zero returns `NULL`. `%` by a fractional literal under 1 is not either: SQLite casts both operands to INTEGER first, so `0.5` truncates to `0` and the remainder is `NULL` on every row. Arithmetic on a `REAL` with no answer is the one exception ([Limits](#limits)).
+Reylo's table of SQLite's functions says which are non-null over non-null inputs: `length`, `coalesce`, `||` and arithmetic are; division by a column is not, since dividing by zero returns `NULL`. Nor is `%` by a fractional literal under 1: SQLite casts both operands to INTEGER first, so `0.5` truncates to `0` and the remainder is `NULL` on every row. Arithmetic on a `REAL` with no answer is the one exception ([Limits](#limits)).
 
 <Inferred>