Hacktoberfest 2026 : les issues que les mainteneurs ont marquées pour octobre, ouvertes et accessibles aux débutants. Parcourir les issues Hacktoberfest

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

Ouverte
  • Quelque chose ne va pas ?
#1,563 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
68/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
Active
Stack technique
mysql, postgresql, python
Domaine
databases

Piste de recherche

Start with adapters/mysql.py around lines 872-874 and compare its return_type handling with the mapping in adapters/postgres.py. Reproduce the listed expressions against MySQL 8.0 and PostgreSQL 15, then check json-type.ipynb and reference/specs/type-system.md. Done means the accepted annotation vocabulary is consistent or clearly rejected across both adapters, with the canonical spelling documented.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

bug

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}})
expression MySQL 8.0 postgres:15
Doc.proj(n="data.n") '7' — str '7' — str
Doc.proj(n="data.n:int") QuerySyntaxError 7 — int
Doc.proj(n="data.n:unsigned") 7 — int 7 — int
Doc.proj(n="data.n:decimal(5,1)") Decimal('7.0') Decimal('7.0')

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.

Langage dominant
Python
Étoiles
197
Forks
98
Merge moyen
1 j 23 h
PR mergées (30 j)
6

Préparer son environnement

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de datajoint/datajoint-python

Toutes les issues de datajoint/datajoint-python

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.