Opened 7 years ago

Closed 7 years ago

#28857 closed Bug (fixed)

Cast function may generate invalid SQL on PostgreSQL for complex expressions

Reported by: Jurica Železnjak Owned by: SShayashi
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: postgres, orm
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description (last modified by Tim Graham)

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

Change History (8)

comment:1 by Tim Graham, 7 years ago

Please provide steps to reproduce the problem.

comment:2 by Jurica Železnjak, 7 years ago

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.

Version 0, edited 7 years ago by Jurica Železnjak (next)

comment:3 by Tim Graham, 7 years ago

Description: modified (diff)
Summary: PostgreSQL Cast function shortcutCast function may generate invalid SQL on PostgreSQL for complex expressions

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.

in reply to:  3 comment:4 by Jurica Železnjak, 7 years ago

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.

comment:5 by Simon Charette, 7 years ago

Triage Stage: UnreviewedAccepted

comment:6 by SShayashi, 7 years ago

Owner: changed from nobody to SShayashi
Status: newassigned

comment:7 by Tim Graham, 7 years ago

Has patch: set
Triage Stage: AcceptedReady for checkin

comment:8 by Tim Graham <timograham@…>, 7 years ago

Resolution: fixed
Status: assignedclosed

In 27557a7a:

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

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