[Django] #28643: Complete the ORM Function Library

Django

unread,

Sep 27, 2017, 5:08:55 PM9/27/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew | Owner: nobody
Pava |
Type: New | Status: new
feature |
Component: Database | Version: master
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 |
-------------------------------------+-------------------------------------
I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist. There are probably several more that I
have missed. These functions are available directly out of the box with
PostgreSQL.

String functions not yet implemented:
ASCII(): Returns numeric value of left-most character
CHR(): Character with the given code.
LEFT(): Returns the leftmost number of characters as specified
LPAD(): Returns the string argument, left-padded with the specified string
LTRIM(): Removes leading spaces
MD5(): Calculates the MD5 hash of string, returning the result in
hexadecimal
REPEAT(): Repeats a string the specified number of times
REPLACE(): Replaces occurrences of a specified string
REVERSE(): Reverse the characters in a string
RIGHT(): Returns the specified rightmost number of characters
RPAD(): Appends string the specified number of times
RTRIM(): Removes trailing spaces
TRIM(): Removes leading and trailing spaces

Numeric functions:
ABS(): Returns the absolute value.
ACOS(): Returns the arccosine.
ASIN(): Returns the arcsine.
ATAN(): Returns the arctangent.
ATAN2(): Returns the arctangent of the two variables passed to it.
CEILING(): Returns the smallest integer value that is not less than a
numeric expression
COS(): Returns the cosine expressed in radians.
COT(): Returns the cotangent.
DEGREES(): Returns a numeric expression converted from radians to degrees.
EXP(): Returns the base of the natural logarithm (e) raised to the power
of a numeric expression.
FLOOR(): Returns the largest integer value that is not greater than a
numeric expression.
LOG(): Returns the natural logarithm of a numeric expression.
MOD(): Returns the remainder of one expression by diving by another
expression.
PI(): Returns the value of pi
POWER(): Returns the value of one expression raised to the power of
another expression
RADIANS(): Returns the value of an expression converted from degrees to
radians.
ROUND(): Returns a numeric expression rounded to an integer. Can be used
to round an expression to a number of decimal points
SIN(): Returns the sine given in radians.
SQRT(): Returns the square root.
TAN(): Returns the tangent expressed in radians.

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

Django

unread,

Sep 27, 2017, 6:15:04 PM9/27/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

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

Comment (by Josh Smeaton):

I had hoped that the 3rd party community would take care of providing
"Function Packs" for each of the backends. But functions are fairly easy
to create in your own project if you need them so I guess few people have
bothered to group them into a distributable package.

I would prefer that the django ecosystem had a standard group of function
expressions that can be used across backends, rather than a bunch of
similar but not quite the same implementations. Then 3rd party libraries
can depend on the known good versions and everyone is better off. Since
the community hasn't seemed to provide such a library (that I'm aware of),
I think it's fine for Django to do so.

Splitting the module up into string/numeric/date types sounds fine. Let's
take inspiration from postgres and other db vendor docs so navigating them
is familiar. Let's begin with functions that have standard support across
our 4 backends, then look into what's left over. If we can approximate
support by combining other functions we can do that. Otherwise if a
particular function only has support for one or two backends we can
consider ignoring it or implementing in a contrib module.

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

Django

unread,

Sep 27, 2017, 6:15:21 PM9/27/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

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 Josh Smeaton):

* cc: josh.smeaton@… (added)
* stage: Unreviewed => Accepted

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

Django

unread,

Sep 28, 2017, 10:26:04 AM9/28/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Changes (by felixxm):

* cc: felixxm (added)

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

Django

unread,

Oct 1, 2017, 3:51:01 PM10/1/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned

Component: Database layer | Version: master
(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 JunyiJ):

* owner: nobody => JunyiJ
* status: new => assigned

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

Django

unread,

Oct 1, 2017, 8:07:59 PM10/1/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Josh Smeaton):

For whoever wants to begin here, I think a good plan of attack will be a
PR per function or per a small set of functions. Then the commit message
would be something like "Refs #28643 -- Added X, Y, Z functions".

That way we can get through smaller pieces at a time, without such a large
burden being placed on any one individual. We could also get some newer
contributors to help in this way. Each function on its own should be a
relatively easy thing to implement. A higher level task of re-organising
the layout into functions/string functions/number may be helpful to get
the ticket rolling.

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

Django

unread,

Oct 4, 2017, 4:05:20 PM10/4/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Matthew Pava):

I did find this third-party utility that handles specific PostgreSQL
functions. I wonder if there are other backends that they could be ported
to.
https://github.com/hypertrack/django-pg-utils

And I also wonder how all of this connects with specific PostgreSQL
aggregate functions already builtin to Django:
https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/aggregates/

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

Django

unread,

Oct 6, 2017, 4:14:41 AM10/6/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Mads Jensen):

Replying to [comment:6 Matthew Pava]:

> I did find this third-party utility that handles specific PostgreSQL
functions. I wonder if there are other backends that they could be ported
to.
> https://github.com/hypertrack/django-pg-utils

From a quick look, these things are already supported by Django.

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

Django

unread,

Oct 9, 2017, 4:39:24 PM10/9/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Description changed by felixxm:

Old description:

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist. There are probably several more that I
have missed. These functions are available directly out of the box with
PostgreSQL.

String functions not yet implemented:
ASCII(): Returns numeric value of left-most character
CHR(): Character with the given code.
LEFT(): Returns the leftmost number of characters as specified
LPAD(): Returns the string argument, left-padded with the specified string

MD5(): Calculates the MD5 hash of string, returning the result in
hexadecimal
REPEAT(): Repeats a string the specified number of times
REPLACE(): Replaces occurrences of a specified string
REVERSE(): Reverse the characters in a string
RIGHT(): Returns the specified rightmost number of characters
RPAD(): Appends string the specified number of times

