Bug #30993 Subqueries: LooseScan strategy produces wrong query results, with duplicates
Submitted: 13 Sep 2007 8:34 Modified: 20 Nov 2010 18:04
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.2-bk OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: LooseScan, subqueries

[13 Sep 2007 8:34] Sergey Petrunya 
Description:
Subquery LooseScan strategy (introduced in 5.2) may produce wrong query result, with outer table' rows occuring several times in the query output.

This bug was found by Igor.

How to repeat:
* Load the attached tables (they are from world database with an extra added index). 

* Check that LooseScan strategy is used:

mysql> EXPLAIN SELECT Name FROM Country WHERE   Country.Code IN (SELECT City.CountryCode FROM City WHERE Population > 5000000);
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+------------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra                  |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+------------------------+
|  1 | PRIMARY     | City    | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4079 | Using where; LooseScan |
|  1 | PRIMARY     | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 |                        |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+------------------------+
2 rows in set (0.00 sec)

 * Run the query and see duplicate rows:

mysql> SELECT Name FROM Country WHERE   Country.Code IN (SELECT City.CountryCode FROM City WHERE Population > 5000000);
+---------------------------------------+
| Name                                  |
+---------------------------------------+
| Brazil                                |
| Brazil                                |
| United Kingdom                        |
| Egypt                                 |
| Indonesia                             |
| India                                 |
| India                                 |
| Iran                                  |
| Japan                                 |
| China                                 |
| China                                 |
| China                                 |
| China                                 |
| Colombia                              |
| Congo, The Democratic Republic of the |
| South Korea                           |
| Mexico                                |
| Pakistan                              |
| Pakistan                              |
| Peru                                  |
| Thailand                              |
| Turkey                                |
| Russian Federation                    |
| United States                         |
+---------------------------------------+
24 rows in set (0.07 sec)
[13 Sep 2007 8:34] Sergey Petrunya 
query table

Attachment: bug_city.sql (text/x-sql), 174.89 KiB.
[13 Sep 2007 8:35] Sergey Petrunya 
Another query table

Attachment: bug_country.sql (text/x-sql), 37.97 KiB.
[19 Sep 2007 12:15] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34397

ChangeSet@1.2602, 2007-09-19 15:14:49+03:00, sergefp@mysql.com +3 -0
  BUG#30993: Subqueries: LooseScan strategy produces wrong query results, with duplicates:
   - Made best_access_path() not to choose loose index scan when the index is not covering
[19 Sep 2007 12:24] Sergey Petrunya 
Please disregard the previous commit, it has changes that are not relevant to this bug.
[19 Sep 2007 12:25] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34399

ChangeSet@1.2602, 2007-09-19 15:24:24+03:00, sergefp@mysql.com +3 -0
  BUG#30993: Subqueries: LooseScan strategy produces wrong query results, with duplicates:
   - Made best_access_path() not to choose loose index scan when the index
     is not covering
[26 Sep 2007 11:29] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34555

ChangeSet@1.2602, 2007-09-26 15:27:14+04:00, sergefp@mysql.com +3 -0
  BUG#30993: Subqueries: LooseScan strategy produces wrong query results, with duplicates:
   - Made best_access_path() not to choose loose index scan when the index
     is not covering
[29 Oct 2007 8:50] Bugs System 
Pushed into 6.0.4-alpha
[13 Nov 2007 19:16] Paul DuBois 
Noted in 6.0.4 changelog.

The LooseScan subquery optimization strategy could produce duplicate 
rows in query results.
[16 Aug 2010 6:34] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:05] Bugs System 
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 18:04] Paul DuBois 
Noted in 5.6.1 changelog.
[8 Apr 2011 14:58] Paul DuBois 
Correction. Bug does not affect 5.6.x users. No changelog entry needed.