Scalar function wrapping a date-part extractor does not parse: SELECT ABS(YEAR(x))

[fupelaqu]

Wrapping a date-part extractor in a scalar function does not parse. The error names a parenthesis and points at FROM, which is nowhere near the cause.

SELECT ABS(YEAR(createdAt)) FROM t
-- ')' expected but 'F' found

Measured

SQL	Result
SELECT ABS(YEAR(createdAt)) FROM t	❌ ')' expected but 'F' found
SELECT UPPER(YEAR(createdAt)) FROM t	❌ same
SELECT ROUND(YEAR(createdAt)) FROM t	❌ same
SELECT ABS(MONTH(createdAt)) FROM t	❌ same
SELECT ABS(QUARTER(createdAt)) FROM t	❌ same
SELECT ABS(YEAR(createdAt) + 1) FROM t	❌ ')' expected but '+' found
SELECT MAX(YEAR(createdAt)) AS m FROM t GROUP BY id	✅
SELECT COUNT(YEAR(createdAt)) AS c FROM t GROUP BY id	✅
SELECT YEAR(DATE_TRUNC(createdAt, MONTH)) FROM t	✅
SELECT ABS(DATE_DIFF(a, b, YEAR)) FROM t	✅
SELECT ABS(WEEKDAY(createdAt)) FROM t	✅
SELECT YEAR(createdAt) + 1 FROM t	✅

So it is specifically scalar function → name-only extractor. Aggregates wrapping an extractor work, an extractor wrapping something else works, and a scalar wrapping a function that consumes its own parentheses (DATE_DIFF, WEEKDAY) works.

Cause

The same unbalanced close that #219 fixed for SCRIPT AS. identifierWithFunction closes with rep1(end) — one or more parentheses — so the inner extractor call consumes both its own ) and the one belonging to the enclosing ABS(. The outer production's end then meets FROM.

It only affects the name-only extractors, which are declared as the bare name (YEAR.regex ^^ (_ => new Year), sql/src/main/scala/app/softnetwork/elastic/sql/parser/function/time/package.scala) and therefore reach that generic path: YEAR, MONTH, DAY, WEEK, QUARTER, EPOCHDAY, YEARDAY, HOUR, MINUTE, SECOND, and the rest of extractor_function. WEEKDAY is immune because day_of_week_tr consumes NAME ( ident ) with a single end of its own.

⚠️ Read #219 before attempting a fix

Three attempts to make the shared production balanced were tried there and all regressed the fixture MAX(year(date_trunc(datetime_parse(…), MINUTE))):

• bounding the close count with repN
• giving the extractors a balanced extractor_identifier
• removing extractor_function from sql_function

Each dropped Year from identifier.functions while .sql still rendered it — the only visible symptom was the emitted painless script losing .get(ChronoField.YEAR). The sql suite stayed green at 487; only softclient4es-sql-bridge/test caught it. rep1sep(sql_function, start) is ambiguous and the greed of rep1(end) is what selects the correct nested parse, so any change to the paren accounting reshuffles which alternative wins.

#219 therefore fixed only the SCRIPT AS manifestation, by deciding the boundary locally (a balanced-paren scan of the script body) and leaving the shared production untouched. This issue is the remaining, general case, and needs the ambiguity itself resolved.

Any fix must be verified against softclient4es-sql-bridge/test (and the other four bridges), not just sql/test, and should assert identifier.functions — not only the .sql round trip, which is blind to this.

Workaround

Compute the extractor at the top level (SELECT YEAR(createdAt) + 1), or wrap with an aggregate where one is acceptable.