SQL: `CREATE PROCEDURE/FUNCTION/TRIGGER` symbols lack body ranges — `callers`/`callees`/`impact` return zero for every SQL symbol, yet `graph_supported` capability flag still reports true

[Widthdom]

Summary

SQL is listed in ReferenceExtractor.SupportedLanguages and every graph-capability surface (inspect --json, MCP analyze_symbol, zero-result payloads for references / callers / callees / impact) reports graph_supported: true with reason "Call-graph extraction is indexed for 'sql'.". In practice, SQL callers / callees / impact return zero for every stored procedure / function / trigger on every indexed SQL file, because CREATE PROCEDURE / CREATE FUNCTION / CREATE TRIGGER symbols are extracted with BodyStyle.None in SymbolExtractor:

// src/CodeIndex/Indexer/SymbolExtractor.cs (SQL patterns)
new("function", new Regex(@"^\s*CREATE\s+(?:OR\s+REPLACE\s+)?(?:PROCEDURE|FUNCTION|TRIGGER)\s+(?<name>[\w.]+)",
    RegexOptions.Compiled | RegexOptions.IgnoreCase), BodyStyle.None),

Without a body range on the caller symbol, ResolveContainerForCall can't attribute a call inside the procedure body to the enclosing procedure — so every row in symbol_references for SQL ends up with container_kind=NULL / container_name=NULL. callers <proc> joins symbols to symbol_references via container_name, so it matches zero rows even when the reference row itself is correct.

The capability metadata claims the opposite:

$ dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll inspect dbo.sp_Target --lang sql --json
... "graph_supported": true,
    "graph_support_reason": "Call-graph extraction is indexed for 'sql'." ...

AI clients and MCP consumers trust this JSON (not the README) to decide whether to call callers / callees / impact, so the current state gives silent-zero answers instead of "use search for SQL graph questions".

This is a distinct root cause from #296 (SQL schema-qualified symbol name mismatch). #296 is about the name-vocabulary mismatch between SymbolExtractor and ReferenceExtractor; this ticket is about container resolution: even after #296 aligns names, callers will still return zero on SQL because no SQL reference row carries a container_name.

Repro

CDIDX=\"dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll\"
REPRO=/tmp/cdidx-sql-bodyrange
rm -rf \"$REPRO\" && mkdir -p \"$REPRO\"

cat > \"$REPRO/procs.sql\" <<'SQL'
CREATE PROCEDURE dbo.sp_Target(@x int)
AS
BEGIN
    SELECT @x;
END;
GO

CREATE PROCEDURE dbo.sp_Caller
AS
BEGIN
    EXEC dbo.sp_Target 1;
END;
GO
SQL

$CDIDX index \"$REPRO\" --rebuild --json >/dev/null
DB=\"$REPRO/.cdidx/codeindex.db\"

echo \"=== references sp_Target (expect: at least 1 call row) ===\"
$CDIDX references sp_Target --db \"$DB\"

echo \"=== callers sp_Target (expect: 1 row — dbo.sp_Caller at procs.sql:10) ===\"
$CDIDX callers sp_Target --db \"$DB\"

echo \"=== symbol_references.container_name (expect: 'dbo.sp_Caller') ===\"
sqlite3 \"$DB\" \"SELECT symbol_name, reference_kind, line, container_kind, container_name FROM symbol_references;\"

echo \"=== inspect capability claim ===\"
$CDIDX inspect sp_Target --lang sql --json | python3 -c \"import json,sys; d=json.load(sys.stdin); print('graph_supported:', d.get('graph_supported'), 'reason:', d.get('graph_support_reason'))\"

Observed (cdidx v1.11.0):

=== references sp_Target ===
call   sp_Target   procs.sql:11:10   →  EXEC dbo.sp_Target 1;
(1 reference)

=== callers sp_Target ===
No callers found.                                        ← BROKEN

=== symbol_references.container_name ===
sp_Target|call|11|||                                     ← container_name NULL

=== inspect capability claim ===
graph_supported: True                                    ← LIES
reason: Call-graph extraction is indexed for 'sql'.

Expected:

