Web lists-archives.com

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




On Fri, May 6, 2016 at 9:09 PM, Aziz Saleh <azizsaleh@xxxxxxxxx> wrote:

>
>
> On Fri, May 6, 2016 at 3:45 AM, haiwen zhu <bugwhen@xxxxxxxxx> 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