[Django] #33358: Adding duration field with default <24h raises an error in oracle

[Django]

#33358: Adding duration field with default <24h raises an error in oracle
-------------------------------------+-------------------------------------
Reporter: Marcello | Owner: nobody
Dalponte |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) | Keywords:
Severity: Normal | durationfield,oracle
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
== BUG DESCRIPTION
When adding a DurationField with a default which is smaller than 24h

{{{#!python
class Scheduler(models.Model):
delay = models.DurationField(default=timedelta(minutes=5))
}}}

I get this error with the oracle backend

{{{
ORA-01735: invalid ALTER TABLE option
}}}

----

== INVESTIGATION
I dug into the issue and I found that the reason why the query errors is
because the query generated by the migration app is
{{{#!sql
ALTER TABLE "EXAMPLE_SCHEDULER" ADD "DELAY" INTERVAL DAY(9) TO SECOND(6)
DEFAULT 0:05:00 NOT NULL;
}}}

this query has two issues:
1. the duration is not enclosed in quotes `0:05:00` -> `'0:05:00'`
2. oracle requires the day to be in the value `'0:05:00'` -> `'0 0:05:00'`

----

== SUGGESTED FIX
A possible fix would be to ensure we format timedelta into the right
format when generating migrations, this means extending
`DatabaseSchemaEditor.prepare_default` to do the formatting before quoting
the value, right now `prepare_default` simply proxies the business logic
to `quote_value` which simply casts the timedelta to string.

----

== ENVIRONMENT
python venv:
{{{
Django==3.1
cx_Oracle==8.3.0
}}}
oracle version:
`Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production`

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

[Django]

#33358: Adding duration field with default <24h raises an error in oracle
-------------------------------------+-------------------------------------

Reporter: Marcello Dalponte | Owner: Marcello
| Dalponte
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
durationfield,oracle | Unreviewed
Has patch: 0 | Needs documentation: 0

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

Changes (by Marcello Dalponte):

* owner: nobody => Marcello Dalponte
* status: new => assigned

Comment:

Here is the fix https://github.com/django/django/pull/15183

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

[Django]

#33358: Adding duration field with default <24h raises an error in oracle
-------------------------------------+-------------------------------------

Reporter: Marcello Dalponte | Owner: Marcello
| Dalponte
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
durationfield,oracle | Unreviewed

Has patch: 1 | Needs documentation: 0

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

Changes (by Marcello Dalponte):

* has_patch: 0 => 1

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

[Django]

#33358: Adding duration field with default <24h raises an error on Oracle.

-------------------------------------+-------------------------------------
Reporter: Marcello Dalponte | Owner: Marcello
| Dalponte
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
durationfield,oracle |
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0

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

Changes (by Mariusz Felisiak):

* needs_tests: 0 => 1
* stage: Unreviewed => Accepted

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

[Django]

#33358: Adding duration field with default <24h raises an error on Oracle.
-------------------------------------+-------------------------------------
Reporter: Marcello Dalponte | Owner: Marcello
| Dalponte
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
durationfield,oracle | checkin
Has patch: 1 | Needs documentation: 0

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

Changes (by Mariusz Felisiak):

* needs_tests: 1 => 0
* stage: Accepted => Ready for checkin

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

[Django]

#33358: Adding duration field with default <24h raises an error on Oracle.
-------------------------------------+-------------------------------------
Reporter: Marcello Dalponte | Owner: Marcello
| Dalponte

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
durationfield,oracle | 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:"3b73f77ad40a06230a47771df02429f1ba45a2f4" 3b73f77a]:
{{{
#!CommitTicketReference repository=""
revision="3b73f77ad40a06230a47771df02429f1ba45a2f4"
Fixed #33358 -- Fixed handling timedelta < 1 day in schema operations on
Oracle.
}}}

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