changes to make things work in the max version

acinclude.m4:
  automatically detect installation paths of OpenSSL instead of hard-coding
  automatically enable --with-vio if --with-openssl is enabled
client/mysqltest.c:
  fixes for embedded library
libmysqld/Makefile.am:
  md5 is now in mysys
libmysqld/libmysqld.c:
  added my_connect(), but now can be removed - will do in the next
  changeset
mysql-test/include/have_bdb.inc:
  disable query log so that result file will not have unneeded 
  show variables
mysql-test/include/have_gemini.inc:
  disable query log so that result file will not have unneeded 
  show variables
mysql-test/include/have_innodb.inc:
  disable query log so that result file will not have unneeded 
  show variables
mysql-test/include/have_openssl.inc:
  disable query log so that result file will not have unneeded 
  show variables
mysql-test/include/have_openssl_1.inc:
  disable query log so that result file will not have unneeded 
  show variables
mysql-test/include/have_openssl_2.inc:
  disable query log so that result file will not have unneeded 
  show variables
mysql-test/r/bdb.result:
  fix for new format
mysql-test/r/have_openssl.require:
  ssl->openssl
mysql-test/r/innodb.result:
  fix for query logging

13 files changed, 1116 insertions, 13 deletions

diff --git a/acinclude.m4 b/acinclude.m4
index b7f43fcb8e6..d9ffb382896 100644
--- a/acinclude.m4
+++ b/acinclude.m4
@@ -707,6 +707,32 @@ AC_DEFUN(MYSQL_CHECK_VIO, [
   AC_SUBST([vio_libs])
 ])
 
