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 )
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 , 7 years ago
comment:2 by , 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.
follow-up: 4 comment:3 by , 7 years ago
Description: | modified (diff) |
---|---|
Summary: | PostgreSQL Cast function shortcut → Cast 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 by , 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 , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:6 by , 7 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Please provide steps to reproduce the problem.