LTRIM(): Removes leading spaces
([https://github.com/django/django/pull/9220 PR #9220])
RTRIM(): Removes trailing spaces
([https://github.com/django/django/pull/9220 PR #9220])

TRIM(): Removes leading and trailing spaces

([https://github.com/django/django/pull/9220 PR #9220])

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:8>

Django

unread,

Oct 11, 2017, 1:29:30 PM10/11/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by felixxm):

I prepared patch to reorganize database functions docs and code
([https://github.com/django/django/pull/9227 PR]). I think we should
organize code and doc as follows (bolded functions doesn't exist):

- `docs/ref/models/database-functions.txt` main section
(`django/db/models/functions/misc.py`):
- ''**Ascii**, Cast, **Chr**, Coalesce, Greatest, Least, Now'';
- `docs/ref/models/database-functions.txt` section `Date Functions`
(`django/db/models/functions/datetime.py`):
- ''Extract, ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,
ExtractQuarter, ExtractSecond, ExtractWeek, ExtractWeekDay, ExtractYear,
Trunc, TruncDate, TruncDay, TruncHour, TruncMinute, TruncMonth,
TruncQuarter, TruncSecond, TruncTime, TruncYear;';
- `docs/ref/models/database-functions.txt` section `Math Functions`
(`django/db/models/functions/math.py`):
- ''**Abs**, **Acos**, **Asin**, **Atan**, **Atan2**, **Ceil**, **Cos**,
**Cot**, **Exp**, **Floor**, **Log**, **Mod**, **Sin**, **Sqrt**,
**Tan**'';
- `docs/ref/models/database-functions.txt` section `Text Functions`
(`django/db/models/functions/text.py`):
- ''Concat, ConcatPair, Length, **Lpad**, Lower, **Ltrim**, **Power**,
**Replace**, **Round**, **Rpad**, **Rtrim**, StrIndex, Substr, **Trim**,
Upper'';
- `docs/ref/models/database-functions.txt` section `Window Functions`
(`django/db/models/functions/window.py`):
- ''CumeDist, DenseRank, FirstValue, Lag, LastValue, Lead, NthValue,
Ntile, PercentRank, Rank, RowNumber''.

Headers and functions in docs and code should be organized alphabetically.
Thanks Tim Graham for suggestions.

I'm not convince that following functions should be implemented because
there not supported on all databases:
- LEFT(), MD5(), REPEAT(), REVERSE(), RIGHT(), DEGREES(), PI(), RADIANS().

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:9>

Django

unread,

Oct 12, 2017, 5:24:10 PM10/12/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Tim Graham):

The "Miscellaneous" group is sort of bothering me.

`Cast`, `Coalesce`, `Greatest`, `Least` sound like candidates for a
section called something like "Comparison and conversion functions"

`Ascii` and `Chr` sound like they could be "text" related.

`Now` sounds like a candidate for "Date Functions".

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:10>

Django

unread,

Oct 13, 2017, 9:01:06 AM10/13/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by felixxm):

Agreed. I updated above comment and both PRs.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:11>

Django

unread,

Oct 13, 2017, 10:13:09 AM10/13/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Matthew Pava):

In regards to Left() and Right(), they are available in PostgreSQL and
MySQL, but not in SQLite or Oracle. However, you can emulate their
functionality using the corresponding Substr function in the database
backend. I would hate to see us leave those out for that reason alone.
At the same time, developers could use the Substr across all databases
instead of using Left and Right.

I was also wondering about the use of Trim. Python doesn't use that term;
instead, it uses strip. I wonder if that really matters. Some developers
may look for a strip functionality in Django ORM not realizing that the
databases use the term Trim. Perhaps that could be clarified in the
documentation.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:12>

Django

unread,

Oct 13, 2017, 12:11:40 PM10/13/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Changes (by Shai Berger):

* cc: Shai Berger (added)

Comment:

Replying to [comment:12 Matthew Pava]:

>
> I was also wondering about the use of Trim. Python doesn't use that
term; instead, it uses strip. I wonder if that really matters. Some
developers may look for a strip functionality in Django ORM not realizing
that the databases use the term Trim. Perhaps that could be clarified in
the documentation.

Unless there is some other use for the term `strip` in the databases, I'd
consider "documenting" this by giving an alias:
{{{
class Trim(Transform):
# ...

Strip = Trim # Give Trim a more Pythonic name
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:13>

Django

unread,

Oct 13, 2017, 12:20:43 PM10/13/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by GitHub <noreply@…>):

In [changeset:"ad8036d715d4447b95d485332511b4edb1a40c0e" ad8036d7]:
{{{
#!CommitTicketReference repository=""
revision="ad8036d715d4447b95d485332511b4edb1a40c0e"
Refs #28643 -- Reorganized database functions docs.

Thanks Tim Graham for the review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:14>

Django

unread,

Oct 13, 2017, 12:40:19 PM10/13/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"8b42a18b2d7bbd9e94ee80458a1cfcbb544e588c" 8b42a18b]:
{{{
#!CommitTicketReference repository=""
revision="8b42a18b2d7bbd9e94ee80458a1cfcbb544e588c"
[2.0.x] Refs #28643 -- Reorganized database functions docs.

Thanks Tim Graham for the review.

Backport of ad8036d715d4447b95d485332511b4edb1a40c0e from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:15>

Django

unread,

Oct 13, 2017, 3:23:32 PM10/13/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by GitHub <noreply@…>):

In [changeset:"4f27e475b30d0cf91be24f3116a54b17789ac403" 4f27e475]:
{{{
#!CommitTicketReference repository=""
revision="4f27e475b30d0cf91be24f3116a54b17789ac403"
Refs #28643 -- Reorganized database functions.

Thanks Tim Graham for the review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:16>

Django

unread,

Oct 14, 2017, 1:43:11 PM10/14/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by felixxm):

[https://github.com/django/django/pull/9220 PR - Ltrim, Rtrim, Strip, and
Trim.]

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:17>

Django

unread,

Oct 16, 2017, 11:17:25 AM10/16/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Matthew Pava):

And, of course, there is a Python ''lstrip'' and ''rstrip'' as well.

{{{
Lstrip = Ltrim # Give Ltrim a more Pythonic name
Rstrip = Rtrim # Give Rtrim a more Pythonic name
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:18>

Django

unread,

Oct 19, 2017, 3:49:33 PM10/19/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by felixxm):

Replying to [comment:18 Matthew Pava]:

> And, of course, there is a Python ''lstrip'' and ''rstrip'' as well.
>
> {{{
> Lstrip = Ltrim # Give Ltrim a more Pythonic name
> Rstrip = Rtrim # Give Rtrim a more Pythonic name
> }}}

I added these aliases.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:19>

Django

unread,

Nov 14, 2017, 3:32:18 PM11/14/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

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

Changes (by felixxm):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:20>

Django

unread,

Nov 30, 2017, 2:58:36 PM11/30/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

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

Changes (by Adam (Chainz) Johnson):

* cc: Adam (Chainz) Johnson (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:21>

Django

unread,

Dec 18, 2017, 4:56:09 PM12/18/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

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

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist. There are probably several more that I
> have missed. These functions are available directly out of the box with
> PostgreSQL.
>
> String functions not yet implemented:
> ASCII(): Returns numeric value of left-most character
> CHR(): Character with the given code.
> LEFT(): Returns the leftmost number of characters as specified
> LPAD(): Returns the string argument, left-padded with the specified
> string

> MD5(): Calculates the MD5 hash of string, returning the result in
> hexadecimal
> REPEAT(): Repeats a string the specified number of times
> REPLACE(): Replaces occurrences of a specified string
> REVERSE(): Reverse the characters in a string
> RIGHT(): Returns the specified rightmost number of characters
> RPAD(): Appends string the specified number of times

> LTRIM(): Removes leading spaces
> ([https://github.com/django/django/pull/9220 PR #9220])
> RTRIM(): Removes trailing spaces
> ([https://github.com/django/django/pull/9220 PR #9220])

> TRIM(): Removes leading and trailing spaces

> ([https://github.com/django/django/pull/9220 PR #9220])
>

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||= String =||
||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||
||ASCII()||Returns numeric value of left-most character||X||X||X||as
UNICODE()||-||
||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

||LEFT()||Returns the leftmost number of characters as

specified||X||-||X||-||-||

||LPAD()||Returns the string argument, left-padded with the specified

string||X||X||X||-||-||

||MD5()||Calculates the MD5 hash of string, returning the result in

hexadecimal||X||-||-||-||-||

||REPEAT()||Repeats a string the specified number of

times||X||-||X||-||-||
||REPLACE()||Replaces occurrences of a specified string||X||X||X||X||-||
||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

||RIGHT()||Returns the specified rightmost number of

characters||X||-||X||-||-||
||RPAD()||Appends string the specified number of times||X||X||X||-||-||
||LTRIM()||Removes leading
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
||RTRIM()||Removes trailing
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||TRIM()||Removes leading and trailing

spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||= Numeric =||
||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||
||ABS()||Returns the absolute value.||X||X||X||X||-||
||ACOS()||Returns the arccosine.||X||X||X||-||-||
||ASIN()||Returns the arcsine.||X||X||X||-||-||
||ATAN()||Returns the arctangent.||X||X||X||-||-||

||ATAN2()||Returns the arctangent of the two variables passed to

it.||X||X||X||-||-||

||CEILING()||Returns the smallest integer value that is not less than a

numeric expression||X||as CEIL()||X||-||-||
||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
||COT()||Returns the cotangent.||X||X||X||-||-||

||DEGREES()||Returns a numeric expression converted from radians to

degrees.||X||X||X||-||-||

||EXP()||Returns the base of the natural logarithm (e) raised to the power

of a numeric expression.||X||X||X||-||-||

||FLOOR()||Returns the largest integer value that is not greater than a

numeric expression.||X||X||X||-||-||

||LOG()||Returns the natural logarithm of a numeric

expression.||X||X||X||-||-||

||MOD()||Returns the remainder of one expression by diving by another

expression.||X||X||X||-||-||
||PI()||Returns the value of pi||X||-||X||-||-||

||POWER()||Returns the value of one expression raised to the power of

another expression||X||X||X||-||-||

||RADIANS()||Returns the value of an expression converted from degrees to

radians.||X||X||X||-||-||

||ROUND()||Returns a numeric expression rounded to an integer. Can be used

to round an expression to a number of decimal points||X||X||X||X||-||
||SIN()||Returns the sine given in radians.||X||X||X||-||-||
||SQRT()||Returns the square root.||X||X||X||-||-||
||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||

--

Comment (by Matthew Pava):

I formatted the list into a table so we could see better what we can do to
address this ticket. It looks like SQLite is missing much functionality
that the other backends support, especially in the math department.

I also suggest implementing Left and Right, but for the unsupported
backends to use the corresponding Substr function.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:22>

Django

unread,

Dec 21, 2017, 4:21:22 AM12/21/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

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

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>

> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||= String =||
> ||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||
> ||ASCII()||Returns numeric value of left-most character||X||X||X||as
> UNICODE()||-||
> ||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

> ||LEFT()||Returns the leftmost number of characters as

> specified||X||-||X||-||-||

> ||LPAD()||Returns the string argument, left-padded with the specified

> string||X||X||X||-||-||

> ||MD5()||Calculates the MD5 hash of string, returning the result in

> hexadecimal||X||-||-||-||-||

> ||REPEAT()||Repeats a string the specified number of

> times||X||-||X||-||-||
> ||REPLACE()||Replaces occurrences of a specified string||X||X||X||X||-||

> ||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

> ||RIGHT()||Returns the specified rightmost number of

> characters||X||-||X||-||-||
> ||RPAD()||Appends string the specified number of times||X||X||X||-||-||
> ||LTRIM()||Removes leading
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
> ||RTRIM()||Removes trailing
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

> ||TRIM()||Removes leading and trailing

> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
>
> ||= Numeric =||
> ||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||
> ||ABS()||Returns the absolute value.||X||X||X||X||-||
> ||ACOS()||Returns the arccosine.||X||X||X||-||-||
> ||ASIN()||Returns the arcsine.||X||X||X||-||-||
> ||ATAN()||Returns the arctangent.||X||X||X||-||-||

> ||ATAN2()||Returns the arctangent of the two variables passed to

> it.||X||X||X||-||-||

> ||CEILING()||Returns the smallest integer value that is not less than a

> numeric expression||X||as CEIL()||X||-||-||
> ||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
> ||COT()||Returns the cotangent.||X||X||X||-||-||

> ||DEGREES()||Returns a numeric expression converted from radians to

> degrees.||X||X||X||-||-||

> ||EXP()||Returns the base of the natural logarithm (e) raised to the

> power of a numeric expression.||X||X||X||-||-||

> ||FLOOR()||Returns the largest integer value that is not greater than a

> numeric expression.||X||X||X||-||-||

> ||LOG()||Returns the natural logarithm of a numeric

> expression.||X||X||X||-||-||

> ||MOD()||Returns the remainder of one expression by diving by another

> expression.||X||X||X||-||-||
> ||PI()||Returns the value of pi||X||-||X||-||-||

> ||POWER()||Returns the value of one expression raised to the power of

> another expression||X||X||X||-||-||

> ||RADIANS()||Returns the value of an expression converted from degrees to

> radians.||X||X||X||-||-||

> ||ROUND()||Returns a numeric expression rounded to an integer. Can be
> used to round an expression to a number of decimal

> points||X||X||X||X||-||
> ||SIN()||Returns the sine given in radians.||X||X||X||-||-||
> ||SQRT()||Returns the square root.||X||X||X||-||-||
> ||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||= **Comparison** =||

||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||

||NULLIF()||Returns NULL if the first argument equals the
second.||X||X||X||X||-||
\\
||||||||||||||= **Text** =||

||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||
||ASCII()||Returns numeric value of left-most character||X||X||X||as
UNICODE()||-||
||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

||LEFT()||Returns the leftmost number of characters as

specified||X||-||X||-||-||

||LPAD()||Returns the string argument, left-padded with the specified

string||X||X||X||-||-||

||MD5()||Calculates the MD5 hash of string, returning the result in

hexadecimal||X||-||-||-||-||

||REPEAT()||Repeats a string the specified number of

times||X||-||X||-||-||
||REPLACE()||Replaces occurrences of a specified string||X||X||X||X||-||

||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

||RIGHT()||Returns the specified rightmost number of

characters||X||-||X||-||-||
||RPAD()||Appends string the specified number of times||X||X||X||-||-||
||LTRIM()||Removes leading
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
||RTRIM()||Removes trailing
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||TRIM()||Removes leading and trailing

spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
\\

||||||||||||||= **Math** =||

||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||

||ABS()||Returns the absolute value.||X||X||X||X||-||
||ACOS()||Returns the arccosine.||X||X||X||-||-||
||ASIN()||Returns the arcsine.||X||X||X||-||-||
||ATAN()||Returns the arctangent.||X||X||X||-||-||

||ATAN2()||Returns the arctangent of the two variables passed to

it.||X||X||X||-||-||

||CEILING()||Returns the smallest integer value that is not less than a

numeric expression||X||as CEIL()||X||-||-||
||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
||COT()||Returns the cotangent.||X||X||X||-||-||

||DEGREES()||Returns a numeric expression converted from radians to

degrees.||X||X||X||-||-||

||EXP()||Returns the base of the natural logarithm (e) raised to the power

of a numeric expression.||X||X||X||-||-||

||FLOOR()||Returns the largest integer value that is not greater than a

numeric expression.||X||X||X||-||-||

||LOG()||Returns the natural logarithm of a numeric

expression.||X||X||X||-||-||

||MOD()||Returns the remainder of one expression by diving by another

expression.||X||X||X||-||-||
||PI()||Returns the value of pi||X||-||X||-||-||

||POWER()||Returns the value of one expression raised to the power of

another expression||X||X||X||-||-||

||RADIANS()||Returns the value of an expression converted from degrees to

radians.||X||X||X||-||-||

||ROUND()||Returns a numeric expression rounded to an integer. Can be used

to round an expression to a number of decimal points||X||X||X||X||-||
||SIN()||Returns the sine given in radians.||X||X||X||-||-||
||SQRT()||Returns the square root.||X||X||X||-||-||
||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||

--

Comment (by Nick Pope):

Updated description and comment:9 to include {{{NullIf}}}.

See the following links:
- http://modern-sql.com/concept/null#nullif
- http://modern-sql.com/feature/case#nullif
- http://modern-sql.com/feature/case#compatibility

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:23>

Django

unread,

Dec 21, 2017, 4:31:43 AM12/21/17

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

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

Description changed by Nick Pope:

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>

> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||= **Comparison** =||
> ||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||
> ||NULLIF()||Returns NULL if the first argument equals the
> second.||X||X||X||X||-||
> \\
> ||||||||||||||= **Text** =||
> ||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||

> ||ASCII()||Returns numeric value of left-most character||X||X||X||as
> UNICODE()||-||
> ||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

> ||LEFT()||Returns the leftmost number of characters as

> specified||X||-||X||-||-||

> ||LPAD()||Returns the string argument, left-padded with the specified

> string||X||X||X||-||-||

> ||MD5()||Calculates the MD5 hash of string, returning the result in

> hexadecimal||X||-||-||-||-||

> ||REPEAT()||Repeats a string the specified number of

> times||X||-||X||-||-||
> ||REPLACE()||Replaces occurrences of a specified string||X||X||X||X||-||

> ||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

> ||RIGHT()||Returns the specified rightmost number of

> characters||X||-||X||-||-||
> ||RPAD()||Appends string the specified number of times||X||X||X||-||-||
> ||LTRIM()||Removes leading
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
> ||RTRIM()||Removes trailing
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

> ||TRIM()||Removes leading and trailing

> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
> \\
> ||||||||||||||= **Math** =||
> ||Name||Description||PostgreSQL||Oracle||MySQL||SQLite||PR||

> ||ABS()||Returns the absolute value.||X||X||X||X||-||
> ||ACOS()||Returns the arccosine.||X||X||X||-||-||
> ||ASIN()||Returns the arcsine.||X||X||X||-||-||
> ||ATAN()||Returns the arctangent.||X||X||X||-||-||

> ||ATAN2()||Returns the arctangent of the two variables passed to

> it.||X||X||X||-||-||

> ||CEILING()||Returns the smallest integer value that is not less than a

> numeric expression||X||as CEIL()||X||-||-||
> ||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
> ||COT()||Returns the cotangent.||X||X||X||-||-||

> ||DEGREES()||Returns a numeric expression converted from radians to

> degrees.||X||X||X||-||-||

> ||EXP()||Returns the base of the natural logarithm (e) raised to the

> power of a numeric expression.||X||X||X||-||-||

> ||FLOOR()||Returns the largest integer value that is not greater than a

> numeric expression.||X||X||X||-||-||

> ||LOG()||Returns the natural logarithm of a numeric

> expression.||X||X||X||-||-||

> ||MOD()||Returns the remainder of one expression by diving by another

> expression.||X||X||X||-||-||
> ||PI()||Returns the value of pi||X||-||X||-||-||

> ||POWER()||Returns the value of one expression raised to the power of

> another expression||X||X||X||-||-||

> ||RADIANS()||Returns the value of an expression converted from degrees to

> radians.||X||X||X||-||-||

> ||ROUND()||Returns a numeric expression rounded to an integer. Can be
> used to round an expression to a number of decimal

> points||X||X||X||X||-||
> ||SIN()||Returns the sine given in radians.||X||X||X||-||-||
> ||SQRT()||Returns the square root.||X||X||X||-||-||
> ||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||

New description:

I was surprised to learn that we didn't have a StrIndex function until

version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||= **Comparison** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||NULLIF()||Returns NULL if the first argument equals the
second.||X||X||X||X||-||

||||||||||||||= **Math** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||ABS()||Returns the absolute value.||X||X||X||X||-||
||ACOS()||Returns the arccosine.||X||X||X||-||-||
||ASIN()||Returns the arcsine.||X||X||X||-||-||
||ATAN()||Returns the arctangent.||X||X||X||-||-||

||ATAN2()||Returns the arctangent of the two variables passed to

it.||X||X||X||-||-||

||CEILING()||Returns the smallest integer value that is not less than a

numeric expression||X||as CEIL()||X||-||-||
||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
||COT()||Returns the cotangent.||X||X||X||-||-||

||DEGREES()||Returns a numeric expression converted from radians to

degrees.||X||X||X||-||-||

||EXP()||Returns the base of the natural logarithm (e) raised to the power

of a numeric expression.||X||X||X||-||-||

||FLOOR()||Returns the largest integer value that is not greater than a

numeric expression.||X||X||X||-||-||

||LOG()||Returns the natural logarithm of a numeric

expression.||X||X||X||-||-||

||MOD()||Returns the remainder of one expression by diving by another

expression.||X||X||X||-||-||
||PI()||Returns the value of pi||X||-||X||-||-||

||POWER()||Returns the value of one expression raised to the power of

another expression||X||X||X||-||-||

||RADIANS()||Returns the value of an expression converted from degrees to

radians.||X||X||X||-||-||

||ROUND()||Returns a numeric expression rounded to an integer.\\Can be

used to round an expression to a number of decimal points||X||X||X||X||-||
||SIN()||Returns the sine given in radians.||X||X||X||-||-||
||SQRT()||Returns the square root.||X||X||X||-||-||
||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||
||||||||||||||= **Text** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||ASCII()||Returns numeric value of left-most character||X||X||X||as
UNICODE()||-||
||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

||LEFT()||Returns the leftmost number of characters as

specified||X||-||X||-||-||

||LPAD()||Returns the string argument, left-padded with the specified

string||X||X||X||-||-||

||LTRIM()||Removes leading
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||MD5()||Calculates the MD5 hash of string, returning the result in

hexadecimal||X||-||-||-||-||

||REPEAT()||Repeats a string the specified number of

times||X||-||X||-||-||
||REPLACE()||Replaces occurrences of a specified string||X||X||X||X||-||

||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

||RIGHT()||Returns the specified rightmost number of

characters||X||-||X||-||-||
||RPAD()||Appends string the specified number of times||X||X||X||-||-||

||RTRIM()||Removes trailing
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||TRIM()||Removes leading and trailing

spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:24>

Django

unread,

Jan 4, 2018, 3:17:23 PM1/4/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

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

Description changed by Mads Jensen:

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>

> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||= **Comparison** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||NULLIF()||Returns NULL if the first argument equals the
> second.||X||X||X||X||-||
> ||||||||||||||= **Math** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

> ||ABS()||Returns the absolute value.||X||X||X||X||-||
> ||ACOS()||Returns the arccosine.||X||X||X||-||-||
> ||ASIN()||Returns the arcsine.||X||X||X||-||-||
> ||ATAN()||Returns the arctangent.||X||X||X||-||-||

> ||ATAN2()||Returns the arctangent of the two variables passed to

> it.||X||X||X||-||-||

> ||CEILING()||Returns the smallest integer value that is not less than a

> numeric expression||X||as CEIL()||X||-||-||
> ||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
> ||COT()||Returns the cotangent.||X||X||X||-||-||

> ||DEGREES()||Returns a numeric expression converted from radians to

> degrees.||X||X||X||-||-||

> ||EXP()||Returns the base of the natural logarithm (e) raised to the

> power of a numeric expression.||X||X||X||-||-||

> ||FLOOR()||Returns the largest integer value that is not greater than a

> numeric expression.||X||X||X||-||-||

> ||LOG()||Returns the natural logarithm of a numeric

> expression.||X||X||X||-||-||

> ||MOD()||Returns the remainder of one expression by diving by another

> expression.||X||X||X||-||-||
> ||PI()||Returns the value of pi||X||-||X||-||-||

> ||POWER()||Returns the value of one expression raised to the power of

> another expression||X||X||X||-||-||

> ||RADIANS()||Returns the value of an expression converted from degrees to

> radians.||X||X||X||-||-||

> ||ROUND()||Returns a numeric expression rounded to an integer.\\Can be
> used to round an expression to a number of decimal

> points||X||X||X||X||-||
> ||SIN()||Returns the sine given in radians.||X||X||X||-||-||
> ||SQRT()||Returns the square root.||X||X||X||-||-||
> ||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||
> ||||||||||||||= **Text** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

> ||ASCII()||Returns numeric value of left-most character||X||X||X||as
> UNICODE()||-||
> ||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

> ||LEFT()||Returns the leftmost number of characters as

> specified||X||-||X||-||-||

> ||LPAD()||Returns the string argument, left-padded with the specified

> string||X||X||X||-||-||

> ||LTRIM()||Removes leading
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

> ||MD5()||Calculates the MD5 hash of string, returning the result in

> hexadecimal||X||-||-||-||-||

> ||REPEAT()||Repeats a string the specified number of

> times||X||-||X||-||-||
> ||REPLACE()||Replaces occurrences of a specified string||X||X||X||X||-||
> ||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

> ||RIGHT()||Returns the specified rightmost number of

> characters||X||-||X||-||-||
> ||RPAD()||Appends string the specified number of times||X||X||X||-||-||
> ||RTRIM()||Removes trailing
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

> ||TRIM()||Removes leading and trailing

> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||= **Comparison** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||NULLIF()||Returns NULL if the first argument equals the
second.||X||X||X||X||-||
||||||||||||||= **Math** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||ABS()||Returns the absolute value.||X||X||X||X||-||
||ACOS()||Returns the arccosine.||X||X||X||-||-||
||ASIN()||Returns the arcsine.||X||X||X||-||-||
||ATAN()||Returns the arctangent.||X||X||X||-||-||

||ATAN2()||Returns the arctangent of the two variables passed to

it.||X||X||X||-||-||

||CEILING()||Returns the smallest integer value that is not less than a

numeric expression||X||as CEIL()||X||-||-||
||COS()||Returns the cosine expressed in radians.||X||X||X||-||-||
||COT()||Returns the cotangent.||X||X||X||-||-||

||DEGREES()||Returns a numeric expression converted from radians to

degrees.||X||X||X||-||-||

||EXP()||Returns the base of the natural logarithm (e) raised to the power

of a numeric expression.||X||X||X||-||-||

||FLOOR()||Returns the largest integer value that is not greater than a

numeric expression.||X||X||X||-||-||

||LOG()||Returns the natural logarithm of a numeric

expression.||X||X||X||-||-||

||MOD()||Returns the remainder of one expression by diving by another

expression.||X||X||X||-||-||
||PI()||Returns the value of pi||X||-||X||-||-||

||POWER()||Returns the value of one expression raised to the power of

another expression||X||X||X||-||-||

||RADIANS()||Returns the value of an expression converted from degrees to

radians.||X||X||X||-||-||

||ROUND()||Returns a numeric expression rounded to an integer.\\Can be

used to round an expression to a number of decimal points||X||X||X||X||-||
||SIN()||Returns the sine given in radians.||X||X||X||-||-||
||SQRT()||Returns the square root.||X||X||X||-||-||
||TAN()||Returns the tangent expressed in radians.||X||X||X||-||-||
||||||||||||||= **Text** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||ASCII()||Returns numeric value of left-most character||X||X||X||as
UNICODE()||-||
||CHR()||Character with the given code||X||X||as CHAR()||as CHAR()||-||

||LEFT()||Returns the leftmost number of characters as

specified||X||-||X||-||-||

||LPAD()||Returns the string argument, left-padded with the specified

string||X||X||X||-||-||

||LTRIM()||Removes leading
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||MD5()||Calculates the MD5 hash of string, returning the result in

hexadecimal||X||-||-||-||-||

||REPEAT()||Repeats a string the specified number of

times||X||-||X||-||-||

||REPLACE()||Replaces occurrences of a specified

string||X||X||X||X||[https://github.com/django/django/pull/9539 9539]||
||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

||RIGHT()||Returns the specified rightmost number of

characters||X||-||X||-||-||
||RPAD()||Appends string the specified number of times||X||X||X||-||-||
||RTRIM()||Removes trailing
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

||TRIM()||Removes leading and trailing

spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:25>

Django

unread,

Jan 5, 2018, 4:53:41 PM1/5/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Old description:

> string||X||X||X||X||[https://github.com/django/django/pull/9539 9539]||
> ||REVERSE()||Reverse the characters in a string||X||-||X||-||-||
> ||RIGHT()||Returns the specified rightmost number of
> characters||X||-||X||-||-||
> ||RPAD()||Appends string the specified number of times||X||X||X||-||-||
> ||RTRIM()||Removes trailing
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
> ||TRIM()||Removes leading and trailing
> spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||= **Comparison** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||NULLIF()||Returns NULL if the first argument equals the

second.||X||X||X||X||[https://github.com/django/django/pull/9543 9543]||

--

Comment (by Mads Jensen):

Added link to `NullIf` PR.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:26>

Django

unread,

Jan 12, 2018, 9:24:03 AM1/12/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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 Thomas Lagae):

* cc: Thomas Lagae (added)

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||= **Comparison** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||NULLIF()||Returns NULL if the first argument equals the

New description:

value.||X||X||X||X||[https://github.com/django/django/pull/9577 9577]||

--

Comment:

Add link to {{{Abs}}} PR.

I am a first time contributor so any feedback is much appreciated.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:27>

Django

unread,

Jan 12, 2018, 6:23:42 PM1/12/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by Matthew Pava:

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||= **Comparison** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||NULLIF()||Returns NULL if the first argument equals the
> second.||X||X||X||X||[https://github.com/django/django/pull/9543 9543]||
> ||||||||||||||= **Math** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||ABS()||Returns the absolute

New description:

||ASCII() (In Python, it is ord())||Returns numeric value of left-most

character||X||X||X||as

UNICODE()||[https://github.com/django/django/pull/9583 9583]||

||CHR()||Character with the given code||X||X||as CHAR()||as

CHAR()||[https://github.com/django/django/pull/9583 9583]||
||LEFT()||Returns the leftmost number of characters as specified||X||as
SUBSTR()||X||as SUBSTR()||[https://github.com/django/django/pull/9583
9583]||

||LPAD()||Returns the string argument, left-padded with the specified
string||X||X||X||-||-||
||LTRIM()||Removes leading
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
||MD5()||Calculates the MD5 hash of string, returning the result in
hexadecimal||X||-||-||-||-||
||REPEAT()||Repeats a string the specified number of
times||X||-||X||-||-||
||REPLACE()||Replaces occurrences of a specified
string||X||X||X||X||[https://github.com/django/django/pull/9539 9539]||
||REVERSE()||Reverse the characters in a string||X||-||X||-||-||

||RIGHT()||Returns the specified rightmost number of characters||X||as
SUBSTR()||X||as SUBSTR()||[https://github.com/django/django/pull/9583
9583]||

||RPAD()||Appends string the specified number of times||X||X||X||-||-||
||RTRIM()||Removes trailing
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||
||TRIM()||Removes leading and trailing
spaces||X||X||X||X||[https://github.com/django/django/pull/9220 9220]||

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:28>

Django

unread,

Jan 13, 2018, 12:50:03 PM1/13/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Thomas Lagae):

When passing a non-numeric value into the {{{Abs}}} function, the
behaviour differs between databases. I think this will also be the case
for other math functions. Should we catch non-numeric values and throw an
error or just document that this behaviour is undefined and differs
between databases?

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:29>

Django

unread,

Jan 17, 2018, 9:58:24 AM1/17/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Matthew Pava):

I added a link to the Ord, Chr, Left, and Right PR.

I just started thinking more about the Left and Right aspects, and it
occurred to me that a more pythonic solution would be the implementation
of slicing instead of having these separate database functions. This
would also change the implementation of Substr since that would no longer
be necessary either.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:30>

Django

unread,

Jan 17, 2018, 8:46:41 PM1/17/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"65728550bd6296871562da02069ddb86e9dd3482" 65728550]:
{{{
#!CommitTicketReference repository=""
revision="65728550bd6296871562da02069ddb86e9dd3482"
Refs #28643 -- Added Replace database function.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:31>

Django

unread,

Jan 31, 2018, 11:22:42 AM1/31/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by Nick Pope:

Old description:

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||= **Comparison** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||{{{NULLIF}}}||Returns {{{NULL}}} if the first argument equals the
second.||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9543

9543]||
||||||||||||||= **Math** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||{{{ABS}}}||Returns the absolute
value.||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9577 9577],
[https://github.com/django/django/pull/9622 9622]||
||{{{ACOS}}}||Returns the
arccosine.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{ASIN}}}||Returns the
arcsine.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{ATAN}}}||Returns the
arctangent.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{ATAN2}}}||Returns the arctangent of the two variables passed to
it.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{CEILING}}}||Returns the smallest integer value that is not less than
a numeric
expression||‎✔||{{{CEIL}}}||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{COS}}}||Returns the cosine expressed in
radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{COT}}}||Returns the
cotangent.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{DEGREES}}}||Returns a numeric expression converted from radians to
degrees.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{EXP}}}||Returns the base of the natural logarithm (e) raised to the
power of a numeric
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{FLOOR}}}||Returns the largest integer value that is not greater than
a numeric
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{LOG}}}||Returns the natural logarithm of a numeric
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{MOD}}}||Returns the remainder of one expression by diving by another
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{PI}}}||Returns the value of
π||‎✔||-||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{POWER}}}||Returns the value of one expression raised to the power of
another
expression||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{RADIANS}}}||Returns the value of an expression converted from degrees
to
radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{ROUND}}}||Returns a numeric expression rounded to an integer.\\Can be