+AC_DEFUN(MYSQL_FIND_OPENSSL, [
+ for d in /usr/ssl/include /usr/local/ssl/include /usr/include/openssl \
+/usr/include/ssl /opt/ssl/include /opt/openssl/include ; do
+  if test -f $d/ssl.h  ; then
+    OPENSSL_INCLUDE=$d
+  fi
+ done
+
+ for d in /usr/ssl/lib /usr/local/ssl/lib /usr/lib/openssl \
+/usr/lib /opt/ssl/lib /opt/openssl/lib ; do
+  if test -f $d/libssl.a -o -f $d/libssl.so  ; then
+    OPENSSL_LIB=$d
+  fi
+ done
+
+ if test -z "$OPENSSL_LIB" -o -z "$OPENSSL_INCLUDE" ; then
+   echo "Could not find an installation of OpenSSL"
+   if test -n "$OPENSSL_LIB" ; then
+    if test "$IS_LINUX" = "true"; then
+      echo "Looks like you've forgotted to install OpenSSL development RPM"
+    fi
+   fi
+  exit 1
+ fi
+
+])
 
 AC_DEFUN(MYSQL_CHECK_OPENSSL, [
 AC_MSG_CHECKING(for OpenSSL)
@@ -719,15 +745,15 @@ AC_MSG_CHECKING(for OpenSSL)
   openssl_includes=""
   if test "$openssl" = "yes"
   then
-    if test -n "$vio_dir"
-    then
-      AC_MSG_RESULT(yes)
-      openssl_libs="-L/usr/local/ssl/lib -lssl -lcrypto"
-      openssl_includes="-I/usr/local/ssl/include"
-      AC_DEFINE(HAVE_OPENSSL)
-    else
-      AC_MSG_RESULT(disabled because --with-vio wasn not used)
-    fi
+    MYSQL_FIND_OPENSSL
+    #force VIO use
+    vio_dir="vio"
+    vio_libs="../vio/libvio.la"
+    AC_DEFINE(HAVE_VIO)
+    AC_MSG_RESULT(yes)
+    openssl_libs="-L$OPENSSL_LIB -lssl -lcrypto"
+    openssl_includes="-I$OPENSSL_INCLUDE"
+    AC_DEFINE(HAVE_OPENSSL)
   else
     AC_MSG_RESULT(no)
   fi
diff --git a/client/mysqltest.c b/client/mysqltest.c
index 52f61e95d8f..b0a21a1cd5b 100644
--- a/client/mysqltest.c
+++ b/client/mysqltest.c
@@ -337,8 +337,10 @@ static void free_used_memory()
 {
   uint i;
   DBUG_ENTER("free_used_memory");
+#ifndef EMBEDDED_LIBRARY  
   if (manager)
     mysql_manager_close(manager);
+#endif  
   close_cons();
   close_files();
   hash_free(&var_hash);
@@ -599,6 +601,7 @@ int open_file(const char* name)
   return 0;
 }
 
+#ifndef EMBEDDED_LIBRARY
 int do_server_start(struct st_query* q)
 {
   return do_server_op(q,"start");
@@ -636,6 +639,7 @@ int do_server_op(struct st_query* q,const char* op)
 
   return 0;
 }
+#endif
 
 int do_source(struct st_query* q)
 {
@@ -1182,6 +1186,7 @@ char* safe_get_param(char* str, char** arg, const char* msg)
   DBUG_RETURN(str);
 }
 
+#ifndef EMBEDDED_LIBRARY
 void init_manager()
 {
   if (!(manager=mysql_manager_init(0)))
@@ -1192,6 +1197,7 @@ void init_manager()
 	manager->last_errno);
 
 }
+#endif
 
 int safe_connect(MYSQL* con, const char* host, const char* user,
 		 const char* pass,
@@ -2101,8 +2107,9 @@ int main(int argc, char** argv)
   if (cur_file == file_stack)
     *++cur_file = stdin;
   *lineno=1;
+#ifndef EMBEDDED_LIBRARY  
   init_manager();
-
+#endif
   if (!( mysql_init(&cur_con->mysql)))
     die("Failed in mysql_init()");
   cur_con->name = my_strdup("default", MYF(MY_WME));
@@ -2134,8 +2141,10 @@ int main(int argc, char** argv)
       case Q_DISABLE_QUERY_LOG: disable_query_log=1; break;
       case Q_SOURCE: do_source(q); break;
       case Q_SLEEP: do_sleep(q); break;
+#ifndef EMBEDDED_LIBRARY	
       case Q_SERVER_START: do_server_start(q); break;
       case Q_SERVER_STOP: do_server_stop(q); break;
+#endif	
       case Q_INC: do_inc(q); break;
       case Q_DEC: do_dec(q); break;
       case Q_ECHO: do_echo(q); break;
diff --git a/libmysqld/Makefile.am b/libmysqld/Makefile.am
index e39cebff04a..a95a9ba403e 100644
--- a/libmysqld/Makefile.am
+++ b/libmysqld/Makefile.am
@@ -47,7 +47,7 @@ sqlsources = convert.cc derror.cc field.cc field_conv.cc filesort.cc \
 	hostname.cc init.cc \
 	item.cc item_buff.cc item_cmpfunc.cc item_create.cc \
 	item_func.cc item_strfunc.cc item_sum.cc item_timefunc.cc \
-	item_uniq.cc key.cc lock.cc log.cc log_event.cc md5.c \
+	item_uniq.cc key.cc lock.cc log.cc log_event.cc \
 	mini_client.cc net_pkg.cc net_serv.cc opt_ft.cc opt_range.cc \
 	opt_sum.cc procedure.cc records.cc slave.cc sql_acl.cc \
 	sql_analyse.cc sql_base.cc sql_cache.cc sql_class.cc \
@@ -65,7 +65,7 @@ sqlobjects = convert.lo derror.lo field.lo field_conv.lo filesort.lo \
 	hostname.lo init.lo \
 	item.lo item_buff.lo item_cmpfunc.lo item_create.lo \
 	item_func.lo item_strfunc.lo item_sum.lo item_timefunc.lo \
-	item_uniq.lo key.lo lock.lo log.lo log_event.lo md5.lo \
+	item_uniq.lo key.lo lock.lo log.lo log_event.lo  \
 	mini_client.lo net_pkg.lo net_serv.lo opt_ft.lo opt_range.lo \
 	opt_sum.lo procedure.lo records.lo slave.lo sql_acl.lo \
 	sql_analyse.lo sql_base.lo sql_cache.lo sql_class.lo \
diff --git a/libmysqld/libmysqld.c b/libmysqld/libmysqld.c
index c6860098ed8..d90012b3bb6 100644
--- a/libmysqld/libmysqld.c
+++ b/libmysqld/libmysqld.c
@@ -1473,6 +1473,107 @@ get_info:
   DBUG_RETURN(0);
 }
 
+/****************************************************************************
+* A modified version of connect().  connect2() allows you to specify
+* a timeout value, in seconds, that we should wait until we
+* derermine we can't connect to a particular host.  If timeout is 0,
+* my_connect() will behave exactly like connect().
+*
+* Base version coded by Steve Bernacki, Jr. <steve@navinet.net>
+*****************************************************************************/
+
+int my_connect(my_socket s, const struct sockaddr *name, uint namelen,
+		    uint timeout)
+{
+#if defined(__WIN__) || defined(OS2)
+  return connect(s, (struct sockaddr*) name, namelen);
+#else
+  int flags, res, s_err;
+  SOCKOPT_OPTLEN_TYPE s_err_size = sizeof(uint);
+  fd_set sfds;
+  struct timeval tv;
+  time_t start_time, now_time;
+
+  /* If they passed us a timeout of zero, we should behave
+   * exactly like the normal connect() call does.
+   */
+
+  if (timeout == 0)
+    return connect(s, (struct sockaddr*) name, namelen);
+
+  flags = fcntl(s, F_GETFL, 0);		  /* Set socket to not block */
+#ifdef O_NONBLOCK
+  fcntl(s, F_SETFL, flags | O_NONBLOCK);  /* and save the flags..  */
+#endif
+
+  res = connect(s, (struct sockaddr*) name, namelen);
+  s_err = errno;			/* Save the error... */
+  fcntl(s, F_SETFL, flags);
+  if ((res != 0) && (s_err != EINPROGRESS))
+  {
+    errno = s_err;			/* Restore it */
+    return(-1);
+  }
+  if (res == 0)				/* Connected quickly! */
+    return(0);
+
+  /* Otherwise, our connection is "in progress."  We can use
+   * the select() call to wait up to a specified period of time
+   * for the connection to suceed.  If select() returns 0
+   * (after waiting howevermany seconds), our socket never became
+   * writable (host is probably unreachable.)  Otherwise, if
+   * select() returns 1, then one of two conditions exist:
+   *
+   * 1. An error occured.  We use getsockopt() to check for this.
+   * 2. The connection was set up sucessfully: getsockopt() will
+   * return 0 as an error.
+   *
+   * Thanks goes to Andrew Gierth <andrew@erlenstar.demon.co.uk>
+   * who posted this method of timing out a connect() in
+   * comp.unix.programmer on August 15th, 1997.
+   */
+
+  FD_ZERO(&sfds);
+  FD_SET(s, &sfds);
+  /*
+   * select could be interrupted by a signal, and if it is, 
+   * the timeout should be adjusted and the select restarted
+   * to work around OSes that don't restart select and 
+   * implementations of select that don't adjust tv upon
+   * failure to reflect the time remaining
+   */
+  start_time = time(NULL);
+  for (;;)
+  {
+    tv.tv_sec = (long) timeout;
+    tv.tv_usec = 0;
+    if ((res = select(s+1, NULL, &sfds, NULL, &tv)) >= 0)
+      break;
+    now_time=time(NULL);
+    timeout-= (uint) (now_time - start_time);
+    if (errno != EINTR || (int) timeout <= 0)
+      return -1;
+  }
+
+  /* select() returned something more interesting than zero, let's
+   * see if we have any errors.  If the next two statements pass,
+   * we've got an open socket!
+   */
+
+  s_err=0;
+  if (getsockopt(s, SOL_SOCKET, SO_ERROR, (char*) &s_err, &s_err_size) != 0)
+    return(-1);
+
+  if (s_err)
+  {						/* getsockopt could succeed */
+    errno = s_err;
+    return(-1);					/* but return an error... */
+  }
+  return(0);					/* It's all good! */
+#endif
+}
+
+
 int STDCALL
 mysql_real_query(MYSQL *mysql, const char *query, uint length)
 {
diff --git a/mysql-test/include/have_bdb.inc b/mysql-test/include/have_bdb.inc
index 0126e30210f..3f7377e7515 100644
--- a/mysql-test/include/have_bdb.inc
+++ b/mysql-test/include/have_bdb.inc
@@ -1,2 +1,4 @@
 -- require r/have_bdb.require
+disable_query_log;
 show variables like "have_bdb";
+enable_query_log;
diff --git a/mysql-test/include/have_gemini.inc b/mysql-test/include/have_gemini.inc
index 78a84ac6f3e..d98c9750714 100644
--- a/mysql-test/include/have_gemini.inc
+++ b/mysql-test/include/have_gemini.inc
@@ -1,2 +1,4 @@
 -- require r/have_gemini.require
+disable_query_log;
 show variables like "have_gemini";
+enable_query_log;
diff --git a/mysql-test/include/have_innodb.inc b/mysql-test/include/have_innodb.inc
index 7dcaef44878..4f83d378cbc 100644
--- a/mysql-test/include/have_innodb.inc
+++ b/mysql-test/include/have_innodb.inc
@@ -1,2 +1,4 @@
 -- require r/have_innodb.require
+disable_query_log;
 show variables like "have_innodb";
+enable_query_log;
diff --git a/mysql-test/include/have_openssl.inc b/mysql-test/include/have_openssl.inc
index b9aa2dadb9e..54c5b04b3e4 100644
--- a/mysql-test/include/have_openssl.inc
+++ b/mysql-test/include/have_openssl.inc
@@ -1,2 +1,4 @@
 -- require r/have_openssl.require
+disable_query_log;
 show variables like "have_openssl";
+enable_query_log;
diff --git a/mysql-test/include/have_openssl_1.inc b/mysql-test/include/have_openssl_1.inc
index 5aa93b688f3..4d3646abdc2 100644
--- a/mysql-test/include/have_openssl_1.inc
+++ b/mysql-test/include/have_openssl_1.inc
@@ -1,2 +1,4 @@
 -- require r/have_openssl_1.require
+disable_query_log;
 show variables like "have_openssl";
+enable_query_log;
diff --git a/mysql-test/include/have_openssl_2.inc b/mysql-test/include/have_openssl_2.inc
index b15af43715b..724d295a398 100644
--- a/mysql-test/include/have_openssl_2.inc
+++ b/mysql-test/include/have_openssl_2.inc
@@ -1,2 +1,4 @@
 -- require r/have_openssl_2.require
+disable_query_log;
 SHOW STATUS LIKE "SSL_get_cipher";
+enable_query_log;
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index 4d26304db4c..a7ddff5c532 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1,3 +1,7 @@
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
+create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=bdb;
+insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
+select id, code, name from t1 order by id;
 id	code	name
 1	1	Tim
 2	1	Monty
@@ -6,6 +10,8 @@ id	code	name
 5	3	Sasha
 6	3	Jeremy
 7	4	Matt
+update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
+select id, code, name from t1 order by id;
 id	code	name
 2	1	Monty
 3	2	David
@@ -14,6 +20,8 @@ id	code	name
 6	3	Jeremy
 7	4	Matt
 8	1	Sinisa
+update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
+select id, code, name from t1 order by id;
 id	code	name
 3	2	David
 4	2	Erik
@@ -22,10 +30,26 @@ id	code	name
 7	4	Matt
 8	1	Sinisa
 12	1	Ralph
+drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+parent_id int(11) DEFAULT '0' NOT NULL,
+level tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (id),
+KEY parent_id (parent_id),
+KEY level (level)
+) type=bdb;
+INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
+update t1 set parent_id=parent_id+100;
+select * from t1 where parent_id=102;
 id	parent_id	level
 8	102	2
 9	102	2
 15	102	2
+update t1 set id=id+1000;
+update t1 set id=1024 where id=1009;
+Duplicate entry '1024' for key 1
+select * from t1;
 id	parent_id	level
 1001	100	0
 1002	101	1
@@ -66,6 +90,8 @@ id	parent_id	level
 1193	105	2
 1202	107	2
 1203	107	2
+update ignore t1 set id=id+1;
+select * from t1;
 id	parent_id	level
 1001	100	0
 1002	101	1
@@ -106,16 +132,22 @@ id	parent_id	level
 1194	105	2
 1202	107	2
 1204	107	2
+update ignore t1 set id=1023 where id=1010;
+select * from t1 where parent_id=102;
 id	parent_id	level
 1008	102	2
 1015	102	2
 1010	102	2
+explain select level from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	level	level	1	const	1	where used; Using index
+explain select level,id from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	level	level	1	const	1	where used; Using index
+explain select level,id,parent_id from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	level	level	1	const	1	where used
+select level,id from t1 where level=1;
 level	id
 1	1002
 1	1003
@@ -123,6 +155,7 @@ level	id
 1	1005
 1	1006
 1	1007
+select level,id,parent_id from t1 where level=1;
 level	id	parent_id
 1	1002	101
 1	1003	101
@@ -130,31 +163,77 @@ level	id	parent_id
 1	1005	101
 1	1006	101
 1	1007	101
+optimize table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
+show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	0	PRIMARY	1	id	A	39	NULL	NULL	
 t1	1	parent_id	1	parent_id	A	9	NULL	NULL	
 t1	1	level	1	level	A	3	NULL	NULL	
+drop table t1;
+CREATE TABLE t1 (
+gesuchnr int(11) DEFAULT '0' NOT NULL,
+benutzer_id int(11) DEFAULT '0' NOT NULL,
+PRIMARY KEY (gesuchnr,benutzer_id)
+) type=BDB;
+replace into t1 (gesuchnr,benutzer_id) values (2,1);
+replace into t1 (gesuchnr,benutzer_id) values (1,1);
+replace into t1 (gesuchnr,benutzer_id) values (1,1);
+select * from t1;
 gesuchnr	benutzer_id
 1	1
 2	1
+drop table t1;
+create table t1 (id int not null primary key, x int not null, key (x)) type=bdb;
+insert into t1 (id, x) values (1, 1);
+replace into t1 (id, x) values (1, 2);
+select * from t1;
 id	x
 1	2
+drop table t1;
+create table t1 (a int) type=bdb;
+insert into t1 values (1), (2);
+optimize table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
+delete from t1 where a = 1;
+select * from t1;
 a
 2
+check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	error	The handler for the table doesn't support check/repair
+drop table t1;
+create table t1 (a int,b varchar(20)) type=bdb;
+insert into t1 values (1,""), (2,"testing");
+delete from t1 where a = 1;
+select * from t1;
 a	b
 2	testing
+create index skr on t1 (a);
+insert into t1 values (3,""), (4,"testing");
+analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
+show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	1	skr	1	a	A	3	NULL	NULL	
+drop table t1;
+create table t1 (a int,b varchar(20),key(a)) type=bdb;
+insert into t1 values (1,""), (2,"testing");
+select * from t1 where a = 1;
 a	b
 1	
+drop table t1;
+create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) type=BDB;
+insert into t1 values ("a",1),("b",2),("a",2),("c",1);
+insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
+insert into t1 (a) values ("a"),("b"),("c"),("d");
+insert into t1 (a) values ('k'),('d');
+insert into t1 (a) values ("a");
+insert into t1 values ("d",last_insert_id());
+select * from t1;
 a	b
 a	1
 a	2
