id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27632	Oracle backend fails to execute a query with an aggregation that contains an expression in the GROUP BY.	Josh Smeaton	Mariusz Felisiak	"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."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	oracle	josh.smeaton@… felisiak.mariusz@… me@…	Accepted	0	0	0	0	0	0