Added option --temporary-tables to test speed of temporary tables

mysql-test/suite/parts/t/partition_repair_myisam-master.opt:
  Added missing file from last push
sql-bench/bench-init.pl.sh:
  Added options:
  --temporary-tables to test speed of temporary tables
sql-bench/server-cfg.sh:
  Added limit for number of temporary tables one can create
sql-bench/test-connect.sh:
  Skip test that doesn't work with temporary tables.
sql-bench/test-create.sh:
  Added limit for number of temporary tables one can create

4 files changed, 77 insertions, 25 deletions

diff --git a/sql-bench/bench-init.pl.sh b/sql-bench/bench-init.pl.sh
index 2d9927947d2..f1d4bd9edec 100644
--- a/sql-bench/bench-init.pl.sh
+++ b/sql-bench/bench-init.pl.sh
@@ -39,7 +39,7 @@ require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
 
 $|=1;				# Output data immediately
 
-$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=$opt_random=$opt_only_missing_tests=0;
+$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=$opt_random=$opt_only_missing_tests=$opt_temporary_tables=0;
 $opt_cmp=$opt_user=$opt_password=$opt_connect_options=$opt_connect_command= "";
 $opt_server="mysql"; $opt_dir="output";
 $opt_host="localhost";$opt_database="test";
@@ -59,7 +59,7 @@ $log_prog_args=join(" ", skip_arguments(\@ARGV,"comments","cmp","server",
 					"use-old-results","skip-test",
 					"optimization","hw",
 					"machine", "dir", "suffix", "log"));
-GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","threads=i","random","old-headers","die-on-errors","create-options=s","hires","tcpip","silent","optimization=s","hw=s","socket=s","connect-options=s","connect-command=s","only-missing-tests") || usage();
+GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","threads=i","random","old-headers","die-on-errors","create-options=s","hires","tcpip","silent","optimization=s","hw=s","socket=s","connect-options=s","connect-command=s","only-missing-tests","temporary-tables") || usage();
 
 usage() if ($opt_help);
 $server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc,
@@ -454,6 +454,9 @@ All benchmarks takes the following options:
   create all MySQL tables as InnoDB tables use:
   --create-options=ENGINE=InnoDB
 
+--temporary-tables
+  Use temporary tables for all tests.
+
 --database (Default $opt_database)
   In which database the test tables are created.
 
diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh
index 3ff1ce99659..76334d76108 100644
--- a/sql-bench/server-cfg.sh
+++ b/sql-bench/server-cfg.sh
@@ -159,6 +159,7 @@ sub new
   $limits{'max_index'}		= 16; # Max number of keys
   $limits{'max_index_parts'}	= 16; # Max segments/key
   $limits{'max_tables'}		= (($machine || '') =~ "^win") ? 5000 : 65000;
+  $limits{'max_temporary_tables'}= 400;
   $limits{'max_text_size'}	= 1000000; # Good enough for tests
   $limits{'multi_drop'}		= 1; # Drop table can take many tables
   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
@@ -189,6 +190,7 @@ sub new
     $self->{'transactions'}	= 1;	# Transactions enabled
     $limits{'max_columns'}	= 90;	# Max number of columns in table
     $limits{'max_tables'}	= 32;   # No comments
+    $limits{'max_temporary_tables'}= $limits{"max_tables"};
   }
   if (defined($main::opt_create_options) &&
       $main::opt_create_options =~ /engine=bdb/i)
@@ -200,6 +202,7 @@ sub new
   {
     $limits{'working_blobs'}	= 0; # Blobs not implemented yet
     $limits{'max_tables'}	= 500;
+    $limits{'max_temporary_tables'}= $limits{"max_tables"};
     $self->{'transactions'}	= 1;	# Transactions enabled
   }
 
@@ -270,7 +273,14 @@ sub create
   my($self,$table_name,$fields,$index,$options) = @_;
   my($query,@queries);
 
