Bug #40925 Equality propagation takes non indexed attribute

Description:
select a, b, c from t1 where a > '2008-11-21' and b = a limit 10;

select a, b, c from t1 where a > '2008-11-21' and b = a limit 10;

Gives different execution plans and thus bad execution times in one case.

How to repeat:
See private comment.

Suggested fix:
Equality propagation should consider indexed attributes.

In the description it should be written:

select a, b, c from t1 where a > '2008-11-21' and b = a limit 10;

select a, b, c from t1 where a > '2008-11-21' and a = b limit 10;

Minimized test case is:

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT KEY, b INT);

INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);

EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2;
SHOW SESSION STATUS LIKE 'Last_query_cost';

EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
SHOW SESSION STATUS LIKE 'Last_query_cost';

DROP TABLE t1;

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

3115 Gleb Shchepa	2009-03-19
      Bug #40925: Equality propagation takes non indexed attribute
      
      Query execution plans and execution time of queries like
      
        select a, b, c from t1 
          where a > '2008-11-21' and b = a limit 10
      
      depended on the order of equality operator parameters:
      "b = a" and "a = b" are not same.
      
      
      An equality propagation algorithm has been fixed:
      the substitute_for_best_equal_field function should not
      substitute a field for an equal field if both fields belong
      to the same table.
      modified:
        mysql-test/r/select.result
        mysql-test/t/select.test
        sql/item.cc

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

3115 Gleb Shchepa	2009-03-19
      Bug #40925: Equality propagation takes non indexed attribute
      
      Query execution plans and execution time of queries like
      
        select a, b, c from t1 
          where a > '2008-11-21' and b = a limit 10
      
      depended on the order of equality operator parameters:
      "b = a" and "a = b" are not same.
      
      
      An equality propagation algorithm has been fixed:
      the substitute_for_best_equal_field function should not
      substitute a field for an equal field if both fields belong
      to the same table.
      modified:
        mysql-test/r/select.result
        mysql-test/t/select.test
        sql/item.cc

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

3132 Gleb Shchepa	2009-03-19
      Bug #40925: Equality propagation takes non indexed attribute
      
      Query execution plans and execution time of queries like
      
        select a, b, c from t1
          where a > '2008-11-21' and b = a limit 10
      
      depended on the order of equality operator parameters:
      "b = a" and "a = b" are not same.
      
      
      An equality propagation algorithm has been fixed:
      the substitute_for_best_equal_field function should not
      substitute a field for an equal field if both fields belong
      to the same table.
      modified:
        mysql-test/r/select.result
        mysql-test/r/select_jcl6.result
        mysql-test/t/select.test
        sql/item.cc

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:gshchepa@mysql.com-20090319082958-26eqd21f1g4iyisy) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

For some queries, an equality propagation problem could cause a = b
and b = a to be handled differently.

Pushed into 5.1.35 and 5.0.83

Noted in 5.0.83, 5.1.35 changelogs.