Bug #40925 Equality propagation takes non indexed attribute
Submitted: 21 Nov 2008 13:33 Modified: 22 Jun 2009 14:12
Reporter: Oli Sennhauser Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.68, 5.1.29 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any

[21 Nov 2008 13:33] Oli Sennhauser 
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.
[21 Nov 2008 14:05] Oli Sennhauser 
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;
[12 Jan 2009 18:37] Gleb Shchepa 
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;
[18 Mar 2009 21:35] 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/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
[18 Mar 2009 22:07] 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/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
[19 Mar 2009 8:09] 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/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
[13 Apr 2009 9:21] Bugs System 
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)
[13 Apr 2009 23:22] Paul DuBois 
Noted in 6.0.11 changelog.

For some queries, an equality propagation problem could cause a = b
and b = a to be handled differently.
[20 Jun 2009 16:42] Georgi Kodinov 
Pushed into 5.1.35 and 5.0.83
[22 Jun 2009 14:12] Paul DuBois 
Noted in 5.0.83, 5.1.35 changelogs.