This changeset belongs to

      WL#3397 Refactoring storage engine test cases (for falcon)
    It contains also fixes according to code review.
    Contents: Testcases which were in history dedicated to InnoDB or MyISAM only.
    Modifications:
    1. Shift the main testing code into include/<testing field>.inc
       Introduce $variables which can be used to omit tests for features which are not supported by
       certain storage engines.
    2. The storage engine to be tested is assigned within the toplevel script (t/<whatever>_<engine>.test)
       via variable $engine_type and the the main testing code is sourced from 
       include/<testing field>.inc
    3. Some toplevel testscripts have to be renamed to
       - avoid immediate or future namespace clashes
       - show via filename which storage engine is tested
    4. Minor code cleanup like remove trailing spaces, some additional comments ....


mysql-test/t/unsafe_binlog_innodb-master.opt:
  Rename: mysql-test/t/innodb_unsafe_binlog-master.opt -> mysql-test/t/unsafe_binlog_innodb-master.opt
mysql-test/r/read_many_rows_innodb.result:
  Rename: mysql-test/r/innodb-big.result -> mysql-test/r/read_many_rows_innodb.result
mysql-test/t/cache_innodb-master.opt:
  Rename: mysql-test/t/innodb_cache-master.opt -> mysql-test/t/cache_innodb-master.opt
mysql-test/t/concurrent_innodb-master.opt:
  Rename: mysql-test/t/innodb_concurrent-master.opt -> mysql-test/t/concurrent_innodb-master.opt
BitKeeper/deleted/.del-index_merge.result:
  Delete: mysql-test/r/index_merge.result
BitKeeper/deleted/.del-index_merge_innodb.result:
  Delete: mysql-test/r/index_merge_innodb.result
BitKeeper/deleted/.del-index_merge_innodb2.result:
  Delete: mysql-test/r/index_merge_innodb2.result
BitKeeper/deleted/.del-index_merge_ror.result:
  Delete: mysql-test/r/index_merge_ror.result
BitKeeper/deleted/.del-index_merge_ror_cpk.result:
  Delete: mysql-test/r/index_merge_ror_cpk.result
mysql-test/r/index_merge_innodb.result:
  BitKeeper file /home/matthias/Arbeit/mysql-5.1-engines/src-1/mysql-test/r/index_merge_innodb.result
mysql-test/t/index_merge_innodb.test:
  BitKeeper file /home/matthias/Arbeit/mysql-5.1-engines/src-1/mysql-test/t/index_merge_innodb.test
mysql-test/t/index_merge_myisam.test:
  BitKeeper file /home/matthias/Arbeit/mysql-5.1-engines/src-1/mysql-test/t/index_merge_myisam.test
mysql-test/include/concurrent.inc:
  1. This file contains now the main testing code of the former t/innodb_concurrent.test.
  2. It is now sourced by t/concurrent_innodb.test.
mysql-test/include/deadlock.inc:
  1. This file contains now the main testing code of the former t/innodb-deadlock.test
  2. It is now sourced by t/deadlock_innodb.test.
mysql-test/include/handler.inc:
  1. This file contains now the main testing code of the former t/innodb_handler.test + t/handler.test.
  2. It is now sourced by t/handler_myisam.test and t/handler_innodb.test.
mysql-test/include/index_merge1.inc:
  1. This file contains now the main testing code of the former t/index_merge.test.
  2. It is now sourced by t/index_merge_myisam.test.
mysql-test/include/index_merge2.inc:
  1. This file contains now the main code of t/index_merge_innodb.test.
  2. It is sourced by t/index_merge_myisam.test and t/index_merge_innodb.test.
mysql-test/include/index_merge_2sweeps.inc:
  1. This file contains now the main code of the former t/index_merge_innodb2.test.
  2. It is sourced by t/index_merge_myisam.test and t/index_merge_innodb.test.
mysql-test/include/index_merge_ror.inc:
  1. This file contains now the main code of the former t/index_merge_ror.test.
  2. It is sourced by t/index_merge_myisam.test.
mysql-test/include/index_merge_ror_cpk.inc:
  1. This file contains now the main testing code of the former t/index_merge_ror_cpk.test.
  2. It is now sourced by t/index_merge_myisam.test and t/index_merge_innodb.test.
