[Django] #33517: Implied behavior of `__second` extractor inside of `F()` expressions does not match documented behavior for at least PostgreSQL
Django
not match documented behavior for at least PostgreSQL
-----------------------------------------+------------------------
Reporter: josefdlange | Owner: nobody
Type: Bug | Status: new
Component: Documentation | Version: 4.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
In the Django documentation
(https://docs.djangoproject.com/en/4.0/ref/models/querysets/#second) the
`__second` extractor is implied to be dealing with integer values for
second in a date time. In at least PostgreSQL, the values from
`EXTRACT`ing `second` from a date time will include "any fractional
seconds" (https://www.postgresql.org/docs/current/functions-datetime.html
#FUNCTIONS-DATETIME-EXTRACT) which means it could be more specific than an
integer.
Because of the implication in Django's documentation, I had expected this
filter to work as expected for a row whose `date_created` and
`date_modified` are within a second of each other but a couple of
milliseconds off:
`.filter(date_created__second=F("date_modified__second"))`
However, that ends up not being true given the Postgres behavior.
My recommendation is merely a documentation update to highlight the
discrepancy of behavior between a value coalesced to Python from the DB
data, and how the column values are perceived on the database side within
a query. I'm struggling for adequate language to explain in such a
context, though :-P happy to discuss further and work together toward this
improvement.
--
Ticket URL: <https://code.djangoproject.com/ticket/33517>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* component: Documentation => Database layer (models, ORM)
* stage: Unreviewed => Accepted
Comment:
Thanks for the report! We should have consistent behavior on all backends.
What do you think about adding `DATE_TRUNC()` in this case? For example:
{{{#!diff
diff --git a/django/db/backends/postgresql/operations.py
b/django/db/backends/postgresql/operations.py
index 399c1b24e7..fd90b95fb8 100644
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -36,6 +36,8 @@ class DatabaseOperations(BaseDatabaseOperations):
return "EXTRACT('isodow' FROM %s)" % field_name
elif lookup_type == 'iso_year':
return "EXTRACT('isoyear' FROM %s)" % field_name
+ elif lookup_type == 'second':
+ return f"EXTRACT('second' FROM DATE_TRUNC('second',
{field_name}))"
else:
return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:1>
Django
-------------------------------------+-------------------------------------
| Nabil Rady
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Mohamed Nabil Rady):
* status: new => assigned
* cc: Mohamed Nabil Rady (added)
* easy: 0 => 1
* owner: nobody => Mohamed Nabil Rady
* has_patch: 0 => 1
* stage: Accepted => Ready for checkin
Comment:
https://github.com/django/django/pull/15450
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Mohamed
| Nabil Rady
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* easy: 1 => 0
* stage: Ready for checkin => Accepted
Comment:
"Ready for checkin" is set by a patch reviewer (and there's no need to set
"Easy pickings").
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Mohamed
| Nabil Rady
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:4>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Mohamed
| Nabil Rady
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:5>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Mohamed
| Nabil Rady
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* keywords: => oracle postgresql
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:6>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Mohamed
| Nabil Rady
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"b7f263551c64e3f80544892e314ed5b0b22cc7c8" b7f26355]:
{{{
#!CommitTicketReference repository=""
revision="b7f263551c64e3f80544892e314ed5b0b22cc7c8"
Refs #33517 -- Prevented __second lookup from returning fractional seconds
on PostgreSQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:7>
Django
Oracle.
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* owner: Mohamed Nabil Rady => (none)
* status: assigned => new
* has_patch: 1 => 0
Comment:
This is still an issue on Oracle. We could use `EXTRACT(SECOND FROM
CAST({field_name} AS TIMESTAMP(0)))` but it won't work with
`DurationField`.
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:8>
Django
Oracle.
-------------------------------------+-------------------------------------
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Yash Singhal):
* owner: (none) => Yash Singhal
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:9>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Joey Lange):
Great suggestion, Mariusz! You've convinced me that consistent and clear
behavior is more important here! Let me know if I can be of assistance as
this moves forward -- sorry I missed the earlier notification(s).
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:10>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Yash Singhal):
hey joey Lange, I'm interested in solving this issue, can I solve it, or
its already been assigned to someone?
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:11>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Joey Lange):
Looks like we're already PR'd and merged, pal! Thanks for the initiative,
though!
https://github.com/django/django/pull/15450
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:12>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak):
This is still an issue on Oracle, see
[https://code.djangoproject.com/ticket/33517#comment:8 comment].
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:13>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Yash Singhal):
@joey Lange, "it is still an issue on oracle" as told by @Mariusz
Felisiak, so can I work on this?
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:14>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Joey Lange):
Truthfully I'm just the reporter of this issue, I have no official stake
in the maintenance of Django as a whole -- so, go ahead, though I'm not
necessarily the person (if there is one) to be giving permission.
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:15>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Carlton Gibson):
Hi Yash — you don't need permission. Please do take a look! Thanks
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:16>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Yash
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Yash Singhal):
Hey Mariusz Felisiak, help me, like on which file I can found this and
what the code is look like and do I need to create a test too?
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:17>
Django
Oracle.
-------------------------------------+-------------------------------------
| Mahmood
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Hisham Mahmood):
* owner: Yash Singhal => Hisham Mahmood
* has_patch: 0 => 1
Comment:
Pull request: https://github.com/django/django/pull/17769
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:18>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Hisham
| Mahmood
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"bbfbf0ab686030f8da5ea22a65c41b9574681262" bbfbf0a]:
{{{
#!CommitTicketReference repository=""
revision="bbfbf0ab686030f8da5ea22a65c41b9574681262"
Refs #33517 -- Prevented __second lookup from returning fractional seconds
on Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:19>
Django
Oracle.
-------------------------------------+-------------------------------------
Reporter: Joey Lange | Owner: Hisham
| Mahmood
Component: Database layer | Version: 4.0
(models, ORM) |
Keywords: oracle postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: assigned => closed
* resolution: => fixed
--
Ticket URL: <https://code.djangoproject.com/ticket/33517#comment:20>