Bug #7079 DISTINCT with ANY/SOME subquery returns "Empty set"
Submitted: 7 Dec 2004 15:44 Modified: 13 Dec 2004 14:39
Reporter: Carsten Pedersen
Status: Closed
Category:Server Severity:S1 (Critical)
Version:4.1.7-standard-log OS:Linux (SuSE Linux 9.1)
Assigned to: Bugs System Target Version:

[7 Dec 2004 15: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 17:55] Victoria Reznichenko 
Can't repeat it with my test data.
Please, upload table structure and data for testing.
[8 Dec 2004 12: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 12:12] Carsten Pedersen 
Data uploaded
[8 Dec 2004 12:46] Carsten Pedersen 
OS updated to reflect real world
[10 Dec 2004 22: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 16: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 14:39] Oleksandr Byelkin 
Thank you for bugreport. Bugfix will be present in version 4.1.8 of server.