Web lists-archives.com

Re: [PHP] Re: A odd case about mysql sub query




On Wed, May 11, 2016 at 12:08 PM, soul11201 <1479418380@xxxxxx> 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