Repository navigation
Description
Where do you use Phoenix
Self-hosted
What version of Phoenix are you using?
20.11.0 (main @ e12748298b366605cc0e2aa60e659f473efbff99)
What happened?
On a self-hosted PostgreSQL deployment, the executeSql MCP tool silently returns NULL / zero rows for the JSON arrow-operator-plus-path spelling, while the identical statement on SQLite returns the value. No error, no note, no partial flag — just a wrong answer:
SELECT attributes ->> '$.llm.model_name' FROM spans→NULLon PostgreSQL,'gpt-4'on SQLite... WHERE attributes ->> '$.llm.model_name' = 'gpt-4'→ 0 rows on PostgreSQL, 1 row on SQLite
The spelling is easy to reach:
- The tool's own refusal message recommends it. When a SQLite caller writes
#>>,parse.pyrefuses with: "#>and#>>are PostgreSQL JSONB operators. On SQLite write json_extract(doc, '$.a.b') or attributes ->> '$.a'." (src/phoenix/server/mcp/sql/parse.py:1338) — so a client writing cross-dialect SQL is steered into exactly the spelling that silently NULLs on PostgreSQL. - The allowlist admits
->/->>alongside#>/#>>as "the same capability -- read one value out of a document" (src/phoenix/server/mcp/sql/allowlist.py:124-131), so the statement is accepted rather than refused. - The identical read in function spelling works on both backends:
json_extract(attributes, '$.llm.model_name')returns'gpt-4'on PostgreSQL, because_canonicalize_postgres_json_extract_functionrewrites it tojsonb_extract_path_text(attributes, 'llm', 'model_name')— that pass exists precisely to make "the portable spelling" work against jsonb (src/phoenix/server/mcp/sql/rewrite.py:596-603).
Minimal reproduction
Against a checkout of main @ e12748298b366605cc0e2aa60e659f473efbff99, with one empty local PostgreSQL database (createdb phoenix_repro_arrow):
import asyncio
import tempfile
from datetime import datetime, timezone
from typing import cast as tcast
from sqlalchemy import URL, Table
from phoenix.db import models
from phoenix.db.engines import aio_postgresql_engine, aio_sqlite_engine
from phoenix.server.app import _db
from phoenix.server.mcp.sql.execute import ExecuteParams, execute_analytics_sql
from phoenix.server.types import DbSessionFactory
# An empty local PostgreSQL database (created beforehand with createdb).
PG = URL.create("postgresql+asyncpg", username="admin", host="localhost",
port=5432, database="phoenix_repro_arrow")
async def seed(conn):
await conn.execute(tcast(Table, models.Project.__table__).insert(),
{"name": "demo", "description": None,
"gradient_start_color": "#000000",
"gradient_end_color": "#ffffff"})
await conn.execute(tcast(Table, models.Trace.__table__).insert(),
{"project_rowid": 1, "trace_id": "t1",
"start_time": datetime(2024, 1, 1, tzinfo=timezone.utc),
"end_time": datetime(2024, 1, 1, 0, 0, 2, tzinfo=timezone.utc)})
await conn.execute(tcast(Table, models.Span.__table__).insert(),
{"trace_rowid": 1, "span_id": "s1", "parent_id": None,
"name": "llm call", "span_kind": "LLM",
"start_time": datetime(2024, 1, 1, tzinfo=timezone.utc),
"end_time": datetime(2024, 1, 1, 0, 0, 1, tzinfo=timezone.utc),
"attributes": {"llm": {"model_name": "gpt-4"}},
"events": [], "status_code": "OK", "status_message": "",
"cumulative_error_count": 0,
"cumulative_llm_token_count_prompt": 30,
"cumulative_llm_token_count_completion": 12,
"llm_token_count_prompt": 30,
"llm_token_count_completion": 12})
async def build(dialect):
if dialect == "sqlite":
path = f"{tempfile.mkdtemp()}/a.db"
engine = aio_sqlite_engine(URL.create("sqlite+aiosqlite", database=path),
migrate=False)
else:
path = None
engine = aio_postgresql_engine(PG, migrate=False)
async with engine.begin() as conn:
await conn.run_sync(models.Base.metadata.create_all)
async with engine.begin() as conn:
await seed(conn)
return DbSessionFactory(db=_db(engine), dialect=dialect), path
async def run(db, path, sql):
try:
result = await execute_analytics_sql(db, ExecuteParams(sql=sql),
sqlite_db_path=path)
except Exception as exc:
return f"UNHANDLED {type(exc).__name__}: {exc}"
env = result.envelope
return f"rows={env.rows} partial={env.row_count_is_partial}"
async def main():
sdb, spath = await build("sqlite")
pdb, _ = await build("postgresql")
for sql in (
"SELECT attributes ->> '$.llm.model_name' AS m FROM spans",
"SELECT count(*) FROM spans WHERE attributes ->> '$.llm.model_name' = 'gpt-4'",
"SELECT json_extract(attributes, '$.llm.model_name') AS m FROM spans",
"SELECT count(*) FROM spans WHERE attributes #>> '{llm,model_name}' = 'gpt-4'",
):
print(f"SQL : {sql}")
print(f" sqlite: {await run(sdb, spath, sql)}")
print(f" pg : {await run(pdb, None, sql)}")
print()
asyncio.run(main())Actual output
SQL : SELECT attributes ->> '$.llm.model_name' AS m FROM spans
sqlite: rows=[['gpt-4']] partial=False
pg : rows=[[None]] partial=False
SQL : SELECT count(*) FROM spans WHERE attributes ->> '$.llm.model_name' = 'gpt-4'
sqlite: rows=[[1]] partial=False
pg : rows=[[0]] partial=False
SQL : SELECT json_extract(attributes, '$.llm.model_name') AS m FROM spans
sqlite: rows=[['gpt-4']] partial=False
pg : rows=[['gpt-4']] partial=False
SQL : SELECT count(*) FROM spans WHERE attributes #>> '{llm,model_name}' = 'gpt-4'
sqlite: UNHANDLED AnalyticsSqlError: `#>` and `#>>` are PostgreSQL JSONB operators. On SQLite write json_extract(doc, '$.a.b') or attributes ->> '$.a'.
pg : rows=[[1]] partial=False
Expected behaviour
The two backends should answer the same statement the same way — or, at minimum, the PostgreSQL path should refuse with guidance (e.g. the #>> '{llm,model_name}' / json_extract(doc, '$.a.b') spelling) instead of silently answering NULL. Basis:
- The sibling pass already rewrites the identical read in function spelling, and that works on PostgreSQL (row 3 above returns
'gpt-4'on both). - On SQLite, the
->>operator form is canonicalized so the same text reads as a path (src/phoenix/server/mcp/sql/rewrite.py:497-498skips only the->operator form, not->>), which is why SQLite answers'gpt-4'. allowlist.py:124-131admits->/->>and#>/#>>as the same capability, andparse.py:1338recommendsattributes ->> '$.a'to SQLite callers — a spelling that itself NULLs on PostgreSQL for nested paths.- Verified on real PostgreSQL 16:
json_extractspelling andattributes #>> '{llm,model_name}'both return the row; only the arrow-plus-path spelling answers NULL.
Root cause
src/phoenix/server/mcp/sql/rewrite.py:649, in _canonicalize_postgres_json_extract_function. The pass skips operator-form nodes (node.args.get("only_json_types")) on the premise — docstring at rewrite.py:605-608 — that "the operator ... already renders correctly". For a path-shaped operand that premise is false: the PostgreSQL parse keeps the whole '$.llm.model_name' as a single JSONPathKey, and the re-emitted attributes ->> '$.llm.model_name' asks jsonb for one key literally named $.llm.model_name — always NULL. Compare rewrite.py:625-631, where the same pass, faced with the analogous key-vs-path hazard for computed operands, explicitly notes it rather than answering silently; here nothing is noted. Same behaviour for ->.
A possible fix direction: in _canonicalize_postgres_json_extract_function, don't skip operator-form nodes whose JSONPath operand is a single JSONPathKey whose text is itself path-shaped (contains .); decompose it like the function form and rewrite the node to jsonb_extract_path/jsonb_extract_path_text. A genuine single key (attributes ->> 'cost') must keep rendering as a key lookup, so the gate would be the embedded $.-shape — or, at minimum, refuse with the #>>/json_extract guidance instead of silently answering NULL.
Additional information
- Python 3.12.14, macOS 26.6.2 (arm64), PostgreSQL 16.15 (self-hosted); SQLite file-backed.
- Reproduced end-to-end through
execute_analytics_sql(the implementation behind theexecuteSqlMCP tool) against a real PostgreSQL 16 database and an identically seeded SQLite database.spans.attributesis stored as JSONB on PostgreSQL (src/phoenix/db/models.py:880, with thepostgresql.JSONBvariant atmodels.py:218-223), so this affects any MCP client reading a nested span attribute on a PostgreSQL deployment. - Searched the tracker for existing reports (
json_extract postgres,jsonb_extract_path,mcp sql,analytics sql postgres) and found none covering this.
Happy to open a PR with the approach above if the direction sounds right.
Metadata
Metadata
Assignees
Labels
Type
Projects
- StatusShow more project fields✅ Done
Activity
BlueX888 commentedon Sep 25, 2026
Reproduced this. Root cause: src/phoenix/server/mcp/sql/rewrite.py:649 — _canonicalize_postgres_json_extract_function skips operator-form nodes, so attributes ->> '$.llm.model_name' reaches PostgreSQL with the whole path as a single key name and jsonb answers NULL.
Minimal reproduction:
Observed output:
Happy to open a PR with this fix, or to be assigned if you prefer to take it.
fix(sql): walk JSON path in PostgreSQL operator-form `->>` extract (c…
fix(sql): walk JSON path in PostgreSQL operator-form `->>` extract (c…
RogerHYang2 commentedon Oct 12, 2026
Thanks, but we are closing this issue as not planned because the engine is behaving as expected. On PostgreSQL,
->>looks up one text key.attributes ->> '$.llm.model_name'asks for a key named$.llm.model_name, andNULLis the right result when that key is absent. On PostgreSQL the path read isattributes #>> '{llm,model_name}'.