Skip to main content

Weird SQL Behavior? No. And the Importance of Table Aliases.

I received this email yesterday with a question about "weird SQL behavior".
I wrote a SQL delete statement with a select statement in its where clause. I made a mistake and forgot to create a column in the table that I used in the subquery. But the table from which I am deleting has a column with the same name. I did not get an error on compilation. Why not? There is no column with this name in this table in the where-clause. As a result I deleted all the rows in the table. 
That last sentence - "I deleted all the rows in the table." - has got to be one of the worst things you ever say to yourself as an Oracle Database developer. Well, OK, there are worse, like "I truncated a table in production accidentally". Still, that's pretty bad.

So is that "weird" SQL behavior? Should the DELETE have failed to compile? Answers: No and No. Let's take a look at an example to drive the point him clearly.

I create two tables:

CREATE TABLE houses
(
   house_id     INTEGER PRIMARY KEY,
   house_name   VARCHAR2 (100),
   address      VARCHAR2 (1000)
)
/

CREATE TABLE rooms
(
   room_id     INTEGER PRIMARY KEY,
   house_id    INTEGER,
   room_name   VARCHAR2 (100),
   FLOOR       INTEGER,
   CONSTRAINT rooms_house FOREIGN KEY (house_id) REFERENCES houses (house_id)
)
/

Then I populate them with data:

BEGIN
   INSERT INTO houses
        VALUES (1, 'Castle Feuerstein', 'Rogers Park, Chicago');

   INSERT INTO rooms
        VALUES (100, 1, 'Kitchen', 1);

   INSERT INTO rooms
        VALUES (200, 1, 'Bedroom', 2);

   COMMIT;
END;
/

OK, time to delete. I write the block below. Notice that my subquery selects the room_id from the houses table. There is no room_id column in houses, so the DELETE should fail to compile, right?

BEGIN
   DELETE FROM rooms
         WHERE room_id = (SELECT room_id FROM houses);

   DBMS_OUTPUT.put_line ('Deleted = ' || SQL%ROWCOUNT);
END;
/

Wrong! Instead, I see Deleted = 2. All the rows in the rooms table deleted. That's some pretty weird SQL, right? Wrong again!

Note: since there are no PL/SQL bind variables in the SQL statement, we don't need to talk at all about name capture in PL/SQL, but you should also be clear about that as well, so here's a link to the doc).

When the SQL engine parses this statement, it needs to resolve all references to identifiers. It does so within the scope of that DELETE statement. But wait, that DELETE statement has within it a sub-query.

So here's how it goes:

1. Does houses have a room_id column?
2. No. OK, does rooms have a room_id column?
3. Yes, so use that.
4. OK, well that essentially leaves us with "room_id = room_id"
5. All rows deleted.

It's easy to verify this flow. Let's add a column named "ROOM_ID" to houses:

ALTER TABLE houses ADD room_id INTEGER
/

Now, when I try to execute that same block of code that performs the delete, I then see Deleted = 0.

No rows were deleted, and that's because the value of houses.room_id is NULL in every row in the table.

The developer who sent me this email was confused and naturally thought that maybe there was something wrong or weird with SQL.

Now, don't get me wrong: Oracle SQL surely has its share of bugs. But I think that after 35 years, you can pretty well assume that for any basic, common statements, the language is pretty solid. So if you get confused about the result of a SQL statement you should:

First, make sure you understand how the language works.

Second, fully qualify all references inside your SQL statement.

Writing a SQL statement like this:

DELETE FROM rooms
 WHERE room_id = (SELECT room_id FROM houses);

Is akin to writing an arithmetic expression like this:

var := 12 * 15/ 3 - 27 + 100;

Believe this: the compiler NEVER GETS CONFUSED by code like this. Only us humans.

So with arithmetic expressions, you should always use parentheses to make your intent clear (and maybe fix a bug or two, as my parentheses do, below):

var := ((12 * 15) / 3) - (27 + 100);

and always fully qualify references to columns in your SQL statements, using table aliases, as in:

DELETE FROM rooms r
 WHERE r.room_id = (SELECT h.room_id FROM houses h);

This very simple step not only removes confusion, but also makes it much easier for developers "down the line" to maintain your complex SQL statements. It also reduces the chances for bugs to creep into said SQL statements.

Comments

  1. Thanks Steven for that explanation but I miss something here: If we add room_id to the table houses, why don't we get an:

    ORA-00918: column ambiguously defined

    error? It looks like for sub-selects Oracle (or even SQL standard) has a kind of precedence in how it is picking attributes? Can you please explain how this works?

    ReplyDelete
  2. Dear Anonymous,

    Regarding "Can you please explain how this works?" - well, I kinda thought that's what I had done, as in:

    1. Does houses have a room_id column?
    2. No. OK, does rooms have a room_id column?
    3. Yes, so use that.
    4. OK, well that essentially leaves us with "room_id = room_id"
    5. All rows deleted.

    and when the room_id column is added to houses, the "search" for a resolution to the identifier "room_id" is completed with step 1.

    ReplyDelete
  3. Playing devil's advocate: "...so the DELETE should fail to compile, right?..." The key word being SHOULD, so yes, it SHOULD fail to compile. That Oracle doesn't treat this as an error after 35 years doesn't make it correct. If that same subquery is executed as a standalone query, Oracle "knows" that "room_id" is an "invalid identifier," raising an ORA-00904. Either the statement is valid or it isn't. Is it really surprising that there's an expectation that a reference to a non-existent column (perhaps a typo) would be an error, especially if coming from another database system where it is an error?

    ReplyDelete
    Replies
    1. "Either the statement is valid or it isn't." I guess you have a different view of what statement means. The subquery is not a statement in and of itself. It is a SUBquery within another query, thus it is parsed within a broader scope, and the reference is resolved.

      I am sorry you don't like this behavior.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel