Why does deterministic function return unexpected numbers in CONNECT BY LEVEL query?

Question

Test #1:

I have a user-defined function and a CONNECT BY LEVEL query:

with function custom_function(p_id in number) return number 
    is
begin
    return p_id;
end;

select 
    custom_function(level)
from 
    dual
connect by level <= 1000

ID
--
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

...
10 rows of 1000

That non-deterministic function work as expected. It returns sequential numbers, similar to if the function wasn't used.

---

Test #2:

This test is the same as the first, except the function is deterministic:

with function custom_function(p_id in number) return number 
    deterministic is  --this is what I changed
begin
    return p_id;
end;

select 
    custom_function(level) id
from 
    dual
connect by level <= 1000

ID
--
 1
 1
 1
 1
 1
 1
 1
 1
 1
 1
...

10 rows of 1000

That deterministic function doesn't work as expected. Unlike test #1, it doesn't return sequential numbers. If we look at the full resultset, it returns:

• 1 x 100
• 101 x 100
• 202 x 100
• 303 x 100
• 404 x 100
• 505 x 100
• 606 x 100
• 707 x 100
• 808 x 100
• 909 x 100

db<>fiddle

---

Why does the DETERMINISTIC function return unexpected numbers in the CONNECT BY LEVEL query?

Answer 1

AS per oracle docs When the database encounters a deterministic function, it tries to use previously calculated results when possible rather than re-executing the function.In your case there is no scope for using previously calculated results.

It is good programming practice to make functions that fall into these categories DETERMINISTIC:

->Functions used in a WHERE, ORDER BY, or GROUP BY clause

->Functions that MAP or ORDER methods of a SQL type

->Functions that help determine whether or where a row appears in a result set

On a side note this seems to be a bug in 18C since the same query is working fine in 19c and 21c with proper results.