#28857: Cast function may generate invalid SQL on PostgreSQL for complex
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
Has patch: 0 | Needs documentation: 0
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.e3d54313138caf9e4f9239e4e593f438%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
