MySQL Bugs: #52605: Adding LIMIT 1 clause to query with complex range predicate causes wrong results

Bug #52605	Adding LIMIT 1 clause to query with complex range predicate causes wrong results
Submitted:	5 Apr 2010 21:30	Modified:	23 Nov 2010 3:23
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0-codebase-bugfixing	OS:	Any
Assigned to:	Olav Sandstå	CPU Architecture:	Any
Tags:	index_condition_pushdown, optimizer_switch

Description:
Adding a LIMIT 1 clause to the following query will result in flat-out incorrect results.
This is not present in 5.1-main and is affected by the index_condition_pushdown flag in optimizer_switch (turning ICP=off will cause this particular error to go away)

Appears related to:
Bug#42019	JOIN + LIMIT + SPECIAL WHERE in query sometimes makes it return wrong resultset, which died due to lack of feedback / reproducibility in 5.1 (but was 6.0-specific)

Original Query:
SELECT `col_int_key` field1  
FROM AA  
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240  
ORDER  BY field1   ;
field1
6

Original EXPLAIN:
EXPLAIN SELECT `col_int_key` field1
FROM AA
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240
ORDER  BY field1   ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	AA	range	PRIMARY,col_int_key	PRIMARY	4	NULL	4	Using index condition; Using where; Using MRR; Using filesort

Modified query:  NOTE- only LIMIT 1 was added
SELECT `col_int_key` field1  
FROM AA  
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240  
ORDER  BY field1    
LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */;
field1
0

Modified EXPLAIN
EXPLAIN SELECT `col_int_key` field1
FROM AA
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240
ORDER  BY field1
LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	AA	range	PRIMARY,col_int_key	col_int_key	5	NULL	14	Using where

How to repeat:
#/* Server0: MySQL 6.0.14-alpha-debug-log */

# NOTE:  none of the other variables matter but index_condition_pushdown
# Set the value to off to get the correct result sets.
/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,mrr=on,mrr_cost_based=off,index_condition_pushdown=on' */;
/*!50400 SET SESSION optimizer_join_cache_level = 4 */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ AA;
--enable_warnings

