diff options
| -rw-r--r-- | mysql-test/r/group_by.result | 47 | ||||
| -rw-r--r-- | mysql-test/t/group_by.test | 39 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 | ||||
| -rw-r--r-- | sql/sql_select.cc | 17 |
4 files changed, 98 insertions, 6 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index d322952af2b..5a3fc7a337c 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -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 diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 993ee89b279..3206893fa6d 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1333,5 +1333,44 @@ SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS al 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 diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 0278ad201af..ca3989d8b2c 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -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); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 72e82dbfe04..7a2940ce6ff 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8554,9 +8554,10 @@ static COND* substitute_for_best_equal_field(COND *cond, @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(COND *cond, JOIN_TAB *tab) 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(COND *cond, JOIN_TAB *tab) 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, POSITION *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, POSITION *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,ORDER *order,ha_rows select_limit, 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; |