ORDER BY <arithmetic over a nullable column> fails once any document is missing a field

[fupelaqu]

ORDER BY <arithmetic over a nullable column> fails the whole query with a null_pointer_exception — but only once some document is missing one of the fields. It works perfectly while every row has a value, so it passes in development and fails in production.

Measured on origin/main 975aa87b, executed on real Elasticsearch 8.18.3

Fixtures: id1 {n: 7, m: 2}, id2 {n: 5} — id2 has no m, which is ordinary in Elasticsearch.

SELECT n FROM t ORDER BY n / m

emits

"sort":[{"_script":{"type":"number","order":"asc","script":{"lang":"painless","source":
  "def param1 = (doc['n'].size() == 0 ? null : doc['n'].value);
   def param2 = (doc['m'].size() == 0 ? null : doc['m'].value);
   (param1 == null || param2 == null) ? null : (param1 / param2)"}}}]

and Elasticsearch answers:

HTTP 400  script_exception: runtime error
  caused_by: null_pointer_exception:
    Cannot invoke "Object.getClass()" because "value" is null

Control, same query, restricted to the row that has both fields — works, sort value 3.0:

{"query":{"term":{"_id":"1"}}, "sort":[{"_script": … same script … }]}   ->  OK, sort [3.0]

The shape of the bug

The null guard is correct — it is what makes the expression safe everywhere else — but a numeric sort script is the one context that cannot accept its result. "type":"number" requires every document to yield a number, and a guarded arithmetic yields null for any document missing either operand.

So the failure is data-dependent, not categorical:

data	outcome
every document has both fields	sorts correctly
one document is missing either field	HTTP 400, the whole query fails

That is the part worth fixing first: the same statement changes from working to failing as documents arrive, with nothing in the SQL to explain why.

Scope

Any ORDER BY whose expression is a null-guarded arithmetic — n / m, n + m, price * qty — over a column that can be missing. ORDER BY n (a plain column) is unaffected: it emits {"n":{"order":"asc"}}, a field sort, with Elasticsearch's own missing-value handling.

Possible directions (none verified)

• give the sort script a numeric fallback instead of null (Elasticsearch's missing on a script sort, or a sentinel chosen by order);
• emit "type":"number" with an explicit missing value;
• reject the statement at build time rather than letting the shard fail.

Which of these is right depends on what ORDER BY should mean for a row whose expression is NULL — SQL says NULLs sort together, first or last by dialect — so this needs a product decision, not just a patch.

Evidence level

Emission read off the generated query; both the failure and the control executed on a real cluster. Pre-existing — verified against origin/main's own emission, unchanged by #382.

Found 2026-09-23 while implementing the / rule for #382.