CODEWITHSUNDEEP
sqloracle-databaseplsqlshort-circuiting
Brian Koser
Brian Koser

Reputation: 449

No short-circuit OR with an Oracle function?

To allow an Super User/Admin to log in to my system, I am running (a larger version of) this query:

Select *
  From mytable
 Where (:id = 'Admin' Or :id = mytable.id);

If I pass a user id I get all the data for that user; if I pass the string 'Admin' I get all the data. This works because Oracle's OR is a short-circuit operator.

However, if I make 'Admin' a package constant and get it with a function, like this

Select *
  From mytable
 Where (:id = mypackage.GetAdminConstant Or :id = mytable.id);

I get ORA-01722: invalid number when I pass 'Admin'.

Why does OR lose its short-circuit aspect when I introduce a function?

Upvotes: 1

Views: 1926

Answers (2)

Rene
Rene

Reputation: 10541

Better use 2 bind variables.

Select *
  From mytable
 Where (:admin = 'Admin' Or (:admin is null and :id = mytable.id));

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

It doesn't lose the short-circuit aspect. But SQL is not a procedural language, and there is no guarantee of the order of evaluation of multiple predicates.

In C, if you write a || b, you know that a will be evaluated first, then b will be evaluated only if necessary.

In SQL, if you write a OR b, you know only that either a or b will be evaluated first, and that the other expression (at least in Oracle) will be evaluated only if necessary.

Looking at the execution plan for the two queries may give some indication of the order of evaluation, or it may not.

I would guess that, in your first case, Oracle can see that the first expression will have the same value for every row, so evaluates it first. When you change to the second case, Oracle now sees a function that could have different results each time it is evaluated, so it will have to check each row, so it tries to do the simple equality check on a column before doing the function call.

I wonder if you would get different results if you marked the function DETERMINISTIC so Oracle would know that it is essentially a constant.

Upvotes: 6

Related Questions