@@ -172,51 +251,136 @@ d	2
 d	5
 e	1
 k	1
+flush tables;
+select count(*) from t1;
 count(*)
 16
+drop table t1;
+create table t1 (n int not null primary key) type=bdb;
+set autocommit=0;
+insert into t1 values (4);
+rollback;
+select n, "after rollback" from t1;
 n	after rollback
+insert into t1 values (4);
+commit;
+select n, "after commit" from t1;
 n	after commit
 4	after commit
+commit;
+insert into t1 values (5);
+insert into t1 values (4);
+Duplicate entry '4' for key 1
+commit;
+select n, "after commit" from t1;
 n	after commit
 4	after commit
 5	after commit
+set autocommit=1;
+insert into t1 values (6);
+insert into t1 values (4);
+Duplicate entry '4' for key 1
+select n from t1;
 n
 4
 5
 6
+rollback;
+drop table t1;
+create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) type=BDB;
+begin;
+insert into t1 values(1,'hamdouni');
+select id as afterbegin_id,nom as afterbegin_nom from t1;
 afterbegin_id	afterbegin_nom
 1	hamdouni
+rollback;
+select id as afterrollback_id,nom as afterrollback_nom from t1;
 afterrollback_id	afterrollback_nom
+set autocommit=0;
+insert into t1 values(2,'mysql');
+select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
 afterautocommit0_id	afterautocommit0_nom
 2	mysql
+rollback;
+select id as afterrollback_id,nom as afterrollback_nom from t1;
 afterrollback_id	afterrollback_nom
+set autocommit=1;
+drop table t1;
+CREATE TABLE t1 (id char(8) not null primary key, val int not null) type=bdb;
+insert into t1 values ('pippo', 12);
+insert into t1 values ('pippo', 12);
+Duplicate entry 'pippo' for key 1
+delete from t1;
+delete from t1 where id = 'pippo';
+select * from t1;
 id	val
+insert into t1 values ('pippo', 12);
+set autocommit=0;
+delete from t1;
+rollback;
+select * from t1;
 id	val
 pippo	12
+delete from t1;
+commit;
+select * from t1;
 id	val
+drop table t1;
+set autocommit=1;
+CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) TYPE=BDB;
+INSERT INTO t1 VALUES (1, 'Jochen');
+select * from t1;
 ID	NAME
 1	Jochen
+drop table t1;
+CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=BDB;
+set autocommit=0;
+INSERT INTO t1  SET _userid='marc@anyware.co.uk';
+COMMIT;
+SELECT * FROM t1;
 _userid
 marc@anyware.co.uk
+SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
 _userid
 marc@anyware.co.uk
+drop table t1;
+set autocommit=1;
+CREATE TABLE t1 (
+user_id int(10) DEFAULT '0' NOT NULL,
+name varchar(100),
+phone varchar(100),
+ref_email varchar(100) DEFAULT '' NOT NULL,
+detail varchar(200),
+PRIMARY KEY (user_id,ref_email)
+)type=bdb;
+INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
+select * from t1 where user_id=10292;
 user_id	name	phone	ref_email	detail
 10292	sanjeev	29153373	sansh777@hotmail.com	xxx
 10292	shirish	2333604	shirish@yahoo.com	ddsds
 10292	sonali	323232	sonali@bolly.com	filmstar
+INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
+select * from t1 where user_id=10292;
 user_id	name	phone	ref_email	detail
 10292	sanjeev	29153373	sansh777@hotmail.com	xxx
 10292	shirish	2333604	shirish@yahoo.com	ddsds
 10292	sonali	323232	sonali@bolly.com	filmstar
+select * from t1 where user_id>=10292;
 user_id	name	phone	ref_email	detail
 10292	sanjeev	29153373	sansh777@hotmail.com	xxx
 10292	shirish	2333604	shirish@yahoo.com	ddsds
 10292	sonali	323232	sonali@bolly.com	filmstar
 10293	shirish	2333604	shirish@yahoo.com	ddsds
+select * from t1 where user_id>10292;
 user_id	name	phone	ref_email	detail
 10293	shirish	2333604	shirish@yahoo.com	ddsds
+select * from t1 where user_id<10292;
 user_id	name	phone	ref_email	detail
 10291	sanjeev	29153373	sansh777@hotmail.com	xxx
+drop table t1;
+CREATE TABLE t1 (a int not null, b int not null,c int not null,
+key(a),primary key(a,b), unique(c),key(a),unique(b));
+show index from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	0	PRIMARY	1	a	A	NULL	NULL	NULL	
 t1	0	PRIMARY	2	b	A	0	NULL	NULL	
@@ -224,69 +388,153 @@ t1	0	b	1	b	A	0	NULL	NULL
 t1	0	c	1	c	A	0	NULL	NULL	
 t1	1	a	1	a	A	NULL	NULL	NULL	
 t1	1	a_2	1	a	A	NULL	NULL	NULL	
+drop table t1;
+create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
+alter table t1 type=BDB;
+insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
+select * from t1;
 col1	col2
 1	1
 2	3
 3	4
 4	4
 5	2
+update t1 set col2='7' where col1='4';
+select * from t1;
 col1	col2
 1	1
 2	3
 3	4
 4	7
 5	2
+alter table t1 add co3 int not null;
+select * from t1;
 col1	col2	co3
 1	1	0
 2	3	0
 3	4	0
 4	7	0
 5	2	0
