Subquery gives no error for a non-existing column with the same name as in the outer query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

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?

edited 37 mins ago

a_horse_with_no_name

36.5k769108

asked 1 hour ago

Alok Patwal

112

New contributor

1

These visibility rules are mandated by the SQL standard
â€“Â a_horse_with_no_name
37 mins ago

add a commentÂ |Â

up vote
2
down vote

favorite

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?

edited 37 mins ago

a_horse_with_no_name

36.5k769108

asked 1 hour ago

Alok Patwal

112

New contributor

1

These visibility rules are mandated by the SQL standard
â€“Â a_horse_with_no_name
37 mins ago

add a commentÂ |Â

up vote
2
down vote

favorite

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?

edited 37 mins ago

a_horse_with_no_name

36.5k769108

asked 1 hour ago

Alok Patwal

112

New contributor

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?

sql subquery

edited 37 mins ago

a_horse_with_no_name

36.5k769108

asked 1 hour ago

Alok Patwal

112

New contributor

edited 37 mins ago

a_horse_with_no_name

36.5k769108

asked 1 hour ago

Alok Patwal

112

New contributor

edited 37 mins ago

a_horse_with_no_name

36.5k769108

edited 37 mins ago

a_horse_with_no_name

36.5k769108

edited 37 mins ago

a_horse_with_no_name

36.5k769108

asked 1 hour ago

Alok Patwal

112

New contributor

asked 1 hour ago

Alok Patwal

112

asked 1 hour ago

Alok Patwal

112

New contributor

Alok Patwal is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

1

These visibility rules are mandated by the SQL standard
â€“Â a_horse_with_no_name
37 mins ago

add a commentÂ |Â

1

These visibility rules are mandated by the SQL standard
â€“Â a_horse_with_no_name
37 mins ago

These visibility rules are mandated by the SQL standard
â€“Â a_horse_with_no_name
37 mins ago

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
3
down vote

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.

answered 41 mins ago

Loki

311

New contributor

add a commentÂ |Â

up vote
1
down vote

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 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.

edited 29 mins ago

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Alok Patwal is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218871%2fsubquery-gives-no-error-for-a-non-existing-column-with-the-same-name-as-in-the-o%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
3
down vote

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)

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)

answered 41 mins ago

Loki

311

New contributor

add a commentÂ |Â

up vote
3
down vote

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)

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)

answered 41 mins ago

Loki

311

New contributor

add a commentÂ |Â

up vote
3
down vote

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)

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)

answered 41 mins ago

Loki

311

New contributor

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)

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)

answered 41 mins ago

Loki

311

New contributor

answered 41 mins ago

Loki

311

New contributor

answered 41 mins ago

Loki

311

answered 41 mins ago

Loki

311

New contributor

Loki is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

up vote
1
down vote

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.

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 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.

edited 29 mins ago

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

add a commentÂ |Â

up vote
1
down vote

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.

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 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.

edited 29 mins ago

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

add a commentÂ |Â

up vote
1
down vote

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.

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 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.

edited 29 mins ago

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

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.

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 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.

edited 29 mins ago

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

edited 29 mins ago

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

answered 35 mins ago

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

72.1k11120195

add a commentÂ |Â

Alok Patwal is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Alok Patwal is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky