19 files changed, 474 insertions, 21 deletions
diff --git a/mysql-test/include/big_test.inc b/mysql-test/include/big_test.inc
new file mode 100644
index 00000000000..6b149540c96
--- /dev/null
+++ b/mysql-test/include/big_test.inc
@@ -0,0 +1,4 @@
+--require r/big_test.require
+disable_query_log;
+eval select $BIG_TEST as using_big_test;
+enable_query_log;
diff --git a/mysql-test/include/federated.inc b/mysql-test/include/federated.inc
new file mode 100644
index 00000000000..1c53b9ed2c5
--- /dev/null
+++ b/mysql-test/include/federated.inc
@@ -0,0 +1,21 @@
+--source ./include/have_federated_db.inc
+
+source ./include/master-slave.inc;
+
+# remote table creation
+
+connection slave;
+--replicate-ignore-db=federated
+stop slave;
+
+--disable_warnings
+# at this point, we are connected to master
+DROP DATABASE IF EXISTS federated;
+--enable_warnings
+CREATE DATABASE federated;
+
+connection master;
+--disable_warnings
+DROP DATABASE IF EXISTS federated;
+--enable_warnings
+CREATE DATABASE federated;
diff --git a/mysql-test/include/federated_cleanup.inc b/mysql-test/include/federated_cleanup.inc
new file mode 100644
index 00000000000..17a6e1e5100
--- /dev/null
+++ b/mysql-test/include/federated_cleanup.inc
@@ -0,0 +1,11 @@
+connection master;
+--disable_warnings
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
+--enable_warnings
+
+connection slave;
+--disable_warnings
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
+--enable_warnings
diff --git a/mysql-test/include/have_eucjpms.inc b/mysql-test/include/have_eucjpms.inc
new file mode 100644
index 00000000000..a5e1a5ac547
--- /dev/null
+++ b/mysql-test/include/have_eucjpms.inc
@@ -0,0 +1,4 @@
+-- require r/have_eucjpms.require
+disable_query_log;
+show collation like "eucjpms_japanese_ci";
+enable_query_log;
diff --git a/mysql-test/include/have_federated_db.inc b/mysql-test/include/have_federated_db.inc
new file mode 100644
index 00000000000..e4cf1366fda
--- /dev/null
+++ b/mysql-test/include/have_federated_db.inc
@@ -0,0 +1,4 @@
+-- require r/have_federated_db.require
+disable_query_log;
+show variables like "have_federated_engine";
+enable_query_log;
diff --git a/mysql-test/include/have_isam.inc b/mysql-test/include/have_isam.inc
deleted file mode 100644
index 830170c921f..00000000000
--- a/mysql-test/include/have_isam.inc
+++ /dev/null
@@ -1,4 +0,0 @@
--- require r/have_isam.require
-disable_query_log;
-show variables like "have_isam";
-enable_query_log;
diff --git a/mysql-test/include/have_lowercase0.inc b/mysql-test/include/have_lowercase0.inc
new file mode 100644
index 00000000000..f967c18928b
--- /dev/null
+++ b/mysql-test/include/have_lowercase0.inc
@@ -0,0 +1,4 @@
+--require r/lowercase0.require
+--disable_query_log;
+show variables like "lower_case_%";
+--enable_query_log;
diff --git a/mysql-test/include/have_ndb.inc b/mysql-test/include/have_ndb.inc
index 9b85197abe8..28fcf18cb16 100644
--- a/mysql-test/include/have_ndb.inc
+++ b/mysql-test/include/have_ndb.inc
@@ -3,6 +3,4 @@
 disable_query_log;
 show variables like "have_ndbcluster";
 enable_query_log;
-#connect (server1,127.0.0.1,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
-#connect (server2,127.0.0.1,root,,test,$MASTER_MYPORT1,$MASTER_MYSOCK1);
-#connection server1;
+
diff --git a/mysql-test/include/ndb_default_cluster.inc b/mysql-test/include/ndb_default_cluster.inc
new file mode 100644
index 00000000000..2f900b6a0b4
--- /dev/null
+++ b/mysql-test/include/ndb_default_cluster.inc
@@ -0,0 +1,4 @@
+-- require r/ndb_default_cluster.require
+disable_query_log;
+show status like "Ndb_connected_host";
+enable_query_log;
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index 0a5bec26fe7..0b6e27619e6 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -104,7 +104,7 @@ drop table t5 ;
 #   c5  integer, c6  bigint, c7  float, c8  double,
 #   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
 #   c13 date, c14 datetime, c15 timestamp(14), c16 time,
