Fixed #23753 -- Added a suite of SQL Functions

Added functions and tests
Added docs and more tests
Added TextField converter to mysql backend
Aliased Value as V in example docs and tests
Removed unicode_compatible in example
Fixed console emulation in examples

6 files changed, 167 insertions, 3 deletions

diff --git a/docs/index.txt b/docs/index.txt
index fb8997a869..7aa9dc5864 100644
--- a/docs/index.txt
+++ b/docs/index.txt
@@ -87,7 +87,8 @@ manipulating the data of your Web application. Learn more about it below:
   :doc:`Custom fields <howto/custom-model-fields>` |
   :doc:`Multiple databases <topics/db/multi-db>` |
   :doc:`Custom lookups <howto/custom-lookups>` |
-  :doc:`Query Expressions <ref/models/expressions>`
+  :doc:`Query Expressions <ref/models/expressions>` |
+  :doc:`Database Functions <ref/models/database-functions>`
 
 * **Other:**
   :doc:`Supported databases <ref/databases>` |
diff --git a/docs/ref/models/database-functions.txt b/docs/ref/models/database-functions.txt
new file mode 100644
index 0000000000..cdd46ff1a2
--- /dev/null
+++ b/docs/ref/models/database-functions.txt
@@ -0,0 +1,153 @@
+==================
+Database Functions
+==================
+
+.. module:: django.db.models.functions
+    :synopsis: Database Functions
+
+.. versionadded:: 1.8
+
+The classes documented below provide a way for users to use functions provided
+by the underlying database as annotations, aggregations, or filters in Django.
+Functions are also :doc:`expressions <expressions>`, so they can be used and
+combined with other expressions like :ref:`aggregate functions
+<aggregation-functions>`.
+
+We'll be using the following model in examples of each function::
+
+    class Author(models.Model):
+        name = models.CharField(max_length=50)
+        age = models.PositiveIntegerField(null=True, blank=True)
+        alias = models.CharField(max_length=50, null=True, blank=True)
+        goes_by = models.CharField(max_length=50, null=True, blank=True)
+
+We don't usually recommend allowing ``null=True`` for ``CharField`` since this
+allows the field to have two "empty values", but it's important for the
+``Coalesce`` example below.
+
+Coalesce
+--------
+
+.. class:: Coalesce(*expressions, **extra)
+
+Accepts a list of at least two field names or expressions and returns the
+first non-null value (note that an empty string is not considered a null
+value). Each argument must be of a similar type, so mixing text and numbers
+will result in a database error.
+
+Usage examples::
+
+    >>> # Get a screen name from least to most public
+    >>> from django.db.models import Sum, Value as V
+    >>> from django.db.models.functions import Coalesce
+    >>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
+    >>> author = Author.objects.annotate(
+    ...    screen_name=Coalesce('alias', 'goes_by', 'name')).get()
+    >>> print(author.screen_name)
+    Maggie
+
+    >>> # Prevent an aggregate Sum() from returning None
+    >>> aggregated = Author.objects.aggregate(
+    ...    combined_age=Coalesce(Sum('age'), V(0)),
+    ...    combined_age_default=Sum('age'))
+    >>> print(aggregated['combined_age'])
+    0
+    >>> print(aggregated['combined_age_default'])
+    None
+
+Concat
+------
+
+.. class:: Concat(*expressions, **extra)
+
+Accepts a list of at least two text fields or expressions and returns the
+concatenated text. Each argument must be of a text or char type. If you want
+to concatenate a ``TextField()`` with a ``CharField()``, then be sure to tell
+Django that the ``output_field`` should be a ``TextField()``. This is also
+required when concatenating a ``Value`` as in the example below.
+
+This function will never have a null result. On backends where a null argument
+results in the entire expression being null, Django will ensure that each null
+part is converted to an empty string first.
+
+Usage example::
+
+    >>> # Get the display name as "name (goes_by)"
+    >>> from django.db.models import CharField, Value as V
+    >>> from django.db.models.functions import Concat
+    >>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
+    >>> author = Author.objects.annotate(
+    ...    screen_name=Concat('name', V(' ('), 'goes_by', V(')'),
+    ...    output_field=CharField())).get()
+    >>> print(author.screen_name)
+    Margaret Smith (Maggie)
+
+Length
+------
+
+.. class:: Length(expression, **extra)
+
+Accepts a single text field or expression and returns the number of characters
+the value has. If the expression is null, then the length will also be null.
+
+Usage example::
+
+    >>> # Get the length of the name and goes_by fields
+    >>> from django.db.models.functions import Length
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.annotate(
+    ...    name_length=Length('name'),
+    ...    goes_by_length=Length('goes_by')).get()
+    >>> print(author.name_length, author.goes_by_length)
+    (14, None)
+
+Lower
+------
+
+.. class:: Lower(expression, **extra)
+
+Accepts a single text field or expression and returns the lowercase
+representation.
+
+Usage example::
+
+    >>> from django.db.models.functions import Lower
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.annotate(name_lower=Lower('name')).get()
+    >>> print(author.name_lower)
+    margaret smith
+
+Substr
+------
+
+.. class:: Substr(expression, pos, length=None, **extra)
+
+Returns a substring of length ``length`` from the field or expression starting
+at position ``pos``. The position is 1-indexed, so the position must be greater
+than 0. If ``length`` is ``None``, then the rest of the string will be returned.
+
+Usage example::
+
+    >>> # Set the alias to the first 5 characters of the name as lowercase
+    >>> from django.db.models.functions import Substr, Lower
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> Author.objects.update(alias=Lower(Substr('name', 1, 5)))
+    1
+    >>> print(Author.objects.get(name='Margaret Smith').alias)
+    marga
+
+Upper
+------
+
+.. class:: Upper(expression, **extra)
+
+Accepts a single text field or expression and returns the uppercase
+representation.
+
+Usage example::
+
+    >>> from django.db.models.functions import Upper
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.annotate(name_upper=Upper('name')).get()
+    >>> print(author.name_upper)
+    MARGARET SMITH
diff --git a/docs/ref/models/expressions.txt b/docs/ref/models/expressions.txt
index 24e4f42834..defcdb0217 100644
--- a/docs/ref/models/expressions.txt
+++ b/docs/ref/models/expressions.txt
@@ -189,6 +189,8 @@ extra attribute ``field_lower`` produced, roughly, from the following SQL::
         ...
         LOWER("app_label"."field") as "field_lower"
 
