~drizzle-trunk/drizzle/development

#
# Test of some show commands
#

--disable_warnings
drop table if exists t1,t2;
drop table if exists t1aa,t2aa;
drop database if exists mysqltest;
drop database if exists mysqltest1;

--enable_warnings

create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);

--echo -- Here we enable metadata just to check that the collation of the
--echo -- resultset is non-binary for string type. This should be changed
--echo -- after Bug#29394 is implemented.

--enable_metadata
check table t1;
insert into t1 values (5,5,5);
check table t1;
show index from t1;
--disable_metadata
--error ER_DUP_ENTRY
insert into t1 values (5,5,5);

--echo -- Here we enable metadata just to check that the collation of the
--echo -- resultset is non-binary for string type. This should be changed
--echo -- after Bug#29394 is implemented.

--enable_metadata
alter table t1 engine="default";
--disable_metadata
alter table t1 engine="default";
drop table t1;

#show variables;

--echo -- Here we enable metadata just to check that the collation of the
--echo -- resultset is non-binary for string type. This should be changed
--echo -- after Bug#29394 is implemented.

--enable_metadata
show variables like "server_id%";
show variables like "SERVER_id%";
show variables like "this_doesn't_exists%";
show table status from test like "this_doesn't_exists%";
show databases;
show databases like "test%";
--disable_metadata

#
# Check of show index
#
create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4));
insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1);

--echo -- Here we enable metadata just to check that the collation of the
--echo -- resultset is non-binary for string type. This should be changed
--echo -- after Bug#29394 is implemented.

--enable_metadata
analyze table t1;
--disable_metadata
show index from t1;

show index from t1;
drop table t1;

#
# Test of SHOW CREATE
#

create temporary table t1 (a int not null);
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
alter table t1 rename t2;
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t2;
drop table t2;

create table t1 (
  name char(20) default 'O''Brien' comment 'O''Brien as default',
  c int not null comment 'int column',
  `c-b` int comment 'name with a minus',
  `space 2` int comment 'name with a space'
  ) comment = 'it\'s a table' ;
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
show full columns from t1;
drop table t1;

create table t1 (a int not null, unique aa (a));
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
drop table t1;
create table t1 (a int not null, primary key (a));
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
drop table t1;

flush tables;
show open tables;
create table t1(n int);
insert into t1 values (1);
show open tables;
drop table t1;

create temporary table t1 (a int not null, b VARCHAR(10), INDEX (b) ) COMMENT="test" ENGINE=MYISAM ROW_FORMAT=fixed;
show create table t1;
alter table t1 ROW_FORMAT=dynamic;
show create table t1;
ALTER TABLE t1 COMMENT="" ROW_FORMAT=default;
show create table t1;
drop table t1;

create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));
show columns from t1;
show full columns from t1;
drop table t1;

#
# Do a create table that tries to cover all types and options
#
create temporary table t1 (
type_int int,
type_bigint bigint,
type_decimal decimal(5,2),
type_numeric numeric(5,2),
empty_char char(0),
type_char char(2),
type_varchar varchar(10),
type_timestamp timestamp not null,
type_date date,
type_datetime datetime,
type_enum enum ('red', 'green', 'blue'),
type_set enum ('red', 'green', 'blue'),
type_blob blob,
index(type_int)
) COMMENT="test" ENGINE=MYISAM ROW_FORMAT=fixed;

# Not tested above: RAID_# UNION INSERT_METHOD DATA DIRECTORY INDEX DIRECTORY
show create table t1;
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
select * from t1;
drop table t1;

#
# Check metadata
#
create table t1 (a int not null);
create table t2 select max(a) from t1;
show columns from t2;
drop table t1,t2;

# Check auto conversions of types

create table t1 (c decimal, d double, f float, r real);
show columns from t1;
drop table t1;

create table t1 (c decimal(3,3), d double(3,3), f float(3,3));
show columns from t1;
drop table t1;

#
# Test for Bug #2593 "SHOW CREATE TABLE doesn't properly double quotes"
#

CREATE TABLE ```ab``cd``` (i INT);
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
SHOW CREATE TABLE ```ab``cd```;
DROP TABLE ```ab``cd```;

CREATE TABLE ```ab````cd``` (i INT);
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
SHOW CREATE TABLE ```ab````cd```;
DROP TABLE ```ab````cd```;

CREATE TABLE ```a` (i INT);
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
SHOW CREATE TABLE ```a`;
DROP TABLE ```a`;

CREATE TABLE `a.1` (i INT);
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
SHOW CREATE TABLE `a.1`;
DROP TABLE `a.1`;


#
# Test for bug #2719 "MEMORY tables status shows wrong or missing data."
#

select @@max_heap_table_size;

CREATE TEMPORARY TABLE t1 (
 a int default NULL,
 KEY a USING BTREE (a)
) ENGINE=MEMORY;

CREATE TEMPORARY TABLE t2 (
 b int default NULL,
 index(b)
) ENGINE=MEMORY;

CREATE TEMPORARY TABLE t3 (
 a int default NULL,
 b int default NULL,
 KEY a USING BTREE (a),
 index(b)
) ENGINE=MEMORY;

