Scalar expressions: ROUND(x, 1) returns an integer, function results fail to compile under an operator or in GREATEST, a simple CASE matches NULL or fails over an expression, and ORDER BY GREATEST(…) fails #416

These defects are in how a scalar expression is rendered to Painless. They sit in the same neighbourhood as #380, which #412 fixes: a value, a type or a null guard ends up wrong relative to the operator or clause that consumes it. Items 1 and 4 return HTTP 200 with wrong values; the others fail loudly or are refused.

Measured on main (f63222d) and on #412's head (0687815), executed on Elasticsearch 8.18.3 through the gateway. PostgreSQL 16.14 ran on the same rows. Every item behaves the same on both builds unless stated.

Fixture

CREATE TABLE t (id KEYWORD, g KEYWORD, s KEYWORD, s2 KEYWORD, n INT, x DOUBLE, y DOUBLE, d DATE, d2 DATE, ts TIMESTAMP, b BOOLEAN, PRIMARY KEY (id)). "—" means the field is absent from the document. In PostgreSQL an absent field is NULL, and ROUND(x, 1) is written ROUND(x::numeric, 1) because PostgreSQL has no round(double precision, integer).

Values are listed in id order r1 … r5.

1. ROUND with a scale returns an integer, even in a plain SELECT

emits

def param2 = Math.pow(10, 1); … Long.valueOf(Math.round((param1 * param2) / param2))

The division by 10^scale happens inside Math.round, so the value is rounded to an integer. Measured in SELECT, WHERE, arithmetic and NULLIF; computed columns and per-group calculations were not measured.

• Expected: PostgreSQL's values.
• Fix direction: Math.round(x * 10^s) / 10^s as a double. Choose the tie rule for negative halves too: PostgreSQL's numeric gives -1.25 → -1.3, while Java's Math.round would give -1.2.

2. A function result fails to compile under an operator or as a GREATEST / LEAST argument, on every document

GREATEST(SQRT(x), 1) fails with the same compile error. Adding WHERE <column> IS NOT NULL changes nothing, so these are not a missing-value problem. Since #412, ABS(n) + 1 over an INT runs (4, 6, 3, NULL, 11).

Two mechanisms: the function's boxed result is combined with an int literal (Cannot cast from [java.lang.Long] to [int]), and the function argument of GREATEST / LEAST reports nullable = false, so the reducer applies Math.max to a value that can be null.

• Expected: PostgreSQL's values.
• Fix direction: guard the NULL around the whole arithmetic, give the function's result the primitive (or def) type the operator needs, and let a function argument of GREATEST / LEAST report that it can be NULL. A bare NULL argument is skipped, as any NULL argument is.

3. A simple CASE over an expression fails on every document

The operand is handled in its column's type rather than the expression's: YEAR(d) is an integer, but param1.toInstant() is called on it as on the date d.

• Fix direction: take the operand's type from the expression, not from its column.

4. A simple CASE matches a NULL operand

The script emits param2 == param2 ? 1 : 0, and in Painless null == null is true. In SQL NULL = NULL is unknown, so the ELSE branch applies. (Since #412 the values are the integers 1 / 0; main answered 1.0 over a DOUBLE operand.)

• Fix direction: guard the operand and each WHEN value against NULL, as the searched form does.

5. A CASE or a function over an aggregate is refused per group

The refusal is loud. A workaround runs: MAX(CASE WHEN n > 1 THEN 1 ELSE 0 END) → 1.0, 0.0, 1.0. HAVING ABS(COUNT(*)) > 1 and the rest of the numeric function family are refused on purpose (dql_statements.md: the boxing conversion compiles in some positions of a group-filter script and not others). An integral rendering of ABS in the bucket_selector compiled and kept the right groups on Elasticsearch 8.18.3, measured on a variant of #412 that was not kept.

• Expected: computed per group, as arithmetic over aggregates already is.
• Fix direction: render a CASE or scalar function whose operands are aggregates as a per-group calculation (bucket_script / bucket_selector), with a rendering that compiles in every position.

6. DATE_TRUNC to a sub-day unit fails when compared with a DATE

In the comparison, the operand is narrowed to a LocalDate before it is truncated: ….toLocalDate().truncatedTo(ChronoUnit.HOURS).

• Expected: compare as timestamps. PostgreSQL promotes the DATE.
• Fix direction: truncate first, or promote the DATE operand.

7. A BIGINT literal past the INT range fails to compile, except in GREATEST / LEAST

The literal is rendered without the L suffix, and Painless reads 3000000000 as an int constant. main renders the same scripts (read from the request). #412 adds the suffix inside GREATEST / LEAST only: GREATEST(n, 3000000000) answers 3000000000 (a Long) on every row there.

• Expected: PostgreSQL's values.
• Fix direction: render a whole-number literal past the INT range with the L suffix wherever a script is emitted.

8. ORDER BY GREATEST(…) sorts by the alias as a field and fails

The ORDER BY expression is matched to the SELECT item, then sorted by its alias as if v were a field of the index.

• Expected: sorted by the computed value.
• Fix direction: sort by a script over the expression, or refuse by name if a script sort cannot express it (see the ORDER BY <arithmetic> limitation in operators.md).

Evidence level

RUN on Elasticsearch 8.18.3, both builds, every row. PostgreSQL 16.14 answers measured. Scripts and types were read from the rendered request on each build. Item 5's integral ABS variant was measured on a development build, not on either commit.