Unintended SQL

7/9/2008

While there are standards for SQL, databases still have differences in how they interpret SQL. Recently I stumbled onto a small difference between SQL Server and Firebird that can cause unfortunate side effects.

Take this SQL as an example. It contains a sub select. These are not real table names.

update Foo set FooID = null where FooID in
    (Select FooID from Widgets where WidgetsID = 1000);

Foo has a FooID field, but Widgets does not. On Firebird this generates a SQL error because Widgets does not have a FoodID field. On SQL Server however the sub select executes. In my case this returned 0 rows, but executed without errors.

If the sub select is run separately:

Select FooID from Widgets where WidgetsID = 1000

Now it fails with a SQL error on both SQL Server and Firebird.

Why?

This puzzled me for a long time. SQL server though treats sub selects differently than Firebird. From SQL Server documentation:

The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

If a field cannot be found in the tables from the sub select, SQL will then use fields from the parent query. This is certainly very useful to use fields of the parent query in the where clause of the sub select, but I am not sure it is so useful to have them referenced in the select statement of the sub select.

I am curious how Oracle, DB2 and others handle this. I am also curious if Firebird explicitly removed them from being able to be used in selects for this reason.

Credits

Thanks to Scott Golightly for the answer to this.

<< Previous Entry  Next Entry >>

Use my contact form to contact me directly.