2

Can it be considered a bug? This query gives duplicate values despite DISTINCT:

select distinct '1'
from 
(
select *
from dual
connect by level <= 10
)
order by dbms_random.value
fetch first 10 rows only

I understand it creates additional column with random values and uses distinct with two columns, but still I expect to get unique values.

Improve this question
3
  • 1
    That looks like a bug to me-- adding a fetch first n rows only clause shouldn't change the number of rows returned (dbfiddle.uk/EgS0a4FZ). Have you submitted a bug report to Oracle to confirm?
    – Justin Cave
    Jun 2 at 14:57
  • The problem isn't just in fetch.. but in combination order by + fetch, if columns in order by have duplicate values. I haven't submitted a bug. I'm just curious. Group by solves this problem
    – Andy DB Analyst
    Jun 2 at 15:04
  • This is actually a bug. I wrote to AskTom, they confirmed it.
    – Andy DB Analyst
    Jun 8 at 9:26

1 Answer 1

Reset to default
0

The subquery that generates the rows from the dual table is not distinct. The DISTINCT keyword in the outer query only eliminates duplicate rows that result from the subquery, but it does not eliminate duplicate values within each row.

Try this:

SELECT DISTINCT '1'
FROM (
  SELECT DISTINCT *
  FROM dual
  CONNECT BY LEVEL <= 10
)
ORDER BY dbms_random.value
FETCH FIRST 10 ROWS ONLY;
Improve this answer
2
  • Subquery is needed to create a table with multiple rows.
    – Andy DB Analyst
    Jun 2 at 19:48
  • Let's say create table my_table as select * from dual connect by level <= 10 ; select distinct '1' from my_table order by dbms_random.value fetch first 10 rows only ;
    – Andy DB Analyst
    Jun 2 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.