MySQL Bugs: #31639: Wrong plan for uncorrelated subquery when loose scan is applicable.

Bug #31639	Wrong plan for uncorrelated subquery when loose scan is applicable.
Submitted:	16 Oct 2007 14:03	Modified:	22 Nov 2010 1:13
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.2.4	OS:	Any
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
See how to repeat.

How to repeat:
-- This is the query file that I'm executing against the servers:
/*
SELECT version();
SELECT Continent, Name, Population FROM Country WHERE ROW(Continent, Population) IN (SELECT Continent, MAX(Population) FROM Country GROUP BY Continent);
\W
EXPLAIN EXTENDED SELECT Continent, Name, Population FROM Country WHERE ROW(Continent, Population) IN (SELECT Continent, MAX(Population) FROM Country GROUP BY Continent);

*/

-- Here are the results:
/*
+------------------+
| version()        |
+------------------+
| 5.0.45flupps-log | 
+------------------+
+---------------+----------------------------------------------+------------+
| Continent     | Name                                         | Population |
+---------------+----------------------------------------------+------------+
| Oceania       | Australia                                    |   18886000 | 
| South America | Brazil                                       |  170115000 | 
| Asia          | China                                        | 1277558000 | 
| Africa        | Nigeria                                      |  111506000 | 
| Europe        | Russian Federation                           |  146934000 | 
| North America | United States                                |  278357000 | 
| Antarctica    | Antarctica                                   |          0 | 
| Antarctica    | Bouvet Island                                |          0 | 
| Antarctica    | South Georgia and the South Sandwich Islands |          0 | 
| Antarctica    | Heard Island and McDonald Islands            |          0 | 
| Antarctica    | French Southern territories                  |          0 | 
+---------------+----------------------------------------------+------------+
+----+--------------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type        | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+--------------------+---------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL   | NULL          | NULL      | NULL    | NULL |  239 | Using where | 
|  2 | DEPENDENT SUBQUERY | Country | index | NULL          | Continent | 5       | NULL |    8 | Using index | 
+----+--------------------+---------+-------+---------------+-----------+---------+------+------+-------------+
Note (Code 1003): select `world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`Population` AS `Population` from `world`.`Country` where <in_optimizer>((`world`.`Country`.`Continent`,`world`.`Country`.`Population`),<exists>(select `world`.`Country`.`Continent` AS `Continent`,max(`world`.`Country`.`Population`) AS `MAX(Population)` from `world`.`Country` group by `world`.`Country`.`Continent` having (((<cache>(`world`.`Country`.`Continent`) = `world`.`Country`.`Continent`) or isnull(`world`.`Country`.`Continent`)) and ((<cache>(`world`.`Country`.`Population`) = max(`world`.`Country`.`Population`)) or isnull(max(`world`.`Country`.`Population`))) and <is_not_null_test>(`world`.`Country`.`Continent`) and <is_not_null_test>(max(`world`.`Country`.`Population`)))))

+-------------------+
| version()         |
+-------------------+
| 5.2.4-alphaflupps | 
+-------------------+
+------------+----------------------------------------------+------------+
| Continent  | Name                                         | Population |
+------------+----------------------------------------------+------------+
| Africa     | Nigeria                                      |  111506000 | 
| Europe     | Russian Federation                           |  146934000 | 
| Antarctica | Antarctica                                   |          0 | 
| Antarctica | Bouvet Island                                |          0 | 
| Antarctica | South Georgia and the South Sandwich Islands |          0 | 
| Antarctica | Heard Island and McDonald Islands            |          0 | 
| Antarctica | French Southern territories                  |          0 | 
+------------+----------------------------------------------+------------+
+----+-------------+---------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | Country | ALL   | NULL          | NULL      | NULL    | NULL |  239 |   100.00 | Using where | 
|  2 | SUBQUERY    | Country | index | NULL          | Continent | 5       | NULL |  239 |     3.35 | Using index | 
+----+-------------+---------+-------+---------------+-----------+---------+------+------+----------+-------------+
Note (Code 1003): select `world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`Population` AS `Population` from `world`.`Country` where <in_optimizer>((`world`.`Country`.`Continent`,`world`.`Country`.`Population`),(`world`.`Country`.`Continent`,`world`.`Country`.`Population`) in ( <materialize> (select `world`.`Country`.`Continent` AS `Continent`,max(`world`.`Country`.`Population`) AS `MAX(Population)` from `world`.`Country` group by `world`.`Country`.`Continent` ), <primary_index_lookup>(`world`.`Country`.`Continent` in <temporary table> on distinct_key)))
*/

Sorry, forgot to mention, I used a copy of world.sql from mysql.com/doc and I also ran this:

ALTER TABLE Country ADD INDEX (Continent, Population);

The bug appears to be fixed in the latest mysql-5.2-opt tree.

Actually, this report consist of two problems. The first one
is already fixed, but there is another problem, where if we define
the index in the previous comment, the explain for the subquery
differs from the explain for the same query if run on its own.

I reopened the bug, and changed the title to reflect the
real problem.

The plan for the subquery:
  explain SELECT Continent, MAX(Population) FROM Country GROUP BY Continent
uses loose index scan:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Country
         type: range
