Is this a bug or an error?

Erik has posted really an interesting question on Laurent’s blog. It caught my attention and I thought of discussing it in greater detail over here.

What happens when you divide any number by a zero? You will end up with an error.

SQL> select 1/0 from dual;
select 1/0 from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

That’s true! The SQL query ends up with ORA-01476 error. Now, what happens when I run this query?

“select 1 from dual where exists (select 1/0 from dual)”

Well, even this should result in an error. Ok, let’s run this query:

SQL> select 1 from dual where exists (select 1/0 from dual);

         1
----------
         1

SQL>

Stupid! Isn’t it?

The query executes successfully instead of returning an error. That should be a bug. Wait a minute! Before arriving at any conclusion let’s test it again, but this time in a different way.

I will create a function which prints a message on console and returns a value.

SQL> create or replace function myfunc return number is
2 begin
3 dbms_output.put_line('test function');
4 return(99);
5 end;
6 /

Function created.

SQL> set serveroutput on
SQL> select myfunc from dual;

FUNC
----------
99

test function

When I use the function in a query, it returns the value and also prints the message “test function”.

Now, let’s put the function in the subquery’s Where clause and see what happens.

SQL> select 1 from dual where exists (select * from dual where myfunc = 99);

1
----------
1

test function

The function gets execute and the query returns the desired output.

Now, let’s place the function in the subquery’s Select list instead of “1/0” and test the query.

SQL> select 1 from dual where exists (select myfunc from dual);

1
----------
1
SQL>

As seen the query executes successfully but doesn’t print me the message “test function”. Why?

The answer is again hidden in Oracle documentation. The documentation for EXISTS is as below:

"An EXISTS condition tests for existence of rows in a subquery. It returns TRUE if a subquery returns at least one row.”

It’s clear that Oracle simply tests for the existence of rows in a subquery and will simply ignore the select list. However, the select list goes through syntax and semantics checks. Oracle never fetches the columns listed in the select list and it’s logical also. The EXISTS condition has nothing to do with the select list rather its interest lies in existence of rows.

This is the reason why “select 1 from dual where exists (select 1/0 from dual);” executes successfully.

Thanks for reading ;-)