[Commits] Rev 3140: fix for LP #bug967242 in file:///home/bell/maria/bzr/work-maria-5.2-lpb967242/

sanja at montyprogram.com sanja at montyprogram.com
Mon Apr 30 09:57:04 EEST 2012


At file:///home/bell/maria/bzr/work-maria-5.2-lpb967242/

------------------------------------------------------------
revno: 3140
revision-id: sanja at montyprogram.com-20120430065703-tflepdkqiclmvnzv
parent: wlad at montyprogram.com-20120425133019-wbbbxfiyh0694s9u
committer: sanja at montyprogram.com
branch nick: work-maria-5.2-lpb967242
timestamp: Mon 2012-04-30 09:57:03 +0300
message:
  fix for LP #bug967242
  
  The problem was in the code (update_const_equal_items()) which marked index parts constant independently of the place where the equality was used.
  In the test suite it marked t2_1.c part constant despite the fact that it connected by OR with other expression.
  
  Solution is to mark constant only top equalities connected with AND.
-------------- next part --------------
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2012-03-14 10:09:03 +0000
+++ b/mysql-test/r/group_by.result	2012-04-30 06:57:03 +0000
@@ -1958,4 +1958,51 @@ field1	field2
 2009-02-19 02:05:00	5
 SET SESSION SQL_MODE=default;
 drop table t1;
+#
+# LP bug#967242 Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE
+#
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('x');
+CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'),
+(0, 'p'),(3, 'j'),(8, 'c');
+SELECT t2_1.b as zzz
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+rand() + 1 > 0 OR
+a = t2_1.c
+GROUP BY zzz;
+zzz
+0
+3
+4
+8
+SELECT t2_1.b as zzz
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+1 > 0 OR
+a = t2_1.c
+GROUP BY zzz;
+zzz
+0
+3
+4
+8
+SELECT t2_1.b as zzz
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+t2_1.b + 1 > 0 OR
+a = t2_1.c
+GROUP BY zzz;
+zzz
+0
+3
+4
+8
+#TODO: in merge with 5.3 add original test suite
+drop table t1, t2;
 # End of 5.2 tests

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2012-03-14 10:09:03 +0000
+++ b/mysql-test/t/group_by.test	2012-04-30 06:57:03 +0000
@@ -1333,5 +1333,44 @@ SELECT alias2.f3 AS field1 , alias2.f1 A
 SET SESSION SQL_MODE=default;
 drop table t1;
 
+--echo #
+--echo # LP bug#967242 Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE
+--echo #
+
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('x');
+CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'),
+(0, 'p'),(3, 'j'),(8, 'c');
+
+SELECT t2_1.b as zzz
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+rand() + 1 > 0 OR
+a = t2_1.c
+GROUP BY zzz;
+
+SELECT t2_1.b as zzz
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+1 > 0 OR
+a = t2_1.c
+GROUP BY zzz;
+
+SELECT t2_1.b as zzz
+FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
+ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
+WHERE
+t2_1.b + 1 > 0 OR
+a = t2_1.c
+GROUP BY zzz;
+
+--echo #TODO: in merge with 5.3 add original test suite
+
+drop table t1, t2;
+
 --echo # End of 5.2 tests
 

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2011-11-24 16:48:58 +0000
+++ b/sql/item_cmpfunc.h	2012-04-30 06:57:03 +0000
@@ -1523,6 +1523,7 @@ public:
   friend int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
                          COND **conds);
   void top_level_item() { abort_on_null=1; }
+  bool top_level() { return abort_on_null; }
   void copy_andor_arguments(THD *thd, Item_cond *item);
   bool walk(Item_processor processor, bool walk_subquery, uchar *arg);
   Item *transform(Item_transformer transformer, uchar *arg);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-04-05 10:01:52 +0000
+++ b/sql/sql_select.cc	2012-04-30 06:57:03 +0000
@@ -8554,9 +8554,10 @@ static COND* substitute_for_best_equal_f
 
   @param cond       condition whose multiple equalities are to be checked
   @param table      constant table that has been read
+  @param const_key  mark key parts as constant
 */
 
-static void update_const_equal_items(COND *cond, JOIN_TAB *tab)
+static void update_const_equal_items(COND *cond, JOIN_TAB *tab, bool const_key)
 {
   if (!(cond->used_tables() & tab->table->map))
     return;
@@ -8567,7 +8568,10 @@ static void update_const_equal_items(CON
     List_iterator_fast<Item> li(*cond_list);
     Item *item;
     while ((item= li++))
-      update_const_equal_items(item, tab);
+      update_const_equal_items(item, tab,
+                               (((Item_cond*) cond)->top_level() &&
+                                ((Item_cond*) cond)->functype() ==
+                                Item_func::COND_AND_FUNC));
   }
   else if (cond->type() == Item::FUNC_ITEM && 
            ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
@@ -8598,7 +8602,8 @@ static void update_const_equal_items(CON
           TABLE *tab= field->table;
           KEYUSE *use;
           for (use= stat->keyuse; use && use->table == tab; use++)
-            if (possible_keys.is_set(use->key) && 
+            if (const_key &&
+                possible_keys.is_set(use->key) &&
                 tab->key_info[use->key].key_part[use->keypart].field ==
                 field)
               tab->const_key_parts[use->key]|= use->keypart_map;
@@ -12236,7 +12241,7 @@ join_read_const_table(JOIN_TAB *tab, POS
   /* Check appearance of new constant items in Item_equal objects */
   JOIN *join= tab->join;
   if (join->conds)
-    update_const_equal_items(join->conds, tab);
+    update_const_equal_items(join->conds, tab, TRUE);
   TABLE_LIST *tbl;
   for (tbl= join->select_lex->leaf_tables; tbl; tbl= tbl->next_leaf)
   {
@@ -12246,7 +12251,7 @@ join_read_const_table(JOIN_TAB *tab, POS
     {
       embedded= embedding;
       if (embedded->on_expr)
-         update_const_equal_items(embedded->on_expr, tab);
+         update_const_equal_items(embedded->on_expr, tab, TRUE);
       embedding= embedded->embedding;
     }
     while (embedding &&
@@ -13799,7 +13804,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
   int ref_key;
   uint ref_key_parts;
   int order_direction= 0;
-  uint used_key_parts;
+  uint used_key_parts= 0;
   TABLE *table=tab->table;
   SQL_SELECT *select=tab->select;
   key_map usable_keys;



More information about the commits mailing list