Re: Re: A odd case about mysql sub query

On Wed, May 11, 2016 at 12:08 PM, soul11201 <1479418380@qq.com> wrote: > 在 2016/5/6 15:45, haiwen zhu 写道: > >> 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. >> >> >> > in mysql 5.5.46, the second result I got was : > > +----+-------+-------+ > | id | col_a | col_b | > +----+-------+-------+ > | 2 | a | b | > | 3 | a | c | > | 4 | a | c | > +----+-------+-------+ > > (:( > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The second sql is： > select * from test where id not in (select id from (select * from test) as > a group by col_a,col_b My expected result is same as the first sql query. -- Best Regards, Haiwen