#27498: Filtering annotated field in SQLite returns wrong results
-------------------------------------+-------------------------------------
Reporter: Tim Düllmann | Owner: Peter
| Inglesby
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, | Triage Stage: Accepted
annotations, filter |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Peter Inglesby):
https://www.sqlite.org/datatype3.html is a useful reference to help
understand what's going on here. I can expand on anything in this comment
if that would be helpful.
The reason that the two queries produce different results is because the
original queryset has a single parameter, 0, which is handled differently
in the two cases.
In both cases, the parameter is converted to `Decimal('0')`. (This is
because the the lookup generated by `filter(qty_needed__gt=0)` knows that
its left hand side's `output_field` is a `DecimalField`.)
In the first case, the parameter is interpolated into the SQL in
`Query.__str__`, which gives us the `...HAVING (...) > 0`.
In the second case, the parameter is passed through to the `sqlite3`
library as an extra argument to `sqlite3.dbapi2.Cursor.execute`. Django
registers an adapter (`rev_typecast_decimal`) for `Decimal` parameters
which converts the parameter to a `str`, so the query that's evaluated in
the second case is equivalent to `...HAVING (...) > '0'`.
SQLite's rules for comparing values of different types includes: "An
INTEGER or REAL value is less than any TEXT or BLOB value". So in the
second case, the `HAVING` clause is always false, since the left hand side
of the `>` operation is NUMERIC (so either an INTEGER or a REAL) and the
right hand side is TEXT. However, in the first case, the right hand side
is an INTEGER, and so the `HAVING` behaves as expected.
So there are two bugs here:
== Bug one: the SQL produced by converting a queryset to a string is often
incorrect
This has been reported before and closed as wontfix. See ticket:1261
(Incorrect quoting in QuerySet.query.__str__()). I agree with wontfixing
this, but I do think it would be good to document the limitations here.
See ticket:27587.
It's worth noting that in this case, the SQL is incorrect in such a way to
produce a query that does the right thing!
== Bug two: on SQLite, the SQL produced by a queryset involving magnitude
comparison against a `DecimalField` is sometimes incorrect
I believe the correct fix here is that when we have a magnitude comparison
whose left hand side is a `DecimalField`, the right hand side should
always be `CAST AS NUMERIC`. This is because the right hand side will
have been converted to a string by the `rev_typecast_decimal` adapter.
Note that in some situations, this `CAST` will be unnecessary, and this
explains why we haven't seen this bug more often. For instance, if the
left hand side of the comparison is a column whose SQL type is `decimal`
then the column has `NUMERIC` affinity, and so SQLite will apply `NUMERIC`
affinity to the right hand side. If the left hand side is more
complicated than this then it will have no affinity, and so no affinity
will be applied to the right hand side. Rather than trying to keep track
of whether the left hand side of a comparison has affinity or not, it is
easier to always include the `CAST`, and I believe that doing so is
harmless.
I have a fix for this, and will create a PR shortly.
--
Ticket URL: <https://code.djangoproject.com/ticket/27498#comment:5>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/063.2013c17baca8186331f3fde3ae50a478%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
