I am debugging a query in Oracle 19c that is trying to sort a
SELECT DISTINCT result by a field not in the query. (Note: This is the wrong way to do it. Do not do this.)
This query is trying to return a unique list of customer names sorted with the most recent sale date first. It returns an expected error, “ORA-01791: not a SELECTed expression”.
SELECT DISTINCT CUSTOMER_NAME FROM SALES ORDER BY LAST_SALE_DATE DESCENDING;
It returns an error because the query is trying to order the result by a field that has not been selected. This makes sense so far.
However, if I simply add
FETCH FIRST 6 ROWS ONLY to the query, it does not return an error (although the result is not correct so do not do this). But the question is why Oracle does not return an error message?
SELECT DISTINCT CUSTOMER_NAME FROM SALES ORDER BY LAST_SALE_DATE DESCENDING FETCH FIRST 6 ROWS ONLY;
Why does adding
FETCH FIRST 6 ROWS ONLY make this work?
Added: The incorrect query will return duplicates if there are multiple records with the same name and date. A search for something like “sql select distinct order by another column” will show several correct ways to do this.
The explain plan for the query shows what is happening.
The parser rewrites the
fetch... clause – it adds analytic
row_number to the
select list, and uses an outer query with a filter on row numbers. It pushes the
unique directive (the
distinct directive from your
select) into the subquery, which is not a valid transformation; I would consider this an obvious bug in Oracle’s implementation of
The explain plan shows the step where the parser creates an inline view where it applies
unique and it adds analytic
row_number(). It doesn’t show the projection (what columns are included in the view), and – critically – what it applies
unique to. A little bit of experimentation suggests the answer though: it applies
unique to the combination of
It’s possible that this has been reported and perhaps fixed in recent versions – what is your Oracle version?