1

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?

Brian Koser
  • 439
  • 1
  • 12
  • 28
  • possible duplicate of [Do modern DBMS include short-circuit boolean evaluation?](http://stackoverflow.com/questions/6960767/do-modern-dbms-include-short-circuit-boolean-evaluation) – Derek Sep 01 '11 at 18:33
  • Not really. The documentation I can find says that it *is* short-circuit; this seems to be a exception to the rule, and I'd like to know why. – Brian Koser Sep 01 '11 at 18:42
  • What is `:id` (is it a bind???) and what type is `mypackage.GetAdminConstant`? – Marius Burz Sep 01 '11 at 18:44
  • Yes, `:id` is a bind variable. `mypackage.GetAdminConstant` returns a Varchar2. – Brian Koser Sep 01 '11 at 18:54

2 Answers2

6

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • When I try to access the constant directly (schema.package.constant) I get `MYCONSTANT is not a procedure or is undefined`. What am I doing wrong? – Brian Koser Sep 01 '11 at 18:53
  • Ah, I see that the documentation I linked to above is for PL/SQL, not SQL. And I checked my execution plan: you were right, it switches the order of evaluation between the two queries. Unfortunate. – Brian Koser Sep 01 '11 at 19:02
  • @Brian -- sorry, it looks like it is actually not possible to directly use a package constant in SQL. Removed that suggestion from my answer. – Dave Costa Sep 01 '11 at 20:07
  • That's too bad. Marking the function as DETERMINISTIC doesn't do it either. I guess I'll have to find a different solution. – Brian Koser Sep 01 '11 at 20:24
  • What about: to_char(:id)=mypackage.GetAdminConstant – Rene Sep 05 '11 at 08:45
  • @Rene -- I think that the invalid number error is being raised when evaluating the other condition, `:id = mytable.id`, because the bind value is a string. – Dave Costa Sep 05 '11 at 11:37
1

Better use 2 bind variables.

Select *
  From mytable
 Where (:admin = 'Admin' Or (:admin is null and :id = mytable.id));
Rene
  • 10,391
  • 5
  • 33
  • 46