used to round an expression to a number of decimal

points||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9622
9622]||
||{{{SIN}}}||Returns the sine given in
radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{SQRT}}}||Returns the square
root.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{TAN}}}||Returns the tangent expressed in
radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||||||||||||||= **Text** =||
||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

||{{{ASCII}}}||Returns numeric value of left-most character. (Equivalent
to {{{ord()}}} in
Python.)||‎✔||‎✔||‎✔||{{{UNICODE}}}||[https://github.com/django/django/pull/9583
9583]||
||{{{CHR}}}||Character with the given
code||‎✔||‎✔||{{{CHAR}}}||{{{CHAR}}}||[https://github.com/django/django/pull/9583
9583]||
||{{{LEFT}}}||Returns the leftmost number of characters as
specified||‎✔||✔**^2^**||‎✔||✔**^2^**||[https://github.com/django/django/pull/9583
9583]||
||{{{LPAD}}}||Returns the string argument, left-padded with the specified
string||‎✔||‎✔||‎✔||-||-||
||{{{LTRIM}}}||Removes leading
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||
||{{{MD5}}}||Calculates the MD5 hash of string, returning the result in
hexadecimal||‎✔||-||-||✔**^1^**||-||
||{{{REPEAT}}}||Repeats a string the specified number of
times||‎✔||-||‎✔||-||-||
||{{{REPLACE}}}||Replaces occurrences of a specified
string||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9539 9539]
[[span(style=font-size:75%, (Merged))]]||
||{{{REVERSE}}}||Reverse the characters in a
string||‎✔||-||‎✔||✔**^1^**||-||
||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||[https://github.com/django/django/pull/9583
9583]||
||{{{RPAD}}}||Appends string the specified number of
times||‎✔||‎✔||‎✔||-||-||
||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||
||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:32>

