Web lists-archives.com

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




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_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.