Continuing the SQL:2016 standard function migration: SUBSTRING, POSITION, OVERLAY

[fudianchn]

Context

In the #673 thread @manticore-projects wrote (2023-05):

In my opinion, this particular production NamedExpressionListExprFirst is cursed (since it depends on a semantic LOOKAHEAD) and should be replaced with a proper implementation of the SQL:2016 standard functions. I did convert() and trim() already and will do the others next.

This also matches the more recent reasoning in #2465: "if we'd designed afresh from scratch, maybe based on SQL:2016 standard, then this would make sense", and the idea to "create DIALECTS as Features which we can use in semantic LOOKAHEAD", where fewer such sites would leave less surface to gate. Unlike a redesign, the migration below is incremental, and each step strictly removes grammar surface (a production, a token and a helper method disappear) instead of adding maintenance burden.

I would like to pick up that direction and do "the others", if it is still current.

Current state on master (406a4d4, 2026-08-15)

Parsed AST types for the named-parameter standard functions:

SQL	Parsed as
TRIM('x' FROM 'yx')	TrimFunction
EXTRACT(DAY FROM SYSDATE)	ExtractExpression
CONVERT(...) (incl. TRY/SAFE variants)	dedicated production (K_CONVERT)
SUBSTRING('abc' FROM 2 FOR 2)	generic Function with namedParameters
POSITION('b' IN 'abc')	generic Function with namedParameters
OVERLAY('ab' PLACING 'cd' FROM 2)	generic Function with namedParameters

The remaining three all go through SpecialStringFunctionWithNamedParameters() and the semantic LOOKAHEAD( { isNamedExprListAhead() } ) (a token scan with paren-depth tracking) into NamedExpressionListExprFirst(), gated by the K_STRING_FUNCTION_NAME token (SUBSTR | SUBSTRING | TRIM | POSITION | OVERLAY; TRIM is already diverted to TrimFunction() before reaching it).

Proposal

Migrate one function per PR, following TrimFunction as the template:

1. SUBSTRING / SUBSTR (FROM, optional FOR)
2. POSITION (IN)
3. OVERLAY (PLACING ... FROM ... FOR ...)

Each PR would carry: a dedicated AST class + production, deparser / validator / TablesNamesFinder wiring, regression tests for the argument shapes (incl. the plain comma forms, which must keep parsing as regular Function), and a gradle jmh master-vs-branch comparison (parseSQLStatements on performance.sql, 10 forks x 10 iterations) since the semantic LOOKAHEAD sits on the parse hot path.

A final cleanup PR once all three are migrated would remove SpecialStringFunctionWithNamedParameters, NamedExpressionListExprFirst, isNamedExprListAhead() and the then-dead K_STRING_FUNCTION_NAME token.

Open design questions (maintainer's call)

1. AST shape: one dedicated class per function (SubstringFunction, PositionFunction, OverlayFunction) mirroring TrimFunction, or a single shared class with a discriminator? Per-function is the established precedent, but adds public types.

2. Compatibility: today SUBSTRING(x FROM 2) parses to Function with getNamedParameters(); after the migration it would parse to the new type, observable through instanceof and visitors. Two options:

   • clean switch with release notes,
   • old parsing kept available behind a Feature flag for a transition period.

   My default would be the clean switch, but this is exactly the public-API category that cannot be repaired later, so I would rather not decide it alone.

3. Order: is SUBSTRING -> POSITION -> OVERLAY fine, or is there a preference?

No expectations on review or merge timing; happy to adjust the split or drop any part of this.