+update t1 set col2='9' where col1='2';
+select * from t1;
 col1	col2	co3
 1	1	0
 2	9	0
 3	4	0
 4	7	0
 5	2	0
+drop table t1;
+create table t1 (a int not null , b int, primary key (a)) type = BDB;
+create table t2 (a int not null , b int, primary key (a)) type = myisam;
+insert into t1 VALUES (1,3) , (2,3), (3,3);
+select * from t1;
 a	b
 1	3
 2	3
 3	3
+insert into t2 select * from t1;
+select * from t2;
 a	b
 1	3
 2	3
 3	3
+delete from t1 where b = 3;
+select * from t1;
 a	b
+insert into t1 select * from t2;
+select * from t1;
 a	b
 1	3
 2	3
 3	3
+select * from t2;
 a	b
 1	3
 2	3
 3	3
+drop table t1,t2;
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+ggid varchar(32) binary DEFAULT '' NOT NULL,
+email varchar(64) DEFAULT '' NOT NULL,
+passwd varchar(32) binary DEFAULT '' NOT NULL,
+PRIMARY KEY (id),
+UNIQUE ggid (ggid)
+) TYPE=BDB;
+insert into t1 (ggid,passwd) values ('test1','xxx');
+insert into t1 (ggid,passwd) values ('test2','yyy');
+insert into t1 (ggid,passwd) values ('test2','this will fail');
+Duplicate entry 'test2' for key 2
+insert into t1 (ggid,id) values ('this will fail',1);
+Duplicate entry '1' for key 1
+select * from t1 where ggid='test1';
 id	ggid	email	passwd
 1	test1		xxx
+select * from t1 where passwd='xxx';
 id	ggid	email	passwd
 1	test1		xxx
+select * from t1 where id=2;
 id	ggid	email	passwd
 2	test2		yyy
+replace into t1 (ggid,id) values ('this will work',1);
+replace into t1 (ggid,passwd) values ('test2','this will work');
+update t1 set id=100,ggid='test2' where id=1;
+Duplicate entry 'test2' for key 2
+select * from t1;
 id	ggid	email	passwd
 1	this will work		
 3	test2		this will work
+select * from t1 where id=1;
 id	ggid	email	passwd
 1	this will work		
+select * from t1 where id=999;
 id	ggid	email	passwd
+drop table t1;
+CREATE TABLE t1 (
+user_name varchar(12),
+password text,
+subscribed char(1),
+user_id int(11) DEFAULT '0' NOT NULL,
+quota bigint(20),
+weight double,
+access_date date,
+access_time time,
+approved datetime,
+dummy_primary_key int(11) NOT NULL auto_increment,
+PRIMARY KEY (dummy_primary_key)
+) TYPE=BDB;
+INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
+INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
+INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
+INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
+INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
+select  user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
 user_name	password	subscribed	user_id	quota	weight	access_date	access_time	approved	dummy_primary_key
 user_0	somepassword	N	0	0	0	2000-09-07	23:06:59	2000-09-07 23:06:59	1
 user_1	somepassword	Y	1	1	1	2000-09-07	23:06:59	2000-09-07 23:06:59	2
 user_2	somepassword	N	2	2	1.4142135623731	2000-09-07	23:06:59	2000-09-07 23:06:59	3
 user_3	somepassword	Y	3	3	1.7320508075689	2000-09-07	23:06:59	2000-09-07 23:06:59	4
 user_4	somepassword	N	4	4	2	2000-09-07	23:06:59	2000-09-07 23:06:59	5
+drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+parent_id int(11) DEFAULT '0' NOT NULL,
+level tinyint(4) DEFAULT '0' NOT NULL,
+KEY (id),
+KEY parent_id (parent_id),
+KEY level (level)
+) type=bdb;
+INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
+INSERT INTO t1 values (179,5,2);
+update t1 set parent_id=parent_id+100;
+select * from t1 where parent_id=102;
 id	parent_id	level
 8	102	2
 9	102	2
 15	102	2
+update t1 set id=id+1000;
+update t1 set id=1024 where id=1009;
+select * from t1;
 id	parent_id	level
 1001	100	0
 1003	101	1
@@ -327,6 +575,8 @@ id	parent_id	level
 1019	103	2
 1005	101	1
 1179	105	2
+update ignore t1 set id=id+1;
+select * from t1;
 id	parent_id	level
 1002	100	0
 1004	101	1
@@ -367,12 +617,16 @@ id	parent_id	level
 1020	103	2
 1006	101	1
 1180	105	2
+update ignore t1 set id=1023 where id=1010;
+select * from t1 where parent_id=102;
 id	parent_id	level
 1009	102	2
 1025	102	2
 1016	102	2
+explain select level from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	level	level	1	const	1	where used; Using index
+select level,id from t1 where level=1;
 level	id
 1	1004
 1	1005
@@ -380,6 +634,7 @@ level	id
 1	1007
 1	1008
 1	1006
+select level,id,parent_id from t1 where level=1;
 level	id	parent_id
 1	1004	101
 1	1005	101
@@ -387,6 +642,7 @@ level	id	parent_id
 1	1007	101
 1	1008	101
 1	1006	101
+select level,id from t1 where level=1 order by id;
 level	id
 1	1003
 1	1004
@@ -394,6 +650,8 @@ level	id
 1	1006
 1	1007
 1	1008
+delete from t1 where level=1;
+select * from t1;
 id	parent_id	level
 1002	100	0
 1009	102	2
@@ -428,43 +686,86 @@ id	parent_id	level
 1022	104	2
 1020	103	2
 1180	105	2
+drop table t1;
+CREATE TABLE t1 (
+sca_code char(6) NOT NULL,
+cat_code char(6) NOT NULL,
+sca_desc varchar(50),
+lan_code char(2) NOT NULL,
+sca_pic varchar(100),
+sca_sdesc varchar(50),
+sca_sch_desc varchar(16),
+PRIMARY KEY (sca_code, cat_code, lan_code),
+INDEX sca_pic (sca_pic)
+) type = bdb ;
+INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
+select count(*) from t1 where sca_code = 'PD';
 count(*)
 1
+select count(*) from t1 where sca_code <= 'PD';
 count(*)
 1
+select count(*) from t1 where sca_pic is null;
 count(*)
 2
+alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
+select count(*) from t1 where sca_code='PD' and sca_pic is null;
 count(*)
 1
+select count(*) from t1 where cat_code='E';
 count(*)
 0
+alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
+select count(*) from t1 where sca_code='PD' and sca_pic is null;
 count(*)
 1
+select count(*) from t1 where sca_pic >= 'n';
 count(*)
 1
+select sca_pic from t1 where sca_pic is null;
 sca_pic
 NULL
 NULL
+update t1 set sca_pic="test" where sca_pic is null;
+delete from t1 where sca_code='pd';
+drop table t1;
+set @a:=now();
+CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) type=bdb;
+insert into t1 (a) values(1),(2),(3);
+select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
 a
 1
 2
 3
+update t1 set a=5 where a=1;
+select a from t1;
 a
 2
 3
 5
+drop table t1;
+flush logs;
+create table t1 (b blob, i int, key (b(100)), key (i), key (i, b(20))) type=bdb;
+insert into t1 values ('this is a blob', 1), (null, -1), (null, null),("",1),("",2),("",3);
+select b from t1 where b = 'this is a blob';
 b
 this is a blob
+select * from t1 where b like 't%';
 b	i
 this is a blob	1
+select b, i from t1 where b is not null;
 b	i
 this is a blob	1
 	1
 	2
 	3
+select * from t1 where b is null and i > 0;
 b	i
+select * from t1 where i is NULL;
 b	i
 NULL	NULL
+update t1 set b='updated' where i=1;
+select * from t1;
 b	i
 updated	1
 NULL	-1
@@ -472,63 +773,331 @@ NULL	NULL
 updated	1
 	2
 	3
+drop table t1;
+create table t1 (a varchar(100) not null, primary key(a), b int not null) type=bdb;
+insert into t1 values("hello",1),("world",2);
+select * from t1 order by b desc;
 a	b
 world	2
 hello	1
+optimize table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
+show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	0	PRIMARY	1	a	A	2	NULL	NULL	
+drop table t1;
+create table t1 (i int, j int )TYPE=BDB;
+insert into t1 values (1,2);
+select * from t1 where i=1 and j=2;
 i	j
 1	2