CREATE TABLE `AA` (
  `col_int_key` int(11) DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
INSERT INTO `AA` VALUES (NULL,1);
INSERT INTO `AA` VALUES (6,2);
INSERT INTO `AA` VALUES (NULL,3);
INSERT INTO `AA` VALUES (6,4);
INSERT INTO `AA` VALUES (NULL,5);
INSERT INTO `AA` VALUES (NULL,6);
INSERT INTO `AA` VALUES (9,7);
INSERT INTO `AA` VALUES (9,8);
INSERT INTO `AA` VALUES (7,9);
INSERT INTO `AA` VALUES (4,10);
INSERT INTO `AA` VALUES (4,11);
INSERT INTO `AA` VALUES (NULL,12);
INSERT INTO `AA` VALUES (NULL,13);
INSERT INTO `AA` VALUES (NULL,14);
INSERT INTO `AA` VALUES (5,15);
INSERT INTO `AA` VALUES (NULL,16);
INSERT INTO `AA` VALUES (NULL,17);
INSERT INTO `AA` VALUES (9,18);
INSERT INTO `AA` VALUES (3,19);
INSERT INTO `AA` VALUES (2,20);
INSERT INTO `AA` VALUES (0,21);
INSERT INTO `AA` VALUES (2,22);
INSERT INTO `AA` VALUES (9,23);
INSERT INTO `AA` VALUES (9,24);

# This is just here as a courtesy / troubleshooting query to see what is in the 
# table / correct results
SELECT * FROM AA ORDER BY pk, col_int_key;

SELECT `col_int_key` field1  
FROM AA  
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240  
ORDER  BY field1   ;

EXPLAIN SELECT `col_int_key` field1
FROM AA
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240
ORDER  BY field1   ;

#/* End of test case for query 0 */

#/* Begin test case for query 1 */

SELECT `col_int_key` field1  
FROM AA  
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240  
ORDER  BY field1    
LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */;

EXPLAIN SELECT `col_int_key` field1
FROM AA
WHERE ( `pk`  BETWEEN  4  AND  5  OR `pk`  <  2  )  AND `col_int_key`  <  240
ORDER  BY field1
LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */;

DROP TABLE AA;
#/* End of test case for query 1 */

Switching from MyISAM to InnoDB (with ICP enabled) makes the test case produce the correct result. This might be due to this being a MyISAM related bug but could also be due to changes in the query plan:

MyISAM plan:
============

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	AA	range	PRIMARY,k1	k1	5	NULL	11	Using where

InnoDB plan:
============

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	AA	range	PRIMARY,k1	PRIMARY	4	NULL	3	Using where; Using filesort

Simplified version of original test case:
=========================================

CREATE TABLE t1 (
  pk INT NOT NULL,
  c1 INT,
  PRIMARY KEY (pk),
  KEY k1 (c1)
);

INSERT INTO t1 VALUES (1,NULL);
INSERT INTO t1 VALUES (2,6);
INSERT INTO t1 VALUES (3,NULL);
INSERT INTO t1 VALUES (4,6);
INSERT INTO t1 VALUES (5,NULL);
INSERT INTO t1 VALUES (6,NULL);
INSERT INTO t1 VALUES (7,9);
INSERT INTO t1 VALUES (8,2);

SELECT pk, c1
FROM t1  
WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;

EXPLAIN SELECT pk, c1
FROM t1  
WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;

DROP TABLE t1;

Evaluation of what happens.
==========================

If we start with the original query but remove the LIMIT 1 from it:

SELECT pk, c1
FROM t1  
WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1;

the correct result is produced. The query plan for this query looks like:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY,k1	PRIMARY	4	NULL	3	Using index condition; Using where; Using MRR; Using filesort

The important thing to observe here is that ICP is used. In this case the
condition "pk BETWEEN 4 AND 5 OR pk < 2" on the primary key (pk) is
pushed down to MyISAM.

If we add "LIMIT 1" to the query:

SELECT pk, c1
FROM t1  
WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;

the query plan is changed to:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY,k1	k1	5	NULL	4	Using where

The two important observations are that this query will now use the "k1"
index (instead of the primary index) and that ICP no longer (seems to
be) used.

=======================================================================

What happens "behind the scene" (ie. in the code) is rougly:

1. In the optimizer (in JOIN::optimize) the two queries above both get
   optimized the same way: ie. as range scans using the primary key (see
   the first query plan above).

2. In one of the last steps the optimizer checks if ICP can be
   used. For both queries above it will push down the following select
   condition:

     "pk BETWEEN 4 AND 5 OR pk < 2" (on the primary key "pk")
 
   to the storage engine. After this the server code will only have to
   evaluate the remainder condition:

     "c1 < 240"

   on records returned from the storage engine.

The query is now ready for "execution":

3. During "execution" (in JOIN::exec()) the two queries above are
   handled differently. The first (without LIMIT) is executed as decided
   the by the optimzer. The interesting things happens to the query when
   LIMIT is added:

   In JOIN::exec() a call to create_sort_index() is done:
   
    /*
	  Here we sort rows for ORDER BY/GROUP BY clause, if the optimiser
	  chose FILESORT to be faster than INDEX SCAN or there is no 
	  suitable index present.
	  Note, that create_sort_index calls test_if_skip_sort_order and may
	  finally replace sorting with index scan if there is a LIMIT clause in
	  the query. XXX: it's never shown in EXPLAIN!
	  OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.
    */
    if (create_sort_index(thd, curr_join,
			    curr_join->group_list ? 
			    curr_join->group_list : curr_join->order,
			    curr_join->select_limit,
			    (select_options & OPTION_FOUND_ROWS ?
			     HA_POS_ERROR : unit->select_limit_cnt),
                            curr_join->group_list ? FALSE : TRUE))

   The create_sort_index() function will again (as stated in
   the comment to the code above) do a call to
   test_if_skip_sort_order(). 

   test_if_skip_sort_order() takes the "LIMIT 1" into account and uses
   cost estimations to try to find if there is a cheaper index to
   use to retrieve the necessary rows from the table. In this case it
   determines that the k1 index (which sorts on c1 column) can be used
   and will likely be cheaper to use than reading the primary key (a
   reasonable decission). So it changes the query plan from reading
   the table using the primary key (pk) to using the k1 key (on c1
   column).

When starting reading form the table/handler the first retrieved record that satisfies the where condtion:

  "c1 < 240"

is wrongly given as result for the query.

The "missing" part of the where condition:

  "pk BETWEEN 4 AND 5 OR pk < 2"

is neither evaluated by the server (since it has pushed it down to the
storage engine) nor by the storage engine (since this was pushed as an
ICP condition on the primary key "pk" but not the storage engine is told
to read using the "k1" index).

Some alternative ideas for how this might be fixed:
===================================================

1. Change the order of doing ICP (pushing index conditions to the
storage engine) and the LIMIT optimization that changes which index to
use. The ICP is today part of JOIN::optimize() and the LIMIT
optimization is part of JOIN::exec()....

2. Avoid using ICP if the query has a LIMIT. This would unfortunately
prevent us from pushing down conditions to the storage engine in many
more cases than where we actually later changes index. Not a good
solution as there should be no reason for not utilizing ICP also for
queries containing a LIMIT.

3. Do not change index to use for LIMIT in test_if_skip_sort_order()
if we have pushed an condition to the storage engine. Not a good
solution as this would prevent us from utilizing the best index for
executing the query (and would result in "performance regressions due
to ICP").

4. Extend test_if_skip_sort_order() to handle the situation where we
have previously pushed a condition to the storage engine and now
changes to a different index to include the part of the where
condition that has been pushed to the storage engine into the
where condition evaluated by the server (more or less: try to revert
or compensate the ICP condition push).

Status: Since test_if_skip_sort_order() already has code that takes
pushed index conditions into account for some of its evaluations the
plan is to use alternative 4. I have a patch that seems to work for
this ready (although I conceptually think alternative 1 would be a
more correct solution to the problem. Unfortunately that would result
in larger code changes and thus be more "risky" - but it would also
simplify the existing code in test_if_skip_sort_order()).

There are already two previous bugs that have been fixed related to this problem:

 * Bug#30622 ORDER BY on a SELECT causes results to be missed
 * Bug#45227 Lost HAVING clause led to a wrong result

If I revert the patch for the last of these the test case in this bug goes away (but the problem in Bug#45277 is re-introduced).

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

3847 Olav Sandstaa	2010-04-29
      Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results
      
      The cause for the wrong result returned when adding the LIMIT 1 to
      this query was:
      
       * during the optimize phase ICP code pushed part of the WHERE clause
         down to the storage engine based on that the query should be
         executed as a range query using the primary key.  
      
       * in the start of the execution phase the LIMIT clause is considered
         and alternative access strategies are evaluated in order to reduce
         the cost for reading records. This resulted in the switching to a
         different index for reading the data from the storage engine.
      
      As a consequence of this change of access strategy (switching index)
      the part of the WHERE clause that had been pushed down to the storage
      engine was never evaluated.
      
      The fix for this is to detect that we have switched index and in that
      case included the condition that has previously been pushed to the
      storage engine into the condition that is evaluated by the server.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results.
     @ mysql-test/r/innodb_icp.result
        Result for test case for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results.
     @ mysql-test/r/myisam_icp.result
        Result for test case for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results.
     @ sql/sql_select.cc
        Extend test_if_skip_sort_order() to handle the situation where parts
        of the WHERE condition has been pushed down to the storage engine
        (ICP) and where we based on cost estimates switches to use a different
        index. In this case we included the parts of the WHERE condition that
        has been pushed down into the condition that is evaluated by the
        server.

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

3185 Olav.Sandstaa@sun.com	2010-05-28
      Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results
            
      The cause for the wrong result returned when adding the LIMIT 1 to
      this query was:
            
      * during the optimize phase ICP code pushed part of the WHERE clause
        down to the storage engine based on that the query should be
        executed as a range query using the primary key.  
            
      * in the start of the execution phase the LIMIT clause is considered
        and alternative access strategies are evaluated in order to reduce
        the cost for reading records. This resulted in the switching to a
        different index for reading the data from the storage engine.
            
      As a consequence of this change of access strategy (switching index)
      the part of the WHERE clause that had been pushed down to the storage
      engine was never evaluated.
            
      The fix for this is to detect that we have switched index and in that
      case included the condition that has previously been pushed to the
      storage engine into the condition that is evaluated by the server.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#52605 Adding LIMIT 1 clause to query with complex 
        range predicate causes wrong results.
     @ mysql-test/r/innodb_icp.result
        Result for test case for Bug#52605 Adding LIMIT 1 clause to query
        with complex range predicate causes wrong results.
     @ mysql-test/r/myisam_icp.result
        Result for test case for Bug#52605 Adding LIMIT 1 clause to query
        with complex range predicate causes wrong results.
     @ sql/sql_select.cc
        Extend test_if_skip_sort_order() to handle the situation where parts
        of the WHERE condition has been pushed down to the storage engine
        (ICP) and where we based on cost estimates switches to use a different
        index. In this case we should ensure that the original WHERE condition 
        as it was before it was pushed to the storage (tab->pre_idx_push_select_cond)
        is used instead of the current where condition stored in the join_tab.
        The code in test_if_skip_sort_order() already used the original where
        condition but restored the current where condition when exiting the function.
        The fix for this is to not restore the current where condition if
        we have changed to use a new index.

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

3188 Olav Sandstaa 2010-06-08
Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results

The cause for the wrong result returned when adding the LIMIT 1 to
this query was:

* during the optimize phase ICP code pushed part of the WHERE clause
down to the storage engine based on that the query should be
executed as a range query using the primary key.

* in the start of the execution phase the LIMIT clause is considered
and alternative access strategies are evaluated in order to reduce
the cost for reading records. This resulted in the switching to a
different index for reading the data from the storage engine.

As a consequence of this change of access strategy (switching index)
the part of the WHERE clause that had been pushed down to the storage
engine was never evaluated.

The fix for this is to detect that we have switched index and in that
case include the condition that was previously been pushed to the
storage engine into the condition that is evaluated by the server.

Note that this patch also fixes another minor (performance) issue:
if the entire where condition was pushed down to the storage engine
then tab->select_cond would be NULL when calling
test_if_skip_sort_order(). If this was replaced by the pushed index
condition it would never be restored back to NULL. This would result
in that the where condition would be evaluated both by the storage
engine and in the server.
@ mysql-test/include/icp_tests.inc
Test case for Bug#52605 Adding LIMIT 1 clause to query with complex
range predicate causes wrong results.
@ mysql-test/r/innodb_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/order_by.result
These changes in two explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server.

Note that the first of the two explain outputs should have included
a "using index condition" entry in the Extra field. This is an existing
issue unrelated to this fix.
@ sql/sql_select.cc
Extend test_if_skip_sort_order() to handle the situation where parts
of the WHERE condition has been pushed down to the storage engine
(ICP) and where we based on cost estimates switches to use a different
index. In this case we should ensure that the original WHERE condition
as it was before it was pushed to the storage (tab->pre_idx_push_select_cond)
is used instead of the current where condition stored in the join_tab.
The code in test_if_skip_sort_order() already used the original where
condition but restored the current where condition when exiting the function.
The fix for this is to not restore the current where condition if
we have changed to use a new index.

The patch also fixes another minor issue in test_if_skip_sort_order():
If tab->select_cond is NULL we would store this NULL in orig_select_cond.
When we later check if orig_select_cond should be restored back into
tab->select_cond we are not able to distinguish between orig_select_cond
being NULL meaning (a) we have not stored anything in it and (b) we
have stored an empty where condition into it. The consequence of this
issue was that the complete where condition could be evaluated both
by the storage engine and by the server.

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

3203 Olav Sandstaa 2010-06-30
Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results

The cause for the wrong result returned when adding the LIMIT 1 to
this query was:

* during the optimize phase ICP code pushed part of the WHERE clause
down to the storage engine based on that the query should be
executed as a range query using the primary key.

* in the start of the execution phase the LIMIT clause is considered
and alternative access strategies are evaluated in order to reduce
the cost for reading records. This resulted in the switching to a
different index for reading the data from the storage engine.

As a consequence of this change of access strategy (switching index)
the part of the WHERE clause that had been pushed down to the storage
engine was never evaluated.

The fix for this is to detect that we have switched index and in that
case include the condition that was previously been pushed to the
storage engine into the condition that is evaluated by the server.

Note that this patch also fixes another minor (performance) issue:
if the entire where condition was pushed down to the storage engine
then tab->select_cond would be NULL when calling
test_if_skip_sort_order(). If this was replaced by the pre-pushed index
condition it would never be restored back to NULL. This would result
in that the where condition would be evaluated both by the storage
engine and in the server.
@ mysql-test/include/icp_tests.inc
Test case for Bug#52605 Adding LIMIT 1 clause to query with complex
range predicate causes wrong results.
@ mysql-test/r/innodb_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/order_by_all.result
This change in explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ mysql-test/r/order_by_icp_mrr.result
This change in explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.

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

3208 Olav Sandstaa 2010-07-02
Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results

The cause for the wrong result returned when adding the LIMIT 1 to
this query was:

* during the optimize phase ICP code pushed part of the WHERE clause
down to the storage engine based on that the query should be
executed as a range query using the primary key.

* in the start of the execution phase the LIMIT clause is considered
and alternative access strategies are evaluated in order to reduce
the cost for reading records. This resulted in the switching to a
different index for reading the data from the storage engine.

As a consequence of this change of access strategy (switching index)
the part of the WHERE clause that had been pushed down to the storage
engine was never evaluated.

The fix for this is to detect that we have switched index and in that
case include the condition that was previously been pushed to the
storage engine into the condition that is evaluated by the server.

Note that this patch also fixes tow other issues:

* The existing code assumed that tab->pre_idx_push_select_cond contained
the complete original select condition. This was not always the case so
instead of using the tab->pre_idx_push_select_cond we add the
actual pushed index condition to the tab->select_cond in the start of the
function.

* if the entire where condition was pushed down to the storage engine
then tab->select_cond would be NULL when calling
test_if_skip_sort_order(). If this was replaced by the pre-pushed index
condition it would never be restored back to NULL. This would result
in that the where condition would be evaluated both by the storage
engine and in the server.
@ mysql-test/include/icp_tests.inc
Test case for Bug#52605 Adding LIMIT 1 clause to query with complex
range predicate causes wrong results.
@ mysql-test/r/innodb_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/order_by_all.result
This change in explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ mysql-test/r/order_by_icp_mrr.result
This change in explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ sql/sql_select.cc
Extend test_if_skip_sort_order() to handle the situation where parts
of the WHERE condition has been pushed down to the storage engine
(ICP) and where we based on cost estimates switches to use a different
index. In this case we should ensure that the original WHERE condition
as it was before it was pushed to the storage
is used instead of the current where condition stored in the join_tab.
The code in test_if_skip_sort_order() already used the original where
condition but restored the current where condition when exiting the function
The fix for this is to detect if we changes to use a new index and then
not restore the current where condition if we have changed to use a new index.

The patch also fixes two other issues in test_if_skip_sort_order():

* The existing code assumed that tab->pre_idx_push_select_cond contained
the complete original select condition. This was not always the case so
instead of using the tab->pre_idx_push_select_cond we add the
actual pushed index condition to the tab->select_cond in the start of the
function and use this during evaluation.

* If tab->select_cond is NULL we would store this NULL in orig_select_cond.
When we later check if orig_select_cond should be restored back into
tab->select_cond we are not able to distinguish between orig_select_cond
being NULL meaning (a) we have not stored anything in it and (b) we
have stored an empty where condition into it. The consequence of this
issue was that the complete where condition could be evaluated both
by the storage engine and by the server.

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

3232 Olav Sandstaa 2010-08-30
Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results

The cause for the wrong result returned when adding the LIMIT 1 to
this query was:

* during the optimize phase ICP code pushed part of the WHERE clause
down to the storage engine based on that the query should be
executed as a range query using the primary key.

* in the start of the execution phase the LIMIT clause is considered
and alternative access strategies are evaluated in order to reduce
the cost for reading records. This resulted in the switching to a
different index for reading the data from the storage engine.

As a consequence of this change of access strategy (switching index)
the part of the WHERE clause that had been pushed down to the storage
engine was never evaluated.

The fix for this is to detect that we have switched index and in that
case ensure that the condition that was previously been pushed to the
storage engine is included in the condition that is evaluated by the
server.

Note that this patch also fixes tow other issues:

* The existing code assumed that tab->pre_idx_push_select_cond contained
the complete original select condition. This was not always the case.
The situation could occur when ref access is used. In this case the
where condition that is covered by the ref access is not included when
creating the initial select condition for a table (in make_cond_for_table().
The tab->pre_idx_push_select_cond is based on this value. Later, just before
calling test_if_skip_sort_order() the where condition corresponding
to the ref access is added to the table's select condition but not
to the tab->pre_idx_push_select_cond (in add_ref_to_table_cond()). This
patch adds a fix for this by extending add_ref_to_table_cond() to also
add the condition for the ref access to tab->pre_idx_push_select_cond.

* if the entire where condition was pushed down to the storage engine
then tab->select_cond would be NULL when calling
test_if_skip_sort_order(). If this was replaced by the pre-pushed index
condition it would never be restored back to NULL. This would result
in that the where condition would be evaluated both by the storage
engine and in the server.
@ mysql-test/include/icp_tests.inc
Test case for Bug#52605 Adding LIMIT 1 clause to query with complex
range predicate causes wrong results.
@ mysql-test/r/innodb_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/order_by_all.result
This change in explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ mysql-test/r/order_by_icp_mrr.result
This change in explain output are caused by the second
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ sql/sql_select.cc
Extend test_if_skip_sort_order() to handle the situation where parts
of the WHERE condition has been pushed down to the storage engine
(ICP) and where we based on cost estimates switches to use a different
index. In this case we should ensure that the original WHERE condition
as it was before it was pushed to the storage
is used instead of the current where condition stored in the join_tab.
The code in test_if_skip_sort_order() already used the original where
condition but restored the current where condition when exiting the function
The fix for this is to detect if we changes to use a new index and then
not restore the current where condition if we have changed to use a new index.

The patch also fixes two other issues in test_if_skip_sort_order():

* The existing code assumed that tab->pre_idx_push_select_cond contained
the complete original select condition. This was not always the case.
The situation could occur when ref access is used. In this case the
where condition that is covered by the ref access is not included when
creating the initial select condition for a table (in make_cond_for_table().
The tab->pre_idx_push_select_cond is based on this value. Later, just before
calling test_if_skip_sort_order() the where condition corresponding
to the ref access is added to the table's select condition but not
to the tab->pre_idx_push_select_cond (in add_ref_to_table_cond()). This
patch adds a fix for this by extending add_ref_to_table_cond() to also
add the condition for the ref access to tab->pre_idx_push_select_cond.

* If tab->select_cond is NULL we would store this NULL in orig_select_cond.
When we later check if orig_select_cond should be restored back into
tab->select_cond we are not able to distinguish between orig_select_cond
being NULL meaning (a) we have not stored anything in it and (b) we
have stored an empty where condition into it. The consequence of this
issue was that the complete where condition could be evaluated both
by the storage engine and by the server.

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

3271 Olav Sandstaa 2010-10-28
Fix for Bug#52605 Adding LIMIT 1 clause to query with complex range predicate causes wrong results

The cause for the wrong result returned when adding the LIMIT 1 to
this query was:

* during the optimize phase ICP code pushed part of the WHERE clause
down to the storage engine based on that the query should be
executed as a range query using the primary key.

* in the start of the execution phase the LIMIT clause is considered
and alternative access strategies are evaluated in order to reduce
the cost for reading records. This resulted in the switching to a
different index for reading the data from the storage engine.

As a consequence of this change of access strategy (switching index)
the part of the WHERE clause that had been pushed down to the storage
engine was never evaluated.

The fix for this is to detect that we have switched index and in that
case ensure that the condition that was previously been pushed to the
storage engine is included in the condition that is evaluated by the
server.

Note that this patch also fixes two other issues:

* The existing code assumed that tab->pre_idx_push_select_cond contained
the complete original select condition. This was not always the case.
The situation could occur when ref access is used. In this case the
where condition that is covered by the ref access is not included when
creating the initial select condition for a table (in make_cond_for_table()).
The tab->pre_idx_push_select_cond is based on this value. Later, just before
calling test_if_skip_sort_order() the where condition corresponding
to the ref access is added to the table's select condition but not
to the tab->pre_idx_push_select_cond (in add_ref_to_table_cond()). This
patch adds a fix for this by extending add_ref_to_table_cond() to also
add the condition for the ref access to tab->pre_idx_push_select_cond.

* if the entire where condition was pushed down to the storage engine
then tab->select_cond would be NULL when calling
test_if_skip_sort_order(). If this was replaced by the pre-pushed index
condition it would never be restored back to NULL. This would result
in that the where condition would be evaluated both by the storage
engine and in the server.
@ mysql-test/include/icp_tests.inc
Test case for Bug#52605 Adding LIMIT 1 clause to query with complex
range predicate causes wrong results.
@ mysql-test/r/innodb_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/innodb_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_all.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/myisam_icp_none.result
Result for test case for Bug#52605 Adding LIMIT 1 clause to query
with complex range predicate causes wrong results.
@ mysql-test/r/order_by_all.result
This change in explain output are caused by the third
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ mysql-test/r/order_by_icp_mrr.result
This change in explain output are caused by the third
issue fixed in this patch. Do to failing to restore tab->select_cond
to NULL the complete where clause was instead evaluated by the server
in addition to being pushed to the storage engine.
@ sql/sql_select.cc
Extend test_if_skip_sort_order() to handle the situation where parts
of the WHERE condition has been pushed down to the storage engine
(ICP) and where we based on cost estimates switches to use a different
index. In this case we should ensure that the original WHERE condition
as it was before it was pushed to the storage
is used instead of the current where condition stored in the join_tab.
The code in test_if_skip_sort_order() already used the original where
condition but restored the current where condition when exiting the function
The fix for this is to detect if we changes to use a new index and then
not restore the current where condition if we have changed to use a new index.

The patch also fixes two other issues in test_if_skip_sort_order():

* The existing code assumed that tab->pre_idx_push_select_cond contained
the complete original select condition. This was not always the case.
The situation could occur when ref access is used. In this case the
where condition that is covered by the ref access is not included when
creating the initial select condition for a table (in make_cond_for_table()).
The tab->pre_idx_push_select_cond is based on this value. Later, just before
calling test_if_skip_sort_order() the where condition corresponding
to the ref access is added to the table's select condition but not
to the tab->pre_idx_push_select_cond (in add_ref_to_table_cond()). This
patch adds a fix for this by extending add_ref_to_table_cond() to also
add the condition for the ref access to tab->pre_idx_push_select_cond.

* If tab->select_cond is NULL we would store this NULL in orig_select_cond.
When we later check if orig_select_cond should be restored back into
tab->select_cond we are not able to distinguish between orig_select_cond
being NULL meaning (a) we have not stored anything in it and (b) we
have stored an empty where condition into it. The consequence of this
issue was that the complete where condition could be evaluated both
by the storage engine and by the server.

Patch pushed to mysql-next-mr-opt-backporting with revision-id:
olav.sandstaa@oracle.com-20101028102310-jhlaajdwd9fy302s

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (merge vers: 5.6.99-m5) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)

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