JSON path type annotation is not portable: data.n:int works on PostgreSQL, raises on MySQL #1563

The attr.path:type annotation accepted by translate_attribute takes a different vocabulary on each backend, with no indication of which is canonical. The same expression works on one and raises a SQL syntax error on the other.

Unrelated to #1561 and #1562, which are about hidden attributes. This one is about an ordinary visible json column.

Repro

@schema
class Doc(dj.Manual):
    definition = """
    doc_id : int32
    ---
    data : json
    """
Doc.insert1({"doc_id": 1, "data": {"system": "PyRat", "n": 7}})

Cause

The two adapters treat return_type differently.

adapters/postgres.py maps the annotation onto a PostgreSQL type before casting:

pg_type = return_type.lower()
if pg_type in ("unsigned", "signed"):
    pg_type = "integer"

so it accepts MySQL's vocabulary and PostgreSQL's own.

adapters/mysql.py:872-874 passes it through verbatim:

return_clause = f" returning {return_type}" if return_type else ""
return f"json_value({quoted_col}, _utf8mb4'$.{path}'{return_clause})"

MySQL's JSON_VALUE ... RETURNING accepts only a fixed set — SIGNED, UNSIGNED, DECIMAL, CHAR, DATE and so on — and int is not among them, so the statement is invalid.

The effect is that the portable vocabulary is MySQL's, PostgreSQL silently tolerates a superset, and a pipeline developed against PostgreSQL can carry an annotation that fails the first time it runs on MySQL. Nothing in the docs or the error says so.

Suggested fix

Normalize in the MySQL adapter as PostgreSQL already does — map int / integer / bigint and similar onto the RETURNING vocabulary — and reject an unmappable annotation with a clear error naming the accepted set, rather than emitting SQL the server will refuse.

Settle which spelling is canonical and document it. json-type.ipynb and reference/specs/type-system.md are the places that would say so; neither currently does.

Also worth noting

Without an annotation, a numeric JSON field comes back as a string on both backends ('7', not 7) — both extraction functions return text. That is defensible, since JSON has no fixed scalar type, but it is undocumented and the annotation is the only remedy. Mentioned because anyone hitting the portability problem above arrived there by trying to fix this.