+create index ax1 on t1 (i,j);
+select * from t1 where i=1 and j=2;
 i	j
 1	2
+drop table t1;
+drop table if exists t1, t2, t3, t4, t5, t6, t7;
+create table t1
+(
+branch_id	int		auto_increment	primary key,
+branch_name	varchar(255)	not null,
+branch_active	int		not null 	default 1,
+unique  branch_name(branch_name),
+index	branch_active(branch_active)
+) type=bdb;
+drop table if exists t2 ;
+create table t2
+(
+target_id	int		auto_increment	primary key,
+target_name	varchar(255)	not null,
+target_active	int		not null 	default 1,
+unique	target_name(target_name),
+index	target_active(target_active)
+) type=bdb;
+drop table if exists t3 ;
+create table t3
+(
+platform_id	int		auto_increment	primary key,
+platform_name	varchar(255)	not null,
+platform_active	int		not null 	default 1,
+unique	platform_name(platform_name),
+index	platform_active(platform_active)
+) type=bdb;
+drop table if exists t4 ;
+create table t4
+(
+product_id	int		auto_increment	primary key,
+product_name	varchar(255)	not null,
+version_file	varchar(255)	not null,
+product_active	int		not null 	default 1,
+unique	product_name(product_name),
+index	product_active(product_active)
+) type=bdb;
+drop table if exists t5 ;
+create table t5
+(
+product_file_id		int		auto_increment	primary key,
+product_id		int		not null,
+file_name		varchar(255)	not null,
+/* cvs module used to find the file version */
+module_name		varchar(255)	not null,
+/* flag whether the file is still included in the product */
+file_included		int		not null	default 1,
+unique	product_file(product_id,file_name),
+index	file_included(file_included)
+) type=bdb;
+drop table if exists t6 ;
+create table t6
+(
+file_platform_id	int		auto_increment	primary key,
+product_file_id		int		not null,
+platform_id		int		not null,
+branch_id		int		not null,
+/* filename in the build system */
+build_filename		varchar(255)	not null,
+/* default filename in the build archive */
+archive_filename	varchar(255)	not null,
+unique  file_platform(product_file_id,platform_id,branch_id)
+) type=bdb;
+drop table if exists t8 ;
+create table t8
+(
+archive_id	int		auto_increment	primary key,
+branch_id	int		not null,
+target_id	int		not null,
+platform_id	int		not null,
+product_id	int		not null,
+status_id	int		not null	default 1,
+unique  archive(branch_id,target_id,platform_id,product_id),
+index	status_id(status_id)
+) type=bdb;
+drop table if exists t7 ;
+create table t7
+(
+build_id	int		auto_increment	primary key,
+branch_id	int		not null,
+target_id	int		not null,
+build_number	int		not null,
+build_date	date		not null,
+/* build system tag, e.g. 'rmanight-022301-1779' */
+build_tag	varchar(255)	not null,	
+/* path relative to the build archive root, e.g. 'current' */
+build_path	text		not null,	
+unique  build(branch_id,target_id,build_number)
+) type=bdb;
+insert into t1 (branch_name)
+values ('RealMedia');
+insert into t1 (branch_name)
+values ('RP8REV');
+insert into t1 (branch_name)
+values ('SERVER_8_0_GOLD');
+insert into t2 (target_name)
+values ('rmanight');
+insert into t2 (target_name)
+values ('playerall');
+insert into t2 (target_name)
+values ('servproxyall');
+insert into t3 (platform_name)
+values ('linux-2.0-libc6-i386');
+insert into t3 (platform_name)
+values ('win32-i386');
+insert into t4 (product_name, version_file)
+values ('realserver', 'servinst');
+insert into t4 (product_name, version_file)
+values ('realproxy', 'prxyinst');
+insert into t4 (product_name, version_file)
+values ('realplayer', 'playinst');
+insert into t4 (product_name, version_file)
+values ('plusplayer', 'plusinst');
+create temporary table tmp1
+select branch_id, target_id, platform_id, product_id
+from t1, t2, t3, t4 ;
+create temporary table tmp2 
+select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id 
+from tmp1 left join t8 
+using (branch_id,target_id,platform_id,product_id) 
+where t8.archive_id is null ;
+insert into t8 
+(branch_id, target_id, platform_id, product_id, status_id)
+select branch_id, target_id, platform_id, product_id, 1
+from tmp2 ;
+drop table tmp1 ;
+drop table tmp2 ;
+insert into t5 (product_id, file_name, module_name)
+values (1, 'servinst', 'server');
+insert into t5 (product_id, file_name, module_name)
+values (2, 'prxyinst', 'server');
+insert into t5 (product_id, file_name, module_name)
+values (3, 'playinst', 'rpapp');
+insert into t5 (product_id, file_name, module_name)
+values (4, 'plusinst', 'rpapp');
+insert into t6 
+(product_file_id,platform_id,branch_id,build_filename,archive_filename)
+values (1, 2, 3, 'servinst.exe', 'win32-servinst.exe');
+insert into t6 
+(product_file_id,platform_id,branch_id,build_filename,archive_filename)
+values (1, 1, 3, 'v80_linux-2.0-libc6-i386_servinst.bin', 'linux2-servinst.exe');
+insert into t6 
+(product_file_id,platform_id,branch_id,build_filename,archive_filename)
+values (3, 2, 2, 'playinst.exe', 'win32-playinst.exe');
+insert into t6 
+(product_file_id,platform_id,branch_id,build_filename,archive_filename)
+values (4, 2, 2, 'playinst.exe', 'win32-playinst.exe');
+insert into t7 
+(branch_id,target_id,build_number,build_tag,build_date,build_path)
+values (2, 2, 1071, 'playerall-022101-1071', '2001-02-21', 'current');
+insert into t7 
+(branch_id,target_id,build_number,build_tag,build_date,build_path)
+values (2, 2, 1072, 'playerall-022201-1072', '2001-02-22', 'current');
+insert into t7 
+(branch_id,target_id,build_number,build_tag,build_date,build_path)
+values (3, 3, 388, 'servproxyall-022201-388', '2001-02-22', 'current');
+insert into t7 
+(branch_id,target_id,build_number,build_tag,build_date,build_path)
+values (3, 3, 389, 'servproxyall-022301-389', '2001-02-23', 'current');
+insert into t7 
+(branch_id,target_id,build_number,build_tag,build_date,build_path)
+values (4, 4, 100, 'foo target-010101-100', '2001-01-01', 'current');
+update t8
+set status_id=2
+where branch_id=2 and target_id=2 and platform_id=2 and product_id=1;
+select t7.build_path
+from 
+t1, 
+t7, 
+t2, 
+t3, 
+t4,
+t5, 
+t6
+where 
+t7.branch_id = t1.branch_id and 
+t7.target_id = t2.target_id and 
+t5.product_id = t4.product_id and
+t6.product_file_id = t5.product_file_id and
+t6.platform_id = t3.platform_id and
+t6.branch_id = t6.branch_id and
+t7.build_id = 1 and
+t4.product_id = 3 and
+t5.file_name = 'playinst' and
+t3.platform_id = 2;
 build_path
 current
+drop table t1, t2, t3, t4, t5, t6, t7, t8;
+CREATE TABLE t1 (
+a tinytext NOT NULL,
+b tinyint(3) unsigned NOT NULL default '0',
+PRIMARY KEY (a(32),b)
+) TYPE=BDB;
+INSERT INTO t1 VALUES ('a',1),('a',2);
+SELECT * FROM t1 WHERE a='a' AND b=2;
 a	b
 a	2
+SELECT * FROM t1 WHERE a='a' AND b in (2);
 a	b
 a	2
+SELECT * FROM t1 WHERE a='a' AND b in (1,2);
 a	b
 a	1
 a	2
+drop table t1;
+CREATE TABLE t1 (
+a int3 unsigned NOT NULL,
+b int1 unsigned NOT NULL,
+UNIQUE (a, b)
+) TYPE = BDB;
+INSERT INTO t1 VALUES (1, 1);
+SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
 MIN(B)	MAX(b)
 1	1
+drop table t1;
+create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=bdb;
+insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
+LOCK TABLES t1 WRITE;
+insert into t1 values (99,1,2,'D'),(1,1,2,'D');
+Duplicate entry '1-1' for key 1
+select id from t1;
 id
 0
 1
 2
