bk commit into 5.1 tree (svoj:1.2367)

[Sergey Vojtovich]

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-12-26 18:04:31+04:00, svoj@stripped +4 -0
  Merge mysql.com:/home/svoj/devel/mysql/BUG25048/mysql-5.0-engines
  into  mysql.com:/home/svoj/devel/mysql/BUG25048/mysql-5.1-engines
  MERGE: 1.1810.2362.29

  BitKeeper/deleted/.del-index_merge.result@stripped, 2006-12-26 17:50:34+04:00,
svoj@stripped +0 -0
    Auto merged
    MERGE: 1.19.2.2

  BitKeeper/deleted/.del-index_merge.result@stripped, 2006-12-26 17:50:34+04:00,
svoj@stripped +0 -0
    Merge rename: mysql-test/r/index_merge.result ->
BitKeeper/deleted/.del-index_merge.result

  BitKeeper/deleted/.del-index_merge_ror.result@stripped, 2006-12-26 17:50:34+04:00,
svoj@stripped +0 -0
    Auto merged
    MERGE: 1.8.1.2

  BitKeeper/deleted/.del-index_merge_ror.result@stripped, 2006-12-26 17:50:34+04:00,
svoj@stripped +0 -0
    Merge rename: mysql-test/r/index_merge_ror.result ->
BitKeeper/deleted/.del-index_merge_ror.result

  mysql-test/include/index_merge_ror.inc@stripped, 2006-12-26 17:50:34+04:00,
svoj@stripped +0 -0
    Auto merged
    MERGE: 1.8.1.2

  mysql-test/include/index_merge_ror.inc@stripped, 2006-12-26 17:50:34+04:00,
svoj@stripped +0 -0
    Merge rename: mysql-test/t/index_merge_ror.test ->
mysql-test/include/index_merge_ror.inc

  sql/opt_range.cc@stripped, 2006-12-26 18:04:25+04:00, svoj@stripped +1 -17
    Use local
    MERGE: 1.159.1.76

# 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:	june.mysql.com
# Root:	/home/svoj/devel/mysql/BUG25048/mysql-5.1-engines/RESYNC

--- 1.19.2.1/mysql-test/r/index_merge.result	2006-12-26 17:47:29 +04:00
+++ 1.23/BitKeeper/deleted/.del-index_merge.result	2006-12-26 17:50:34 +04:00
@@ -397,10 +397,10 @@ count(*)
 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.8.1.1/mysql-test/r/index_merge_ror.result	2006-12-26 17:47:29 +04:00
+++ 1.11/BitKeeper/deleted/.del-index_merge_ror.result	2006-12-26 17:50:34 +04:00
@@ -4,7 +4,7 @@ count(*)
 64801
 explain select key1,key2 from t1 where key1=100 and key2=100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	58	Using intersect(key1,key2); Using
where; Using index
+1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	77	Using intersect(key1,key2); Using
where; Using index
 select key1,key2 from t1 where key1=100 and key2=100;
 key1	key2
 100	100
@@ -18,14 +18,14 @@ insert into t1 (key1, key2, key3, key4, 
 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
 explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	58	Using intersect(key1,key2); Using
where
+1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	77	Using intersect(key1,key2); Using
where
 select key1,key2,filler1 from t1 where key1=100 and key2=100;
 key1	key2	filler1
 100	100	key1-key2-key3-key4
 100	100	key1-key2
 explain select key1,key2 from t1 where key1=100 and key2=100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	58	Using intersect(key1,key2); Using
where; Using index
+1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	77	Using intersect(key1,key2); Using
where; Using index
 select key1,key2 from t1 where key1=100 and key2=100;
 key1	key2
 100	100
@@ -84,7 +84,7 @@ select key1,key2,key3,key4,filler1 from 
 key1	key2	key3	key4	filler1
 explain select key1,key2 from t1 where key1=100 and key2=100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	57	Using intersect(key1,key2); Using
where; Using index
+1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	76	Using intersect(key1,key2); Using
where; Using index
 select key1,key2 from t1 where key1=100 and key2=100;
 key1	key2
 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200,
200,'key1-key2-key3-key4-1');
@@ -121,10 +121,10 @@ key1	key2	key3	key4	filler1
 -1	-1	200	-1	key3
 explain select * from t1 where st_a=1 and st_b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	st_a,st_b	4,4	NULL	2637	Using
