diff options
| -rw-r--r-- | mysql-test/r/having.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/select.result | 19 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/varbinary.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/select.test | 23 | ||||
| -rw-r--r-- | sql/item.cc | 2 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 37 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 | ||||
| -rw-r--r-- | sql/sql_select.cc | 78 |
9 files changed, 151 insertions, 15 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index e54f6d7f2a4..225d5a475ff 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -12,7 +12,7 @@ explain extended select count(a) as b from t1 where a=0 having b >=0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 0) having (count(`test`.`t1`.`a`) >= 0) +Note 1003 select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where 0 having (count(`test`.`t1`.`a`) >= 0) drop table t1; CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index df68cdfff53..02f27423f99 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3371,3 +3371,22 @@ NULL a NULL drop table t1,t2; select * from (select * left join t on f1=f2) tt; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on f1=f2) tt' at line 1 +CREATE TABLE t1 (sku int PRIMARY KEY, pr int); +CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); +INSERT INTO t1 VALUES +(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10); +INSERT INTO t2 VALUES +(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), +(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); +SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr +FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); +sku sppr name sku pr +20 10 bbb 10 10 +20 10 bbb 20 10 +EXPLAIN +SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr +FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 6 Using where +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index d8561915564..bd0fa5ae661 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -547,7 +547,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = _latin1'1'))) and (`test`.`t1`.`numeropost` = _latin1'1')) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = _latin1'1')) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result index e55e6b35915..e62051df5cd 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -15,7 +15,7 @@ explain extended select * from t1 where UNIQ=0x38afba1d73e6a18a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const UNIQ UNIQ 8 const 1 Warnings: -Note 1003 select `test`.`t1`.`ID` AS `ID`,`test`.`t1`.`UNIQ` AS `UNIQ` from `test`.`t1` where (`test`.`t1`.`UNIQ` = 4084688022709641610) +Note 1003 select `test`.`t1`.`ID` AS `ID`,`test`.`t1`.`UNIQ` AS `UNIQ` from `test`.`t1` where 1 drop table t1; select x'hello'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'hello'' at line 1 diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 01d5f2eb4d1..c4fe1906cbc 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2848,3 +2848,26 @@ drop table t1,t2; # --error 1064 select * from (select * left join t on f1=f2) tt; + +# +# Bug #16504: re-evaluation of Item_equal object after reading const table +# + +CREATE TABLE t1 (sku int PRIMARY KEY, pr int); +CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); + +INSERT INTO t1 VALUES + (10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10); + +INSERT INTO t2 VALUES + (10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), + (50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); + +SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr + FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); +EXPLAIN +SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr + FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); + + +DROP TABLE t1,t2; diff --git a/sql/item.cc b/sql/item.cc index 8ffe7a6b2fd..e14367c7de1 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3744,7 +3744,7 @@ Item *Item_field::replace_equal_field(byte *arg) if (item_equal) { Item_field *subst= item_equal->get_first(); - if (!field->eq(subst->field)) + if (subst && !field->eq(subst->field)) return subst; } return this; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 2d74ea9c518..53efb65e1cb 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -3602,7 +3602,8 @@ void Item_equal::add(Item *c) Item_func_eq *func= new Item_func_eq(c, const_item); func->set_cmp_func(); func->quick_fix_field(); - cond_false = !(func->val_int()); + if ((cond_false= !func->val_int())) + const_item_cache= 1; } void Item_equal::add(Item_field *f) @@ -3734,13 +3735,45 @@ void Item_equal::sort(Item_field_cmpfunc cmp, void *arg) } while (swap); } + +/* + Check appearance of new constant items in the multiple equality object + + SYNOPSIS + check() + + DESCRIPTION + The function checks appearance of new constant items among + the members of multiple equalities. Each new constant item is + compared with the designated constant item if there is any in the + multiple equality. If there is none the first new constant item + becomes designated. + + RETURN VALUES + none +*/ + +void Item_equal::check_const() +{ + List_iterator<Item_field> it(fields); + Item *item; + while ((item= it++)) + { + if (item->const_item()) + { + it.remove(); + add(item); + } + } +} + bool Item_equal::fix_fields(THD *thd, Item **ref) { List_iterator_fast<Item_field> li(fields); Item *item; not_null_tables_cache= used_tables_cache= 0; const_item_cache= 0; - while ((item=li++)) + while ((item= li++)) { table_map tmp_table_map; used_tables_cache|= item->used_tables(); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 89aafa5721e..3d94832412e 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1196,6 +1196,7 @@ public: bool contains(Field *field); Item_field* get_first() { return fields.head(); } void merge(Item_equal *item); + void check_const(); enum Functype functype() const { return MULT_EQUAL_FUNC; } longlong val_int(); const char *func_name() const { return "multiple equal"; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3fa77a73e41..13e0381349a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -136,7 +136,7 @@ static enum_nested_loop_state end_write_group(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); static int test_if_group_changed(List<Cached_item> &list); -static int join_read_const_table(JOIN_TAB *tab, POSITION *pos); +static int join_read_const_table(JOIN *join, JOIN_TAB *tab, POSITION *pos); static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); @@ -2111,7 +2111,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds, s= p_pos->table; s->type=JT_SYSTEM; join->const_table_map|=s->table->map; - if ((tmp=join_read_const_table(s, p_pos))) + if ((tmp=join_read_const_table(join, s, p_pos))) { if (tmp > 0) DBUG_RETURN(1); // Fatal error @@ -2148,7 +2148,8 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds, s->type=JT_SYSTEM; join->const_table_map|=table->map; set_position(join,const_count++,s,(KEYUSE*) 0); - if ((tmp= join_read_const_table(s,join->positions+const_count-1))) + if ((tmp= join_read_const_table(join, s, + join->positions+const_count-1))) { if (tmp > 0) DBUG_RETURN(1); // Fatal error @@ -2200,7 +2201,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds, if (create_ref_for_key(join, s, start_keyuse, found_const_table_map)) DBUG_RETURN(1); - if ((tmp=join_read_const_table(s, + if ((tmp=join_read_const_table(join, s, join->positions+const_count-1))) { if (tmp > 0) @@ -6796,8 +6797,8 @@ static COND *build_equal_items_for_cond(COND *cond, return item_equal; } /* - For each field reference in cond, not from equalitym predicates, - set a pointer to the multiple equality if belongs to (if there is any) + For each field reference in cond, not from equal item predicates, + set a pointer to the multiple equality it belongs to (if there is any) */ cond= cond->transform(&Item::equal_fields_propagator, (byte *) inherited); @@ -6982,7 +6983,7 @@ static int compare_fields_by_table_order(Item_field *field1, NOTES Before generating an equality function checks that it has not - been generated for multiple equalies of the upper levels. + been generated for multiple equalities of the upper levels. E.g. for the following where condition WHERE a=5 AND ((a=b AND b=c) OR c>4) the upper level AND condition will contain =(5,a), @@ -7155,7 +7156,7 @@ static COND* substitute_for_best_equal_field(COND *cond, { cond= eliminate_item_equal(cond, cond_equal->upper_levels, item_equal); // This occurs when eliminate_item_equal() founds that cond is - // always false and substitues it with Item_int 0. + // always false and substitutes it with Item_int 0. // Due to this, value of item_equal will be 0, so just return it. if (cond->type() != Item::COND_ITEM) break; @@ -7177,6 +7178,45 @@ static COND* substitute_for_best_equal_field(COND *cond, } +/* + Check appearance of new constant items in multiple equalities + of a condition after reading a constant table + + SYNOPSIS + check_const_equal_item() + cond condition whose multiple equalities are to be checked + table constant table that has been read + + DESCRIPTION + The function retrieves the cond condition and for each encountered + multiple equality checks whether new constants have appeared after + reading the constant (single row) table tab. If so it adjusts + the multiple equality appropriately. +*/ + +static void check_const_equal_items(COND *cond, + JOIN_TAB *tab) +{ + if (!(cond->used_tables() & tab->table->map)) + return; + + if (cond->type() == Item::COND_ITEM) + { + List<Item> *cond_list= ((Item_cond*) cond)->argument_list(); + List_iterator_fast<Item> li(*cond_list); + Item *item; + while ((item= li++)) + check_const_equal_items(item, tab); + } + else if (cond->type() == Item::FUNC_ITEM && + ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + Item_equal *item_equal= (Item_equal *) cond; + item_equal->check_const(); + } +} + + /* change field = field to field = const for each found field = const in the and_level @@ -10099,7 +10139,7 @@ int safe_index_read(JOIN_TAB *tab) static int -join_read_const_table(JOIN_TAB *tab, POSITION *pos) +join_read_const_table(JOIN *join, JOIN_TAB *tab, POSITION *pos) { int error; DBUG_ENTER("join_read_const_table"); @@ -10151,6 +10191,26 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) } if (!table->null_row) table->maybe_null=0; + + /* Check appearance of new constant items in Item_equal objects */ + if (join->conds) + check_const_equal_items(join->conds, tab); + TABLE_LIST *tbl; + for (tbl= join->select_lex->leaf_tables; tbl; tbl= tbl->next_leaf) + { + TABLE_LIST *embedded; + TABLE_LIST *embedding= tbl; + do + { + embedded= embedding; + if (embedded->on_expr) + check_const_equal_items(embedded->on_expr, tab); + embedding= embedded->embedding; + } + while (embedding && + embedding->nested_join->join_list.head() == embedded); + } + DBUG_RETURN(0); } |