Opened 7 years ago

Last modified 7 years ago

#28857 closed Bug

Cast function may generate invalid SQL on PostgreSQL for complex expressions — at Version 3

Reported by: Jurica Železnjak Owned by: nobody
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 (3)

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
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 7 years ago by Jurica Železnjak (previous) (diff)

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.

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