Django

unread,

Feb 23, 2018, 10:23:43 AM2/23/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"f82de6bfb1c3dc468f6eb7472b292cc432d00338" f82de6bf]:
{{{
#!CommitTicketReference repository=""
revision="f82de6bfb1c3dc468f6eb7472b292cc432d00338"
Refs #28643 -- Added Ord, Chr, Left, and Right database functions.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:33>

Django

unread,

Mar 15, 2018, 3:57:55 PM3/15/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"9421aee35e629f610ca739f13f7e37c1af27c1ac" 9421aee]:
{{{
#!CommitTicketReference repository=""
revision="9421aee35e629f610ca739f13f7e37c1af27c1ac"
Refs #28643 -- Added LTrim, RTrim, and Trim database functions.

Thanks Tim Graham and Mads Jensen for reviews.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:34>

Django

unread,

Mar 18, 2018, 11:43:50 AM3/18/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||= **Comparison** =||
> ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||

New description:

string||‎✔||‎✔||‎✔||-||[https://github.com/django/django/pull/9798 9798]||

||{{{LTRIM}}}||Removes leading
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||
||{{{MD5}}}||Calculates the MD5 hash of string, returning the result in
hexadecimal||‎✔||-||-||✔**^1^**||-||
||{{{REPEAT}}}||Repeats a string the specified number of
times||‎✔||-||‎✔||-||-||
||{{{REPLACE}}}||Replaces occurrences of a specified
string||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9539 9539]
[[span(style=font-size:75%, (Merged))]]||
||{{{REVERSE}}}||Reverse the characters in a
string||‎✔||-||‎✔||✔**^1^**||-||
||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||[https://github.com/django/django/pull/9583
9583]||
||{{{RPAD}}}||Appends string the specified number of

times||‎✔||‎✔||‎✔||-||[https://github.com/django/django/pull/9798 9798]||

||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||
||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:35>

Django

unread,

Mar 19, 2018, 12:35:43 PM3/19/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"cede5111bbeea1f02a7d35941a4264c7ff95df0a" cede5111]:
{{{
#!CommitTicketReference repository=""
revision="cede5111bbeea1f02a7d35941a4264c7ff95df0a"
Refs #28643 -- Added LPad and RPad database functions.

Thanks Tim Graham for the review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:36>

Django

unread,

Mar 21, 2018, 2:14:51 PM3/21/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

New description:

times||‎✔||-||‎✔||✔**^1^**||[https://github.com/django/django/pull/9808
9808]||

||{{{REPLACE}}}||Replaces occurrences of a specified
string||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9539 9539]
[[span(style=font-size:75%, (Merged))]]||
||{{{REVERSE}}}||Reverse the characters in a
string||‎✔||-||‎✔||✔**^1^**||-||
||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||[https://github.com/django/django/pull/9583
9583]||
||{{{RPAD}}}||Appends string the specified number of
times||‎✔||‎✔||‎✔||-||[https://github.com/django/django/pull/9798 9798]||
||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||
||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
9220]||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:37>

Django

unread,

Mar 22, 2018, 8:23:48 PM3/22/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by Nick Pope:

Old description:

> times||‎✔||-||‎✔||✔**^1^**||[https://github.com/django/django/pull/9808
> 9808]||
> ||{{{REPLACE}}}||Replaces occurrences of a specified
> string||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9539 9539]
> [[span(style=font-size:75%, (Merged))]]||
> ||{{{REVERSE}}}||Reverse the characters in a
> string||‎✔||-||‎✔||✔**^1^**||-||
> ||{{{RIGHT}}}||Returns the specified rightmost number of
> characters||‎✔||✔**^2^**||‎✔||✔**^2^**||[https://github.com/django/django/pull/9583
> 9583]||
> ||{{{RPAD}}}||Appends string the specified number of
> times||‎✔||‎✔||‎✔||-||[https://github.com/django/django/pull/9798 9798]||
> ||{{{RTRIM}}}||Removes trailing
> spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
> 9220]||
> ||{{{TRIM}}}||Removes leading and trailing
> spaces||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9220
> 9220]||
>
> - **^1^** Function can be easily supported on SQLite with a
> [https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
> user defined function].
> - **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||||= **Comparison** =||
||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{NullIf}}}||{{{NULLIF}}}||Returns {{{NULL}}} if the first argument

equals the
second.||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9543
9543]||
||||||||||||||||= **Math** =||

||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{Abs}}}||{{{ABS}}}||Returns the absolute
value.||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9622
9622]||
||{{{ACos}}}||{{{ACOS}}}||Returns the

