Below is the list of changes that have just been committed into a local
5.1 repository of svoj. When svoj 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@stripped, 2006-09-12 18:42:10+05:00, svoj@april.(none) +7 -0
Merge april.(none):/home/svoj/devel/mysql/BUG20256/mysql-5.0-engines
into april.(none):/home/svoj/devel/mysql/BUG20256/mysql-5.1-engines
MERGE: 1.1810.1897.27
BitKeeper/deleted/.del-index_merge.result@stripped, 2006-09-12 18:27:07+05:00, svoj@april.(none) +0 -0
Auto merged
MERGE: 1.19.1.2
BitKeeper/deleted/.del-index_merge.result@stripped, 2006-09-12 18:27:07+05:00, svoj@april.(none) +0 -0
Merge rename: mysql-test/r/index_merge.result -> BitKeeper/deleted/.del-index_merge.result
mysql-test/include/index_merge1.inc@stripped, 2006-09-12 18:42:08+05:00, svoj@april.(none) +1 -1
Manual merge.
MERGE: 1.14.1.2
mysql-test/include/index_merge1.inc@stripped, 2006-09-12 18:27:07+05:00, svoj@april.(none) +0 -0
Merge rename: mysql-test/t/index_merge.test -> mysql-test/include/index_merge1.inc
sql/ha_myisam.cc@stripped, 2006-09-12 18:27:07+05:00, svoj@april.(none) +0 -0
Auto merged
MERGE: 1.147.1.20
sql/ha_myisam.h@stripped, 2006-09-12 18:27:07+05:00, svoj@april.(none) +0 -0
Auto merged
MERGE: 1.68.1.1
sql/handler.cc@stripped, 2006-09-12 18:27:07+05:00, svoj@april.(none) +0 -0
Auto merged
MERGE: 1.167.20.1
sql/handler.h@stripped, 2006-09-12 18:42:08+05:00, svoj@april.(none) +1 -1
Manual merge.
MERGE: 1.138.2.40
sql/opt_range.cc@stripped, 2006-09-12 18:42:08+05:00, svoj@april.(none) +1 -2
Manual merge.
MERGE: 1.159.1.59
# 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: svoj
# Host: april.(none)
# Root: /home/svoj/devel/mysql/BUG20256/mysql-5.1-engines/RESYNC
--- 1.187/sql/ha_myisam.cc 2006-09-12 18:42:16 +05:00
+++ 1.188/sql/ha_myisam.cc 2006-09-12 18:42:16 +05:00
@@ -147,6 +147,14 @@
can_enable_indexes(1)
{}
+handler *ha_myisam::clone(MEM_ROOT *mem_root)
+{
+ ha_myisam *new_handler= static_cast <ha_myisam *>(handler::clone(mem_root));
+ if (new_handler)
+ new_handler->file->state= file->state;
+ return new_handler;
+}
+
static const char *ha_myisam_exts[] = {
".MYI",
--- 1.74/sql/ha_myisam.h 2006-09-12 18:42:16 +05:00
+++ 1.75/sql/ha_myisam.h 2006-09-12 18:42:16 +05:00
@@ -45,6 +45,7 @@
public:
ha_myisam(TABLE_SHARE *table_arg);
~ha_myisam() {}
+ handler *clone(MEM_ROOT *mem_root);
const char *table_type() const { return "MyISAM"; }
const char *index_type(uint key_number);
const char **bas_ext() const;
--- 1.254/sql/handler.cc 2006-09-12 18:42:16 +05:00
+++ 1.255/sql/handler.cc 2006-09-12 18:42:16 +05:00
@@ -1416,6 +1416,15 @@
/****************************************************************************
** General handler functions
****************************************************************************/
+handler *handler::clone(MEM_ROOT *mem_root)
+{
+ handler *new_handler= get_new_handler(table, mem_root, table->s->db_type);
+ if (new_handler && !new_handler->ha_open(table->s->path, table->db_stat,
+ HA_OPEN_IGNORE_IF_LOCKED))
+ return new_handler;
+ return NULL;
+}
+
void handler::ha_statistic_increment(ulong SSV::*offset) const
--- 1.232/sql/handler.h 2006-09-12 18:42:16 +05:00
+++ 1.233/sql/handler.h 2006-09-12 18:42:16 +05:00
@@ -942,6 +942,7 @@
{
/* TODO: DBUG_ASSERT(inited == NONE); */
}
+ virtual handler *clone(MEM_ROOT *mem_root);
/* This is called after create to allow us to set up cached variables */
void init()
{
--- 1.228/sql/opt_range.cc 2006-09-12 18:42:16 +05:00
+++ 1.229/sql/opt_range.cc 2006-09-12 18:42:16 +05:00
@@ -1164,11 +1164,7 @@
}
thd= head->in_use;
- if (!(file= get_new_handler(head->s, thd->mem_root, head->s->db_type)))
- goto failure;
- DBUG_PRINT("info", ("Allocated new handler 0x%lx", (long) file));
- if (file->ha_open(head, head->s->normalized_path.str, head->db_stat,
- HA_OPEN_IGNORE_IF_LOCKED))
+ if (!(file= head->file->clone(thd->mem_root)))
{
/* Caller will free the memory */
goto failure;
--- 1.19.1.1/mysql-test/r/index_merge.result 2006-09-12 18:42:16 +05:00
+++ 1.22/BitKeeper/deleted/.del-index_merge.result 2006-09-12 18:42:16 +05:00
@@ -397,10 +397,10 @@
8704
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 24 Using intersect(cola,colb); Using where
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 24 Using intersect(cola,colb); Using where
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
drop table t1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--- 1.14.1.1/mysql-test/t/index_merge.test 2006-09-12 18:42:16 +05:00
+++ 1.17/mysql-test/include/index_merge1.inc 2006-09-12 18:42:16 +05:00
@@ -1,6 +1,26 @@
+# include/index_merge1.inc
#
# Index merge tests
#
+# The variables
+# $engine_type -- storage engine to be tested
+# $merge_table_support -- 1 storage engine supports merge tables
+# -- 0 storage engine does not support merge tables
+# have to be set before sourcing this script.
+#
+# Note: The comments/expectations refer to MyISAM.
+# They might be not valid for other storage engines.
+#
+# Last update:
+# 2006-08-02 ML test refactored
+# old name was t/index_merge.test
+# main code went into include/index_merge1.inc
+#
+
+--echo #---------------- Index merge test 1 -------------------------------------------
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
+
--disable_warnings
drop table if exists t0, t1, t2, t3, t4;
--enable_warnings
@@ -8,7 +28,7 @@
# Create and fill a table with simple keys
create table t0
(
- key1 int not null,
+ key1 int not null,
INDEX i1(key1)
);
@@ -36,11 +56,11 @@
update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
analyze table t0;
-# 1. One index
+# 1. One index
explain select * from t0 where key1 < 3 or key1 > 1020;
# 2. Simple cases
-explain
+explain
select * from t0 where key1 < 3 or key2 > 1020;
select * from t0 where key1 < 3 or key2 > 1020;
@@ -48,6 +68,7 @@
explain
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
+# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
# 3. Check that index_merge doesn't break "ignore/force/use index"
@@ -60,8 +81,8 @@
# 4. Check if conjuncts are grouped by keyuse
-explain
- select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
+explain
+ select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
(key1>10 and key1<12) or (key2>100 and key2<110);
# 5. Check index_merge with conjuncts that are always true/false
@@ -78,13 +99,13 @@
explain select * from t0 where key2=10 or key3=3 or key4 is null;
# some more complicated cases
-explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
+explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
(key3=10) or (key4 <=> null);
-explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
+explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
(key3=10) or (key4 <=> null);
# 6.Several ways to do index_merge, (ignored) index_merge vs. range
-explain select * from t0 where
+explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
explain
@@ -93,23 +114,23 @@
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
-explain select * from t0 where
+explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
-
+
# now index_merge is not used at all when "range" is possible
-explain select * from t0 where
+explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 100);
# this even can cause "all" scan:
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 1000);
-
+
# 7. Complex cases
# tree_or(List<SEL_IMERGE>, range SEL_TREE).
-explain select * from t0 where
- ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
- or
+explain select * from t0 where
+ ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+ or
key2 > 5;
explain select * from t0 where
@@ -121,18 +142,18 @@
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
or
key1 < 7;
-
-# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
-explain select * from t0 where
- ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
- or
+
+# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
+explain select * from t0 where
+ ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
+ or
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
-
+
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
@@ -152,7 +173,7 @@
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
-
+
# 8. Verify that "order by" after index merge uses filesort
select * from t0 where key1 < 5 or key8 < 4 order by key1;
@@ -186,10 +207,8 @@
key2_1 int not null,
key2_2 int not null,
key3 int not null,
-
index i1a (key1a, key1b),
index i1b (key1b, key1a),
-
index i2_1(key2, key2_1),
index i2_2(key2, key2_1)
);
@@ -197,8 +216,8 @@
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
# the following will be handled by index_merge:
-select * from t4 where key1a = 3 or key1b = 4;
-explain select * from t4 where key1a = 3 or key1b = 4;
+select * from t4 where key1a = 3 or key1b = 4;
+explain select * from t4 where key1a = 3 or key1b = 4;
# and the following will not
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
@@ -213,27 +232,27 @@
insert into t1 select * from t0;
# index_merge on first table in join
-explain select * from t0 left join t1 on (t0.key1=t1.key1)
- where t0.key1=3 or t0.key2=4;
+explain select * from t0 left join t1 on (t0.key1=t1.key1)
+ where t0.key1=3 or t0.key2=4;
select * from t0 left join t1 on (t0.key1=t1.key1)
where t0.key1=3 or t0.key2=4;
-explain
+explain
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
# index_merge vs. ref
-explain
-select * from t0,t1 where (t0.key1=t1.key1) and
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2=4) and t1.key1<200;
# index_merge vs. ref
-explain
-select * from t0,t1 where (t0.key1=t1.key1) and
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2<4) and t1.key1=2;
# index_merge on second table in join
-explain select * from t0,t1 where t0.key1 = 5 and
+explain select * from t0,t1 where t0.key1 = 5 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
# Fix for bug#1974
@@ -241,7 +260,7 @@
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
# index_merge inside union
-explain select * from t1 where key1=3 or key2=4
+explain select * from t1 where key1=3 or key2=4
union select * from t1 where key1<4 or key3=5;
# index merge in subselect
@@ -256,21 +275,23 @@
alter table t3 add keyC int not null, add index iC(keyC);
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
-explain select * from t3 where
+explain select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
-
+
select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
- key9=9 or keyA=10 or keyB=11 or keyC=12;
+ key9=9 or keyA=10 or keyB=11 or keyC=12;
# Test for Bug#3183
explain select * from t0 where key1 < 3 or key2 < 4;
+# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
select * from t0 where key1 < 3 or key2 < 4;
update t0 set key8=123 where key1 < 3 or key2 < 4;
+# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
select * from t0 where key1 < 3 or key2 < 4;
delete from t0 where key1 < 3 or key2 < 4;
@@ -283,47 +304,47 @@
insert into t4 values (1),(4),(3);
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size= 4000;
-explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
- from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+ from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
where (A.key1 < 500000 or A.key2 < 3)
and (B.key1 < 500000 or B.key2 < 3);
-select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
- from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+ from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
where (A.key1 < 500000 or A.key2 < 3)
and (B.key1 < 500000 or B.key2 < 3);
update t0 set key1=1;
-explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
- from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+ from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
where (A.key1 = 1 or A.key2 = 1)
and (B.key1 = 1 or B.key2 = 1);
-select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
- from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+ from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
where (A.key1 = 1 or A.key2 = 1)
and (B.key1 = 1 or B.key2 = 1);
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
-# The next query will not use index i7 in intersection if the OS doesn't
+# The next query will not use index i7 in intersection if the OS doesn't
# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
# scan cost estimates depend on ha_myisam::ref_length)
--replace_column 9 #
--replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?"
-explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
- from t0 as A, t0 as B
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+ from t0 as A, t0 as B
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
-select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
- from t0 as A, t0 as B
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+ from t0 as A, t0 as B
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
set join_buffer_size= @save_join_buffer_size;
-# Test for BUG#4177 ends
+# Test for BUG#4177 ends
drop table t0, t1, t2, t3, t4;
@@ -357,13 +378,15 @@
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
drop table t1;
+if ($merge_table_support)
+{
#
# BUG#17314: Index_merge/intersection not choosen by the optimizer for MERGE tables
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
- a int, b int,
+ a int, b int,
filler1 char(200), filler2 char(200),
key(a),key(b)
);
@@ -371,7 +394,7 @@
create table t2 like t1;
create table t3 (
- a int, b int,
+ a int, b int,
filler1 char(200), filler2 char(200),
key(a),key(b)
) engine=merge union=(t1,t2);
@@ -383,6 +406,7 @@
drop table t3;
drop table t0, t1, t2;
+}
#
# BUG#20256 - LOCK WRITE - MyISAM
| Thread |
|---|
| • bk commit into 5.1 tree (svoj:1.2279) | Sergey Vojtovich | 12 Sep |