+select id from t1;
 id
 0
 1
 2
+UNLOCK TABLES;
+DROP TABLE t1;
+create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=bdb;
+insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
+LOCK TABLES t1 WRITE;
+begin;
+insert into t1 values (99,1,2,'D'),(1,1,2,'D');
+Duplicate entry '1-1' for key 1
+select id from t1;
 id
 0
 1
 2
+insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
+commit;
+select id,id3 from t1;
 id	id3
 0	0
 1	1
 2	2
 100	2
+UNLOCK TABLES;
+DROP TABLE t1;
+CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY  (SYAIN_NO,KINMU_DATE)) TYPE=BerkeleyDB;
+CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY  (SYAIN_NO,STR_DATE) ) TYPE=BerkeleyDB;
+select T1.KINMU_DATE from t1 T1 ,t2 T2 where  T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO;
 KINMU_DATE
+select T1.KINMU_DATE from t1 T1 ,t2 T2 where  T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO;
 KINMU_DATE
+DROP TABLE t1,t2;
+drop table if exists t1;
+create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) type=bdb;
+insert into t1 values (1,1), (1,2);
+select * from t1 where a = 1;
 a	b
 1	1
 1	2
+select t1.*, t2.* from t1, t1 t2 where t1.a = t2.a and t2.a = 1;
 a	b	a	b
 1	1	1	1
 1	1	1	2
 1	2	1	1
 1	2	1	2
+select * from t1 where a = 1;
 a	b
 1	1
 1	2
+drop table t1;
+create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=bdb;
+insert into t1 values (0,0,0,'ABCDEFGHIJ');
+create table t2 (id int NOT NULL,primary key (id)) type=bdb;
+LOCK TABLES t1 WRITE, t2 WRITE;
+insert into t2 values(1);
+SELECT t1.* FROM t1 WHERE id IN (1);
 id	id2	id3	dummy1
+SELECT t1.* FROM t2 left outer join t1 on (t1.id=t2.id);
 id	id2	id3	dummy1
 NULL	NULL	NULL	NULL
+delete from t1 where id3 >= 0 and id3 <= 0;
+drop table t1,t2;
+CREATE TABLE t1 (i varchar(48) NOT NULL default '', p varchar(255) default NULL,s varchar(48) NOT NULL default '', PRIMARY KEY  (i), UNIQUE(p,s)) TYPE=BDB;
+INSERT INTO t1 VALUES ('00000000-e6c4ddeaa6-003b8-83458387','programs/xxxxxxxx.wmv','00000000-e6c4ddeb32-003bc-83458387');
+SELECT * FROM t1 WHERE p='programs/xxxxxxxx.wmv';
 i	p	s
 00000000-e6c4ddeaa6-003b8-83458387	programs/xxxxxxxx.wmv	00000000-e6c4ddeb32-003bc-83458387
+drop table t1;
+CREATE TABLE t1 ( STR_DATE varchar(8) NOT NULL default '',INFO_NOTE varchar(200) default NULL,PRIMARY KEY  (STR_DATE) ) TYPE=BerkeleyDB;
+select INFO_NOTE from t1 where STR_DATE = '20010610';
 INFO_NOTE
+select INFO_NOTE from t1 where STR_DATE < '20010610';
 INFO_NOTE
+select INFO_NOTE from t1 where STR_DATE > '20010610';
 INFO_NOTE
+drop table t1;
diff --git a/mysql-test/r/have_openssl.require b/mysql-test/r/have_openssl.require
index 9bdffdf0c13..dae48a472b5 100644
--- a/mysql-test/r/have_openssl.require
+++ b/mysql-test/r/have_openssl.require
@@ -1,2 +1,2 @@
 Variable_name	Value
-have_ssl	YES
+have_openssl	YES
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 67aa024b117..c96ce4e5e33 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1,3 +1,7 @@
+drop table if exists t1,t2;
+create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb;
+insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
+select id, code, name from t1 order by id;
 id	code	name
 1	1	Tim
 2	1	Monty
@@ -6,6 +10,8 @@ id	code	name
 5	3	Sasha
 6	3	Jeremy
 7	4	Matt
+update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
+select id, code, name from t1 order by id;
 id	code	name
 2	1	Monty
 3	2	David
@@ -14,6 +20,8 @@ id	code	name
 6	3	Jeremy
 7	4	Matt
 8	1	Sinisa
+update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
+select id, code, name from t1 order by id;
 id	code	name
 3	2	David
 4	2	Erik
@@ -22,10 +30,26 @@ id	code	name
 7	4	Matt
 8	1	Sinisa
 12	1	Ralph
+drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+parent_id int(11) DEFAULT '0' NOT NULL,
+level tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (id),
+KEY parent_id (parent_id),
+KEY level (level)
+) type=innodb;
+INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
+update t1 set parent_id=parent_id+100;
+select * from t1 where parent_id=102;
 id	parent_id	level
 8	102	2
 9	102	2
 15	102	2
+update t1 set id=id+1000;
+update t1 set id=1024 where id=1009;
+Duplicate entry '1024' for key 1
+select * from t1;
 id	parent_id	level
 1001	100	0
 1002	101	1
@@ -66,6 +90,8 @@ id	parent_id	level
 1193	105	2
 1202	107	2
 1203	107	2
+update ignore t1 set id=id+1;
+select * from t1;
 id	parent_id	level
 1001	100	0
 1002	101	1
@@ -106,16 +132,22 @@ id	parent_id	level
 1194	105	2
 1202	107	2
 1204	107	2
+update ignore t1 set id=1023 where id=1010;
+select * from t1 where parent_id=102;
 id	parent_id	level
 1008	102	2
 1010	102	2
 1015	102	2
+explain select level from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	index	level	level	1	NULL	39	where used; Using index
+explain select level,id from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	index	level	level	1	NULL	39	where used; Using index
+explain select level,id,parent_id from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	level	NULL	NULL	NULL	39	where used
+select level,id from t1 where level=1;
 level	id
 1	1002
 1	1003
@@ -123,6 +155,7 @@ level	id
 1	1005
 1	1006
 1	1007
+select level,id,parent_id from t1 where level=1;
 level	id	parent_id
 1	1002	101
 1	1003	101
@@ -130,72 +163,196 @@ level	id	parent_id
 1	1005	101
 1	1006	101
 1	1007	101
+optimize table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	error	The handler for the table doesn't support check/repair
+show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	0	PRIMARY	1	id	A	2	NULL	NULL	
 t1	1	parent_id	1	parent_id	A	4	NULL	NULL	
 t1	1	level	1	level	A	4	NULL	NULL	
+drop table t1;
+CREATE TABLE t1 (
+gesuchnr int(11) DEFAULT '0' NOT NULL,
+benutzer_id int(11) DEFAULT '0' NOT NULL,
+PRIMARY KEY (gesuchnr,benutzer_id)
+) type=innodb;
+replace into t1 (gesuchnr,benutzer_id) values (2,1);
+replace into t1 (gesuchnr,benutzer_id) values (1,1);
+replace into t1 (gesuchnr,benutzer_id) values (1,1);
+select * from t1;
 gesuchnr	benutzer_id
 1	1
 2	1
+drop table t1;
+create table t1 (a int) type=innodb;
+insert into t1 values (1), (2);
+optimize table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	error	The handler for the table doesn't support check/repair
+delete from t1 where a = 1;
+select * from t1;
 a
 2
+check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
+drop table t1;
+create table t1 (a int,b varchar(20)) type=innodb;
+insert into t1 values (1,""), (2,"testing");
+delete from t1 where a = 1;
+select * from t1;
 a	b
 2	testing
+create index skr on t1 (a);
+insert into t1 values (3,""), (4,"testing");
+analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	error	The handler for the table doesn't support check/repair
+show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	1	skr	1	a	A	3	NULL	NULL	
+drop table t1;
+create table t1 (a int,b varchar(20),key(a)) type=innodb;
+insert into t1 values (1,""), (2,"testing");
+select * from t1 where a = 1;
 a	b
 1	
+drop table t1;
+create table t1 (n int not null primary key) type=innodb;
+set autocommit=0;
+insert into t1 values (4);
+rollback;
+select n, "after rollback" from t1;
 n	after rollback