arccosine.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{ASin}}}||{{{ASIN}}}||Returns the

arcsine.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{ATan}}}||{{{ATAN}}}||Returns the

arctangent.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{ATan2}}}||{{{ATAN2}}}||Returns the arctangent of the two variables

passed to
it.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Ceil}}}||{{{CEILING}}}||Returns the smallest integer value that is

not less than a numeric
expression||‎✔||{{{CEIL}}}||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Cos}}}||{{{COS}}}||Returns the cosine expressed in

radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Cot}}}||{{{COT}}}||Returns the
cotangent.||‎✔||‎✔**^5^**||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{Degrees}}}||{{{DEGREES}}}||Returns a numeric expression converted

from radians to
degrees.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Exp}}}||{{{EXP}}}||Returns the base of the natural logarithm (e)

raised to the power of a numeric
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Floor}}}||{{{FLOOR}}}||Returns the largest integer value that is not

greater than a numeric
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Ln}}}||{{{LN}}}||Returns the natural logarithm of a numeric

expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Log}}}||{{{LOG(B, X)}}}||Returns the logarithm of a numeric

expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Mod}}}||{{{MOD}}}||Returns the remainder of one expression by diving

by another
expression.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Pi}}}||{{{PI}}}||Returns the value of
π||‎✔||✔**^4^**||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||{{{Power}}}||{{{POWER}}}||Returns the value of one expression raised to