intersect(st_a,st_b); Using where
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	st_a,st_b	4,4	NULL	3515	Using
intersect(st_a,st_b); Using where
 explain select st_a,st_b from t1 where st_a=1 and st_b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	st_a,st_b	4,4	NULL	2637	Using
intersect(st_a,st_b); Using where; Using index
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	st_a,st_b	4,4	NULL	3515	Using
intersect(st_a,st_b); Using where; Using index
 explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b	st_b	4	const	15093	Using
where
@@ -136,35 +136,35 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ref	stb_swt1a_2b,stb_swt1b,st_b	stb_swt1a_2b	8	const,const	3879	Using where
 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and
swt2b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt12a,stb_swt1a_2b	12,12	NULL	44	Using
intersect(sta_swt12a,stb_swt1a_2b); Using where
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt12a,stb_swt1a_2b	12,12	NULL	58	Using
intersect(sta_swt12a,stb_swt1a_2b); Using where
 explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) 
 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b	sta_swt12a,stb_swt1b	12,8	NULL	44	Using
intersect(sta_swt12a,stb_swt1b); Using where
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b	sta_swt12a,stb_swt1b	12,8	NULL	58	Using
intersect(sta_swt12a,stb_swt1b); Using where
 explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) 
 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b	sta_swt1a,sta_swt2a,stb_swt1b	8,8,8	NULL	43	Using
intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
+1	SIMPLE	t1	index_merge	sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b	sta_swt1a,sta_swt2a,stb_swt1b	8,8,8	NULL	57	Using
intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
 explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) 
 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt1a,sta_swt2a,st_a,st_b	sta_swt1a,sta_swt2a,st_b	8,8,4	NULL	168	Using
intersect(sta_swt1a,sta_swt2a,st_b); Using where
+1	SIMPLE	t1	index_merge	sta_swt1a,sta_swt2a,st_a,st_b	sta_swt1a,sta_swt2a,st_b	8,8,4	NULL	223	Using
intersect(sta_swt1a,sta_swt2a,st_b); Using where
 explain select * from t1 
 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt12a,stb_swt1a_2b	12,12	NULL	44	Using
intersect(sta_swt12a,stb_swt1a_2b); Using where
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt12a,stb_swt1a_2b	12,12	NULL	58	Using
intersect(sta_swt12a,stb_swt1a_2b); Using where
 explain select * from t1 
 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	174	Using
intersect(sta_swt1a,stb_swt1b); Using where
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	232	Using
intersect(sta_swt1a,stb_swt1b); Using where
 explain select st_a from t1 
 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	174	Using
intersect(sta_swt1a,stb_swt1b); Using where; Using index
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	232	Using
intersect(sta_swt1a,stb_swt1b); Using where; Using index
 explain select st_a from t1 
 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	174	Using
