This Oracle SQL SELECT shouldn't work. Why does it?-CodePudding

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.

CodePudding user response：

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 fetch....

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 customer_name and last_sales_date.

It's possible that this has been reported and perhaps fixed in recent versions - what is your Oracle version?