Skip to Main Content
  • Questions

  • WITH Function Returns Results that Do Not Meet Constraint

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Steve.

Asked: June 23, 2019 - 4:12 am UTC

Last updated: June 25, 2019 - 3:56 pm UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi Tom et al,

Code below uses a function declared inside of a WITH clause. Function calls dbms_random and returns the result to my WITH table. Final SELECT queries WITH table using a constraint, but I get results that do not meet the constraint. The final "where getrnd = 10" is being ignored. It's as though code execution is exiting out of context.

1) Can you tell me why this is happening?
2) Can you tell me what I am doing wrong? As far as I can tell, this *should* work.
3) Not a question, but just check out how the first function declaration (using parameter) returns results more often than no-parameterized declaration. Very odd.

Note, not looking for alternatives or workarounds - just want to know why my code doesn't do what I think it should do.

Thanks Tom!

with  
function  
  --- many more times we get a result set (we aren't even using the parameter)  
  getrand(pval in number) return number is

--  --- many fewer times we get a result set (yes, keep trying, it does happen)  
--  getrand                 return number is

begin  
  return round(dbms_random.value(0,20));  
end;  
  
my_rnd as  
  (  
    select object_id, getrand(object_id) getrnd from all_objects  
--    select object_id, getrand            getrnd from all_objects  
  )  
  
select * from my_rnd where getrnd = 10;





with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/ikk0v71uikuvrbq9tebrpt88a

and Chris said...

Yep, something strange is happening here...

But the problem stems from this being a non-deterministic function.

The database may run the function twice per row. Once in the where clause. And again in the select. Each call could give different results. Leading to the "unexpected" behaviour you see.

You can see this because the filter in the plan happens against the table itself. 

create table t as 
  select level c1 from dual
  connect by level <= 100;
  
with
function  
  getrand(pval in number) return number is 
begin  
  return round(dbms_random.value(0,20));  
end;  

my_rnd as  
  (  
    select c1, getrand(c1) getrnd
    from   t
  )  
  
select * from my_rnd where getrnd = 10; 
/

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PREDICATE'));

Plan hash value: 1601196873                                                
                                                                           
----------------------------------                                         
| Id  | Operation         | Name |                                         
----------------------------------                                         
|   0 | SELECT STATEMENT  |      |                                         
|*  1 |  TABLE ACCESS FULL| T    |                                         
----------------------------------                                         
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - filter("GETRAND"("C1")=10)


You can avoid this behaviour by materializing the subquery that calls the random function first. This ensures you're selecting and filtering on the same value.

UPDATED 25 June 2019: Behaviour is "expected" (if weird), not a bug as previously stated.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Chris, but...

Steve, June 24, 2019 - 4:40 pm UTC

--- Chris, I was able to repeat the behaviour using a table I created:
drop table my_tab;
create table my_tab as select object_id from all_objects fetch first 100 rows only;
select * from my_tab;

with
function
getrand(pval in number) return number is
begin
return round(dbms_random.value(0,20));
end;

my_rnd as
(
select object_id, getrand(object_id) getrnd from my_tab
)

select object_id, getrnd from my_rnd where getrnd = 10;

Chris Saxon
June 25, 2019 - 7:47 am UTC

Nice investigation, thanks for sharing this.

My LiveSQL link for code using a table

Steve, June 24, 2019 - 4:41 pm UTC

https://livesql.oracle.com/apex/livesql/s/ikt9p1we9lm5d7gsbhwnm3gf1

Good observation

siva, June 25, 2019 - 1:43 pm UTC

Hi Steve/Chris,

I am adding my observations here .

1.Adding some character to function result , we are getting wrong result .

with
function
getrand(pval in number) return number is
begin
return round(dbms_random.value(0,20));
end;
my_rnd as
(
select object_id, getrand(object_id)||'_x' getrnd from my_tab
)
select object_id, getrnd from my_rnd where getrnd = '10_x';


2.Adding analytical function to the function . Here we are getting the expected result.

with
function
getrand(pval in number) return number is
begin
return round(dbms_random.value(0,20));
end;
my_rnd as
(
select object_id, max( getrand(object_id)) over ( partition by object_id ) getrnd from my_tab
)
select getrnd , count(*) from my_rnd --where getrnd = 10;
group by cube(getrnd)

Use /*+ MATERIALIZE */ hint

siva, June 25, 2019 - 2:48 pm UTC

Hi Steve ,

By using /*+ MATERIALIZE */ hint we can get the desired result .

I think this is a feature . While using procedure and function in with clause we have to use this hint.


with
function
getrand(pval in number) return number is
begin
return round(dbms_random.value(0,20));
end;
my_rnd as
(
select /*+ MATERIALIZE */ object_id, getrand(object_id) getrnd from my_tab
)
select object_id, getrnd from my_rnd where getrnd = 10
Chris Saxon
June 25, 2019 - 3:29 pm UTC

Yes, materializing the results forces the database to compute the subquery first. Then filter on the results.

So the database can't push the predicate inside the view and end up calling the function twice for one row.

Just one more thing

Steve, June 25, 2019 - 3:12 pm UTC

Thanks for your contributions Siva. Your analytics code shows that all records are being processed. I too discovered this when, after removing the getrnd = 10 constraint, I get 100 records back (this is using the my_tab table I created). So, I submit that the constraint actually works - that the few records we get back when using the constraint are *actually* the ones where the result of function call is in fact 10. Problem is that the column value displayed doesn't show 10.

It makes some sense that materializing the result before displaying would help. However, the original problem remains. I think the next thing to investigate is if the problem exists only with call to dbms_random.value. I tried two other things and they worked fine - returning a hard coded value, and doing a for loop to sum the digits of a number. I wonder if a call to another package function or procedure could result in similar behaviour.

Thanks again!
Chris Saxon
June 25, 2019 - 3:34 pm UTC

I've amended my answer. The problem is that dbms_random is non-deterministic. And - because the database pushes the where clause inside the subquery - it calls it twice for each row.

Once in the where clause. And again in the select clause. These can (and as you've found do) give different results.

Materializing the subquery avoids this problem by forcing the database to compute the function for each row and storing the output. Then applying the where on its output.

The upshot is: whenever you call a non-deterministic function in SQL, unexpected things can happen!

Ah, Thanks Chris

Steve, June 25, 2019 - 3:35 pm UTC

I just saw your update. Yes, it's weird. Being non-deterministic explains why the different results when the filter is processed, but it doesn't explain why the filter calls the function again. Why does that happen?

Yes, materializing the result before filtering makes sense, but it feels like a workaround. I told Oracle to filter on the result, and it didn't. Instead, it's like it used the "definition" of the getrnd column, "getrand(object_id)", to process the filter. I just tried to cast the column to number in the WITH table - no help.

Being non-deterministic explains the different display values. But what explains the second call to the function when I told it to use the result of the first call? Sure feels like a bug. Either way, I'll file it in my gotchas file.

Thanks Chris and Siva!

Chris Saxon
June 25, 2019 - 3:55 pm UTC

The database processes the where clause before the select clause. So it's going:

where random_fn = val

Then for the rows that pass this check, you have:

select random_fn

So that's two separate invocations of the function.

I told Oracle to filter on the result, and it didn't

No. A subquery is NOT a way of telling the database "do this first". It's just a logical construct to make the statement easier to write/understand.

The optimizer's free to rearrange your SQL statements to what it assumes are logically equivalent statements. But this only holds for deterministic functions!

IMHO, Non-deterministic is not the problem

Steve, June 25, 2019 - 3:53 pm UTC

It's exactly why I told O to use the result column getrnd. I knew calling the function again would give a different result. Using materialize works, but I might object on a very large result set.

So pushing the predicate is why the function gets called again? I tried all the applicable hints, esp. NO_PUSH_PRED, to no avail. Ugh!

Still, I appreciate your help. Bug or not, it's a gotcha in my book.
Chris Saxon
June 25, 2019 - 3:56 pm UTC

So pushing the predicate is why the function gets called again?

Yep.

siva, June 27, 2019 - 10:50 am UTC

Hi Steve ,

Below query is also working.

with
function
getrand(pval in number) return number is
begin
return round(dbms_random.value(0,20));
end;
my_rnd as
(
select object_id, (select getrand(object_id) from dual ) getrnd from my_tab
)
select object_id, getrnd from my_rnd where getrnd = 11;

The similar situation Connor explained in below link .

https://connor-mcdonald.com/2018/06/07/call-me-many-many-times/
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.