-#   c17 year, c18 bit, c19 bool, c20 char,
+#   c17 year, c18 tinyint, c19 bool, c20 char,
 #   c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
 #   c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
 #   c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
diff --git a/mysql-test/include/ps_create.inc b/mysql-test/include/ps_create.inc
index dfc9c494b46..b2a6fc4b920 100644
--- a/mysql-test/include/ps_create.inc
+++ b/mysql-test/include/ps_create.inc
@@ -33,8 +33,8 @@ eval create table t9
   c1  tinyint, c2  smallint, c3  mediumint, c4  int,
   c5  integer, c6  bigint, c7  float, c8  double,
   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
-  c13 date, c14 datetime, c15 timestamp(14), c16 time,
-  c17 year, c18 bit, c19 bool, c20 char,
+  c13 date, c14 datetime, c15 timestamp, c16 time,
+  c17 year, c18 tinyint, c19 bool, c20 char,
   c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
   c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
   c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
diff --git a/mysql-test/include/ps_modify.inc b/mysql-test/include/ps_modify.inc
index eb6820934f3..633c317f4b2 100644
--- a/mysql-test/include/ps_modify.inc
+++ b/mysql-test/include/ps_modify.inc
@@ -59,7 +59,6 @@ execute stmt1 using @arg00;
 select a,b from t1 where b=@arg00;
 
 ## truncate a table
---error 1295
 prepare stmt1 from 'truncate table t1' ;
 
 
@@ -175,11 +174,8 @@ where a=2
 limit 1';
 execute stmt1 ;
 select a,b from t1 where b = 'bla' ;
-# currently (May 2004, Version 4.1) it is impossible
--- error 1064
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 
 --disable_query_log
 select '------ insert tests ------' as test_sequence ;
diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 9a413bff2f3..27a86f88231 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -300,10 +300,8 @@ set @arg00=1;
 prepare stmt1 from ' select a,b from t1 order by a
 limit 1 ';
 execute stmt1 ;
-# currently (May 2004, Version 4.1) it is impossible
--- error 1064
-prepare stmt1 from ' select a,b from t1
-limit ? ';
+prepare stmt1 from ' select a,b from t1 order by a limit ? ';
+execute stmt1 using @arg00;
 
 ##### parameter used in many places
 set @arg00='b' ;
diff --git a/mysql-test/include/rpl_stmt_seq.inc b/mysql-test/include/rpl_stmt_seq.inc
index d56ee02e33c..3c91505d0d6 100644
--- a/mysql-test/include/rpl_stmt_seq.inc
+++ b/mysql-test/include/rpl_stmt_seq.inc
@@ -7,7 +7,7 @@
 ###############################################################
 # Debug options : To debug this test script
 ###############################################################
-let $showbinlog= 0;
+let $show_binlog= 0;
 let $manipulate= 1;
 
 ######## The typical test sequence
