Why query does not fail with nonexistent column in subquery?
![Multi tool use Multi tool use](http://sgv.ssvwv.com/sg/ssvwvcomimagb.png)
Multi tool use
I misspelled in my query and faced with MySQL's strange behaviour.
create table aaa (id bigint auto_increment primary key,
amount int not null,
other_column varchar(20)) engine=InnoDB
create table bbb (aaa_id bigint not null,
comment varchar(200),
key(aaa_id)) engine=InnoDB;
insert into aaa(other_column, amount) values ('hello, world', 12),
('second string', 15),
('one more', 100);
insert into bbb value (2, 'no 2s!');
The following query produces null
result (I typed 'id' instead of 'aaa_id'):
select sum(amount) from aaa where id not in (select id from bbb);
"Maybe 'id' has special meaning for MySQL", I thought. But, the following query executes normally and returns 127
(as if subquery returns empty result):
select sum(amount) from aaa where id not in (select other_column from bbb);
The following queries produce expected result: first one fails with Unknown column 'id2' in 'field list'
, and the second one returns 112
:
select sum(amount) from aaa where id not in (select id2 from bbb);
select sum(amount) from aaa where id not in (select aaa_id from bbb);
As it can be seen, MySQL somehow executes subquery if the column exists in the outer query. But what the meaning of those columns in the subquery?
Tested on 5.1.70 and 5.5.
mysql
add a comment |
I misspelled in my query and faced with MySQL's strange behaviour.
create table aaa (id bigint auto_increment primary key,
amount int not null,
other_column varchar(20)) engine=InnoDB
create table bbb (aaa_id bigint not null,
comment varchar(200),
key(aaa_id)) engine=InnoDB;
insert into aaa(other_column, amount) values ('hello, world', 12),
('second string', 15),
('one more', 100);
insert into bbb value (2, 'no 2s!');
The following query produces null
result (I typed 'id' instead of 'aaa_id'):
select sum(amount) from aaa where id not in (select id from bbb);
"Maybe 'id' has special meaning for MySQL", I thought. But, the following query executes normally and returns 127
(as if subquery returns empty result):
select sum(amount) from aaa where id not in (select other_column from bbb);
The following queries produce expected result: first one fails with Unknown column 'id2' in 'field list'
, and the second one returns 112
:
select sum(amount) from aaa where id not in (select id2 from bbb);
select sum(amount) from aaa where id not in (select aaa_id from bbb);
As it can be seen, MySQL somehow executes subquery if the column exists in the outer query. But what the meaning of those columns in the subquery?
Tested on 5.1.70 and 5.5.
mysql
add a comment |
I misspelled in my query and faced with MySQL's strange behaviour.
create table aaa (id bigint auto_increment primary key,
amount int not null,
other_column varchar(20)) engine=InnoDB
create table bbb (aaa_id bigint not null,
comment varchar(200),
key(aaa_id)) engine=InnoDB;
insert into aaa(other_column, amount) values ('hello, world', 12),
('second string', 15),
('one more', 100);
insert into bbb value (2, 'no 2s!');
The following query produces null
result (I typed 'id' instead of 'aaa_id'):
select sum(amount) from aaa where id not in (select id from bbb);
"Maybe 'id' has special meaning for MySQL", I thought. But, the following query executes normally and returns 127
(as if subquery returns empty result):
select sum(amount) from aaa where id not in (select other_column from bbb);
The following queries produce expected result: first one fails with Unknown column 'id2' in 'field list'
, and the second one returns 112
:
select sum(amount) from aaa where id not in (select id2 from bbb);
select sum(amount) from aaa where id not in (select aaa_id from bbb);
As it can be seen, MySQL somehow executes subquery if the column exists in the outer query. But what the meaning of those columns in the subquery?
Tested on 5.1.70 and 5.5.
mysql
I misspelled in my query and faced with MySQL's strange behaviour.
create table aaa (id bigint auto_increment primary key,
amount int not null,
other_column varchar(20)) engine=InnoDB
create table bbb (aaa_id bigint not null,
comment varchar(200),
key(aaa_id)) engine=InnoDB;
insert into aaa(other_column, amount) values ('hello, world', 12),
('second string', 15),
('one more', 100);
insert into bbb value (2, 'no 2s!');
The following query produces null
result (I typed 'id' instead of 'aaa_id'):
select sum(amount) from aaa where id not in (select id from bbb);
"Maybe 'id' has special meaning for MySQL", I thought. But, the following query executes normally and returns 127
(as if subquery returns empty result):
select sum(amount) from aaa where id not in (select other_column from bbb);
The following queries produce expected result: first one fails with Unknown column 'id2' in 'field list'
, and the second one returns 112
:
select sum(amount) from aaa where id not in (select id2 from bbb);
select sum(amount) from aaa where id not in (select aaa_id from bbb);
As it can be seen, MySQL somehow executes subquery if the column exists in the outer query. But what the meaning of those columns in the subquery?
Tested on 5.1.70 and 5.5.
mysql
mysql
asked Apr 13 '15 at 18:09
![](https://i.stack.imgur.com/qJ35E.jpg?s=32&g=1)
![](https://i.stack.imgur.com/qJ35E.jpg?s=32&g=1)
Ernest SadykovErnest Sadykov
629625
629625
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
This query:
select sum(amount)
from aaa
where id not in (select id from bbb);
Is interpreted as:
select sum(aaa.amount)
from aaa
where aaa.id not in (select aaa.id from bbb);
because bbb.id
does not exist. When writing SQL, I suggest that you always use table aliases. The query that you thought you were writing:
select sum(aaa.amount)
from aaa
where aaa.id not in (select bbb.id from bbb);
would generate the error you expect.
I think I understood. In the first case (aaa.id
),not in
condition discards every row in theaaa
table. Andsum
, as an aggregate function, returnsnull
. In theaaa.other_column
case, evidently, MySQL accepts every row due to type mismatch betweenid
column andother_column
.
– Ernest Sadykov
Apr 13 '15 at 18:31
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
add a comment |
If you created the bbb
table like this:
create table bbb (aaa_id bigint not null,
^^^^^^
why would you use
select sum(amount) from aaa where id not in (select id from bbb);
^^
then instead? The DB's not telepathic, it can't read your mind. If you tell it to use field x
in a table, then it's going to need x
to actually exist, and won't randomly pick some OTHER field.
The subquery COULD "reach out" and pick up field names from the outer/parent query, if tell the DB to do so, e.g.
SELECT id
FROM aaa
WHERE
id in (SELECT ... FROM bbb WHERE aaa.id = bbb.somefield)
1
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f29612136%2fwhy-query-does-not-fail-with-nonexistent-column-in-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
This query:
select sum(amount)
from aaa
where id not in (select id from bbb);
Is interpreted as:
select sum(aaa.amount)
from aaa
where aaa.id not in (select aaa.id from bbb);
because bbb.id
does not exist. When writing SQL, I suggest that you always use table aliases. The query that you thought you were writing:
select sum(aaa.amount)
from aaa
where aaa.id not in (select bbb.id from bbb);
would generate the error you expect.
I think I understood. In the first case (aaa.id
),not in
condition discards every row in theaaa
table. Andsum
, as an aggregate function, returnsnull
. In theaaa.other_column
case, evidently, MySQL accepts every row due to type mismatch betweenid
column andother_column
.
– Ernest Sadykov
Apr 13 '15 at 18:31
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
add a comment |
This query:
select sum(amount)
from aaa
where id not in (select id from bbb);
Is interpreted as:
select sum(aaa.amount)
from aaa
where aaa.id not in (select aaa.id from bbb);
because bbb.id
does not exist. When writing SQL, I suggest that you always use table aliases. The query that you thought you were writing:
select sum(aaa.amount)
from aaa
where aaa.id not in (select bbb.id from bbb);
would generate the error you expect.
I think I understood. In the first case (aaa.id
),not in
condition discards every row in theaaa
table. Andsum
, as an aggregate function, returnsnull
. In theaaa.other_column
case, evidently, MySQL accepts every row due to type mismatch betweenid
column andother_column
.
– Ernest Sadykov
Apr 13 '15 at 18:31
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
add a comment |
This query:
select sum(amount)
from aaa
where id not in (select id from bbb);
Is interpreted as:
select sum(aaa.amount)
from aaa
where aaa.id not in (select aaa.id from bbb);
because bbb.id
does not exist. When writing SQL, I suggest that you always use table aliases. The query that you thought you were writing:
select sum(aaa.amount)
from aaa
where aaa.id not in (select bbb.id from bbb);
would generate the error you expect.
This query:
select sum(amount)
from aaa
where id not in (select id from bbb);
Is interpreted as:
select sum(aaa.amount)
from aaa
where aaa.id not in (select aaa.id from bbb);
because bbb.id
does not exist. When writing SQL, I suggest that you always use table aliases. The query that you thought you were writing:
select sum(aaa.amount)
from aaa
where aaa.id not in (select bbb.id from bbb);
would generate the error you expect.
answered Apr 13 '15 at 18:18
Gordon LinoffGordon Linoff
776k35306409
776k35306409
I think I understood. In the first case (aaa.id
),not in
condition discards every row in theaaa
table. Andsum
, as an aggregate function, returnsnull
. In theaaa.other_column
case, evidently, MySQL accepts every row due to type mismatch betweenid
column andother_column
.
– Ernest Sadykov
Apr 13 '15 at 18:31
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
add a comment |
I think I understood. In the first case (aaa.id
),not in
condition discards every row in theaaa
table. Andsum
, as an aggregate function, returnsnull
. In theaaa.other_column
case, evidently, MySQL accepts every row due to type mismatch betweenid
column andother_column
.
– Ernest Sadykov
Apr 13 '15 at 18:31
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
I think I understood. In the first case (
aaa.id
), not in
condition discards every row in the aaa
table. And sum
, as an aggregate function, returns null
. In the aaa.other_column
case, evidently, MySQL accepts every row due to type mismatch between id
column and other_column
.– Ernest Sadykov
Apr 13 '15 at 18:31
I think I understood. In the first case (
aaa.id
), not in
condition discards every row in the aaa
table. And sum
, as an aggregate function, returns null
. In the aaa.other_column
case, evidently, MySQL accepts every row due to type mismatch between id
column and other_column
.– Ernest Sadykov
Apr 13 '15 at 18:31
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
@ErnestSadykov . . . Yes. That is the follow-on logic. I just wanted to explain that the problem is in how the query is parsed. It doesn't do what you want -- and you seem to have a good understanding of what it is doing.
– Gordon Linoff
Apr 13 '15 at 19:29
add a comment |
If you created the bbb
table like this:
create table bbb (aaa_id bigint not null,
^^^^^^
why would you use
select sum(amount) from aaa where id not in (select id from bbb);
^^
then instead? The DB's not telepathic, it can't read your mind. If you tell it to use field x
in a table, then it's going to need x
to actually exist, and won't randomly pick some OTHER field.
The subquery COULD "reach out" and pick up field names from the outer/parent query, if tell the DB to do so, e.g.
SELECT id
FROM aaa
WHERE
id in (SELECT ... FROM bbb WHERE aaa.id = bbb.somefield)
1
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
add a comment |
If you created the bbb
table like this:
create table bbb (aaa_id bigint not null,
^^^^^^
why would you use
select sum(amount) from aaa where id not in (select id from bbb);
^^
then instead? The DB's not telepathic, it can't read your mind. If you tell it to use field x
in a table, then it's going to need x
to actually exist, and won't randomly pick some OTHER field.
The subquery COULD "reach out" and pick up field names from the outer/parent query, if tell the DB to do so, e.g.
SELECT id
FROM aaa
WHERE
id in (SELECT ... FROM bbb WHERE aaa.id = bbb.somefield)
1
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
add a comment |
If you created the bbb
table like this:
create table bbb (aaa_id bigint not null,
^^^^^^
why would you use
select sum(amount) from aaa where id not in (select id from bbb);
^^
then instead? The DB's not telepathic, it can't read your mind. If you tell it to use field x
in a table, then it's going to need x
to actually exist, and won't randomly pick some OTHER field.
The subquery COULD "reach out" and pick up field names from the outer/parent query, if tell the DB to do so, e.g.
SELECT id
FROM aaa
WHERE
id in (SELECT ... FROM bbb WHERE aaa.id = bbb.somefield)
If you created the bbb
table like this:
create table bbb (aaa_id bigint not null,
^^^^^^
why would you use
select sum(amount) from aaa where id not in (select id from bbb);
^^
then instead? The DB's not telepathic, it can't read your mind. If you tell it to use field x
in a table, then it's going to need x
to actually exist, and won't randomly pick some OTHER field.
The subquery COULD "reach out" and pick up field names from the outer/parent query, if tell the DB to do so, e.g.
SELECT id
FROM aaa
WHERE
id in (SELECT ... FROM bbb WHERE aaa.id = bbb.somefield)
answered Apr 13 '15 at 18:11
Marc BMarc B
314k31320421
314k31320421
1
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
add a comment |
1
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
1
1
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
That's the problem. I don't want the DB to read my mind. I want it to fail, but it does not and returns strange (null) result instead.
– Ernest Sadykov
Apr 13 '15 at 18:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f29612136%2fwhy-query-does-not-fail-with-nonexistent-column-in-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
gT8s ujbAZ ykZoAfvi e,74dZ09sic22dfEEuwWJ PgIMq,F 2wIbZLIpL1e4Soev,B6jEyujTkl4GglTSa8ZrbO2Yp