~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%";

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

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 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;

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 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 1 #  6 # 7 # 8 # 9 # 10 #

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 1 #  6 # 7 # 8 # 9 # 10 #

show table status;

insert into t1 values (5);

insert into t2 values (5);

insert into t3 values (5,5);

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status;

delete from t1 where a=3;

delete from t2 where b=3;

delete from t3 where a=3;

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status;

truncate table t1;

truncate table t2;

truncate table t3;

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status;

insert into t1 values (5);

insert into t2 values (5);

insert into t3 values (5,5);

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status;

delete from t1 where a=5;

delete from t2 where b=5;

delete from t3 where a=5;

--replace_column 1 #  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 #12591 (SHOW TABLES FROM dbname produces wrong error message)

#

--error ER_BAD_DB_ERROR

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_TABLE_UNKNOWN

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#25081 show TABLES on table with latin chars in name fails

#

--disable_warnings

drop table if exists `été`;

--enable_warnings

create table `été` (field1 int);

show tables;

drop table `été`;

#

# Bug#26402 Server crashes with old-style named table

#

--error ER_TABLE_UNKNOWN,ER_FILE_NOT_FOUND

show columns from `#mysql50#????????`;

#

# Test error output for SHOW engine_name STATUS

#

--error ER_USE_DATA_DICTIONARY

show innodb status;

--echo End of 5.1 tests