+See :doc:`database-functions` for a list of built-in database functions.
+
 The ``Func`` API is as follows:
 
 .. class:: Func(*expressions, **extra)
diff --git a/docs/ref/models/index.txt b/docs/ref/models/index.txt
index b7f5ab6635..d1d3680dd8 100644
--- a/docs/ref/models/index.txt
+++ b/docs/ref/models/index.txt
@@ -16,3 +16,4 @@ Model API reference. For introductory material, see :doc:`/topics/db/models`.
    queries
    lookups
    expressions
+   database-functions
diff --git a/docs/releases/1.8.txt b/docs/releases/1.8.txt
index f1960304c4..3d38ec2443 100644
--- a/docs/releases/1.8.txt
+++ b/docs/releases/1.8.txt
@@ -66,8 +66,8 @@ New data types
   backends. There is a corresponding :class:`form field
   <django.forms.DurationField>`.
 
-Query Expressions
-~~~~~~~~~~~~~~~~~
+Query Expressions and Database Functions
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 :doc:`Query Expressions </ref/models/expressions>` allow users to create,
 customize, and compose complex SQL expressions. This has enabled annotate
@@ -75,6 +75,12 @@ to accept expressions other than aggregates. Aggregates are now able to
 reference multiple fields, as well as perform arithmetic, similar to ``F()``
 objects.
 
+A collection of :doc:`database functions </ref/models/database-functions>` is
+also included with functionality such as
+:class:`~django.db.models.functions.Coalesce`,
+:class:`~django.db.models.functions.Concat`, and
+:class:`~django.db.models.functions.Substr`.
+
 ``TestCase`` data setup
 ~~~~~~~~~~~~~~~~~~~~~~~
 
diff --git a/docs/spelling_wordlist b/docs/spelling_wordlist
index a80391e292..03b36c5bc0 100644
--- a/docs/spelling_wordlist
+++ b/docs/spelling_wordlist
@@ -616,6 +616,7 @@ subpackages
 subqueries
 subquery
 subselect
+substr
 subtemplate
 subtemplates
 subviews