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?

Amphoteric

Since any function to be used within a query should surely be deterministic anyway, you would think that Oracle would have made inline functions deterministic by default. However it seems there were some implementation issues in the first couple of releases, e.g. dbfiddle.uk/… – Plaice

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.

Agnella

Nice catch about it working fine later releases. I can confirm that it works in 21c: dbfiddle.uk/…. – Amphoteric

it works in 19c too – Agnella