Re: A odd case about mysql sub query

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_a); > > 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.