MySQL Bugs: #7079: DISTINCT with ANY/SOME subquery returns "Empty set"

Bug #7079	DISTINCT with ANY/SOME subquery returns "Empty set"
Submitted:	7 Dec 2004 14:44	Modified:	13 Dec 2004 13:39
Reporter:	Carsten Pedersen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.7-standard-log	OS:	Linux (SuSE Linux 9.1)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
RPM installation using RPMs from dev.mysql.com/downloads

World database as downloaded from dev.mysql.com/doc

Executing:

SELECT Continent AS c
FROM Country
WHERE Code <> SOME (
 SELECT Code
 FROM Country
 WHERE Continent = c
 AND Population < 200
);

returns 90 rows.

Executing the same statement with DISTINCT in the outer query, e.g. 

SELECT DISTINCT Continent AS c
FROM Country
WHERE Code <> SOME (
 SELECT Code
 FROM Country
 WHERE Continent = c
 AND Population < 200
);

returns "Empty set"

How to repeat:
See above

Can't repeat it with my test data.
Please, upload table structure and data for testing.

Copy of the data file downloaded from dev.mysql.com

Attachment: world.sql.gz (application/x-gzip, text), 89.31 KiB.

Data uploaded

OS updated to reflect real world

ChangeSet 
  1.2159 04/12/10 23:41:22 bell@sanja.is.com.ua +3 -0 
  new reference which refer to current value not to result used for resolving outer refernces 
if subqueri is not in HAVING clause (BUG#7079) 
  and the same used for subquery transformetion

Sorry, I forgot test suite in previous cset. 
 
ChangeSet 
  1.2159 04/12/11 17:13:19 bell@sanja.is.com.ua +5 -0 
    new reference which refer to current value not to result used for resolving outer 
  refernces if subqueri is not in HAVING clause (BUG#7079) 
    and the same used for subquery transformetion

Thank you for bugreport. Bugfix will be present in version 4.1.8 of server.