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

[7 Dec 2004 14:44] Carsten Pedersen
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
[7 Dec 2004 16:55] MySQL Verification Team
Can't repeat it with my test data.
Please, upload table structure and data for testing.
[8 Dec 2004 11:10] Carsten Pedersen
Copy of the data file downloaded from dev.mysql.com

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

[8 Dec 2004 11:12] Carsten Pedersen
Data uploaded
[8 Dec 2004 11:46] Carsten Pedersen
OS updated to reflect real world
[10 Dec 2004 21:44] Oleksandr Byelkin
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
[11 Dec 2004 15:16] Oleksandr Byelkin
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
[13 Dec 2004 13:39] Oleksandr Byelkin
Thank you for bugreport. Bugfix will be present in version 4.1.8 of server.