[Django] #32483: querying JSONField in sqlite returns ints for booleans

[Django]

#32483: querying JSONField in sqlite returns ints for booleans
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
matthewcornell |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: jsonfield querying
Severity: Normal | sqlite
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have a model with a JSONField:

{{{
class PredictionData(models.Model):
data = models.JSONField()
}}}

One of the rows contains this dict: `{'value': True}`.

I'm querying the model's JSON using 'data__value':

{{{
PredictionData.objects.values_list('data', 'data__value')
}}}

I get correct results for postgres (a boolean) but incorrect for sqlite3
(an int). For this query, sqlite3 wrongly returns:

{{{
({'value': True}, 1)
}}}

whereas postgres correctly returns

{{{
({'value': True}, True)
}}}

Same behavior with False/0.

versions:
Python 3.9.1
sqlite3.sqlite_version # '3.33.0'
django.__version__ # '3.1.7'

--
Ticket URL: <https://code.djangoproject.com/ticket/32483>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#32483: querying JSONField in sqlite returns ints for booleans
-------------------------------------+-------------------------------------

Reporter: matthewcornell | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Rohith P R):

* stage: Unreviewed => Accepted

Comment:

I was able to reproduce the issue:

{{{
from django.db import models

class Messages(models.Model):
json_field = models.JSONField()
}}}

