Web lists-archives.com

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




在 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