+insert into t1 values (4);
+commit;
+select n, "after commit" from t1;
 n	after commit
 4	after commit
+commit;
+insert into t1 values (5);
+insert into t1 values (4);
+Duplicate entry '4' for key 1
+commit;
+select n, "after commit" from t1;
 n	after commit
 4	after commit
 5	after commit
+set autocommit=1;
+insert into t1 values (6);
+insert into t1 values (4);
+Duplicate entry '4' for key 1
+select n from t1;
 n
 4
 5
 6
+rollback;
+drop table t1;
+create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) type=innodb;
+begin;
+insert into t1 values(1,'hamdouni');
+select id as afterbegin_id,nom as afterbegin_nom from t1;
 afterbegin_id	afterbegin_nom
 1	hamdouni
+rollback;
+select id as afterrollback_id,nom as afterrollback_nom from t1;
 afterrollback_id	afterrollback_nom
+set autocommit=0;
+insert into t1 values(2,'mysql');
+select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
 afterautocommit0_id	afterautocommit0_nom
 2	mysql
+rollback;
+select id as afterrollback_id,nom as afterrollback_nom from t1;
 afterrollback_id	afterrollback_nom
+set autocommit=1;
+drop table t1;
+CREATE TABLE t1 (id char(8) not null primary key, val int not null) type=innodb;
+insert into t1 values ('pippo', 12);
+insert into t1 values ('pippo', 12);
+Duplicate entry 'pippo' for key 1
+delete from t1;
+delete from t1 where id = 'pippo';
+select * from t1;
 id	val
+insert into t1 values ('pippo', 12);
+set autocommit=0;
+delete from t1;
+rollback;
+select * from t1;
 id	val
 pippo	12
+delete from t1;
+commit;
+select * from t1;
 id	val
+drop table t1;
+create table t1 (a integer) type=innodb;
+begin;
+rename table t1 to t2;
+create table t1 (b integer) type=innodb;
+insert into t1 values (1);
+rollback;
+drop table t1;
+rename table t2 to t1;
+drop table t1;
+set autocommit=1;
+CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) TYPE=innodb;
+INSERT INTO t1 VALUES (1, 'Jochen');
+select * from t1;
 ID	NAME
 1	Jochen
+drop table t1;
+CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=innodb;
+set autocommit=0;
+INSERT INTO t1  SET _userid='marc@anyware.co.uk';
+COMMIT;
+SELECT * FROM t1;
 _userid
 marc@anyware.co.uk
+SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
 _userid
 marc@anyware.co.uk
+drop table t1;
+set autocommit=1;
+CREATE TABLE t1 (
+user_id int(10) DEFAULT '0' NOT NULL,
+name varchar(100),
+phone varchar(100),
+ref_email varchar(100) DEFAULT '' NOT NULL,
+detail varchar(200),
+PRIMARY KEY (user_id,ref_email)
+)type=innodb;
+INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
+select * from t1 where user_id=10292;
 user_id	name	phone	ref_email	detail
 10292	sanjeev	29153373	sansh777@hotmail.com	xxx
 10292	shirish	2333604	shirish@yahoo.com	ddsds
 10292	sonali	323232	sonali@bolly.com	filmstar
+INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
+select * from t1 where user_id=10292;
 user_id	name	phone	ref_email	detail
 10292	sanjeev	29153373	sansh777@hotmail.com	xxx
 10292	shirish	2333604	shirish@yahoo.com	ddsds
 10292	sonali	323232	sonali@bolly.com	filmstar
+select * from t1 where user_id>=10292;
 user_id	name	phone	ref_email	detail
 10292	sanjeev	29153373	sansh777@hotmail.com	xxx
 10292	shirish	2333604	shirish@yahoo.com	ddsds
 10292	sonali	323232	sonali@bolly.com	filmstar
 10293	shirish	2333604	shirish@yahoo.com	ddsds
+select * from t1 where user_id>10292;
 user_id	name	phone	ref_email	detail
 10293	shirish	2333604	shirish@yahoo.com	ddsds
+select * from t1 where user_id<10292;
 user_id	name	phone	ref_email	detail
 10291	sanjeev	29153373	sansh777@hotmail.com	xxx
+drop table t1;
+CREATE TABLE t1 (a int not null, b int not null,c int not null,
+key(a),primary key(a,b), unique(c),key(a),unique(b));
+show index from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	0	PRIMARY	1	a	A	NULL	NULL	NULL	
 t1	0	PRIMARY	2	b	A	0	NULL	NULL	
@@ -203,69 +360,153 @@ t1	0	b	1	b	A	0	NULL	NULL
 t1	0	c	1	c	A	0	NULL	NULL	
 t1	1	a	1	a	A	NULL	NULL	NULL	
 t1	1	a_2	1	a	A	NULL	NULL	NULL	
+drop table t1;
+create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
+alter table t1 type=innodb;
+insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
+select * from t1;
 col1	col2
 1	1
 2	3
 3	4
 4	4
 5	2
+update t1 set col2='7' where col1='4';
+select * from t1;
 col1	col2
 1	1
 2	3
 3	4
 4	7
 5	2
+alter table t1 add co3 int not null;
+select * from t1;
 col1	col2	co3
 1	1	0
 2	3	0
 3	4	0
 4	7	0
 5	2	0
+update t1 set col2='9' where col1='2';
+select * from t1;
 col1	col2	co3
 1	1	0
 2	9	0
 3	4	0
 4	7	0
 5	2	0
+drop table t1;
+create table t1 (a int not null , b int, primary key (a)) type = innodb;
+create table t2 (a int not null , b int, primary key (a)) type = myisam;
+insert into t1 VALUES (1,3) , (2,3), (3,3);
+select * from t1;
 a	b
 1	3
 2	3
 3	3
+insert into t2 select * from t1;
+select * from t2;
 a	b
 1	3
 2	3
 3	3
+delete from t1 where b = 3;
+select * from t1;
 a	b
+insert into t1 select * from t2;
+select * from t1;
 a	b
 1	3
 2	3
 3	3
+select * from t2;
 a	b
 1	3
 2	3
 3	3
+drop table t1,t2;
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+ggid varchar(32) binary DEFAULT '' NOT NULL,
+email varchar(64) DEFAULT '' NOT NULL,
+passwd varchar(32) binary DEFAULT '' NOT NULL,
+PRIMARY KEY (id),
+UNIQUE ggid (ggid)
+) TYPE=innodb;
+insert into t1 (ggid,passwd) values ('test1','xxx');
+insert into t1 (ggid,passwd) values ('test2','yyy');
+insert into t1 (ggid,passwd) values ('test2','this will fail');
+Duplicate entry 'test2' for key 2
+insert into t1 (ggid,id) values ('this will fail',1);
+Duplicate entry '1' for key 1
+select * from t1 where ggid='test1';
 id	ggid	email	passwd
 1	test1		xxx
+select * from t1 where passwd='xxx';
 id	ggid	email	passwd
 1	test1		xxx
+select * from t1 where id=2;
 id	ggid	email	passwd
 2	test2		yyy
+replace into t1 (ggid,id) values ('this will work',1);
+replace into t1 (ggid,passwd) values ('test2','this will work');
+update t1 set id=100,ggid='test2' where id=1;
+Duplicate entry 'test2' for key 2
+select * from t1;
 id	ggid	email	passwd
 1	this will work		
 4	test2		this will work
+select * from t1 where id=1;
 id	ggid	email	passwd
 1	this will work		
+select * from t1 where id=999;
 id	ggid	email	passwd
+drop table t1;
+CREATE TABLE t1 (
+user_name varchar(12),
+password text,
+subscribed char(1),
+user_id int(11) DEFAULT '0' NOT NULL,
+quota bigint(20),
+weight double,
+access_date date,
+access_time time,
+approved datetime,
+dummy_primary_key int(11) NOT NULL auto_increment,
+PRIMARY KEY (dummy_primary_key)
+) TYPE=innodb;
+INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
+INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
+INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
+INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
+INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
+select  user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
 user_name	password	subscribed	user_id	quota	weight	access_date	access_time	approved	dummy_primary_key
 user_0	somepassword	N	0	0	0	2000-09-07	23:06:59	2000-09-07 23:06:59	1
 user_1	somepassword	Y	1	1	1	2000-09-07	23:06:59	2000-09-07 23:06:59	2
 user_2	somepassword	N	2	2	1.4142135623731	2000-09-07	23:06:59	2000-09-07 23:06:59	3
 user_3	somepassword	Y	3	3	1.7320508075689	2000-09-07	23:06:59	2000-09-07 23:06:59	4
 user_4	somepassword	N	4	4	2	2000-09-07	23:06:59	2000-09-07 23:06:59	5
+drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+parent_id int(11) DEFAULT '0' NOT NULL,
+level tinyint(4) DEFAULT '0' NOT NULL,
+KEY (id),
+KEY parent_id (parent_id),
+KEY level (level)
+) type=innodb;
+INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
+INSERT INTO t1 values (179,5,2);
+update t1 set parent_id=parent_id+100;
+select * from t1 where parent_id=102;
 id	parent_id	level
 8	102	2
 9	102	2
 15	102	2
+update t1 set id=id+1000;
+update t1 set id=1024 where id=1009;
+select * from t1;
 id	parent_id	level
 1001	100	0
 1003	101	1
@@ -306,6 +547,8 @@ id	parent_id	level
 1019	103	2
 1005	101	1
 1179	105	2
+update ignore t1 set id=id+1;
+select * from t1;
 id	parent_id	level
 1002	100	0
 1004	101	1
@@ -346,12 +589,16 @@ id	parent_id	level
 1020	103	2
 1006	101	1
 1180	105	2
+update ignore t1 set id=1023 where id=1010;
+select * from t1 where parent_id=102;
 id	parent_id	level
 1009	102	2
 1025	102	2
 1016	102	2
+explain select level from t1 where level=1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	level	level	1	const	6	where used; Using index
+select level,id from t1 where level=1;
 level	id
 1	1004
 1	1005
@@ -359,6 +606,7 @@ level	id
 1	1007
 1	1008
 1	1006
+select level,id,parent_id from t1 where level=1;
 level	id	parent_id
 1	1004	101
 1	1005	101
@@ -366,6 +614,7 @@ level	id	parent_id
 1	1007	101
 1	1008	101
 1	1006	101
+select level,id from t1 where level=1 order by id;
 level	id
 1	1003
 1	1004
@@ -373,6 +622,8 @@ level	id
 1	1006
 1	1007
 1	1008
+delete from t1 where level=1;
+select * from t1;
 id	parent_id	level
 1002	100	0
 1009	102	2
@@ -407,77 +658,212 @@ id	parent_id	level
 1022	104	2
 1020	103	2
 1180	105	2
+drop table t1;
+CREATE TABLE t1 (
+sca_code char(6) NOT NULL,
+cat_code char(6) NOT NULL,
+sca_desc varchar(50),
+lan_code char(2) NOT NULL,
+sca_pic varchar(100),
+sca_sdesc varchar(50),
+sca_sch_desc varchar(16),
+PRIMARY KEY (sca_code, cat_code, lan_code),
+INDEX sca_pic (sca_pic)
+) type = innodb ;
+INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
+select count(*) from t1 where sca_code = 'PD';
 count(*)
 1
+select count(*) from t1 where sca_code <= 'PD';
 count(*)
 1
+select count(*) from t1 where sca_pic is null;
 count(*)
 2
+alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
+select count(*) from t1 where sca_code='PD' and sca_pic is null;
 count(*)
 1
+select count(*) from t1 where cat_code='E';
 count(*)
 0
+alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
+select count(*) from t1 where sca_code='PD' and sca_pic is null;
 count(*)
 1
+select count(*) from t1 where sca_pic >= 'n';
 count(*)
 1
+select sca_pic from t1 where sca_pic is null;
 sca_pic
 NULL
 NULL
+update t1 set sca_pic="test" where sca_pic is null;
+delete from t1 where sca_code='pd';
+drop table t1;
+set @a:=now();
+CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) type=innodb;
+insert into t1 (a) values(1),(2),(3);
+select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
 a
 1
 2
 3
+update t1 set a=5 where a=1;
+select a from t1;
 a
 2
 3
 5
+drop table t1;
+create table t1 (a varchar(100) not null, primary key(a), b int not null) type=innodb;
+insert into t1 values("hello",1),("world",2);
+select * from t1 order by b desc;
 a	b
 world	2
 hello	1
+optimize table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	error	The handler for the table doesn't support check/repair
+show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Comment
 t1	0	PRIMARY	1	a	A	2	NULL	NULL	
+drop table t1;
+create table t1 (i int, j int ) TYPE=innodb;
+insert into t1 values (1,2);
+select * from t1 where i=1 and j=2;
 i	j
 1	2
+create index ax1 on t1 (i,j);
+select * from t1 where i=1 and j=2;
 i	j
 1	2
+drop table t1;
+CREATE TABLE t1 (
+a int3 unsigned NOT NULL,
+b int1 unsigned NOT NULL,
+UNIQUE (a, b)
+) TYPE = innodb;
+INSERT INTO t1 VALUES (1, 1);
+SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
 MIN(B)	MAX(b)
 1	1
+drop table t1;
+CREATE TABLE t1 (a int unsigned NOT NULL) type=innodb;
+INSERT INTO t1 VALUES (1);
+SELECT * FROM t1;
 a
 1
+DROP TABLE t1;
+create table t1 (a int  primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) type = innodb;
+insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
+explain select * from t1 where a > 0 and a < 50;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	PRIMARY	PRIMARY	4	NULL	1	where used
+drop table t1;
+create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=innodb;
+insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
+LOCK TABLES t1 WRITE;
+insert into t1 values (99,1,2,'D'),(1,1,2,'D');
+Duplicate entry '1-1' for key 1
+select id from t1;
 id
 0
 1
 2
+select id from t1;
 id
 0
 1
 2
+UNLOCK TABLES;
+DROP TABLE t1;
+create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=innodb;
+insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
+LOCK TABLES t1 WRITE;
+begin;
+insert into t1 values (99,1,2,'D'),(1,1,2,'D');
+Duplicate entry '1-1' for key 1
+select id from t1;
 id
 0
 1
 2
+insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
+commit;
+select id,id3 from t1;
 id	id3
 0	0
 1	1
 2	2
 100	2
+UNLOCK TABLES;
+DROP TABLE t1;
+create table t1 (a char(20), unique (a(5))) type=innodb;
+Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the table handler doesn't support unique sub keys
+create table t1 (a char(20), index (a(5))) type=innodb;
+show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` char(20) default NULL,
   KEY `a` (`a`)
 ) TYPE=InnoDB
+drop table t1;
+create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb;
+insert into t1 values (NULL),(NULL),(NULL);
+delete from t1 where a=3;
+insert into t1 values (NULL);
+select * from t1;
 a
 1
 2
 4
+drop table t1;
+create table t1
+(
+id int auto_increment primary key,
+name varchar(32) not null,
+value text not null,
+uid int not null,
+unique key(name,uid)
+) type=innodb;
+insert into t1 values (1,'one','one value',101),
+(2,'two','two value',102),(3,'three','three value',103);
+set insert_id=5;
+replace into t1 (value,name,uid) values ('other value','two',102);
+delete from t1 where uid=102;
+set insert_id=5;
+replace into t1 (value,name,uid) values ('other value','two',102);
+set insert_id=6;
+replace into t1 (value,name,uid) values ('other value','two',102);
+select * from t1;
 id	name	value	uid
 1	one	one value	101
 3	three	three value	103
 6	two	other value	102
+drop table t1;
+create database test_$1;
+create table test_$1.t1 (a int not null) type= innodb;
+insert into test_$1.t1 values(1);
+create table test_$1.t2 (a int not null) type= myisam;
+insert into test_$1.t2 values(1);
+create table test_$1.t3 (a int not null) type= heap;
+insert into test_$1.t3 values(1);
+commit;
+drop database test_$1;
+show tables from test_$1;
+Can't read dir of './test_$1/' (Errcode: 2)
+create table t1 (a int not null) type= innodb;
+insert into t1 values(1),(2);
+truncate table t1;
+Can't execute the given command because you have active locked tables or an active transaction
+commit;
+truncate table t1;
+select * from t1;
 a
+insert into t1 values(1),(2);
+delete from t1;
+select * from t1;
 a
+commit;
+drop table t1;