Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2125 06/03/31 21:26:17 igor@stripped +9 -0
Fixed bug #16504.
Multiple equalities were not adjusted after reading constant tables.
It resulted in neglecting good index based methods that could be
used to access of other tables.
sql/sql_select.cc
1.403 06/03/31 21:26:11 igor@stripped +69 -9
Fixed bug #16504.
Adjusted multiple equalities after reading constant tables.
Fixed a few typo in comments.
sql/item_cmpfunc.h
1.121 06/03/31 21:26:11 igor@stripped +1 -0
Fixed bug #16504.
Added method Item_equal::check_const that check appearance of new
constant items in a multiple equality.
sql/item_cmpfunc.cc
1.196 06/03/31 21:26:11 igor@stripped +35 -2
Fixed bug #16504.
Added method Item_equal::check_const that check appearance of new
constant items in a multiple equality.
sql/item.cc
1.211 06/03/31 21:26:11 igor@stripped +1 -1
Fixed bug #16504.
An Item_equal object may contain only a constant member.
It may happen after reading constant tables.
mysql-test/t/select.test
1.98 06/03/31 21:26:11 igor@stripped +23 -0
Added a test case for bug #16504.
mysql-test/r/varbinary.result
1.16 06/03/31 21:26:11 igor@stripped +1 -1
Adjusted a test case results after fix for bug #16504.
mysql-test/r/subselect.result
1.138 06/03/31 21:26:11 igor@stripped +1 -1
Adjusted a test case results after fix for bug #16504.
mysql-test/r/select.result
1.119 06/03/31 21:26:10 igor@stripped +19 -0
Added a test case for bug #16504.
mysql-test/r/having.result
1.21 06/03/31 21:26:10 igor@stripped +1 -1
Adjusted a test case results after fix for bug #16504.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: igor
# Host: rurik.mysql.com
# Root: /home/igor/dev/mysql-5.0-0
--- 1.210/sql/item.cc 2006-03-27 18:28:49 -08:00
+++ 1.211/sql/item.cc 2006-03-31 21:26:11 -08:00
@@ -3744,7 +3744,7 @@
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;
--- 1.195/sql/item_cmpfunc.cc 2006-03-24 12:44:49 -08:00
+++ 1.196/sql/item_cmpfunc.cc 2006-03-31 21:26:11 -08:00
@@ -3602,7 +3602,8 @@
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 @@
} 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();
--- 1.120/sql/item_cmpfunc.h 2006-02-25 07:46:25 -08:00
+++ 1.121/sql/item_cmpfunc.h 2006-03-31 21:26:11 -08:00
@@ -1196,6 +1196,7 @@
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"; }
--- 1.402/sql/sql_select.cc 2006-03-30 11:34:07 -08:00
+++ 1.403/sql/sql_select.cc 2006-03-31 21:26:11 -08:00
@@ -136,7 +136,7 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
{
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 @@
}
+/*
+ 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 @@
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 @@
}
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);
}
--- 1.137/mysql-test/r/subselect.result 2006-03-23 06:09:27 -08:00
+++ 1.138/mysql-test/r/subselect.result 2006-03-31 21:26:11 -08:00
@@ -547,7 +547,7 @@
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);
--- 1.20/mysql-test/r/having.result 2006-02-02 23:56:02 -08:00
+++ 1.21/mysql-test/r/having.result 2006-03-31 21:26:10 -08:00
@@ -12,7 +12,7 @@
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',
--- 1.118/mysql-test/r/select.result 2006-01-13 05:27:37 -08:00
+++ 1.119/mysql-test/r/select.result 2006-03-31 21:26:10 -08:00
@@ -3371,3 +3371,22 @@
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;
--- 1.15/mysql-test/r/varbinary.result 2004-07-19 22:48:24 -07:00
+++ 1.16/mysql-test/r/varbinary.result 2006-03-31 21:26:11 -08:00
@@ -15,7 +15,7 @@
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
--- 1.97/mysql-test/t/select.test 2006-01-13 05:27:37 -08:00
+++ 1.98/mysql-test/t/select.test 2006-03-31 21:26:11 -08:00
@@ -2848,3 +2848,26 @@
#
--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;
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2125) BUG#16504 | igor | 1 Apr |