-  $query="create table $table_name (";
+  if ($main::opt_temporary_tables)
+  {
+    $query="create temporary table $table_name (";
+  }
+  else
+  {
+    $query="create table $table_name (";
+  }
   foreach $field (@$fields)
   {
 #    $field =~ s/ decimal/ double(10,2)/i;
@@ -393,6 +403,7 @@ sub new
   $limits{'max_conditions'}	= 74;
   $limits{'max_columns'}	= 75;
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
   $limits{'max_text_size'}	= 32000;
   $limits{'query_size'}		= 65535;
   $limits{'max_index'}		= 5;
@@ -622,7 +633,9 @@ sub new
   $limits{'max_conditions'}	= 9999;		# This makes Pg real slow
   $limits{'max_index'}		= 64;		# Big enough
   $limits{'max_index_parts'}	= 16;
-  $limits{'max_tables'}		= 5000;		# 10000 crashes pg 7.0.2
+  $limits{'max_tables'}		= 65000;
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 65000;	# Good enough for test
   $limits{'multi_drop'}		= 1;
   $limits{'order_by_position'}  = 1;
@@ -873,6 +886,8 @@ sub new
   $limits{'max_conditions'}	= 9999;		# Probably big enough
   $limits{'max_columns'}	= 2000;		# From crash-me
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 65492;	# According to tests
   $limits{'query_size'}		= 65535;	# Probably a limit
   $limits{'max_index'}		= 64;		# Probably big enough
@@ -1104,6 +1119,7 @@ sub new
 			# above this value .... but can handle 2419 columns
 			# maybe something for crash-me ... but how to check ???
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
   $limits{'max_text_size'}	= 4095;		# max returned ....
   $limits{'query_size'}		= 65535;	# Not a limit, big enough
   $limits{'max_index'}		= 64;		# Big enough
@@ -1374,6 +1390,8 @@ sub new
   $limits{'max_conditions'}	= 9999; # (Actually not a limit)
   $limits{'max_columns'}	= 254;	# Max number of columns in table
   $limits{'max_tables'}		= 65000; # Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 2000; # Limit for blob test-connect
   $limits{'query_size'}		= 65525; # Max size with default buffers.
   $limits{'max_index'}		= 16; # Max number of keys
@@ -1647,6 +1665,8 @@ sub new
   $limits{'max_column_name'}	= 18; # max table and column name
   $limits{'max_columns'}	= 994;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_index'}		= 64; # Max number of keys
   $limits{'max_index_parts'}	= 15; # Max segments/key
   $limits{'max_text_size'}	= 65535;  # Max size with default buffers. ??
@@ -1835,6 +1855,8 @@ sub new
   $limits{'max_conditions'}	= 97; # We get 'Query is too complex'
   $limits{'max_columns'}	= 255;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 255;  # Max size with default buffers.
   $limits{'query_size'}		= 65535; # Not a limit, big enough
   $limits{'max_index'}		= 32; # Max number of keys
@@ -2020,6 +2042,8 @@ sub new
   $limits{'max_conditions'}	= 1030; # We get 'Query is too complex'
   $limits{'max_columns'}	= 250;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 9830;  # Max size with default buffers.
   $limits{'query_size'}		= 9830; # Max size with default buffers.
   $limits{'max_index'}		= 64; # Max number of keys
@@ -2216,6 +2240,8 @@ sub new
   $limits{'max_conditions'}	= 1030; # We get 'Query is too complex'
   $limits{'max_columns'}	= 250;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 9830;  # Max size with default buffers.
   $limits{'query_size'}		= 9830; # Max size with default buffers.
   $limits{'max_index'}		= 64; # Max number of keys
@@ -2448,6 +2474,8 @@ sub new
   $limits{'max_conditions'}	= 50; # (Actually not a limit)
   $limits{'max_columns'}	= 254;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 2000; # Limit for blob test-connect
   $limits{'query_size'}		= 65525; # Max size with default buffers.
   $limits{'max_index'}		= 16; # Max number of keys
@@ -2652,6 +2680,8 @@ sub new
   $limits{'max_conditions'}	= 418; # We get 'Query is too complex'
   $limits{'max_columns'}	= 500;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
   $limits{'max_text_size'}	= 254;  # Max size with default buffers.
   $limits{'query_size'}		= 254; # Max size with default buffers.
   $limits{'max_index'}		= 48; # Max number of keys
@@ -2830,6 +2860,7 @@ sub new
   $limits{'max_conditions'}	= 9999; # (Actually not a limit)
   $limits{'max_columns'}	= 252;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
   $limits{'max_text_size'}	= 15000; # Max size with default buffers.
   $limits{'query_size'}		= 1000000; # Max size with default buffers.
   $limits{'max_index'}		= 32; # Max number of keys
@@ -3032,6 +3063,7 @@ sub new
   $limits{'max_conditions'}	= 9999; # (Actually not a limit)
   $limits{'max_columns'}	= 252;	# Max number of columns in table
   $limits{'max_tables'}		= 65000;	# Should be big enough
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
   $limits{'max_text_size'}	= 15000; # Max size with default buffers.
   $limits{'query_size'}		= 1000000; # Max size with default buffers.
   $limits{'max_index'}		= 65000; # Max number of keys
@@ -3228,6 +3260,7 @@ sub new
   # The following should be 8192, but is smaller because Frontbase crashes..
   $limits{'max_columns'}	= 150;	# Max number of columns in table
   $limits{'max_tables'}		= 5000;	# 10000 crashed FrontBase
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
   $limits{'max_text_size'}	= 65000; # Max size with default buffers.
   $limits{'query_size'}		= 8000000; # Max size with default buffers.
   $limits{'max_index'}		= 38; # Max number of keys
@@ -3440,6 +3473,7 @@ sub new
   $limits{'max_conditions'}	= 9999; # (Actually not a limit) *
   $limits{'max_columns'}	= 1023;	# Max number of columns in table *
   $limits{'max_tables'}		= 65000;	# Should be big enough * unlimited actually
+  $limits{'max_temporary_tables'}= $limits{"max_tables"};
   $limits{'max_text_size'}	= 15000; # Max size with default buffers. 
   $limits{'query_size'}		= 64*1024; # Max size with default buffers. *64 kb by default. May be set by system variable 
   $limits{'max_index'}		= 510; # Max number of keys *
diff --git a/sql-bench/test-connect.sh b/sql-bench/test-connect.sh
index d0f3f0791a4..52566f7bb54 100644
--- a/sql-bench/test-connect.sh
+++ b/sql-bench/test-connect.sh
@@ -161,41 +161,48 @@ if ($opt_fast && defined($server->{vacuum}))
 {
   $server->vacuum(0,\$dbh);
 }
-$dbh->disconnect;
+if (!$main::opt_temporary_tables)
+{
+    $dbh->disconnect;
+}
 
 #
 # First test connect/select/disconnect
 #
-print "Testing connect/select 1 row from table/disconnect\n";
+if (!$main::opt_temporary_tables)
+{
+  print "Testing connect/select 1 row from table/disconnect\n";
 
-$loop_time=new Benchmark;
-$errors=0;
+  $loop_time=new Benchmark;
+  $errors=0;
 
-for ($i=0 ; $i < $small_loop_count ; $i++)
-{
-  for ($j=0; $j < $max_test ; $j++)
+  for ($i=0 ; $i < $small_loop_count ; $i++)
   {
-    last if ($dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password));
-    $errors++;
-  }
-  die $DBI::errstr if ($j == $max_test);
+    for ($j=0; $j < $max_test ; $j++)
+    {
+        last if ($dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password));
+        $errors++;
+    }
+    die $DBI::errstr if ($j == $max_test);
 
-  $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record
+    $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record
     or die $DBI::errstr;
-  $dbh->disconnect;
-}
+    $dbh->disconnect;
+  }
 
