PL/SQL Bug

As I was recently working on optimizing and tuning a definitely too large Oracle package I encountered a weird behavior that cost me a bit of hair, before I realized what is the real cause my code wasn’t working as expected. Have a look at this PL/SQL code:

declare
id pls_integer := 12;
query_result pls_integer;
begin
  select c.id into query_result from customers c where c.id = id;
end;

Where, as you might imagine, customers column has PK column id, which I want to stress out because the above query can always only return one record (or zero if matching id doesn’t exist but for the sake of this post let’s assume it does exist). The code looks like it should work, but it returns an following error:

ORA-01422: exact fetch returns more than requested number of rows.

Well apparently when Oracle sees a where clause like this one,

c.id = id

where the “id” is both the name of column AND a PL/SQL variable name, it’s a bit confused and assumes we try to filter on column id by value of the same column which obviously is always true. So it returns all records in a table, much different than what you’d expect. Very unexpected, seems like Oracle was confused and decided to spread the confusion a little bit, share it with developer. I mean couldn’t he throw an error, like:

"ORA-666: Hey, this is confusing, I don’t know what you mean, is Id a variable? Is it a column name? Please, be more specific..” 

Instead of just trying to guess what it might mean and return something.. and my god, who would like to add a where condition that’s always true in the first place ?

Have you seen HowFuckedIsMyDatabase ? The one about Oracle is just brilliant :

Warning: oci_connect(): ORA-$$$$: Insert coin to continue

Btw working code is below, just change the variable name:

declare
v_id pls_integer := 12;
query_result pls_integer;
begin
  select c.id into query_result from customers c where c.id = id;
end;