Cast function may generate invalid SQL on PostgreSQL for complex expressions

[Jurica Železnjak]

In some cases when using the shortcut notation "::", the database gives an error.

GOOD: CAST("sensors_sensordata"."data" #>> '{Temperature}' AS DOUBLE PRECISION) AS "temp"

ERROR: "sensors_sensordata"."data" #>> '{Temperature}'::DOUBLE PRECISION AS "temp"

I've got a response from PostgreSQL people and they've said:

The precedence between the two is different. The equivalent would be:
("sensors_sensordata"."data" #>> '{Temperature}')::DOUBLE PRECISION
In other words, not a bug.

So it seems the as_postgresql() method needs to wrap the expression into parenthesis.
​https://docs.djangoproject.com/en/1.11/_modules/django/db/models/functions/base/#Cast

[Tim Graham]

Please provide steps to reproduce the problem.

[Jurica Železnjak]

models.py

from django.contrib.postgres.fields import JSONField
from django.db import models

class SensorData(models.Model):
    timestamp = models.DateTimeField()
    data = JSONField()

""" sample value for data: {
          "Temperature": 19.9,
          "Relative Humidity": 29.4,
          "PM2.5": 9.8,
          "TVOC": 79.0,
          "CO2": 476.0,
          "CO": 0.0,
          "Air Pressure": 977.1,
          "Ozone": 21.3,
          "NO2": 68.1,
          "Timestamp": 1511770361,
          "DateTime": "2017-11-27 09:12"
        }
"""

from django.db.models.functions import Cast
from django.db.models import Func, Avg, FloatField

class ObjectAtPath(Func):
    function = '#>>'
    template = "%(expressions)s%(function)s'{%(path)s}'"
    arity = 1

    def __init__(self, expression, path, **extra):
        # if path is a list, convert it to a comma separated string
        if isinstance(path, (list, tuple)):
            path = ','.join(path)

        super().__init__(expression, path=path, **extra)


SensorData.objects.all().aggregate(temp=Avg(Cast(ObjectAtPath('data', 'Temperature'), output_field=FloatField())))

I think this should be enough to reproduce the error.

[Tim Graham]

It looks like parentheses could also be added to ObjectAtPath.template. Can you reproduce the problem with any of the built-in database functions? With either solution, it seems like unnecessary parenthesis would be present in some cases. It would be nice to avoid that if possible.