Web lists-archives.com

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




在 2016/5/12 7:28, haiwen zhu 写道:
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.


select * from test where id not in (select id from (select * from test) as a group by col_a,col_b

select * from test.new_table where id not in (select id from (select id from test.new_table
group by col_a,col_b) as a);


yes i got the same result in mysql 5.5.46


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php