Web lists-archives.com

[PHP] A odd case about mysql sub query




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