[Django] #35111: DateField's __range and __in lookups against expressions produce invalid values on SQLite.

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: nobody
Charette |
Type: Bug | Status: new
Component: Database | Version: 5.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
#22288 (4f138fe5a496a81115c4fba6615a517fc62c3b17) only partially addressed
the issue of iterable right-hand-side lookups (`__range` and `__in`) by
adapting Oracle's and SQLite operation modules to special case the
adaptation of datetime and time value while missing that date values are
still converted to string.

This causes queries of the form

{{{#!python
Person.objects.filter(
dob__between=(F("dob"), F("dob"))
)
}}}

To be turned into

{{{#!sql
SELECT * FROM person WHERE dob BETWEEN "Col('dob')" AND "Col('dob')"
}}}

I believe the fundamental flaw in 4f138fe5a496a81115c4fba6615a517fc62c3b17
is that expressions (`hasattr(obj, "resolve_expression")`) should never be
passed to `Field.get_db_prep_value` in the first place during the
`Lookup`'s right-hand-side ''processing'' phase just like they are not
passed to `Field.get_prep_value`
[https://github.com/django/django/blob/92d6cff6a2fee7a3f9244081b84fd82c50cc71aa/django/db/models/lookups.py#L287-L295
during the preparation phase].

Initially discovered on [https://forum.djangoproject.com/t/range-lookup-
with-complex-expressions/26795/7 the forum] by Joel Feiner.

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------

Reporter: Simon Charette | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0

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

Changes (by Simon Charette):

* has_patch: 0 => 1

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------

Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned

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

Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* owner: nobody => Simon Charette
* status: new => assigned
* stage: Unreviewed => Accepted

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| 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:"0fcee1676c7f14bb08e2cc662898dee56d9cf207" 0fcee16]:
{{{
#!CommitTicketReference repository=""
revision="0fcee1676c7f14bb08e2cc662898dee56d9cf207"
Fixed #35111 -- Fixed compilation of DateField __in/__range rhs on SQLite
and MySQL.

Also removed tests that ensured that adapt_(date)timefield backend
operations where able to deal with expressions when it's not the case
for any other adapt methods.
}}}

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Claude Paroz):

Does this has any effect on #25591?

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

From a quick look I don't think it does Claude as the logic changed here
only relates to lookups. In other words, `RangeField` and `RangeLookup`
are completely different beasts.

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

[Django]

#35111: DateField's __range and __in lookups against expressions produce invalid
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Claude Paroz):

OK, thanks Simon and sorry for the noise.

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