Re: A odd case about mysql sub query

On Fri, May 6, 2016 at 9:09 PM, Aziz Saleh <azizsaleh@gmail.com> wrote: > > > On Fri, May 6, 2016 at 3:45 AM, haiwen zhu <bugwhen@gmail.com> wrote: > >> Hi guys, >> >> I have a table like this: >> +----+-------+-------+ >> | id | col_a | col_b | >> +----+-------+-------+ >> | 1 | a | b | >> | 2 | a | b | >> | 3 | a | c | >> | 4 | a | c | >> +----+-------+-------+ >> >> execute sql a: >> >> > select * from test where id not in (select id from (select id from test >> > group by col_a,col_b) as a); >> >> can get result >> +----+-------+-------+ >> | id | col_a | col_b | >> +----+-------+-------+ >> | 2 | a | b | >> | 4 | a | c | >> +----+-------+-------+ >> while execute sql b: >> >> > select * from test where id not in (select id from (select * from test) >> as >> > a group by col_a,col_b); >> >> get empty result. >> >> I'm very confused with this, somebody has idea about why? >> I tried in 10.1.13-MariaDB and mysql5.6. >> >> >> -- >> >> Best Regards, >> Haiwen >> > > Run the lowest sub-query on its own and see if it gets you the expected > results and go to the top level by level. That is the best way to debug it > imo. > The two sub query get same result， that's why i most confused. > select id from (select id from test group by col_a,col_b) as a > select id from (select * from test) as a group by col_a,col_a -- Best Regards, Haiwen