DEV Community

Cover image for Oracle GROUP BY Integer Literals: Schrödinger's GROUP BY
Tomas
Tomas

Posted on

Oracle GROUP BY Integer Literals: Schrödinger's GROUP BY

#sql #oracle #database

Recently, PawSQL's SQL parser stumbled on a bizarre edge case.

The SQL below ran perfectly fine in an Oracle client — yet PawSQL threw an array-out-of-bounds error during parsing:

SELECT category, count(1) 
FROM products 
GROUP BY CATEGORY
UNION ALL
SELECT 23 as category, 100
FROM product_23
GROUP BY 23
Enter fullscreen mode Exit fullscreen mode

The parser tried to map GROUP BY 23 to the 23rd column in the SELECT list — but the SELECT list only had 2 columns. Boom.

This raises a deeper question: when you write an integer literal in GROUP BY, is it a column position or a plain constant value?

The answer depends on which database — and which Oracle version — you're using.

Before Oracle 23c: an Integer Is Just an Integer

In Oracle versions prior to 23c, GROUP BY 23 treats 23 as an ordinary constant.

📊 What actually happens: all rows land in a single group. Grouping by a constant is equivalent to no grouping at all.

In most cases, this is not what the developer intended — they likely meant "group by the 23rd column in the SELECT list."

Oracle 23c and Later: a New Toggle

Oracle 23c introduced a critical parameter: GROUP_BY_POSITION_ENABLED.

Value Behavior
FALSE (default) Legacy mode — GROUP BY 23 → group by the constant value 23
TRUE Positive integers in GROUP BY are treated as positional references, pointing to the Nth column in the SELECT list

⚡ Same SQL. Same database version. Different parameter settings. Wildly different results.

What About Other Databases?

MySQL and PostgreSQL took a different path — they treat integers in GROUP BY as positional references by default.

That is, GROUP BY 1 means "group by the first column in SELECT" — the exact opposite of Oracle's default behavior.

When migrating across databases, this discrepancy is a ticking time bomb.

💎 Three Recommendations

  1. Never use integer literals in GROUP BY. Use explicit column names or aliases. This is the only way to guarantee consistent behavior across platforms.
  2. If you must use positional references (GROUP BY 1, 2), verify the target database's version and default behavior first.
  3. Oracle 23c users: check your GROUP_BY_POSITION_ENABLED setting — flipping this parameter changes the behavior of every SQL statement that uses positional GROUP BY.

🔧 PawSQL's Safety Net

After fixing the parser bug that uncovered this issue, PawSQL also ships a built-in audit rule — Avoid GROUP BY Ordinal Positions — that automatically catches integer literals in GROUP BY (whether used as constants or position references) before SQL reaches production, and raises a clear warning. Integer constants produce entirely different grouping results across Oracle, MySQL, and PostgreSQL; positional ordinals, while supported by most databases, significantly degrade code readability and cross-platform portability.

Top comments (0)