possible_keys: NULL
          key: Continent
      key_len: 1
          ref: NULL
         rows: 8
        Extra: Using index for group-by

However, the plan for the same query used as a non-correlated subquery,
shows index scan when it is executed via materialization: 

SELECT Continent, Name, Population
FROM Country WHERE ROW(Continent, Population) IN
(SELECT Continent, MAX(Population) FROM Country GROUP BY Continent)

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: Country
         type: index
possible_keys: NULL
          key: Continent
      key_len: 5
          ref: NULL
         rows: 239
        Extra: Using index

The bug is in that the plan must be the same in both cases,
and it must use loose index scan for the subquery.

The problem is that inside get_best_group_min_max() we get
the JOIN object of the subquery via
join= thd->lex->current_select->join;
which incorrectly retrieves the JOIN object of the outer query.

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/36258

ChangeSet@1.2616, 2007-10-24 16:15:22+03:00, timour@linux-h5lp.site +4 -0
  BUG#31639: Wrong plan for uncorrelated subquery when loose scan
             is applicable.
  
  There were two distinct problems in this bug:
  1. At execution time the subquery under an IN predicate was optimized with
     the global memeber thd->lex->current_select pointing to the outer select.
     This caused the subquery to be optimized differently when
     JOIN::optimize needed to analyze current_select.
  
     Fixed by setting the correct current_select for the optimization
     and execution (materialization) of the subquery.
  2. When running EXPLAIN, optimization took a different path because
     - Item_exists_subselect::fix_length_and_dec sets the subquery
       LIMIT to 1, then
     - mysql_explain_union(), which is called only for EXPLAIN called
       unit->set_limit(unit->global_parameters), which resets
       join->unit->select_limit_cnt to 1, this in turn
     - affected make_join_select to call sel->test_quick_select, which
       deleted the quick_select access method.
     Fixed by setting the limit correctly when IN is executed via
     materialization.

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/36265

ChangeSet@1.2616, 2007-10-24 17:03:48+03:00, timour@linux-h5lp.site +8 -0
  BUG#31639: Wrong plan for uncorrelated subquery when loose scan
             is applicable.
  
  There were two distinct problems in this bug:
  1. At execution time the subquery under an IN predicate was optimized with
     the global memeber thd->lex->current_select pointing to the outer select.
     This caused the subquery to be optimized differently when
     JOIN::optimize needed to analyze current_select.
  
     Fixed by setting the correct current_select for the optimization
     and execution (materialization) of the subquery.
  2. When running EXPLAIN, optimization took a different path because
     - Item_exists_subselect::fix_length_and_dec sets the subquery
       LIMIT to 1, then
     - mysql_explain_union(), which is called only for EXPLAIN called
       unit->set_limit(unit->global_parameters), which resets
       join->unit->select_limit_cnt to 1, this in turn
     - affected make_join_select to call sel->test_quick_select, which
       deleted the quick_select access method.
     Fixed by setting the limit correctly when IN is executed via
     materialization.

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/36323

ChangeSet@1.2616, 2007-10-25 12:07:16+03:00, timour@linux-h5lp.site +8 -0
  BUG#31639: Wrong plan for uncorrelated subquery when loose scan
             is applicable.
  
  There were two distinct problems in this bug:
  1. At execution time the subquery under an IN predicate was optimized with
     the global memeber thd->lex->current_select pointing to the outer select.
     This caused the subquery to be optimized differently when
     JOIN::optimize needed to analyze current_select.
  
     Fixed by setting the correct current_select for the optimization
     and execution (materialization) of the subquery.
  2. When running EXPLAIN, optimization took a different path because
     - Item_exists_subselect::fix_length_and_dec sets the subquery
       LIMIT to 1, then
     - mysql_explain_union(), which is called only for EXPLAIN called
       unit->set_limit(unit->global_parameters), which resets
       join->unit->select_limit_cnt to 1, this in turn
     - affected make_join_select to call sel->test_quick_select, which
       deleted the quick_select access method.
     Fixed by setting the limit correctly when IN is executed via
     materialization.

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/36346

ChangeSet@1.2616, 2007-10-25 15:58:57+03:00, timour@linux-h5lp.site +8 -0
  BUG#31639: Wrong plan for uncorrelated subquery when loose scan
             is applicable.
  
  There were two distinct problems in this bug:
  1. At execution time the subquery under an IN predicate was optimized with
     the global memeber thd->lex->current_select pointing to the outer select.
     This caused the subquery to be optimized differently when
     JOIN::optimize needed to analyze current_select.
  
     Fixed by setting the correct current_select for the optimization
     and execution (materialization) of the subquery.
  2. When running EXPLAIN, optimization took a different path because
     - Item_exists_subselect::fix_length_and_dec sets the subquery
       LIMIT to 1, then
     - mysql_explain_union(), which is called only for EXPLAIN called
       unit->set_limit(unit->global_parameters), which resets
       join->unit->select_limit_cnt to 1, this in turn
     - affected make_join_select to call sel->test_quick_select, which
       deleted the quick_select access method.
     Fixed by setting the limit correctly when IN is executed via
     materialization.

Pushed into 6.0.4-alpha

Noted in 6.0.4 changelog.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Noted in 5.6.1.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.