9

I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.

I run this query:

select * from t1 where c1 in (select c1 from t2);

The above query should give an error as c1 is not present in t2. Instead, it returns all the rows from t1. Another version of the above query with delete which can be much more disastrous:

delete from t1 where c1 in (select c1 from t2);

The above query deletes all the rows from t1 when it is just supposed to give an error.

I have noticed this behavior occurs only when the column in the subquery has the same name as the outer one. Meaning,

select * from t1 where c1 in (select c3 from t2);

will throw an error as expected:

 ERROR 1054 (42S22): Unknown column 'c3' in 'field list'

By the way, I have checked for the same issue on PostgreSQL 9.6.3 and the behavior is exactly the same. Any explanation for this strange behavior?

1
  • 1
    These visibility rules are mandated by the SQL standard
    – user1822
    Commented Sep 29, 2018 at 10:09

2 Answers 2

10

I run this query:

select * from t1 where c1 in (select c1 from t2);

The above query should give an error as c1 is not present in t2. Instead, it returns all the rows from t1.

No, the query should not give an error. It's a common mistake (thinking that the c1 in (select c1 from t2) refers to t2. It doesn't due to scope resolution, i.e. how column names are resolved (how it is found which table they are referring to). The query:

select * from t1 where c1 in (select c1 from t2);

Can resolve to three different options:

  • when t2 has a column named c1, it runs as:

      select * from t1 where c1 in (select t2.c1 from t2);
    
  • when t2 does not have a column named c1, but t1 has, it runs as:
    (this is your case!)

      select * from t1 where c1 in (select t1.c1 from t2);
    
  • and when neither t2 nor t1 have a column named c1, it will throw an error:

      select * from t1 where c1 in (select c1 from t2);
    

    -- Error is thrown ("Unknown column c1" or something like that)

Another version of the above query with delete which can be much more disastrous:

delete from t1 where c1 in (select c1 from t2);

The above query deletes all the rows from t1 when it is just supposed to give an error.

For the same reasons, no. The query resolves to and runs as:

delete from t1 where c1 in (select t1.c1 from t2);

so it will delete all rows from t1 (that c1 is not null) as long as t2 table is not empty.


How to avoid these problems?

Always prefix column references with their table names. By doing this, you will always have the result you want or get an error if the column doesn't appear in the table you are prefixing it with.

Your queries should be:

select t1.* from t1 where t1.c1 in (select t2.c1 from t2);

delete from t1 where t1.c1 in (select t2.c1 from t2);

Both of them will throw an error if there is no column c1 in table t2.

10
  • 2
    While I understand the how of this functioning (due to scope resolution), I can only come up with very contrived examples for this behavior to be appear useful in actual databases (as opposed to a very subtle-but-disastrous bug opportunity). Am I missing some obvious use cases for columns in a subquery being selectable from outside the subquery scope? The behavior exists in other SQL platforms, so it seems to be an accepted convention.
    – bsplosion
    Commented Mar 9, 2021 at 17:03
  • 1
    @bsplosion The behaviour is according to SQL standard. All correlated subqueries is the most common use. Eg. writing select * from t1 where c1 in (select c2 from t2); as select * from t1 where exists (select * from t2 where c1 = c2); As to the unexpected behaviours in some occasions, it's usually due to human errors on writing wrong column name. That's why many recommend to always prefix columns with the respective table name. Others (not so common but I've seen it) prefer to have all column names across all tables in a database with different names, so prefixing is not needed. Commented Mar 9, 2021 at 20:17
  • 2
    Thanks for the reply! My comment was more speculation about whether there's a good reason to have that behavior to begin with. Correlated subqueries make sense when outer values are used in filter conditions, though specifically selecting values from the outer query via the subquery is more of a stretch - you could just select them in the outer if that's what you wanted, or leverage an exists clause instead (if trying to do a filtered IN clause). Regardless, I'm sure people smarter than myself have a good reason to allow that behavior, I'm just having trouble seeing it.
    – bsplosion
    Commented Mar 9, 2021 at 20:26
  • Oh, in the select list of a subquery? Yeah, I don't think it is particularly useful there but there isn't good reason to make such an exclusion either. Commented Mar 9, 2021 at 20:30
  • 2
    Ha, and that links back to your even more direct answer to the question about that select behavior, all the way from 2013! So there are ways to use it creatively, albeit carefully. Still seems like the kind of thing that should come with a warning label, but that take is pretty nifty.
    – bsplosion
    Commented Mar 9, 2021 at 20:49
8

It is actualy not an error. You are really referencing the column from outer table.

Because of this "feature" you are able to write correlated subqueries and reference the fields from outer query in the inner query.

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 where c1=c2)

Usually this is used in inner query WHERE clause but there is no reason why it wouldn't be used in the SELECT part. For example you might be wanting to concatenate or add field from outer query with field from inner to get the result.

SELECT * FROM t1 WHERE total IN (SELECT c1+c2 FROM t2 WHERE c2>c1)

The above is confusing so the best way to prevent errors as you describe is to prefix fields with table names to all the fields.

SELECT t1.c1  FROM t1 WHERE t1.c1 IN (SELECT t2.c2 FROM t2)

Now if you mistakenly put t2.c1 in subquery you will get an error.

The other queries will also be more understendable:

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 where t1.c1=t2.c2)

SELECT * FROM t1 WHERE t1.total IN (SELECT t1.c1+t2.c2 FROM t2 WHERE t2.c2>t1.c1)

It is good to build this habit in writing the queries early because in more complex databases there are always fields with the same or similar names (primary keys are usually always "id" etc..) that could lead to serious errors. It is not only problem of wrong deletes but I've seen important reports giving wrong figures for years.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.