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

JSON path equality against a non-string value: silently empty on MySQL, raises on PostgreSQL

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

Personne n'a encore pris cette issue.

Évaluation

Difficulté
3/5
Temps estimé
1-2 jours
Accessibilité débutants
75/100
Type d'issue
Bug
Clarté
Clairement spécifiée
Activité
Active
Stack technique
mysql, postgresql, python
Domaine
databases

Piste de recherche

Start in condition.py around prep_value at line 336, then trace adapter.json_path_expr for the MySQL and PostgreSQL implementations. Reproduce the listed restrictions against both backends and add regression coverage for string, integer, and boolean values. Done means JSON-path comparisons behave consistently across backends and never silently return wrong rows.

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

Description

bug

Table & {"json_attr.field": value} only behaves correctly when value is a string. A Python bool returns the wrong answer on MySQL and raises on PostgreSQL; a Python int raises on PostgreSQL.

Sibling of #1563 — same root (JSON extraction yields text), different side: that one is the :type annotation on projection, this one is the Python value on restriction.

Repro

@schema
class E(dj.Manual):
    definition = """
    name : varchar(32)
    ---
    s : json
    """
E.insert([
    {"name": "a", "s": {"vendor": "Acme",  "ch": 64, "cal": True}},
    {"name": "b", "s": {"vendor": "Other", "ch": 16, "cal": False}},
])

Each row should select ['a']:

restriction MySQL 8.0 postgres:15
& {"s.vendor": "Acme"} ['a'] ['a']
& {"s.ch": 64} ['a'] UndefinedFunction: operator does not exist: text = integer
& {"s.ch": "64"} ['a'] ['a']
& {"s.cal": True} [] UndefinedFunction: operator does not exist: text = boolean
& {"s.cal": "true"} ['a'] ['a']

The MySQL boolean row is the serious one: no error, no rows, and the natural reading of an empty result is "nothing is calibrated."

Cause

adapter.json_path_expr yields json_value(...) / jsonb_extract_path_text(...), both of which return text. prep_value (condition.py:336) then renders the Python value by its own type, so the comparison becomes text = <non-text>:

  • PostgreSQL refuses the comparison outright.
  • MySQL coerces for numerics, which is why 64 happens to work, but compares the extracted true against 1 for a bool and matches nothing.

The asymmetry is invisible to a user: the same expression is correct, wrong, or an error depending on the value's Python type and the backend.

Suggested fix

On the JSON-path branch of prep_value, render the comparison value as text — or cast the extraction to the value's type — so that True, 64 and "Acme" all behave the same way on both backends.

Whichever way, a bool must not silently match nothing. If a given comparison cannot be made portable, raising is acceptable; returning the wrong rows is not.

Documentation

This is almost certainly why tutorials/advanced/json-type.ipynb teaches "Filtering on JSON Content — fetch then filter in Python" and lists "Filter in Python" as an inherent property of JSON in its Design Guidelines. Server-side filtering does work, with the value passed as a string; the tutorial's advice reads as a limitation of the type rather than of this behavior. Worth revisiting together.

Langage dominant
Python
Étoiles
197
Forks
98
Merge moyen
23 h 9 min
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.