Skip to content

[BUG]: executeSql on PostgreSQL: attributes ->> '$.llm.model_name' silently returns NULL / matches no rows while SQLite returns the value #16519

Closed as not planned

Description

@BlueX888

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 → NULL on 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:

  1. The tool's own refusal message recommends it. When a SQLite caller writes #>>, parse.py refuses 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.
  2. 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.
  3. 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_function rewrites it to jsonb_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-498 skips only the -> operator form, not ->>), which is why SQLite answers 'gpt-4'.
  • allowlist.py:124-131 admits ->/->> and #>/#>> as the same capability, and parse.py:1338 recommends attributes ->> '$.a' to SQLite callers — a spelling that itself NULLs on PostgreSQL for nested paths.
  • Verified on real PostgreSQL 16: json_extract spelling and attributes #>> '{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 the executeSql MCP tool) against a real PostgreSQL 16 database and an identically seeded SQLite database. spans.attributes is stored as JSONB on PostgreSQL (src/phoenix/db/models.py:880, with the postgresql.JSONB variant at models.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.

Activity

  1. BlueX888 commented on Sep 25, 2026

    @BlueX888
    Author

    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:

    Seed one span with attributes {"llm": {"model_name": "gpt-4"}} on both backends, then via execute_analytics_sql:
    SELECT attributes ->> '$.llm.model_name' AS m FROM spans
    SELECT count(*) FROM spans WHERE attributes ->> '$.llm.model_name' = 'gpt-4'
    

    Observed output:

    sqlite: rows=[['gpt-4']] partial=False
    pg    : rows=[[None]] partial=False
    sqlite: rows=[[1]] partial=False
    pg    : rows=[[0]] partial=False
    

    Happy to open a PR with this fix, or to be assigned if you prefer to take it.

  2. added a commit that references this issue on Sep 27, 2026
    5e17d7a
  3. added
    bugSomething isn't working
    language: pythonIssues with python as the primary language of use
    and removed
    triageissues that need triage
    on Oct 5, 2026
  4. added a commit that references this issue on Oct 12, 2026
    705de52
  5. RogerHYang2 commented on Oct 12, 2026

    @RogerHYang2
    Contributor

    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, and NULL is the right result when that key is absent. On PostgreSQL the path read is attributes #>> '{llm,model_name}'.

  6. moved this from 📘 Todo to ✅ Done in phoenixon Oct 12, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't workingc/mcplanguage: pythonIssues with python as the primary language of use

Type

No type

Projects

  • Status
    ✅ Done

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions