Hacktoberfest 2026：維護者為十月標記出來的 issue，仍然開放、適合新手。 瀏覽 Hacktoberfest issue

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

未關閉
  • 哪裡不對？
#1,564 0 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視

還沒有人認領這個 Issue。

評估

難度
3/5
預估耗時
1-2 天
新手友好度
75/100
Issue 類型
缺陷
描述清晰度
描述清楚
活躍度
活躍
技術堆疊
mysql, postgresql, python
領域
databases

研究方向

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.

由索引模型根據 Issue 內容生成。

描述

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.

主要語言
Python
星號
197
分支
98
平均合併
23 小時 9 分鐘
30 天內合併 PR
6

環境準備

  • 提供 Dockerfile 或 Docker Compose 檔案
  • 沒有 Pull Request 範本
  • 閱讀貢獻指南

從這裡開始

  1. 先讀完整個 Issue，再讀專案的貢獻指南。
  2. 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
  3. Fork 儲存庫，在一個分支上完成修改。
  4. 送出 Pull Request，並在描述裡引用這個 Issue 編號。

datajoint/datajoint-python 的其他 Issue

查看 datajoint/datajoint-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。