Repository navigation
An arithmetic expression is rejected as the operand of a scalar function, a CAST or a CASE branch #391
Description
FLOOR(n / m), CAST(n + m AS INTEGER) and COALESCE(n / m, 0) are parse errors. An arithmetic
expression is accepted wherever a value goes — a SELECT item, a WHERE, an aggregate — but not as
the operand of a scalar function, of a CAST / TRY_CAST / CONVERT, or of a CASE branch.
Measured on origin/main 975aa87b
Parse outcome only (Parser(sql)), no execution needed — these never reach Elasticsearch.
| statement | |
|---|---|
SELECT CAST(n / m AS INTEGER) AS c FROM t |
❌ '(binary|varbinary)' expected but '/' found |
SELECT CAST(n + m AS INTEGER) AS c FROM t |
❌ — any arithmetic operator, not just / |
SELECT FLOOR(n / m) AS c FROM t |
❌ ')' expected but '/' found |
SELECT ABS(n / m) AS c FROM t |
❌ |
SELECT COALESCE(n / m, 0) AS c FROM t |
❌ |
SELECT CASE WHEN n > 1 THEN n / m ELSE 0 END AS c FROM t |
❌ '(END)\b' expected but '/' found |
SELECT CAST((n / m) AS INTEGER) AS c FROM t |
❌ — parenthesising does not help |
SELECT FLOOR((n / m)) AS c FROM t |
❌ |
The rule is directional. A function INSIDE arithmetic is fine; arithmetic INSIDE a function is not:
SELECT ABS(n) / m AS c FROM t |
✅ |
SELECT CAST(n AS DOUBLE) / m AS c FROM t |
✅ |
SELECT total / NULLIF(cnt, 0) AS c FROM t |
✅ |
SELECT SUM(price) * 2 AS c FROM t |
✅ |
SELECT FLOOR(x) AS c FROM t |
✅ (control — the function itself is fine) |
🔴 Aggregates are EXEMPT, which bounds the severity: the everyday BI shape works — and this is
established by EXECUTION, not by the parse. SELECT SUM(price * qty) emits a sum over a Painless
script that really multiplies both doc values (a plain SUM(price) still compiles to
{"sum":{"field":"price"}}), and on a real ES 8.18.3 over (10.0x3), (2.5x4), (1.0x2) it returns
42.0; AVG(n + 1) returns 5.333…; grouped by status it returns a=40.0, b=2.0. All the
expected values.
SELECT SUM(price * qty) AS c FROM t |
✅ |
SELECT SUM(n / m) AS c FROM t · AVG(n + 1) · COUNT(n + 1) · MAX(n - 1) |
✅ |
Every venue, not just the SELECT list
SELECT n FROM t WHERE FLOOR(n / m) > 1 |
❌ |
SELECT n FROM t ORDER BY CAST(n / m AS INTEGER) |
❌ |
SELECT FLOOR(n / m) AS c, COUNT(*) FROM t GROUP BY FLOOR(n / m) |
❌ |
CREATE TABLE u (n INTEGER, m INTEGER, c INTEGER SCRIPT AS (FLOOR(n / m))) |
❌ |
Why it matters more now
Once / yields a floating-point result (the 0.24.0 rule — see #382), there is no single-expression
way to obtain a truncated quotient. This engine has no DIV (MySQL) or // (DuckDB) operator, and
the two rewrites a user would reach for — CAST(n / m AS INTEGER) and FLOOR(n / m) — are exactly
the shapes this gap rejects.
The only workaround is to compute the division into a column and cast that column:
CREATE TABLE t (n INTEGER, m INTEGER, q DOUBLE SCRIPT AS (n / m));
SELECT CAST(q AS INTEGER) AS whole FROM t; -- 3 for 7 / 2That works for a table you own and is no help at all in an ad-hoc query against someone else's index.
This is NOT #267 — do not reopen it
#267 was "CAST/TRY_CAST/CONVERT reject bare literal operands — CAST('125' AS BIGINT) does not parse",
closed 2026-09-04. Its subject is fixed, verified on the same clone:
SELECT CAST('125' AS BIGINT) PARSES
SELECT TRY_CAST('125' AS BIGINT) PARSES
SELECT CONVERT('125', BIGINT) PARSES
A closed issue whose stated defect works should not be reopened to carry a different one; this gap is
wider than #267 ever described (it hits + as much as /, FLOOR/ABS/COALESCE/CASE as much as
CAST, and every venue).
Evidence level
Parse-level, measured on a clean git clone --local at origin/main 975aa87b. Nothing here reaches
Elasticsearch, so there is no execution to report and no silent-wrong-answer risk — every shape above
fails loudly, at parse time. What is NOT established is the fix's shape: whether the operand
productions should admit an arithmetic expression directly, or whether the parenthesised form
(FLOOR((n / m))) should be made to work first as the smaller change.
Found 2026-09-23 while documenting the / rule for #382, from a doc note that cited #267 in error.
Activity
fupelaqu commentedon Oct 6, 2026
Three more shapes, re-measured on
main(f63222d) and on #412's head (0687815)These are parse outcomes from
Parser(sql), and the gateway on Elasticsearch 8.18.3 confirms them (400 Error parsing SQL statement […]: end of input expected). Both builds give the same result. PostgreSQL 16.14 accepts all three.mainand #412SELECT id FROM t WHERE n IN (CAST('1' AS INT), 2)end of input expectedIN (CAST('3' AS INT), 2)keeps the row withn = 3)SELECT g, MAX(d) - INTERVAL 1 DAY FROM t GROUP BY gend of input expected2024-02-29 00:00:00, …)SELECT id, CAST(n + x AS DOUBLE) FROM tend of input expectedControls, same builds, executed:
mainand #412SELECT id FROM t WHERE n IN (1, 2)SELECT id, CAST(n AS DOUBLE) + x AS v FROM tSELECT id, (n + x)::DOUBLE AS v FROM tSELECT id, d - INTERVAL 1 DAY AS v FROM tSELECT g, DATE_SUB(MAX(d), INTERVAL 1 DAY) AS v FROM t GROUP BY gAggregation function must be the first function in the chainWhat this adds to the issue:
CAST(n + x AS DOUBLE)is this issue's own shape (arithmetic as the operand of aCAST), and fix(sql): date arithmetic follows the SQL engines; type checks wait for the column types #412 does not change it.(n + x)::DOUBLEis the spelling that works.INlist takes literals only. ACASTinside the list does not parse.-or withDATE_SUB. Subtracting it from a column works.