Opened 10 months ago

Closed 10 months ago

Last modified 10 months ago

#32203 closed Bug (fixed)

Error with values with JSON path lookup in Sqlite when value is numeric.

Reported by: Gordon Wrigley Owned by: sage
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords:
Cc: sage Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Gordon Wrigley)

This Django 3.1.3, I only see this with Sqlite, it works fine with MySQL and Postgres.

When I do a path lookup with values like Bob.objects.values("my_json_field__position") if there is an integer, float or bool in "position" then I get a JSON decode error.
Strings, nones, dicts and lists all work, fetching the top level dict works and filtering on the path lookup works.

TypeError: the JSON object must be str, bytes or bytearray, not float

..\..\.venv\data_browser\lib\site-packages\django\db\models\query.py:287: in __iter__
    self._fetch_all()
..\..\.venv\data_browser\lib\site-packages\django\db\models\query.py:1308: in _fetch_all
    self._result_cache = list(self._iterable_class(self))
..\..\.venv\data_browser\lib\site-packages\django\db\models\query.py:111: in __iter__
    for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
..\..\.venv\data_browser\lib\site-packages\django\db\models\sql\compiler.py:1100: in apply_converters
    value = converter(value, expression, connection)
..\..\.venv\data_browser\lib\site-packages\django\db\models\fields\json.py:79: in from_db_value
    return json.loads(value, cls=self.decoder)

Change History (10)

comment:1 Changed 10 months ago by Gordon Wrigley

Description: modified (diff)

comment:2 Changed 10 months ago by sage

Cc: sage added

comment:3 Changed 10 months ago by sage

Component: UncategorizedDatabase layer (models, ORM)
Owner: changed from nobody to sage
Status: newassigned
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

This is because SQLite's JSON_EXTRACT returns deserialized values. A simple solution is just to add TypeError here, which will also partially "solve" problems with people who use the JSON data type on PostgreSQL. Another option is to wrap the value in JSON_QUOTE... which I think might be the better option.

I'm guessing there's also a bug in querying __key on {"key": "\"value\""}, which will return value rather than "value". I think wrapping the value in JSON_QUOTE should fix this as well... but it would break ordering (and maybe some other stuff).

Last edited 10 months ago by sage (previous) (diff)

comment:4 Changed 10 months ago by sage

Has patch: set

PR. I opted to catch TypeError instead. Need opinions on how to proceed.

comment:5 Changed 10 months ago by Gordon Wrigley

I should add that I see this on both Windows and Linux

comment:6 Changed 10 months ago by Mariusz Felisiak

Severity: NormalRelease blocker

comment:7 Changed 10 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 7408c4cd:

Refs #32203 -- Added tests for QuerySet.values()/values_list() on key transforms with non-trivial values.

comment:8 Changed 10 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In fe6e5824:

Fixed #32203 -- Fixed QuerySet.values()/values_list() crash on key transforms with non-string values on SQLite.

Thanks Gordon Wrigley for the report.

comment:9 Changed 10 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In a2abeb3:

[3.1.x] Refs #32203 -- Added tests for QuerySet.values()/values_list() on key transforms with non-trivial values.

Backport of 7408c4cd157ae18971302a2baf7aacce7c204a09 from master

comment:10 Changed 10 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In a7935fe:

[3.1.x] Fixed #32203 -- Fixed QuerySet.values()/values_list() crash on key transforms with non-string values on SQLite.

Thanks Gordon Wrigley for the report.

Backport of fe6e5824218bab7cf47dee112d68325b338f9947 from master

Note: See TracTickets for help on using tickets.
Back to Top