mysql-test/include/mix1.inc:
  1. This file contains now the main testing code of the t/innodb_mysql.test
  2. The name mix1.inc was used because the test contains subtests for different fields.
  3. It is sourced by t/innodb_mysql.test.
  4. Fixes:
      - Assign $other_engine_type instead of hardcoded MyISAM.
      - improve comment
      - remove redundant subtest
      - analyze table t4 instead of wrong table t1
      - remove not needed "eval set storage_engine = $engine_type;"
mysql-test/include/mix2.inc:
  1. This file is a copy of the main testing code of the t/innodb.test
     A copy has to be used, because t/innodb.test is to be maintained by INNOBASE only.
  2. The name mix2.inc was used because the test contains subtests for different fields.
  3. It is sourced by t/mix2_myisam.test.
  4. Fixes:
      - improved comment
      - additional "eval SET SESSION STORAGE_ENGINE = $other_engine_type;" at beginning of tests
      - assign $other_engine_type instead of hardcoded MyISAM or HEAP
      - assign $other_engine_type where it is needed to preserve test logics
      - correct logical bugs
      - improve(extend) "checksum table" test
mysql-test/include/query_cache.inc:
  1. This file contains now the main testing code of the former t/innodb_cache.test.
  2. It is now sourced by t/cache_innodb.test
mysql-test/include/read_many_rows.inc:
  1. This file contains now the main testing code of the former t/innodb_big.test.
  2. It is now sourced by t/read_many_rows_innodb.test.
mysql-test/include/rowid_order.inc:
  1. This file contains now the main testing code of t/rowid_order_innodb.test.
  2. It is now sourced by t/rowid_order_innodb.test.
mysql-test/include/unsafe_binlog.inc:
  1. This file contains now the main testing code of the former t/innodb_unsafe_binlog.test.
  2. It is now sourced by t/unsafe_binlog_innodb.test.
mysql-test/r/cache_innodb.result:
  Updated result
mysql-test/r/concurrent_innodb.result:
  Updated result
mysql-test/r/deadlock_innodb.result:
  Updated result
mysql-test/r/handler_innodb.result:
  Updated result
mysql-test/r/handler_myisam.result:
  Updated result
mysql-test/r/index_merge_myisam.result:
  Updated result
mysql-test/r/innodb_mysql.result:
  Updated result
mysql-test/r/mix2_myisam.result:
  Updated result
mysql-test/r/rowid_order_innodb.result:
  Updated result
mysql-test/r/unsafe_binlog_innodb.result:
  Updated result
mysql-test/t/cache_innodb.test:
  1. Renaming of t/innodb_cache.test to t/cache_innodb.test
  2. Main code is now sourced from include/query_cache.inc.