the power of another
expression||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Radians}}}||{{{RADIANS}}}||Returns the value of an expression

converted from degrees to
radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Round}}}||{{{ROUND}}}||Returns a numeric expression rounded to an

integer.\\Can be used to round an expression to a number of decimal
points||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9622
9622]||

||{{{Sin}}}||{{{SIN}}}||Returns the sine given in

radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Sqrt}}}||{{{SQRT}}}||Returns the square

root.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||

||{{{Tan}}}||{{{TAN}}}||Returns the tangent expressed in

radians.||‎✔||‎✔||‎✔||✔**^1^**||[https://github.com/django/django/pull/9622
9622]||
||||||||||||||||= **Text** =||

||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{Ord}}}||{{{ASCII}}}||Returns numeric value of left-most character.
(Equivalent to {{{ord()}}} in
Python.)||‎✔||‎✔||‎✔||{{{UNICODE}}}||~~9583~~||
||{{{Chr}}}||{{{CHR}}}||Character with the given
code||‎✔||‎✔||{{{CHAR}}}||{{{CHAR}}}||~~9583~~||
||{{{Left}}}||{{{LEFT}}}||Returns the leftmost number of characters as
specified||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||{{{LPad}}}||{{{LPAD}}}||Returns the string argument, left-padded with
the specified string||‎✔||‎✔||‎✔||✔**^1^**||~~9798~~||
||{{{LTrim}}}||{{{LTRIM}}}||Removes leading
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{MD5}}}||{{{MD5}}}||Calculates the MD5 hash of string, returning the

result in hexadecimal||‎✔||-||-||✔**^1^**||-||

||{{{Repeat}}}||{{{REPEAT}}}||Repeats a string the specified number of
times||‎✔||✔**^3^**||‎✔||✔**^1^**||[https://github.com/django/django/pull/9808
9808]||
||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified
string||‎✔||‎✔||‎✔||‎✔||~~9539~~||
||{{{Reverse}}}||{{{REVERSE}}}||Reverse the characters in a
string||‎✔||-||‎✔||✔**^1^**||-||
||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
times||‎✔||‎✔||‎✔||✔**^1^**||~~9798~~||
||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.

- **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
- **^4^** Behaviour can be emulated by directly substituting constant
{{{math.pi}}}.
- **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:38>

Django

unread,

Mar 23, 2018, 1:28:52 PM3/23/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||||= **Comparison** =||

New description:

specified||‎✔||✔**^2, 6^**||‎✔||✔**^2^**||~~9583~~||

||{{{LPad}}}||{{{LPAD}}}||Returns the string argument, left-padded with
the specified string||‎✔||‎✔||‎✔||✔**^1^**||~~9798~~||
||{{{LTrim}}}||{{{LTRIM}}}||Removes leading
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{MD5}}}||{{{MD5}}}||Calculates the MD5 hash of string, returning the
result in hexadecimal||‎✔||-||-||✔**^1^**||-||
||{{{Repeat}}}||{{{REPEAT}}}||Repeats a string the specified number of
times||‎✔||✔**^3^**||‎✔||✔**^1^**||[https://github.com/django/django/pull/9808
9808]||
||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified

string||‎✔||‎✔**^6^**||‎✔||‎✔||~~9539~~||

||{{{Reverse}}}||{{{REVERSE}}}||Reverse the characters in a
string||‎✔||-||‎✔||✔**^1^**||-||
||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of

times||‎✔||‎✔**^6^**|||‎✔||✔**^1^**||~~9798~~||

||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
- **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
- **^4^** Behaviour can be emulated by directly substituting constant
{{{math.pi}}}.
- **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.

- **^6^** Doesn't work properly with multibyte characters sets on Oracle.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:39>

Django

unread,

Apr 3, 2018, 1:36:32 PM4/3/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"55cc26941a1eb6093bf9602e67a2b5fdf7e68683" 55cc269]:
{{{
#!CommitTicketReference repository=""
revision="55cc26941a1eb6093bf9602e67a2b5fdf7e68683"
Refs #28643 -- Added Repeat database function.

Thanks Tim Graham and Nick Pope for reviews.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:40>

Django

unread,

Apr 3, 2018, 1:39:00 PM4/3/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

New description:

times||‎✔||✔**^3^**||‎✔||✔**^1^**||~~9808~~||

||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified
string||‎✔||‎✔**^6^**||‎✔||‎✔||~~9539~~||
||{{{Reverse}}}||{{{REVERSE}}}||Reverse the characters in a
string||‎✔||-||‎✔||✔**^1^**||-||
||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
times||‎✔||‎✔**^6^**|||‎✔||✔**^1^**||~~9798~~||
||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
- **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
- **^4^** Behaviour can be emulated by directly substituting constant
{{{math.pi}}}.
- **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.
- **^6^** Doesn't work properly with multibyte characters sets on Oracle.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:41>

Django

unread,

Jun 29, 2018, 4:16:32 PM6/29/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

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

Changes (by Tim Graham):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:42>

Django

unread,

Jul 5, 2018, 11:02:45 AM7/5/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"a0b19a0f5b1731cf575546175034da53f5af5367" a0b19a0]:
{{{
#!CommitTicketReference repository=""
revision="a0b19a0f5b1731cf575546175034da53f5af5367"
Refs #28643 -- Added math database functions.

Thanks Nick Pope for much review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:43>

Django

unread,

Aug 15, 2018, 6:22:51 PM8/15/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Nick Pope:

Old description:

> times||‎✔||✔**^3^**||‎✔||✔**^1^**||~~9808~~||
> ||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified
> string||‎✔||‎✔**^6^**||‎✔||‎✔||~~9539~~||
> ||{{{Reverse}}}||{{{REVERSE}}}||Reverse the characters in a
> string||‎✔||-||‎✔||✔**^1^**||-||
> ||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
> characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
> ||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
> times||‎✔||‎✔**^6^**|||‎✔||✔**^1^**||~~9798~~||
> ||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
> spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
> ||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
> spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
>
> - **^1^** Function can be easily supported on SQLite with a
> [https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
> user defined function].
> - **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
> - **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
> - **^4^** Behaviour can be emulated by directly substituting constant
> {{{math.pi}}}.
> - **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.
> - **^6^** Doesn't work properly with multibyte characters sets on Oracle.

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||||= **Comparison** =||
||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{NullIf}}}||{{{NULLIF}}}||Returns {{{NULL}}} if the first argument
equals the
second.||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9543
9543]||
||||||||||||||||= **Math** =||
||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{Abs}}}||{{{ABS}}}||Returns the absolute

