List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:March 18 2009 10:20pm
Subject:bzr commit into mysql-6.0-bugteam branch (gshchepa:3115) Bug#40925
View as plain text  
#At file:///work/bzr/6.0-40925/ based on revid:aelkin@stripped

 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

per-file messages:
  mysql-test/r/select.result
    Added test case for bug #40925.
  mysql-test/t/select.test
    Added test case for bug #40925.
  sql/item.cc
    Bug #40925: Equality propagation takes non indexed attribute
    
    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 file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2009-02-10 14:51:18 +0000
+++ b/mysql-test/r/select.result	2009-03-18 22:20:35 +0000
@@ -4417,3 +4417,17 @@ Warnings:
 Warning	1292	Incorrect date value: '10:41:7' for column 'date_nokey' at row 1
 DROP TABLE A,C;
 End of 5.1 tests
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
+EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
+DROP TABLE t1;
+End of 6.0 tests

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2008-12-24 15:31:10 +0000
+++ b/mysql-test/t/select.test	2009-03-18 22:20:35 +0000
@@ -3770,3 +3770,17 @@ SELECT date_nokey FROM C 
 DROP TABLE A,C;
 
 --echo End of 5.1 tests
+
+#
+# Bug #40925: Equality propagation takes non indexed attribute
+#
+
+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;
+EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
+
+DROP TABLE t1;
+
+--echo End of 6.0 tests

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2009-02-20 12:37:37 +0000
+++ b/sql/item.cc	2009-03-18 22:20:35 +0000
@@ -4631,7 +4631,7 @@ Item *Item_field::replace_equal_field(uc
       return const_item;
     }
     Item_field *subst= item_equal->get_first();
-    if (subst && !field->eq(subst->field))
+    if (subst && field->table != subst->field->table && !field->eq(subst->field))
       return subst;
   }
   return this;

Thread
bzr commit into mysql-6.0-bugteam branch (gshchepa:3115) Bug#40925Gleb Shchepa18 Mar