diff --git a/mysql-test/include/show_msg.inc b/mysql-test/include/show_msg.inc
new file mode 100755
index 00000000000..659dce14686
--- /dev/null
+++ b/mysql-test/include/show_msg.inc
@@ -0,0 +1,25 @@
+#### include/show_msg.inc
+#
+# This file writes the value set in @message into the
+# a protocol file as part of executing a test sequence
+#
+# Usage:
+#    Add the following to any *.test file:
+#      :
+#    let $message= <value>;
+#    --source include/show_msg.inc
+#      :
+#
+# Attention:
+#   - Please do not write any spaces between $message and the "=", because the
+#     assignment will not work.
+#   - Be careful with single quotes. They must be escaped like "''" or "\'".
+#
+# "include/show_msg80.inc" contains a detailed description and examples.
+
+--disable_query_log
+eval SET @utf8_message = CONVERT('$message' using utf8);
+select @utf8_message as ""
+union
+select repeat(CONVERT('-' using utf8),char_length(@utf8_message));
+--enable_query_log
diff --git a/mysql-test/include/show_msg80.inc b/mysql-test/include/show_msg80.inc
new file mode 100755
index 00000000000..42fb35edbcc
--- /dev/null
+++ b/mysql-test/include/show_msg80.inc
@@ -0,0 +1,118 @@
+#### include/show_msg80.inc
+#
+# This file writes the value set in @message into the a protocol file as part
+# of executing a test sequence with a dash line that is fixed on 80 characters.
+#
+# This can be used in the case of long messages, multi line messages that 
+# exceed 80 or if an 80 char line is desired for short messages.
+#
+# Usage:
+#    Add the following to any *.test file:
+#      :
+#    let $message= <value>;
+#    --source include/show_msg80.inc
+#      :
+#
+# Attention:
+#   - Please do not write any spaces between $message and the "=", because the
+#     assignment will not work.
+#   - Be careful with single quotes within the value. They must be escaped like
+#     "''" or "\'".
+#   - Do not keep the value between single quotes.
+#
+#
+# Content of "$message" and protocol output depending on the assignment:
+# ----------------------------------------------------------------------
+#
+#   I is assumed, that the value is not kept between double quotes.
+#
+#   <x>  first character after "$message=", 
+#        where the content is not (space or tab)
+#   <y*> first character after beginning of the line,
+#        where the content is not (space or tab)
+#   <z>  last char before ";"
+#    |   beginning or end of line
+#           
+#   script: let $message=   <x><whatever0>|
+#           | <y1><whatever1>| 
+#           |................| 
+#           |      <yn><whatevern><z>;
+#   content: "<x><whatever0><new line><y1><whatever1><new line>
+#             ....<new line><yn><whatevern><z>"
+#   protocol output: |<x><whatever0>|
+#                    |<y1><whatever1>|
+#                    |.....|
+#                    |<yn><whatevern><z>|
+#                    |--- 80 dashes ---|
+#
+# Attention:
+#   <x> and <y*> set to characters like "-$#" which are also used 
+#   to start comments, options and the names of mysqltest variables
+#   lead to syntax errors or mangled messages.
+#
+#
+# Examples of messages:
+# ---------------------
+#
+#   Variant1 (ease of use): 
+#      Several lines with indentation kept between double quotes
+#      script:   |let $message= 
+#                |" Testcase 3.1 :  Ensure that Pi is not an|
+#                |                  integer number.|
+#                |                  Third line";
+#      protocol: |" Testcase 3.1 :  Ensure that Pi is not an|
+#                |                  integer number.|
+#                |                  Third line"|
+#                |------ 80 dashes ----|
+#
+#      Please mention that
+#      - the '"' preserves the indentation.
+#      - it is easy to write the script lines to get a fine indentation,
+#        if the value starts at the beginning of a new line
+#      - the '"' is printed
+#      - there are the least or no problems with characters like "-#$"
+#
+#
+#   Variant 2 (grep the messages from the protocol is easy):
+#      Several lines with indentation + auxiliary character (".")
+#              at the (non tab or space) beginning of every message line
+#      script:   |let $message= . Testcase 3.1 :  Ensure that Pi is not an|
+#                |              .                 integer number.|
+#                |              .                 Third line;
+#      protocol: |. Testcase 3.1 :  Ensure that Pi is not an|
+#                |.                 integer number.|
+#                |.                 Third line|
+#                |------ 80 dashes ----|
+#      Please mention that
+#      - the auxiliary character preserves the indentation.
+#      - it is easy to write the script lines to get a fine indentation
+#      - the auxiliary character is printed
+#      - it is recommended to use "." as auxiliary character
+#      - auxiliary characters like "-'$#" cause problems
+#      
+#
+#
+#   Bad variant1: Several lines with lost indentation
+#      script:   |let $message=     Here is message line 1
+#                |    message line 2;
+#      protocol: |Here is message line 1|
+#                |message line 2|
+#                |------ 80 dashes ----|
+#      Please mention, that the leading spaces of the message lines disappeared.
+#
+#   Bad variant2: Several lines leading to a syntax error, because of "-"
+#      script:   |let $message= - This is a message
+#                |   - with a second and
+#                |   - third line;
+#      protocol: | - third line;;
+#                |ERROR 42000: You have an error ... near '- third line'
+#                + several following errors
+#
+#
+
+--disable_query_log
+eval SET @utf8_message = CONVERT('$message' using utf8);
+select @utf8_message as ""
+union
+select repeat(CONVERT('-' using utf8),80);
+--enable_query_log
diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc
index 5a7aa26c65a..c66590b2fd8 100644
--- a/mysql-test/include/system_db_struct.inc
+++ b/mysql-test/include/system_db_struct.inc
@@ -10,3 +10,5 @@ show create table user;
 show create table func;
 show create table tables_priv;
 show create table columns_priv;