value.||‎✔||‎✔||‎✔||‎✔||~~9622~~||
||{{{ACos}}}||{{{ACOS}}}||Returns the
arccosine.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||
||{{{ASin}}}||{{{ASIN}}}||Returns the
arcsine.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||
||{{{ATan}}}||{{{ATAN}}}||Returns the
arctangent.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{ATan2}}}||{{{ATAN2}}}||Returns the arctangent of the two variables

passed to it.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Ceil}}}||{{{CEILING}}}||Returns the smallest integer value that is
not less than a numeric

expression||‎✔||{{{CEIL}}}||‎✔||✔**^1^**||~~9622~~||

||{{{Cos}}}||{{{COS}}}||Returns the cosine expressed in

radians.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||
||{{{Cot}}}||{{{COT}}}||Returns the
cotangent.||‎✔||‎✔**^5^**||‎✔||✔**^1^**||~~9622~~||

||{{{Degrees}}}||{{{DEGREES}}}||Returns a numeric expression converted

from radians to degrees.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Exp}}}||{{{EXP}}}||Returns the base of the natural logarithm (e)
raised to the power of a numeric

expression.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Floor}}}||{{{FLOOR}}}||Returns the largest integer value that is not

greater than a numeric expression.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Ln}}}||{{{LN}}}||Returns the natural logarithm of a numeric

expression.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Log}}}||{{{LOG(B, X)}}}||Returns the logarithm of a numeric

expression.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Mod}}}||{{{MOD}}}||Returns the remainder of one expression by diving

by another expression.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Pi}}}||{{{PI}}}||Returns the value of

π||‎✔||✔**^4^**||‎✔||✔**^1^**||~~9622~~||

||{{{Power}}}||{{{POWER}}}||Returns the value of one expression raised to

the power of another expression||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Radians}}}||{{{RADIANS}}}||Returns the value of an expression

converted from degrees to radians.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Round}}}||{{{ROUND}}}||Returns a numeric expression rounded to an
integer.\\Can be used to round an expression to a number of decimal

points||‎✔||‎✔||‎✔||‎✔||~~9622~~||

||{{{Sin}}}||{{{SIN}}}||Returns the sine given in

radians.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Sqrt}}}||{{{SQRT}}}||Returns the square

root.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

||{{{Tan}}}||{{{TAN}}}||Returns the tangent expressed in

radians.||‎✔||‎✔||‎✔||✔**^1^**||~~9622~~||

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:44>

Django

unread,

