Opened 2 years ago

Closed 2 years ago

Last modified 2 years 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 Abdullah
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords:
Cc: Sage Abdullah 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 2 years ago by Gordon Wrigley

Description: modified (diff)

comment:2 Changed 2 years ago by Sage Abdullah

Cc: Sage Abdullah added

comment:3 Changed 2 years ago by Sage Abdullah

Component: UncategorizedDatabase layer (models, ORM)
Owner: changed from nobody to Sage Abdullah
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 2 years ago by Sage Abdullah (previous) (diff)

comment:4 Changed 2 years ago by Sage Abdullah

Has patch: set

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

comment:5 Changed 2 years ago by Gordon Wrigley

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

comment:6 Changed 2 years ago by Mariusz Felisiak

Severity: NormalRelease blocker

comment:7 Changed 2 years 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 2 years 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 2 years 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 2 years 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