mysql-test/t/concurrent_innodb.test:
  1. Renaming of t/innodb_concurrent.test to t/concurrent_innodb.test
  2. Main code is now sourced from include/concurrent.inc.
     Attention: This test fails even in the old version. (BUG#21579).
                --> added to t/disabled.def
mysql-test/t/deadlock_innodb.test:
  1. Renaming of t/innodb_deadlock.test to t/deadlock_innodb.test
  2. Main code is now sourced from include/deadlock.inc.
mysql-test/t/disabled.def:
  Add the test concurrent_innodb because of 
  BUG#21579 2006-08-11 mleich innodb_concurrent random failures with varying differences
mysql-test/t/handler_innodb.test:
  1. Renaming of t/innodb_handler.test to t/handler_innodb.test
  2. Main code is now sourced from include/handler.inc.
     include/handler.inc = united code of former t/handler.test and t/innodb_handler.test
mysql-test/t/handler_myisam.test:
  1. Renaming of t/handler.test to t/handler_myisam.test
  2. Main code is now sourced from include/handler.inc.
     include/handler.inc = united code of former t/handler.test and t/handler_innodb.test.
mysql-test/t/innodb_mysql.test:
  1. Main code is now sourced from include/mix1.inc.
  2. Test was not renamed because t/innodb.test refers to it.
mysql-test/t/mix2_myisam.test:
  New test: MyISAM variant of mix2 ( = t/innodb.test)
mysql-test/t/read_many_rows_innodb.test:
  1. Renaming of t/innodb_big.test to t/read_many_rows_innodb.test
  2. Main code is now sourced from include/read_many_rows.inc.
mysql-test/t/rowid_order_innodb.test:
  Main code is now sourced from t/rowid_order.inc.
mysql-test/t/unsafe_binlog_innodb.test:
  1. Renaming of t/innodb_unsafe_binlog.test to t/unsafe_binlog_innodb.test
  2. Main code is now sourced from include/unsafe_binlog.inc.

1 files changed, 128 insertions, 0 deletions

diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc
new file mode 100644
index 00000000000..cfc2ed3885e
--- /dev/null
+++ b/mysql-test/include/index_merge_ror_cpk.inc
@@ -0,0 +1,128 @@
+# include/index_merge_ror_cpk.inc
+#
+# Clustered PK 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 InnoDB.
+#       They might be not valid for other storage engines.
+#
+# Last update:
+# 2006-08-02 ML test refactored
+#               old name was t/index_merge_ror_cpk.test
+#               main code went into include/index_merge_ror_cpk.inc
+#
+
+--echo #---------------- Clustered PK ROR-index_merge tests -----------------------------
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
+
+--disable_warnings
+drop table if exists  t1;
+--enable_warnings
+
+create table t1
+(
+  pk1 int not null,
+  pk2 int not null,
+
+  key1 int not null,
+  key2 int not null,
+
+  pktail1ok  int not null,
+  pktail2ok  int not null,
+  pktail3bad int not null,
+  pktail4bad int not null,
+  pktail5bad int not null,
+
+  pk2copy int not null,
+  badkey  int not null,
+
+  filler1 char (200),
+  filler2 char (200),
+  key (key1),
+  key (key2),
+
+  /* keys with tails from CPK members */
+  key (pktail1ok, pk1),
+  key (pktail2ok, pk1, pk2),
+  key (pktail3bad, pk2, pk1),
+  key (pktail4bad, pk1, pk2copy),
+  key (pktail5bad, pk1, pk2, pk2copy),
+
+  primary key (pk1, pk2)
+);
+
+--disable_query_log
+set autocommit=0;
+let $1=10000;
+while ($1)
+{
+  eval insert into t1 values ($1 div 10,$1 mod 100,   $1/100,$1/100,   $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2');
+  dec $1;
+}
+set autocommit=1;
+--enable_query_log
+
+# Verify that range scan on CPK is ROR
+# (use index_intersection because it is impossible to check that for index union)
+explain select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
+# CPK scan + 1 ROR range scan is a special case
+select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
+
+# Verify that CPK fields are considered to be covered by index scans
+explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
+select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
+
+# Verify that CPK is always used for index intersection scans
+# (this is because it is used as a filter, not for retrieval)
+explain select * from t1 where badkey=1 and key1=10;
+--replace_column 9 ROWS
+explain select * from t1 where pk1 < 7500 and key1 = 10;
+
+# Verify that keys with 'tails' of PK members are ok.
+explain select * from t1 where pktail1ok=1 and key1=10;
+explain select * from t1 where pktail2ok=1 and key1=10;
+
+# Note: The following is actually a deficiency, it uses sort_union currently.
+#       This comment refers to InnoDB and is probably not valid for other engines.
+explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
+
+# The expected rows differs a bit from platform to platform
+--replace_result 98 ROWS 99 ROWS
+explain select * from t1 where pktail3bad=1 and key1=10;
+explain select * from t1 where pktail4bad=1 and key1=10;
+explain select * from t1 where pktail5bad=1 and key1=10;
+
+# Test for problem with innodb key values prefetch buffer:
+explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
+select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
+
+drop table t1;
+# Testcase for BUG#4984
+create table t1
+(
+  RUNID varchar(22),
+  SUBMITNR varchar(5),
+  ORDERNR char(1),
+  PROGRAMM varchar(8),
+  TESTID varchar(4),
+  UCCHECK char(1),
+  ETEXT varchar(80),
+  ETEXT_TYPE char(1),
+  INFO char(1),
+  SEVERITY tinyint(3),
+  TADIRFLAG char(1),
+  PRIMARY KEY  (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
+  KEY `TVERM~KEY`  (PROGRAMM,TESTID,UCCHECK)
+) DEFAULT CHARSET=latin1;
+
+update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
+WHERE
+ `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
+ `TESTID`='' AND `UCCHECK`='';
+
+drop table t1;
+