GROUP BY with ORDER BY and LIMIT can lead to invalid error message
Details
| Detail name | Value |
|---|---|
| Changelog Number | 17916 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 7.1.23, Exasol 8.23.0 |
| Resolution Date | 2023-09-11 |
Description
A query with GROUP BY may report the error ' not a valid GROUP BY expression' although the GROUP BY is valid if the following conditions are met:
- The query contains GROUP BY.
- The query contains ORDER BY.
- The query contains LIMIT.
- The GROUP BY consists only of referenced columns and does not contain expressions.
- The GROUP BY contains at least three referenced columns belonging to the same table.
- One of these columns is either:
- The order by contains an expression on a column that is not the primary key/unique column, but belongs to the table of the primary key/unique column.
- A primary key of the table.
- Part of an existing index and there are no duplicate elements in the column.
Preparation
CREATE OR REPLACE TABLE test.t AS VALUES (1,2,3) AS t(a,b,c); ENFORCE GLOBAL INDEX ON test.t(a);
Example
SELECT c FROM test.t GROUP BY a,b,c ORDER BY UPPER(c) LIMIT 100;
Workaround
Put the query into a subselect with ORDER BY FALSE:
SELECT * FROM (SELECT c FROM test.t GROUP BY a,b,c ORDER BY FALSE) ORDER BY UPPER(c) LIMIT 100;
Fix
Those queries run as expected.
