Why query does not fail with nonexistent column in subquery?

Multi tool use
Multi tool use












2















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.










share|improve this question



























    2















    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.










    share|improve this question

























      2












      2








      2








      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.










      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Apr 13 '15 at 18:09









      Ernest SadykovErnest Sadykov

      629625




      629625
























          2 Answers
          2






          active

          oldest

          votes


















          2














          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.






          share|improve this answer
























          • 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



















          0














          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)





          share|improve this answer



















          • 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











          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
          });


          }
          });














          draft saved

          draft discarded


















          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









          2














          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.






          share|improve this answer
























          • 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
















          2














          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.






          share|improve this answer
























          • 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














          2












          2








          2







          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.






          share|improve this answer













          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










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



















          • 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

















          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













          0














          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)





          share|improve this answer



















          • 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
















          0














          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)





          share|improve this answer



















          • 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














          0












          0








          0







          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)





          share|improve this answer













          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)






          share|improve this answer












          share|improve this answer



          share|improve this answer










          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














          • 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


















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          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





















































          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
          T8BLWS9 1jiHoipOz f 1q0SDauIcx OnXv2fpuF,qIYK,D55ap9qYqKr,j2Hy GSRIEyKt7 0 Jku0MyWbzaE

          Popular posts from this blog

          Monofisismo

          Angular Downloading a file using contenturl with Basic Authentication

          Olmecas