Opened 12 days ago

Last modified 12 days ago

#28857 new Bug

Cast function may generate invalid SQL on PostgreSQL for complex expressions

Reported by: Jurica Železnjak Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: postgres, orm
Cc: Triage Stage: Accepted
Has patch: no 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 (5)

comment:1 Changed 12 days ago by Tim Graham

Please provide steps to reproduce the problem.

comment:2 Changed 12 days ago by 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.

Last edited 12 days ago by Jurica Železnjak (previous) (diff)

comment:3 Changed 12 days ago by Tim Graham

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.

comment:4 in reply to:  3 Changed 12 days ago by 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.

comment:5 Changed 12 days ago by Simon Charette

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