insert into t1 values (1),(2);
insert into t2 values (1),(2);
insert into t3 values (1,1),(2,2);
--replace_column 6 # 7 # 8 # 9 #
show table status;
insert into t1 values (3),(4);
insert into t2 values (3),(4);
insert into t3 values (3,3),(4,4);
--replace_column 6 # 7 # 8 # 9 #
show table status;
insert into t1 values (5);
insert into t2 values (5);
insert into t3 values (5,5);
--replace_column 6 # 7 # 8 # 9 #
show table status;
delete from t1 where a=3;
delete from t2 where b=3;
delete from t3 where a=3;
--replace_column 6 # 7 # 8 # 9 # 10 #
show table status;
truncate table t1;
truncate table t2;
truncate table t3;
--replace_column 6 # 7 # 8 # 9 #
show table status;
insert into t1 values (5);
insert into t2 values (5);
insert into t3 values (5,5);
--replace_column 6 # 7 # 8 # 9 #
show table status;
delete from t1 where a=5;
delete from t2 where b=5;
delete from t3 where a=5;
--replace_column 6 # 7 # 8 # 9 # 10 #
show table status;

drop table t1, t2, t3;

# Test that USING <keytype> is always shown in SHOW CREATE TABLE when it was
# specified during table creation, but not otherwise. (Bug #7235)
CREATE TEMPORARY TABLE t1 (i int, KEY (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# Test that when an index is created with the default key algorithm and
# altered to another storage engine, it gets the default key algorithm
# for that storage engine, but when it is specified, the specified type is
# preserved.
CREATE TEMPORARY TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ENGINE=MEMORY;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ENGINE=MEMORY;
SHOW CREATE TABLE t1;
DROP TABLE t1;

# Test for BUG#9439 "Reporting wrong datatype for sub_part on show index"
CREATE TABLE t1(
  field1 text NOT NULL,
  PRIMARY KEY(field1(180))
);
--enable_metadata
show index from t1;
--disable_metadata
drop table t1;

# Test for BUG#11635: mysqldump exports TYPE instead of USING for HASH
create table t1 (
  c1 int NOT NULL,
  c2 int NOT NULL,
  PRIMARY KEY USING HASH (c1),
  INDEX USING BTREE(c2)
);
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
SHOW CREATE TABLE t1;
DROP TABLE t1;

#
# BUG 12183 -  SHOW OPEN TABLES behavior doesn't match grammar
# First we close all open tables with FLUSH tables and then we open some.
#

--echo
--echo # Bug#12183: SHOW OPEN TABLES behavior doesn't match grammar.
--echo

# NOTE: SHOW OPEN TABLES does not sort result list by database or table names.
# Tables are listed in the order they were opened. We can not use the system
# database (mysql) for the test here, because we have no control over the order
# of opening tables in it. Consequently, we can not use 'SHOW OPEN TABLES'.

--disable_warnings
DROP DATABASE IF EXISTS mysqltest1;
--enable_warnings

CREATE DATABASE mysqltest1;
use mysqltest1;

--echo

CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);

--echo

--disable_ps_protocol
FLUSH TABLES;

--echo

SELECT 1 FROM t1;
SELECT 1 FROM t2;

--echo

SHOW OPEN TABLES FROM mysqltest1;

--echo

SHOW OPEN TABLES FROM mysqltest1 LIKE 'z%';

--echo

SHOW OPEN TABLES FROM mysqltest1 LIKE 't1%';

--echo

SHOW OPEN TABLES FROM mysqltest1 LIKE '%1%';

--echo

FLUSH TABLES;
--enable_ps_protocol

--echo

DROP DATABASE mysqltest1;
use test;

--echo

#
# BUG #12591 (SHOW TABLES FROM dbname produces wrong error message)
#
--error 1049
SHOW TABLES FROM non_existing_database;

--echo End of 4.1 tests

#
# Check that SHOW TABLES and SHOW COLUMNS give a error if there is no
# referenced database and table respectively.
#
--error ER_BAD_DB_ERROR
SHOW TABLES FROM no_such_database;
--error ER_NO_SUCH_TABLE
SHOW COLUMNS FROM no_such_table;

#
# Bug #30088: Can't disable myisam-recover by a value of ""
#
show variables like 'myisam_recover_options';

--echo End of 5.0 tests

#
# Bug #19874: SHOW COLUMNS and SHOW KEYS handle identifiers containing
# \ incorrectly
#
create database `mysqlttest\1`;
create table `mysqlttest\1`.`a\b` (a int);
show tables from `mysqlttest\1`;
show fields from `mysqlttest\1`.`a\b`;
show columns from `a\b` from `mysqlttest\1`;
show keys from `mysqlttest\1`.`a\b`;
drop table `mysqlttest\1`.`a\b`;
drop database `mysqlttest\1`;

#
# Bug#24392: SHOW ENGINE MUTEX STATUS is a synonym for SHOW INNODB STATUS
#

--error ER_UNKNOWN_STORAGE_ENGINE
show engine foobar status;
--error 1064
show engine foobar logs;
--error 1064
show engine foobar mutex;

--error ER_UNKNOWN_STORAGE_ENGINE
show engine mutex status;

show engine csv status;

#
# Bug#25081 SHOW FULL TABLES on table with latin chars in name fails
#
--disable_warnings
drop table if exists `été`;
--enable_warnings
create table `été` (field1 int);
show full tables;
drop table `été`;

#
# Bug#26402 Server crashes with old-style named table
#
--error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND
show columns from `#mysql50#????????`;

--echo End of 5.1 tests