#27632: Oracle backend fails to execute a query with an aggregation that
contains
an expression in the GROUP BY.
-------------------------------------+-------------------------------------
Reporter: Josh | Owner: nobody
Smeaton |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords: oracle
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Oracle fails on queries where there is an expression containing replace
parameters in the SELECT list and GROUP BY list, because the database
appears to check the SELECT and GROUP BY match **before** doing parameter
substitution.
Parameters in cx_Oracle follow the form `:arg0` and `:arg1` or named as
`:price` and `:discount`. Since the arguments in the SELECT and GROUP BY
have a different argument number, the database rejects the query with the
following error:
{{{
*** django.db.utils.DatabaseError: ORA-00979: not a GROUP BY expression
}}}
Failing test for aggregation_regress:
{{{
Book.objects.annotate(
discount_price=F('price') * 0.75
).values(
'discount_price'
).annotate(sum_discount=Sum('price'))
}}}
SQL and parameters:
{{{
'SELECT
("AGGREGATION_REGRESS_BOOK"."PRICE" * :arg0) AS "DISCOUNT_PRICE",
SUM("AGGREGATION_REGRESS_BOOK"."PRICE") AS "SUM_DISCOUNT"
FROM "AGGREGATION_REGRESS_BOOK"
GROUP BY ("AGGREGATION_REGRESS_BOOK"."PRICE" * :arg1),
"AGGREGATION_REGRESS_BOOK"."NAME"
ORDER BY "AGGREGATION_REGRESS_BOOK"."NAME" ASC'
args: (0.75, 0.75)
}}}
Django can't really do a whole lot here without changing parameter
substitution to use named arguments, or by somehow keeping track of what
parameter positions are bound to which expression, and reusing the
argument names when the Oracle backend replaces the "%s" placeholders with
the ":argN" format that cx_Oracle requires. In either case, it'd involve a
very large refactoring, and even then I'm not sure how feasible it would
be.
I *think* this is a problem that should be solved by Oracle. If anyone is
able to find any references to this bug in Oracle documentation or systems
I'd love to see it. As I don't work for a company using Oracle anymore,
I'm not able to utilise support to investigate further.
--
Ticket URL: <https://code.djangoproject.com/ticket/27632>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.8310a2885199fc71b9872627075bdeef%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