-$end_time=new Benchmark;
-print "Warning: $errors connections didn't work without a time delay\n" if ($errors);
-print "Time to connect+select_1_row ($small_loop_count): " .
+  $end_time=new Benchmark;
+  print "Warning: $errors connections didn't work without a time delay\n" if ($errors);
+  print "Time to connect+select_1_row ($small_loop_count): " .
   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 
+  $dbh = $server->connect();
+}
+
 #
 # The same test, but without connect/disconnect
 #
 print "Testing select 1 row from table\n";
 
-$dbh = $server->connect();
 $loop_time=new Benchmark;
 
 for ($i=0 ; $i < $opt_loop_count ; $i++)
diff --git a/sql-bench/test-create.sh b/sql-bench/test-create.sh
index 40fd9c49ae8..e2a51f96fff 100644
--- a/sql-bench/test-create.sh
+++ b/sql-bench/test-create.sh
@@ -47,7 +47,15 @@ if ($opt_small_test)
   $create_loop_count/=1000;
 }
 
-$max_tables=min($limits->{'max_tables'},$opt_loop_count);
+if ($opt_temporary_tables)
+{
+    $max_tables=min($limits->{'max_tables'},$opt_loop_count);
+}
+else
+{
+  $max_tables=min($limits->{'max_tables'},$opt_loop_count);
+  $max_tables=400;
+}
 
 if ($opt_small_test)
 {
@@ -71,7 +79,7 @@ $dbh = $server->connect();
 if ($opt_force) # If tables used in this test exist, drop 'em
 {
   print "Okay..Let's make sure that our tables don't exist yet.\n\n";
-  for ($i=1 ; $i <= $max_tables ; $i++)
+  for ($i=1 ; $i <= max($max_tables, $create_loop_count) ; $i++)
   {
     $dbh->do("drop table bench_$i" . $server->{'drop_attr'});
   }
@@ -245,7 +253,7 @@ for ($i=2 ; $i <= $keys ; $i++)
 }
 
 $loop_time=new Benchmark;
-for ($i=1 ; $i <= $opt_loop_count ; $i++)
+for ($i=1 ; $i <= $create_loop_count ; $i++)
 {
   do_many($dbh,$server->create("bench_$i", \@fields, \@keys));
   $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr;