Oracle where condition priority

Question

I've a table with a varchar column (A) and another integer column(B) indicating the type of data present in A. If B is 0, then A will always contain numeric digits.

So when I form an sql like this

SELECT COUNT(*) FROM TAB WHERE B = 0 AND TO_NUMBER(A) = 123;

I get an exception invalid number.

I expect B = 0 to be evaluated first, and then TO_NUMBER(A) second, but from the above scenario I suspect TO_NUMBER(A) is evaluated first. Is my guess correct?

see http://stackoverflow.com/q/8900631/272742 – turbanoff Mar 29 '12 at 07:39 — turbanoff, Mar 29 '12 at 07:39

score 2 · Accepted Answer · answered Mar 29 '12 at 07:38

In contrast to programming languages like C, C#, Java etc., SQL doesn't have so called conditional logical operators. For conditional logical operators, the right operand is only evaluated if it can influence the result. So the evaluation of && stops if the left operand returns false. For || it stops if the left operand returns true.

In SQL, both operands are always evaluated. And it's up to the query optimizer to choose which one is evaluated first.

I propose you create the following function, which is useful in many cases:

FUNCTION IS_NUMBER(P_NUMBER VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
  X NUMBER;
BEGIN
  X := TO_NUMBER(P_NUMBER);
  RETURN X;
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;

Then you can rewrite your query as:

SELECT COUNT(*) FROM TAB WHERE B = 0 AND IS_NUMBER(A) = 123;

You can also use the function to check whether a string is a number.

"both operands are always evaluated" is not necessarily true. The key point is that the operands (i.e. logical expressions in this case) are evaluated in arbitrary order, not necessarily the order that they appear in the query. — Dave Costa, Mar 29 '12 at 11:39

score 2 · Answer 2 · answered Mar 29 '12 at 11:45

2

Here's a simple way to force the check on B to occur first.

SELECT COUNT(*) FROM TAB
WHERE 123 = DECODE(B, 0, TO_NUMBER(A), NULL);

answered Mar 29 '12 at 11:45

Dave Costa

47,262
8
56
72

turbanoff · Answer 3 · 2012-03-30T03:48:24.563

1

you can use subquery to be confident in the correctness of the result

select /*+NO_MERGE(T)*/ count(*)
from (
    select *
    from TAB
    where B = 0
) T
where TO_NUMBER(A) = 123

edited Mar 30 '12 at 03:48

answered Mar 29 '12 at 07:43

turbanoff

2,439
6
42
99

I don't believe this guarantees the order of evaluation. The optimizer can merge the outer query into the inline view if it thinks it would be more efficient. A `NO_MERGE` hint might prevent this. – Dave Costa Mar 29 '12 at 11:42
The optimizer can merge, but in this case he is obliged to make a short-circuit evaluation, otherwise queries are obtained are not equivalent – turbanoff Mar 29 '12 at 11:52
I think you're wrong about that. Try `SELECT count(*) from ( select * FROM dual where dummy = 'Y') TAB where TO_NUMBER(dummy) = 123` -- that gives me an invalid number error. But if I change to `SELECT /*+ NO_MERGE(tab) */ ...`, it runs without error. – Dave Costa Mar 29 '12 at 11:59
Such a situation is possible only with `dual` the table. Try `create table test_dual as select * from dual` and `SELECT count(*) from ( select * FROM test_dual where dummy = 'Y') TAB where TO_NUMBER(dummy) = 123`, it runs without error – turbanoff Mar 29 '12 at 12:18
1

Try replacing the literal `'Y'` with a function returning that value. In my test the query now gets an invalid number exception again; the optimizer likely chooses to delay the function call in case it is costly. I believe this shows that the optimizer is not under any *obligation* to evaluate the two conditions in a particular order; either order of evaluation is logically equivalent. (Furthermore, in this case, the error still occurs even with the NO_MERGE hint, which surprises me a bit.) – Dave Costa Mar 29 '12 at 15:00

score 0 · Answer 4 · answered Mar 29 '12 at 10:25

0

in your particular example, you can compare varchars like this:

SELECT COUNT(*) FROM TAB WHERE B = 0 AND A = '123';

or if you trust oracle to do the implicit conversions, this should almost always work (i don't know in what cases it won't work, but it would be hard to debug if something went wrong)

SELECT COUNT(*) FROM TAB WHERE B = 0 AND A = 123;

answered Mar 29 '12 at 10:25

Aprillion

21,510
5
55
89

1

Potentially gives different results -- for example, if A='123.0', then the string comparison would fail, but the number comparison would succeed. – Dave Costa Mar 29 '12 at 11:41

score -1 · Answer 5 · answered Mar 29 '12 at 07:36

-1

It should test B = 0 first.
I not sure your guess is correct or not though without seeing sample data.

SELECT *
FROM DUAL
WHERE 1=0 AND 1/0=0

You can try add 1/0 = 0 as the last statement (like this query)
to your query to know if Oracle short circuits the logical operator.

sqlfiddle here.

answered Mar 29 '12 at 07:36

cctan

2,015
3
19
29

1

`1=0` is a constant, the optimizer will reduce it to `false`, unlike `B=0` that can be evaluated first, second or in parallel with the second condition.. – Aprillion Mar 29 '12 at 08:59
@deathApril after further reading, all the decision comes down to the optimizer? so actually there is no way to infer from query without seeing the data? – cctan Mar 29 '12 at 09:38
i don't know whether "there is no way".. it's just that OP's example can't be reduced to your example.. – Aprillion Mar 29 '12 at 10:28

Oracle where condition priority

5 Answers5