Sunday, September 18, 2011

Division by Zero (again)



I have looked at division by zero in earlier posts and I recently received the following E-mail about it from Laurent Schneider who, incidentally, wrote the book advertised above:

... au fait, tu savais que 

SELECT * FROM DUAL WHERE EXISTS (SELECT 1/0 FROM DUAL)

ne retournait pas d'erreur ?

Bon week-end
Laurent

So I decided to give it a try and it's true! Oracle does not check that the select in the subquery can return a valid value. It only seems to be concerned with whether any where clause included in the statement is satisfied:

SQL> SELECT * FROM DUAL WHERE EXISTS
  2  (SELECT 1/0 FROM DUAL)
  3  /

D
-
X

SQL> SELECT * FROM DUAL WHERE EXISTS
  2  (SELECT 1/0 FROM DUAL WHERE 1=2)
  3  /

no rows selected

SQL> SELECT * FROM DUAL WHERE EXISTS
  2  (SELECT 1/0 FROM DUAL WHERE 1=1)
  3  /

D
-
X

No comments: