G-3182

🆓

Error

Always specify column names/aliases instead of positional references in GROUP BY clauses.

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v23.3.0 +)

Reason

If you use a numeric literal in the group by clause in an Oracle Database prior to version 23c, then this literal is not required. It is simply a constant.

Starting with Oracle Database 23c, it is possible to use a literal in the group by clause to refer to a column name or column alias in the select list. However, this only works if the group_by_position_enabled parameter is set to true. In any case, it is not convenient for the readers of the code to have to count the columns in the select list to know how the result is grouped.

Since the meaning of a literal depends on the configuration and database version, the intention is unclear and might lead to an incorrect result.

Example

Non-Compliant Example

select job_id
      ,sum(salary) as sum_salary
  from employees
 group by job_id,2
 order by job_id;

Issues

Line	Column	Message
4	18	Positional reference in GROUP BY clause.

Compliant Solution - ★★★★★

select job_id
      ,sum(salary) as sum_salary
  from employees
 group by job_id
 order by job_id;

References

similar to SQLFluff ambiguous.column_references
The scope of ambiguous.column_references is ORDER BY and GROUP BY, forcing either position numbers or column names.
same as Trivadis G-3182