[Django] #37014: SQLite emulation of db_default involving Extract/Trunc might unexpectedly apply overridden timezone for inserted values

[Django]

#37014: SQLite emulation of db_default involving Extract/Trunc might unexpectedly
apply overridden timezone for inserted values
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: tzinfo, TIME_ZONE, | Triage Stage:
Extract, Trunc | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
As developed in this [https://forum.djangoproject.com/t/extract-isnt-safe-
to-use-in-db-default-on-sqlite-if-time-zone-changes-at-some-point/44781
forum post], if
- `USE_TZ = True`
- a model field uses `Extract` or `Trunc` in a `db_default` expression
- a test case uses `override_settings()` to test timezone behavior

... then in scenarios where Django must emulate the database default for
SQLite in Python, the value will be transformed according to the
overridden timezone setting, but this is an improper emulation, since the
database's default cannot be overridden by merely flipping a Django
setting.

The scenarios where Django must emulate the db default for SQLite vary
depending on edges like:
- whether any field's value has been set
- whether the field with Extract/Trunc is last
- possibly: whether create() vs. bulk_create() is called
----
There are two ways to fix this as described on forum:
- introduce a heuristic (Simon: `if ddl_compiler_heuristic():`) to switch
on whether to use the project default timezone or the test's overridden
one
- implement `supports_default_keyword_in_insert` on SQLite by emulating it
in its insert compiler

Having only thought about it today, the second approach seems like it
would be simpler to implement and potentially pay broader dividends for
other use cases.

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

[Django]

#37014: SQLite emulation of db_default involving Extract/Trunc might unexpectedly
apply overridden timezone for inserted values
-------------------------------------+-------------------------------------

Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: tzinfo, TIME_ZONE, | Triage Stage: Accepted
Extract, Trunc |

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

Changes (by Simon Charette):

* stage: Unreviewed => Accepted

Comment:

I also think the second solution is the preferable one as there are likely
other expressions out there that rely on global state expected to be
captured at field definition time and might suffer from the same problem.
It also more aligned with how we've been doing things historically when
running into these kind of problems.
--
Ticket URL: <https://code.djangoproject.com/ticket/37014#comment:1>

[Django]

#37014: SQLite emulation of db_default involving Extract/Trunc might unexpectedly
apply overridden timezone for inserted values
-------------------------------------+-------------------------------------

Reporter: Jacob Walls | Owner: Praful
| Gulani
Type: Bug | Status: assigned

Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: tzinfo, TIME_ZONE, | Triage Stage: Accepted
Extract, Trunc |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Praful Gulani):

* owner: (none) => Praful Gulani
* status: new => assigned

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

[Django]

#37014: SQLite emulation of db_default involving Extract/Trunc might unexpectedly
apply overridden timezone for inserted values
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Praful
| Gulani
Type: Bug | Status: assigned
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: tzinfo, TIME_ZONE, | Triage Stage: Accepted
Extract, Trunc |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Praful Gulani):

* has_patch: 0 => 1

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

[Django]

#37014: SQLite emulation of db_default involving Extract/Trunc might unexpectedly
apply overridden timezone for inserted values
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Praful
| Gulani
Type: Bug | Status: assigned
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: tzinfo, TIME_ZONE, | Triage Stage: Accepted
Extract, Trunc |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

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