+show create table procs_priv;
+show create table proc;
diff --git a/mysql-test/include/testdb_only.inc b/mysql-test/include/testdb_only.inc
new file mode 100644
index 00000000000..ddc3f123d45
--- /dev/null
+++ b/mysql-test/include/testdb_only.inc
@@ -0,0 +1,30 @@
+#################### include/testdb_only.inc ######################
+#                                                                 #
+# We must prevent to work on databases created by customers,      #
+# because we DROP/CREATE/MODIFY objects with sometimes common     #
+# names like STAFF, EMPLOYEE etc.                                 #
+#                                                                 #
+# Therefore we check the environment variable USE_RUNNING_SERVER. #
+# USE_RUNNING_SERVER is exported by "mysql-test-run" and could    #
+# contain the following values:                                   #
+# 0  -- mysql-test-run was started without the --extern option    #
+#       That means the test will be performed within the test     #
+#       area 'mysql-test/var/...' .                               #
+# 1  -- mysql-test-run was started with the --extern option       #
+#       That means the test will be performed by an already       #
+#       running server and data modifications will most probably  #
+#       outside of the common test area 'mysql-test/var/...' .    #
+#                                                                 #
+# If USE_RUNNING_SERVER is not 0 the test will be skipped.        #
+#                                                                 #
+###################################################################
+
+--disable_query_log
+eval set @USE_RUNNING_SERVER= '$USE_RUNNING_SERVER';
+--require r/testdb_only.require
+SELECT 'use extern server'
+         AS "Variable_name    ",
+         IF(@USE_RUNNING_SERVER= '1','YES',
+            IF(@USE_RUNNING_SERVER= '0','NO','UNEXPECTED'))
+         AS "Value" ;
+--enable_query_log
diff --git a/mysql-test/include/varchar.inc b/mysql-test/include/varchar.inc
new file mode 100644
index 00000000000..13b4315f2b8
--- /dev/null
+++ b/mysql-test/include/varchar.inc
@@ -0,0 +1,238 @@
+# Initialise
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+
+disable_query_log;
+select "--- Testing varchar ---";
+enable_query_log;
+
+#
+# Simple basic test that endspace is saved
+#
+
+create table t1 (v varchar(10), c char(10), t text);
+insert into t1 values('+ ', '+ ', '+ ');
+set @a=repeat(' ',20);
+insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
+select concat('*',v,'*',c,'*',t,'*') from t1;
+
+# Check how columns are copied
+show create table t1;
+create table t2 like t1;
+show create table t2;
+create table t3 select * from t1;
+show create table t3;
+alter table t1 modify c varchar(10);
+show create table t1;
+alter table t1 modify v char(10);
+show create table t1;
+alter table t1 modify t varchar(10);
+show create table t1;
+select concat('*',v,'*',c,'*',t,'*') from t1;
+drop table t1,t2,t3;
+
+#
+# Testing of keys
+#
+create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
+show create table t1;
+disable_query_log;
+let $1=10;
+while ($1)
+{
+  let $2=27;
+  eval set @space=repeat(' ',10-$1);
+  while ($2)
+  {
+    eval set @char=char(ascii('a')+$2-1);
+    insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space));
+    dec $2;
+  }
+  dec $1;
+}
+enable_query_log;
+select count(*) from t1;
+insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
+select count(*) from t1 where v='a';
+select count(*) from t1 where c='a';
+select count(*) from t1 where t='a';
+select count(*) from t1 where v='a  ';
+select count(*) from t1 where c='a  ';
+select count(*) from t1 where t='a  ';
+select count(*) from t1 where v between 'a' and 'a ';
+select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
+select count(*) from t1 where v like 'a%';
+select count(*) from t1 where c like 'a%';
+select count(*) from t1 where t like 'a%';
+select count(*) from t1 where v like 'a %';
+# Test results differ for BDB, see comments in bdb.test
+# and they are also different from MySAM test results.
+--replace_column 9 #
+explain select count(*) from t1 where v='a  ';
+--replace_column 9 #
+explain select count(*) from t1 where c='a  ';
+--replace_column 9 #
+explain select count(*) from t1 where t='a  ';
+--replace_column 9 #
+explain select count(*) from t1 where v like 'a%';
+--replace_column 9 #
+explain select count(*) from t1 where v between 'a' and 'a ';
+--replace_column 9 #
+explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
+
+--error 1062
+alter table t1 add unique(v);
+alter table t1 add key(v);
+select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
+--replace_column 6 # 9 #
+explain select * from t1 where v='a';
+
+# GROUP BY
+
+select v,count(*) from t1 group by v limit 10;
+select v,count(t) from t1 group by v limit 10;
+select v,count(c) from t1 group by v limit 10;
+select sql_big_result v,count(t) from t1 group by v limit 10;
+select sql_big_result v,count(c) from t1 group by v limit 10;
+select c,count(*) from t1 group by c limit 10;
+select c,count(t) from t1 group by c limit 10;
+select sql_big_result c,count(t) from t1 group by c limit 10;
+select t,count(*) from t1 group by t limit 10;
+select t,count(t) from t1 group by t limit 10;
+select sql_big_result t,count(t) from t1 group by t limit 10;
+
+#
+# Test varchar > 255 bytes
+#
+
+alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
+show create table t1;
+select count(*) from t1 where v='a';
+select count(*) from t1 where v='a  ';
+select count(*) from t1 where v between 'a' and 'a ';
+select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
+select count(*) from t1 where v like 'a%';
+select count(*) from t1 where v like 'a %';
+--replace_column 9 #
+explain select count(*) from t1 where v='a  ';
+--replace_column 9 #
+explain select count(*) from t1 where v like 'a%';
+--replace_column 9 #
+explain select count(*) from t1 where v between 'a' and 'a ';
+--replace_column 9 #
+explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
+--replace_column 9 #
+explain select * from t1 where v='a';
+
+# GROUP BY
+
+select v,count(*) from t1 group by v limit 10;
+select v,count(t) from t1 group by v limit 10;
+select sql_big_result v,count(t) from t1 group by v limit 10;
+
+#
+# Test varchar > 255 bytes, key < 255
+#
+
+alter table t1 drop key v, add key v (v(30));
+show create table t1;
+select count(*) from t1 where v='a';
+select count(*) from t1 where v='a  ';
+select count(*) from t1 where v between 'a' and 'a ';
+select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
+select count(*) from t1 where v like 'a%';
+select count(*) from t1 where v like 'a %';
+--replace_column 9 #
+explain select count(*) from t1 where v='a  ';
+--replace_column 9 #
+explain select count(*) from t1 where v like 'a%';
+--replace_column 9 #
+explain select count(*) from t1 where v between 'a' and 'a ';
+--replace_column 9 #
+explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
+--replace_column 9 #
+explain select * from t1 where v='a';
+
+# GROUP BY
+
+select v,count(*) from t1 group by v limit 10;
+select v,count(t) from t1 group by v limit 10;
+select sql_big_result v,count(t) from t1 group by v limit 10;
+
+#
+# Test varchar > 512 (special case for GROUP BY becasue of
+# CONVERT_IF_BIGGER_TO_BLOB define)
+#
+
+alter table t1 modify v varchar(600), drop key v, add key v (v);
+show create table t1;
+select v,count(*) from t1 group by v limit 10;
+select v,count(t) from t1 group by v limit 10;
+select sql_big_result v,count(t) from t1 group by v limit 10;
+
+drop table t1;
+
+#
+# Test unique keys
+#
+
+create table t1 (a char(10), unique (a));
+insert into t1 values ('a   ');
+--error 1062
+insert into t1 values ('a ');
+
+alter table t1 modify a varchar(10);
+--error 1062
+insert into t1 values ('a '),('a  '),('a   '),('a         ');
+--error 1062
+insert into t1 values ('a     ');
+--error 1062
+insert into t1 values ('a          ');
+--error 1062
+insert into t1 values ('a ');
+update t1 set a='a  ' where a like 'a%';
+select concat(a,'.') from t1;
+update t1 set a='abc    ' where a like 'a ';
+select concat(a,'.') from t1;
+update t1 set a='a      ' where a like 'a %';
+select concat(a,'.') from t1;
+update t1 set a='a  ' where a like 'a      ';
+select concat(a,'.') from t1;
+drop table t1;
+
+#
+# test show create table
+#
+
+create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
+show create table t1;
+drop table t1;
+create table t1 (v char(10) character set utf8);
+show create table t1;
+drop table t1;
+
+create table t1 (v varchar(10), c char(10)) row_format=fixed;
+show create table t1;
+insert into t1 values('a','a'),('a ','a ');
+select concat('*',v,'*',c,'*') from t1;
+drop table t1;
+
+#
+# Test long varchars
+#
+
+create table t1 (v varchar(65530), key(v(10)));
+insert into t1 values(repeat('a',65530));
+select length(v) from t1 where v=repeat('a',65530);
+drop table t1;
+
+#
+# Bug #9489: problem with hash indexes
+#
+
+create table t1(a int, b varchar(12), key ba(b, a));
+insert into t1 values (1, 'A'), (20, NULL);
+explain select * from t1 where a=20 and b is null;
+select * from t1 where a=20 and b is null;
+drop table t1;