• callers sp_Target → 1 row (dbo.sp_Caller at procs.sql:10), or
• graph_supported: false (or graph_degraded: true with a reason like \"sql_container_resolution_missing\") so AI clients don't route callers / callees / impact queries to SQL indexes.

Suspected root cause

Two contributing gaps, both in SQL SymbolExtractor patterns:

1. BodyStyle.None — SQL CREATE PROCEDURE/FUNCTION/TRIGGER symbols carry only a line number for the CREATE statement, no body_start_line / body_end_line. ReferenceExtractor.ResolveContainerForCall relies on walking indexed symbols that contain the current line inside their body range; with no body range, nothing contains the call site and container_name stays NULL on every row.
2. Capability metadata not gated on body-range support — ReferenceExtractor.SupportsSymbolGraph returns true for any language in SupportedLanguages. It doesn't know that SQL's symbol shape precludes caller attribution. Downstream the graph_supported JSON claim therefore overstates reality.

Suggested direction

Two independent fixes:

A. Give SQL PROCEDURE / FUNCTION / TRIGGER a real body range

T-SQL / MySQL / PostgreSQL all delimit proc/function bodies in slightly different ways. Pragmatic shapes worth supporting:

• T-SQL: AS BEGIN ... END[;] [GO] or AS BEGIN ... END[;]\nGO at top-level.
• T-SQL: AS followed by a single statement until the next GO or the next CREATE.
• MySQL / MariaDB: BEGIN ... END; inside a DELIMITER // block.
• PostgreSQL: AS $$ ... $$ LANGUAGE plpgsql; / AS $tag$ ... $tag$.

Option: add a SQL-specific body-range resolver that scans forward from the CREATE line for the statement terminator (GO, END at top-level, ; outside a string). Even a coarse "body = everything from CREATE PROCEDURE line to the first GO or end-of-file" heuristic is enough to let ResolveContainerForCall find the enclosing procedure on the common T-SQL shape.

B. Make graph_supported honest for SQL until A lands

Minimum change: in SupportsSymbolGraph, return false (or a new graph_degraded=true with reason) for sql symbols that don't carry a body range. Propagate graph_support_reason = \"SQL references are indexed, but caller/callee/impact container resolution is not yet end-to-end for SQL; use 'search' for 'who calls this proc?' questions.\" so AI clients route around it instead of getting silent zeros.

Scope

• src/CodeIndex/Indexer/SymbolExtractor.cs — SQL PROCEDURE / FUNCTION / TRIGGER patterns gain a body-range strategy (not BodyStyle.None). Consider adding a SQL-specific body resolver.
• src/CodeIndex/Indexer/ReferenceExtractor.cs — SupportsSymbolGraph / GetGraphSupportReason gain SQL-specific degradation (pending A).
• src/CodeIndex/Cli/QueryCommandRunner.cs / src/CodeIndex/Mcp/McpToolHandlers.cs — surface the degraded reason in zero-result payloads and inspect output.
• tests/CodeIndex.Tests/SymbolExtractorTests.cs — fixture asserting body range is assigned to CREATE PROCEDURE dbo.sp_X AS BEGIN ... END.
• tests/CodeIndex.Tests/QueryCommandRunnerTests.cs or McpServerTests.cs — end-to-end callers dbo.sp_Target returns the enclosing caller after SQL: schema-qualified symbol names (dbo.fn_X) don't match bare reference names (fn_X) — callers always returns zero, references on the definition line produces a phantom self-reference #296 and this ticket both land, and meanwhile graph_supported for SQL is degraded with the correct reason.

Related

• SQL: procedure-call reference extraction misses the dominant T-SQL form — EXEC <proc> / EXECUTE <proc> (no parentheses, optional argument list) #232 — T-SQL EXEC / CALL no-parens reference extraction (extractor-only, now fixed). Left this gap explicit in the PR description as out-of-scope because it requires changing SymbolExtractor body-range strategy.
• SQL: schema-qualified symbol names (dbo.fn_X) don't match bare reference names (fn_X) — callers always returns zero, references on the definition line produces a phantom self-reference #296 — SQL schema-qualified symbol name mismatch (dbo.fn_X vs bare fn_X). Orthogonal root cause: even after SQL: schema-qualified symbol names (dbo.fn_X) don't match bare reference names (fn_X) — callers always returns zero, references on the definition line produces a phantom self-reference #296 aligns names, callers will still return zero without container resolution.
• SQL: FROM table, JOIN table, INSERT INTO table, CTE usage, EXEC usp_*, and view references are all invisible — plus INSERT INTO audit_log (col, …) is captured as a spurious function call #284 — Broader SQL reference coverage (FROM/JOIN/INTO etc.). Also distinct: SQL: FROM table, JOIN table, INSERT INTO table, CTE usage, EXEC usp_*, and view references are all invisible — plus INSERT INTO audit_log (col, …) is captured as a spurious function call #284 is about what references are captured; this ticket is about attributing captured references to an enclosing procedure.

Environment

• cdidx v1.11.0 (locally built from fix/issue-232-tsql-exec-references).
• Platform: macOS darwin-arm64.
• Identified by codex adversarial review during SQL: procedure-call reference extraction misses the dominant T-SQL form — EXEC <proc> / EXECUTE <proc> (no parentheses, optional argument list) #232 iteration loop.

[Widthdom]

Starting work on this in branch fix/issue-429-sql-body-ranges.