Opened 3 years ago

Closed 3 years ago

Last modified 3 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 by Gordon Wrigley, 3 years ago

Description: modified (diff)

comment:2 by Sage Abdullah, 3 years ago

Cc: Sage Abdullah added

comment:3 by Sage Abdullah, 3 years ago

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 is 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.

Version 0, edited 3 years ago by Sage Abdullah (next)

comment:4 by Sage Abdullah, 3 years ago

Has patch: set

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

comment:5 by Gordon Wrigley, 3 years ago

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

comment:6 by Mariusz Felisiak, 3 years ago

Severity: NormalRelease blocker

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 7408c4cd:

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

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

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