intersect(sta_swt1a,stb_swt1b); Using where; Using index
+1	SIMPLE	t1	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	232	Using
intersect(sta_swt1a,stb_swt1b); Using where; Using index
 drop table t0,t1;
 create table t2 (
 a char(10),

--- 1.8.1.1/mysql-test/t/index_merge_ror.test	2006-12-26 17:47:29 +04:00
+++ 1.12/mysql-test/include/index_merge_ror.inc	2006-12-26 17:50:34 +04:00
@@ -1,10 +1,27 @@
+# include/index_merge_ror.inc
 #
-#  ROR-index_merge tests. 
+#  ROR-index_merge tests.
 #
+# The variable
+#     $engine_type       -- storage engine to be tested
+# has 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_ror.test
+#               main code went into include/index_merge_ror.inc
+#
+
+--echo #---------------- ROR-index_merge tests -----------------------
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
+
 --disable_warnings
 drop table if exists  t0,t1,t2;
 --enable_warnings
---disable_query_log
 create table t1
 (
   /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
@@ -29,7 +46,7 @@ create table t1
   filler4 char (200),
   filler5 char (200),
   filler6 char (200),
-  
+
   /* order of keys is important */
   key sta_swt12a(st_a,swt1a,swt2a),
   key sta_swt1a(st_a,swt1a),
@@ -47,21 +64,26 @@ create table t1
   key(key4)
 ) ;
 
-# Fill table 
+# Fill table
 create table t0 as select * from t1;
+--disable_query_log
+--echo # Printing of many insert into t0 values (....) disabled.
 let $cnt=1000;
 while ($cnt)
 {
   eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3',
'data4', 'data5', 'data6');
   dec $cnt;
 }
+--enable_query_log
 
 alter table t1 disable keys;
+--disable_query_log
+--echo # Printing of many insert into t1 select .... from t0 disabled.
 let $1=4;
 while ($1)
 {
   let $2=4;
-  while ($2) 
+  while ($2)
   {
     let $3=4;
     while ($3)
@@ -74,6 +96,7 @@ while ($1)
  dec $1;
 }
 
+--echo # Printing of many insert into t1 (...) values (....) disabled.
 # Row retrieval tests
 # -1 is used for values 'out of any range we are using'
 # insert enough rows for index intersection to be used for (key1,key2)
@@ -90,18 +113,17 @@ while ($cnt)
   eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1,
100,'key2-key4');
   dec $cnt;
 }
-alter table t1 enable keys;
 --enable_query_log
+alter table t1 enable keys;
 select count(*) from t1;
 
-# One row results tests for cases where a single row matches all conditions 
+# One row results tests for cases where a single row matches all conditions
 explain select key1,key2 from t1 where key1=100 and key2=100;
 select key1,key2 from t1 where key1=100 and key2=100;
-
 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or
key3=100 and key4=100;
 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and
key4=100;
 
-# Several-rows results 
+# Several-rows results
 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
 
@@ -151,7 +173,7 @@ explain select key1,key2 from t1 where k
 select key1,key2 from t1 where key1=100 and key2=100;
 
 # ROR-union tests with various cases.
-#  All scans returning duplicate rows: 
+#  All scans returning duplicate rows:
 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200,
200,'key1-key2-key3-key4-1');
 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200,
200,'key1-key2-key3-key4-2');
 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200,
200,'key1-key2-key3-key4-3');
@@ -170,7 +192,7 @@ explain select key1,key2,key3,key4,fille
 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or
key4=200;
 
 ##
-## Optimizer tests 
+## Optimizer tests
 ##
 
 # Check that the shortest key is used for ROR-intersection, covering and non-covering.
@@ -180,9 +202,9 @@ explain select st_a,st_b from t1 where s
 # Check if "ingore index" syntax works
 explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
 
-# Do many tests 
+# Do many tests
 # Check that keys that don't improve selectivity are skipped.
-# 
+#
 
 # Different value on 32 and 64 bit
 --replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a,
@@ -192,25 +214,25 @@ explain select * from t1 where st_b=1 an
 
 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and
swt2b=1;
 
-explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) 
+explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
   where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 
-explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) 
+explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
   where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 
-explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) 
+explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
   where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 
-explain select * from t1 
+explain select * from t1
   where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
 
-explain select * from t1 
+explain select * from t1
   where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 
-explain select st_a from t1 
+explain select st_a from t1
   where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 
-explain select st_a from t1 
+explain select st_a from t1
   where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 
 drop table t0,t1;
@@ -241,7 +263,7 @@ insert into t2 select * from t2;
 select count(a) from t2 where a='BBBBBBBB';
 select count(a) from t2 where b='BBBBBBBB';
 
-# BUG#1: 
+# BUG#1:
 --replace_result a a_or_b b a_or_b
 explain select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
 select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';