Opened 9 years ago

Closed 9 years ago

#23753 closed New feature (fixed)

Provide a set of SQL functions

Reported by: Josh Smeaton Owned by: Josh Smeaton
Component: Database layer (models, ORM) Version: dev
Severity: Release blocker Keywords: expressions
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Josh Smeaton)

When #14030 lands there will be an easy way to implement SQL functions like LENGTH() and COALESCE(). There are several tickets requesting the implementation of certain functions, and this ticket aims to collate those requests, and extend the number of functions to a useable minimum subset. There was a discussion on the mailing list that calls out some implementation details as well as the set of functions to implement:!msg/django-developers/HggiPzwkono/SgXt4iNjTIYJ

List of functions:

  • Coalesce
  • ToLower/Lower
  • ToUpper/Upper
  • Length
  • Case-When / IIF
  • Substring
  • As/Cast for casting the return value of a complex expression without defining the output_field of each sub-component)

This list isn't necessarily complete or final. If there are other functions that you'd like to see implemented in core, please comment, and I'll update the list above. Particular implementation details can be discussed on the PR or below once a PR is submitted. I'll aim to have these completed very soon after #14030 lands, and I expect these to be available in the same django version that incorporates 14030.

Linked tickets:

  • #23363 (Length)
  • #21208 (Coalesce in .extra)
  • #10929 (Coalesce for Aggregates - could also be done by customising the aggregate directly)

Change History (11)

comment:1 by Josh Smeaton, 9 years ago

Description: modified (diff)

comment:2 by Tim Graham, 9 years ago

Triage Stage: UnreviewedAccepted

comment:3 by Josh Smeaton, 9 years ago

Owner: changed from nobody to Josh Smeaton
Status: newassigned

comment:4 by Josh Smeaton, 9 years ago

I've begun working on this ticket at

Initial implementation of coalesce and a single very basic test. I'll be adding extra tests and more functions over the coming couple of weeks. When it's getting close to merge-able I'll open a PR for comments. But feel free to make comments before then too.

comment:5 by Josh Smeaton, 9 years ago

I've implemented the following so far:

  • Coalesce
  • Concat (accepts 2 params only)
  • ConcatMany (same as concat, but can ConcatMany(a,b,c,d)
  • Length
  • Lower
  • Substr
  • Upper

Still need to do Case/When, and possibly the As/Cast wrapper, which simply assigns an output_field. Should be about a week away until Case/When is done and docs begin to be written.

comment:6 by Josh Smeaton, 9 years ago

Has patch: set
Triage Stage: AcceptedReady for checkin

I've decided against an As wrapper (whose sole role would be to provide an output_field for multiple expressions), because I couldn't really find a need for it in core. We can revisit this decision in the future if users ask for it.

Case/When expressions are currently being worked on by Michal as discussed on the ML. I'll create a separate ticket to track that.

Otherwise, this PR is ready for a full review.

comment:7 by Josh Smeaton <josh.smeaton@…>, 9 years ago

Resolution: fixed
Status: assignedclosed

In 47182965465f47657cbab6858a6a8637cc32b2df:

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

comment:8 by Tim Graham, 9 years ago

Has patch: unset
Resolution: fixed
Severity: NormalRelease blocker
Status: closednew
Triage Stage: Ready for checkinAccepted

There's a test failure on Oracle/Python 2:


Traceback (most recent call last):
  File "/mnt/jenkinsdata/workspace/django-oracle/database/oracle11/python/python3.3/django/db/backends/", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/mnt/jenkinsdata/workspace/django-oracle/database/oracle11/python/python3.3/django/db/backends/oracle/", line 942, in execute
    return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.DatabaseError: ORA-00932: inconsistent datatypes: expected NCHAR got NCLOB

comment:9 by Josh Smeaton, 9 years ago

The problem here is that NCLOB (TextField) and NCHAR (CharField) are too different in Oracle terms, so the output of the expression with regards to Oracle is not defined.

We should be able to solve this by wrapping CharField parts of the expression in a TO_NCLOB(). If determining whether or not something is a CharField is too difficult, then we can just wrap each expression part inside a TO_NCLOB if self.output_field is a TextField.

I'll also add some additional tests which combine CharFields and TextFields, where the CharFields are other expressions - like Lower('charfield').

comment:10 by Josh Smeaton, 9 years ago

Has patch: set
Triage Stage: AcceptedReady for checkin

I implemented the solution above, which passes on my local oracle install.

comment:11 by Josh Smeaton <josh.smeaton@…>, 9 years ago

Resolution: fixed
Status: newclosed

In 1cdfbde4db1ac6bbddb2cf8dd5192831bc0f9ce3:

Fixed #23753 -- Oracle failure with Coalesce

Note: See TracTickets for help on using tickets.
Back to Top