bug: Trino backend: `.cast(\"json\")` on a string column silently produces NULL instead of parsing the JSON

[gabrielecalvo]

What happened?

StringColumn.cast("json") on the Trino backend compiles to a plain CAST(x AS JSON).
Per Trino's own semantics, CAST(varchar AS JSON) treats the string as a literal scalar
value and wraps it as a JSON string ('{"a":1}' → the JSON string "{\"a\":1}") — it
does not parse the varchar's contents as JSON syntax. The function that does that is
JSON_PARSE(x).

The practical effect: any .cast("json") on a string column of actual JSON text produces a
JSON string scalar instead of a JSON object/array. Every downstream field access on it
(["field"], .int, .str, .array, etc.) then returns NULL, and if that value ends up
inside .unnest(...), Trino's CROSS JOIN UNNEST silently drops the row for a NULL
array — so a query with a .cast("json") → field access → .unnest() chain runs to
completion with no exception and returns an empty table, which looks identical to "no
data matched."

I hit this via AWS Athena (ibis.backends.athena, which subclasses the Trino compiler), but
reproduced it against vanilla open-source Trino too (see below), so it's not Athena-specific.

Reproduction

docker run -d --name ibis-issue-trino -p 8080:8080 trinodb/trino:latest
# wait ~10-20s for startup (docker logs -f ibis-issue-trino)

import ibis

con = ibis.trino.connect(host="localhost", port=8080, user="test", database="memory", schema="default")
con.raw_sql("CREATE TABLE memory.default.events (raw VARCHAR)")
con.raw_sql("""INSERT INTO memory.default.events (raw) VALUES ('{"a":1}')""")

t = con.table("events")
t.select(a=t.raw.cast("json")["a"]).to_pandas()
#      a
# 0  None    <-- expected 1, got None

Isolating it at the SQL level (no ibis involved) confirms the cause directly:

-- ibis's generated cast+extract: silently NULL
SELECT JSON_EXTRACT(CAST(raw AS JSON), '$.a') FROM memory.default.events;
-- ""

-- using JSON_PARSE instead of CAST: works correctly
SELECT JSON_EXTRACT(JSON_PARSE(raw), '$.a') FROM memory.default.events;
-- "1"

this gist reproduces the full pattern I hit in practice
(extract a nested object field, then unnest an array field from it), showing the same
.cast("json") misstep cascading into a fully empty result set with 0 rows instead of the
expected 3.

Suggested fix location

ibis/backends/sql/compilers/trino.py, TrinoCompiler.visit_Cast (currently falls through
to the base compiler's plain CAST for any cast whose to is JSON). It needs a branch like
the existing numeric→timestamp special case just above it:

def visit_Cast(self, op, *, arg, to):
    from_ = op.arg.dtype
    ...
    if from_.is_string() and to.is_json():
        return self.f.json_parse(arg)
    return super().visit_Cast(op, arg=arg, to=to)

What version of ibis are you using?

12.0.0 (sqlglot 30.12.0)

What backend(s) are you using, if any?

Trino (reproduced on vanilla trinodb/trino:latest via Docker); also observed on AWS Athena
(ibis.backends.athena, which subclasses the Trino compiler).

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct

[ivarfnox]

If you are using Trino with JSON types, do you not also get performance issues since Trino does not use the JSON type for JSON parsing? Trino only parses strings for JSON path functions, as far as I can remember.

[gabrielecalvo]

That's a fair point, thanks for bringing it to my attention. I believe you are correct, it will be a performance hit. But if a user has to work with that data, might be better to correct the behaviour. I see there is already a fix (#12077).

If one is chaining into json_extract-style operations, it may be faster in ibis to route through a JSON-path extraction that operates on the varchar directly rather than casting first. That's arguably a separate optimization/API question. Worth its own issue if there's appetite, since it'd mean ibis emitting json_extract(varchar_col, path) instead of json_extract(json_parse(varchar_col), path) when no other consumer of the parsed value exists in the expression tree.