Nov 30, 2018, 1:59:47 PM11/30/18

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"c3bbf1fd4cc73cf0f6109c81f2e44d44a4e25a0b" c3bbf1fd]:
{{{
#!CommitTicketReference repository=""
revision="c3bbf1fd4cc73cf0f6109c81f2e44d44a4e25a0b"
Refs #28643 -- Skipped ATan2() workaround on SpatiaLite 5+.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:45>

Django

unread,

Jan 8, 2019, 6:18:33 PM1/8/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------

Reporter: Matthew Pava | Owner: Nick Pope

Needs tests: 0 | Patch needs improvement: 0

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

Changes (by Nick Pope):

* owner: JunyiJ => Nick Pope
* needs_better_patch: 1 => 0

Comment:

I have added a new [https://github.com/django/django/pull/10764 PR] that
cleans up some of the compatibility mixins added as part of the math
function work so that they can be reused.
It also makes use of these mixins for aggregates defined in
`django.db.models.aggregates`.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:46>

Django

unread,

Jan 8, 2019, 7:31:18 PM1/8/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||||= **Comparison** =||
> ||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||{{{NullIf}}}||{{{NULLIF}}}||Returns {{{NULL}}} if the first argument
> equals the
> second.||‎✔||‎✔||‎✔||‎✔||[https://github.com/django/django/pull/9543
> 9543]||
> ||||||||||||||||= **Math** =||
> ||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||{{{Abs}}}||{{{ABS}}}||Returns the absolute

New description:

string||‎✔||✔**^6, 7^**||‎✔||✔**^1^**||-||

||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
- **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
- **^4^** Behaviour can be emulated by directly substituting constant
{{{math.pi}}}.
- **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.
- **^6^** Doesn't work properly with multibyte characters sets on Oracle.

- **^7^** The {{{REVERSE}}} function is undocumented on Oracle.

--

Comment (by Nick Pope):

Added a [https://github.com/django/django/pull/10827 PR] implementing
`Reverse()`.
Note that `REVERSE` exists for Oracle but is undocumented and works on
bytes, not multi-byte strings. (Support table updated in ticket
description.)

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:47>

Django

unread,

Jan 8, 2019, 7:33:05 PM1/8/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by Nick Pope:

Old description:

New description:

7^**||‎✔||✔**^1^**||[https://github.com/django/django/pull/10827 10827]||

||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
- **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
- **^4^** Behaviour can be emulated by directly substituting constant
{{{math.pi}}}.
- **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.
- **^6^** Doesn't work properly with multibyte characters sets on Oracle.
- **^7^** The {{{REVERSE}}} function is undocumented on Oracle.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:48>

Django

unread,

Jan 10, 2019, 1:14:56 PM1/10/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"4b9d72210f0201efeafb9703e166aa3b38c04873" 4b9d7221]:
{{{
#!CommitTicketReference repository=""
revision="4b9d72210f0201efeafb9703e166aa3b38c04873"
Refs #28643 -- Added NullIf database function.

Thanks Nick Pope, Mariusz Felisiak, and Tim Graham for reviews.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:49>

Django

unread,

Jan 12, 2019, 11:06:16 AM1/12/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by Tim Graham:

Old description:

> 7^**||‎✔||✔**^1^**||[https://github.com/django/django/pull/10827 10827]||
> ||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
> characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
> ||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
> times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
> ||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
> spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
> ||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
> spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
>
> - **^1^** Function can be easily supported on SQLite with a
> [https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
> user defined function].
> - **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
> - **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
> - **^4^** Behaviour can be emulated by directly substituting constant
> {{{math.pi}}}.
> - **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.
> - **^6^** Doesn't work properly with multibyte characters sets on Oracle.
> - **^7^** The {{{REVERSE}}} function is undocumented on Oracle.

New description:

I was surprised to learn that we didn't have a StrIndex function until
version 2, and yet we had Substr since at least version 1.8. I wonder how
users were using Substr without also finding a use for StrIndex this whole
time. Anyway, since we seem to be adding these functions one at a time,
why don't we work on trying to get the built-ins implemented in one sweep
instead?

We may even want to split the documentation page
(https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
into further categories with String functions and Numeric functions.

This is just a sample checklist, with corresponding attributes to which
backend has them available.

||||||||||||||||= **Comparison** =||
||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||`NullIf`||`NULLIF`||Returns `NULL` if the first argument equals the

second.||‎✔||‎✔||‎✔||‎✔||~~9543~~||

7^**||‎✔||✔**^1^**||~~10827~~||
||`Right`||`RIGHT`||Returns the specified rightmost number of

characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||`RPad`||`RPAD`||Appends string the specified number of
times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
||`RTrim`||`RTRIM`||Removes trailing spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||`Trim`||`TRIM`||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].

- **^2^** Can be emulated by using `SUBSTR`.
- **^3^** Can be emulated by using `LENGTH` and `RPAD`.
- **^4^** Can be emulated by directly substituting constant `math.pi`.
- **^5^** Can be emulated by using `1 / TAN(X)`.

- **^6^** Doesn't work properly with multibyte characters sets on Oracle.
- **^7^** The `REVERSE` function is undocumented on Oracle.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:50>

Django

unread,

Jan 12, 2019, 11:06:34 AM1/12/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"abf8e390a4161a051ed1c4be11b9447866b06ba8" abf8e390]:
{{{
#!CommitTicketReference repository=""
revision="abf8e390a4161a051ed1c4be11b9447866b06ba8"
Refs #28643 -- Added Reverse database function.

Thanks Mariusz Felisiak for Oracle advice and review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:51>

Django

unread,

Jan 14, 2019, 4:03:10 PM1/14/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"e85afa5943695457c85e9bc1c5dc0d985004e303" e85afa59]:
{{{
#!CommitTicketReference repository=""
revision="e85afa5943695457c85e9bc1c5dc0d985004e303"
Refs #28643 -- Changed StdDev() to use NumericOutputFieldMixin.

Keeps precision instead of forcing DecimalField to FloatField.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:54>

Django

unread,

Jan 14, 2019, 4:03:10 PM1/14/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"c690afb873cac8035a3cb3be7c597a5ff0e4b261" c690afb8]:
{{{
#!CommitTicketReference repository=""
revision="c690afb873cac8035a3cb3be7c597a5ff0e4b261"
Refs #28643 -- Changed Avg() to use NumericOutputFieldMixin.

Keeps precision instead of forcing DecimalField to FloatField.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:53>

Django

unread,

Jan 14, 2019, 4:03:10 PM1/14/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"846624ed0858aec0e51baebaa5b397e135c6d1dc" 846624ed]:
{{{
#!CommitTicketReference repository=""
revision="846624ed0858aec0e51baebaa5b397e135c6d1dc"
Refs #28643 -- Extracted DurationField logic for Avg() and Sum() into
mixin.

Also addresses Sum() not handling the filter option correctly.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:56>

Django

unread,

Jan 14, 2019, 4:03:10 PM1/14/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"6d4efa8e6a4cc7be4ba957dec71f6f63cd58700d" 6d4efa8e]:
{{{
#!CommitTicketReference repository=""
revision="6d4efa8e6a4cc7be4ba957dec71f6f63cd58700d"
Refs #28643 -- Changed Variance() to use NumericOutputFieldMixin.

Keeps precision instead of forcing DecimalField to FloatField.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:55>

Django

unread,

Jan 14, 2019, 4:03:11 PM1/14/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"3d5e0f8394688d40036e27cfcfac295e6fe62269" 3d5e0f83]:
{{{
#!CommitTicketReference repository=""
revision="3d5e0f8394688d40036e27cfcfac295e6fe62269"
Refs #28643 -- Moved db function mixins to a separate module.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:52>

Django

unread,

Jan 14, 2019, 4:21:05 PM1/14/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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 Tim Graham):

* has_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:57>

Django

unread,

Feb 18, 2019, 1:44:34 PM2/18/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

> I was surprised to learn that we didn't have a StrIndex function until
> version 2, and yet we had Substr since at least version 1.8. I wonder
> how users were using Substr without also finding a use for StrIndex this
> whole time. Anyway, since we seem to be adding these functions one at a
> time, why don't we work on trying to get the built-ins implemented in one
> sweep instead?
>
> We may even want to split the documentation page
> (https://docs.djangoproject.com/en/dev/ref/models/database-functions/)
> into further categories with String functions and Numeric functions.
>
> This is just a sample checklist, with corresponding attributes to which
> backend has them available.
>
> ||||||||||||||||= **Comparison** =||
> ||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||`NullIf`||`NULLIF`||Returns `NULL` if the first argument equals the

New description:

hexadecimal||‎✔||✔**^8^**||✔||✔**^1^**||-||

||`Repeat`||`REPEAT`||Repeats a string the specified number of
times||‎✔||✔**^3^**||‎✔||✔**^1^**||~~9808~~||
||`Replace`||`REPLACE`||Replaces occurrences of a specified
string||‎✔||‎✔**^6^**||‎✔||‎✔||~~9539~~||
||`Reverse`||`REVERSE`||Reverse the characters in a string||‎✔||✔**^6,
7^**||‎✔||✔**^1^**||~~10827~~||
||`Right`||`RIGHT`||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||`RPad`||`RPAD`||Appends string the specified number of
times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
||`RTrim`||`RTRIM`||Removes trailing spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||`Trim`||`TRIM`||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Can be emulated by using `SUBSTR`.
- **^3^** Can be emulated by using `LENGTH` and `RPAD`.
- **^4^** Can be emulated by directly substituting constant `math.pi`.
- **^5^** Can be emulated by using `1 / TAN(X)`.
- **^6^** Doesn't work properly with multibyte characters sets on Oracle.
- **^7^** The `REVERSE` function is undocumented on Oracle.

- **^8^** Can be supported by using `STANDARD_HASH` on Oracle.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:58>

Django

unread,

Feb 18, 2019, 3:47:15 PM2/18/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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 felixxm):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/11004 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:59>

Django

unread,

Feb 18, 2019, 7:21:48 PM2/18/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by Nick Pope):

[https://github.com/django/django/pull/11006 PR] implementing `SHA1()`,
`SHA224()`, `SHA256()`, `SHA384()` and `SHA512()`.

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:60>

Django

unread,

Feb 19, 2019, 9:55:30 AM2/19/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

> in hexadecimal||‎✔||✔**^8^**||✔||✔**^1^**||-||

New description:

hexadecimal||‎✔||✔**^8^**||✔||✔**^1^**||11004||

||`Repeat`||`REPEAT`||Repeats a string the specified number of
times||‎✔||✔**^3^**||‎✔||✔**^1^**||~~9808~~||
||`Replace`||`REPLACE`||Replaces occurrences of a specified
string||‎✔||‎✔**^6^**||‎✔||‎✔||~~9539~~||
||`Reverse`||`REVERSE`||Reverse the characters in a string||‎✔||✔**^6,
7^**||‎✔||✔**^1^**||~~10827~~||
||`Right`||`RIGHT`||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||`RPad`||`RPAD`||Appends string the specified number of
times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
||`RTrim`||`RTRIM`||Removes trailing spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||`Trim`||`TRIM`||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Can be emulated by using `SUBSTR`.
- **^3^** Can be emulated by using `LENGTH` and `RPAD`.
- **^4^** Can be emulated by directly substituting constant `math.pi`.
- **^5^** Can be emulated by using `1 / TAN(X)`.
- **^6^** Doesn't work properly with multibyte characters sets on Oracle.
- **^7^** The `REVERSE` function is undocumented on Oracle.
- **^8^** Can be supported by using `STANDARD_HASH` on Oracle.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:61>

Django

unread,

Feb 21, 2019, 4:53:55 AM2/21/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(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
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"9ff18c08c32cf54d3c3a7a9e459d44711adba30f" 9ff18c08]:
{{{
#!CommitTicketReference repository=""
revision="9ff18c08c32cf54d3c3a7a9e459d44711adba30f"
Refs #28643 -- Added MD5 database function.

Thanks Tim Graham, Nick Pope and Simon Charette for reviews.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:62>

Django

unread,

Feb 21, 2019, 4:56:27 AM2/21/19

to django-...@googlegroups.com

#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope

Type: New feature | Status: closed

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

Severity: Normal | Resolution: fixed

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 felixxm):

* status: assigned => closed
* resolution: => fixed

Old description:

> in hexadecimal||‎✔||✔**^8^**||✔||✔**^1^**||11004||

New description:

hexadecimal||‎✔||✔**^8^**||✔||✔**^1^**||~~11004~~||

||`Repeat`||`REPEAT`||Repeats a string the specified number of
times||‎✔||✔**^3^**||‎✔||✔**^1^**||~~9808~~||
||`Replace`||`REPLACE`||Replaces occurrences of a specified
string||‎✔||‎✔**^6^**||‎✔||‎✔||~~9539~~||
||`Reverse`||`REVERSE`||Reverse the characters in a string||‎✔||✔**^6,
7^**||‎✔||✔**^1^**||~~10827~~||
||`Right`||`RIGHT`||Returns the specified rightmost number of
characters||‎✔||✔**^2^**||‎✔||✔**^2^**||~~9583~~||
||`RPad`||`RPAD`||Appends string the specified number of
times||‎✔||‎✔**^6^**||✔||✔**^1^**||~~9798~~||
||`RTrim`||`RTRIM`||Removes trailing spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||
||`Trim`||`TRIM`||Removes leading and trailing
spaces||‎✔||‎✔||‎✔||‎✔||~~9220~~||

- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Can be emulated by using `SUBSTR`.
- **^3^** Can be emulated by using `LENGTH` and `RPAD`.
- **^4^** Can be emulated by directly substituting constant `math.pi`.
- **^5^** Can be emulated by using `1 / TAN(X)`.
- **^6^** Doesn't work properly with multibyte characters sets on Oracle.
- **^7^** The `REVERSE` function is undocumented on Oracle.
- **^8^** Can be supported by using `STANDARD_HASH` on Oracle.