[Django] #28857: PostgreSQL Cast function shortcut
Django
-------------------------------------+-------------------------------------
Reporter: jzelez | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords: postgres,orm
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 Postgre 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
--
Ticket URL: <https://code.djangoproject.com/ticket/28857>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres,orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Please provide steps to reproduce the problem.
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres,orm | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:2>
Django
expressions
-------------------------------------+-------------------------------------
Reporter: Jurica Železnjak | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres,orm | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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 Postgre 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
New description:
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
--
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:3>
Django
expressions
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres,orm | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jurica Železnjak):
Replying to [comment:3 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:4>
Django
expressions
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:5>
Django
expressions
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres,orm | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => SShayashi
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:6>
Django
expressions
-------------------------------------+-------------------------------------
Reporter: Jurica Železnjak | Owner: SShayashi
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres,orm | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 1.11 => master
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:7>
Django
expressions
-------------------------------------+-------------------------------------
Reporter: Jurica Železnjak | Owner: SShayashi
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* stage: Accepted => Ready for checkin
Comment:
[https://github.com/django/django/pull/9552 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:7>
Django
expressions
-------------------------------------+-------------------------------------
Reporter: Jurica Železnjak | Owner: SShayashi
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: postgres,orm | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"27557a7a99ab1ad032c699dc01e114a5e6504b0a" 27557a7a]:
{{{
#!CommitTicketReference repository=""
revision="27557a7a99ab1ad032c699dc01e114a5e6504b0a"
Fixed #28857 -- Fixed invalid SQL when using Cast with complex expressions
on PostgreSQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28857#comment:8>