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

class SensorData(BaseModel):
    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.

[Jurica Železnjak]

Replying to 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.

True, but that's not the point. The point is that with the default implementation of Cast function it would work fine always, but with the overridden postgresql-specific it does not.
ObjectAtPath does its own thing like it should - fetches a value by key from JSON field.

    def as_postgresql(self, compiler, connection):
        # CAST would be valid too, but the :: shortcut syntax is more readable.
        return self.as_sql(compiler, connection, template='%(expressions)s::%(db_type)s')

But the reasoning for the built-in shortcut postgresql-specific method is that it would be more readable - while that's true - it leads to the mentioned problem.

[Tim Graham]

​PR

[Tim Graham <timograham@…>]

In 27557a7a:

Fixed #28857 -- Fixed invalid SQL when using Cast with complex expressions on PostgreSQL.