{{{
>>> from app.models import Messages
>>> result = Messages.objects.values_list('json_field',
'json_field__is_true')
>>> print(result)
<QuerySet [({'is_true': True}, 1)]>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:1>

[Django]

#32483: querying JSONField in sqlite returns ints for booleans
-------------------------------------+-------------------------------------

Reporter: matthewcornell | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by matthewcornell:

Old description:

> I have a model with a JSONField:
>
> {{{
> class PredictionData(models.Model):
> data = models.JSONField()
> }}}
>
> One of the rows contains this dict: `{'value': True}`.
>
> I'm querying the model's JSON using 'data__value':
>
> {{{
> PredictionData.objects.values_list('data', 'data__value')
> }}}
>
> I get correct results for postgres (a boolean) but incorrect for sqlite3
> (an int). For this query, sqlite3 wrongly returns:
>
> {{{
> ({'value': True}, 1)
> }}}
>
> whereas postgres correctly returns
>
> {{{
> ({'value': True}, True)
> }}}
>
> Same behavior with False/0.
>

> versions:
> Python 3.9.1
> sqlite3.sqlite_version # '3.33.0'
> django.__version__ # '3.1.7'

New description:

I have a model with a JSONField:

{{{
class PredictionData(models.Model):
data = models.JSONField()
}}}

One of the rows contains this dict: {{{{'value': True}}}}.

I'm querying the model's JSON using {{{'data__value'}}}:

{{{
PredictionData.objects.values_list('data', 'data__value')
}}}

I get correct results for postgres (a boolean) but incorrect for sqlite3
(an int). For this query, sqlite3 wrongly returns:

{{{
({'value': True}, 1)
}}}

whereas postgres correctly returns

{{{
({'value': True}, True)
}}}

Same behavior with False/0.

versions:
Python 3.9.1
sqlite3.sqlite_version # '3.33.0'
django.__version__ # '3.1.7'

--

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:2>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |

Severity: Release blocker | Resolution:

Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* cc: sage (added)
* severity: Normal => Release blocker

Comment:

Regression tests:
{{{
diff --git a/tests/model_fields/test_jsonfield.py
b/tests/model_fields/test_jsonfield.py
index 89b78de708..43ca00a4d1 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -804,6 +804,16 @@ class TestQuerying(TestCase):
with self.subTest(lookup=lookup):
self.assertEqual(qs.values_list(lookup, flat=True).get(),
expected)

+ def test_key_values_boolean(self):
+ qs = NullableJSONModel.objects.filter(value__h=True)
+ tests = [
+ ('value__h', True),
+ ('value__i', False),
+ ]
+ for lookup, expected in tests:
+ with self.subTest(lookup=lookup):
+ self.assertIs(qs.values_list(lookup, flat=True).get(),
expected)
+
@skipUnlessDBFeature('supports_json_field_contains')
def test_key_contains(self):
self.assertIs(NullableJSONModel.objects.filter(value__foo__contains='ar').exists(),
False)
}}}

Related to #27481 and #32203.

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:3>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: nobody

Type: Bug | Status: new
Component: Documentation | Version: 3.1
Severity: Normal | Resolution:

Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* component: Database layer (models, ORM) => Documentation
* severity: Release blocker => Normal

Comment:

It seems there is not much we can do, the SQL query is correct.
Unfortunately `JSON_EXTRACT()` returns integers for boolean values and
it's a [https://www.sqlite.org/json1.html#jex documented] behavior:

> ''The json_extract(X,P1,P2,...) extracts and returns one or more values
from the well-formed JSON at X. If only a single path P1 is provided, then
the SQL datatype of the result is NULL for a JSON null, INTEGER or REAL
for a JSON numeric value, **an INTEGER zero for a JSON false value, an
INTEGER one for a JSON true value**, ...''

There is no way to recognize that we should automatically cast a value to
boolean, you can use `Cast('json_field__is_true', models.BooleanField())`.

Changing to a documentation issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:4>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: nobody

Type: Bug | Status: new

Component: Documentation | Version: 3.1
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

OK, there is a way with using `JSON_TYPE()`, however it's quite
complicated (see the
[https://github.com/django/django/compare/master...felixxm:refs-32483?expand=1
draft changeset]) and
`model_fields.test_jsonfield.TestQuerying.test_isnull_key_or_none` doesn't
work with this change. I would prefer to document this caveat in Django <
4.0 and keep this ticket as a cleanup/optimization.

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:5>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: nobody

Type: Bug | Status: new

Component: Documentation | Version: 3.1
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Matthew Cornell):

Thank you for looking into this Rohith and Mariusz. For now I will avoid
the query feature and just pull the data out once Django has deserialized
the field.

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:6>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------

Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |

Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* owner: nobody => Mariusz Felisiak
* status: new => assigned
* component: Documentation => Database layer (models, ORM)

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:7>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"c6b07627fcb5d1c8d2082714ef5adb63bee6cf4c" c6b0762]:
{{{
#!CommitTicketReference repository=""
revision="c6b07627fcb5d1c8d2082714ef5adb63bee6cf4c"
Refs #32483 -- Doc'd caveat about using JSONField key transforms to
booleans with QuerySet.values()/values_list() on SQLite.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:8>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"49970b5e4d187f39f9fb35c334cd957e3a9e1d61" 49970b5]:
{{{
#!CommitTicketReference repository=""
revision="49970b5e4d187f39f9fb35c334cd957e3a9e1d61"
[3.2.x] Refs #32483 -- Doc'd caveat about using JSONField key transforms

to booleans with QuerySet.values()/values_list() on SQLite.

Backport of c6b07627fcb5d1c8d2082714ef5adb63bee6cf4c from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:9>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"5ab1b7bc26b18496b296dc9766fcbdfe7f7a585c" 5ab1b7bc]:
{{{
#!CommitTicketReference repository=""
revision="5ab1b7bc26b18496b296dc9766fcbdfe7f7a585c"
[3.1.x] Refs #32483 -- Doc'd caveat about using JSONField key transforms

to booleans with QuerySet.values()/values_list() on SQLite.

Backport of c6b07627fcb5d1c8d2082714ef5adb63bee6cf4c from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:10>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: jsonfield querying | Triage Stage: Accepted
sqlite |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/14077 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:11>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: jsonfield querying | Triage Stage: Ready for
sqlite | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:12>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak

Type: Bug | Status: closed

Component: Database layer | Version: 3.1
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: jsonfield querying | Triage Stage: Ready for
sqlite | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"71ec102b01fcc85acae3819426a4e02ef423b0fa" 71ec102b]:
{{{
#!CommitTicketReference repository=""
revision="71ec102b01fcc85acae3819426a4e02ef423b0fa"
Fixed #32483 -- Fixed QuerySet.values()/values_list() on JSONField key
transforms with booleans on SQLite.

Thanks Matthew Cornell for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:14>

[Django]

#32483: QuerySet.values()/values_list() with JSONField returns integers instead of
booleans on SQLite.
-------------------------------------+-------------------------------------
Reporter: Matthew Cornell | Owner: Mariusz
| Felisiak

Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: jsonfield querying | Triage Stage: Ready for
sqlite | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"c4df8b86c7fac52d95eda3440edc397fc13c3e56" c4df8b86]:
{{{
#!CommitTicketReference repository=""
revision="c4df8b86c7fac52d95eda3440edc397fc13c3e56"
Refs #32483 -- Added tests QuerySet.values()/values_list() on key
transforms with structures containing booleans.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32483#comment:13>