0

I'm looking at a script I didn't write here. and it looks something like this:

SELECT
...
AND (
       A.FIELD IN
       (
        ...
        ...
       )
       OR B.FIELD IN
       (
        ...
        ...
       ) 
    )
...

On it's own the script runs fine. No errors. But when I change it to:

SELECT
...
AND B.FIELD IN
       (
        ...
        ...
       ) 
...

Oracle throws an ORA-01722: invalid number. How come it doesn't throw it for the first query though?

Updated

A.FIELD is a number B.FIELD is a VARCHAR2

The value is working against is a number. So I understand and agree with the error, but I wondered why it isn't thrown in the first query. But the second it is.

Jonnny
  • 4,939
  • 11
  • 63
  • 93
  • 2
    Hard to tell without seeing any of the values in the `B.FIELD`'s `IN`. – xlecoustillier Oct 20 '14 at 13:07
  • 3
    I would assume the first statement isn't evaluating B.Field section because A.Field is true in the OR statement, although I'm not sure if SQL evaluates statements lazily. – Grice Oct 20 '14 at 13:09
  • @X.L.Ant I've updated my Q. I understand why the error occurs, but not why it doesn't occur in the first statement but does in the second? How does Oracle execute a query like this – Jonnny Oct 20 '14 at 13:12
  • @JGrice I wondered about this, but for my own interest and understanding thought I'd ask here – Jonnny Oct 20 '14 at 13:13
  • I guess @JGrice is right. Oracle doesn't guarantee short circuiting in SQL, but I can't think of any reason besides that. – xlecoustillier Oct 20 '14 at 13:14

1 Answers1

2

From Oracle's docs:

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error.

See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref481 for further explanation and examples.

They also have this separate documentation specifically mentioning that for OR: http://www.oracle-base.com/articles/misc/short-circuit-evaluation-in-plsql.php

Troy Gizzi
  • 2,480
  • 1
  • 14
  • 15
  • 2
    I agree, but this is an SQL query, not PL/SQL. – xlecoustillier Oct 20 '14 at 13:17
  • 1
    [This fiddle](http://sqlfiddle.com/#!4/a6611/3) reproduces the error. The short circuiting seems to work here, but I'm quite surprised this even works as the types are incompatible. Unless finding some official documentation about this particular case in SQL, I would not rely too much on this. – xlecoustillier Oct 20 '14 at 13:22
  • 3
    PL/SQL short-circuits, but [SQL may or may not - it depends...](http://stackoverflow.com/a/8900752/266304). – Alex Poole Oct 20 '14 at 13:26
  • @X.L.Ant Thanks, I just looked at the fiddle and replicated the error. I have not done a lot with Oracle really, so thought I should ask those who are far more experienced with it. – Jonnny Oct 20 '14 at 13:35
  • @X.L.Ant Oracle automatically converts types as necessary, so `varchar2` and `number` are not per se incompatible. (Of course you could expect that Oracle automatically checks all concerned `varchar2` literals if they can be converted, but apparently the special case of literals is not handled differently than other `varchar2` expressions) – Erich Kitzmueller Oct 20 '14 at 15:35