Skip to content

G-3182: Always specify column names/aliases instead of positional references in GROUP BY clauses.

Blocker

Reliability

Restriction

Oracle Database 23c

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 (bad)

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

Example (good)

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