~drizzle-trunk/drizzle/development

# check that CSV engine was compiled in, as the result of the test depends

# on the presence of the log tables (which are CSV-based).

--source include/have_csv.inc

# Test for information_schema.schemata &

# show databases

--disable_warnings

DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;

--enable_warnings

show variables where variable_name like "skip_show_database";

select * from information_schema.SCHEMATA where schema_name > 'm';

select schema_name from information_schema.schemata;

show databases like 't%';

show databases;

show databases where `database` = 't%';

# Test for information_schema.tables &

# show tables

create database mysqltest;

create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));

create table test.t2(a int);

create table t3(a int, KEY a_data (a));

create table mysqltest.t4(a int);

create table t5 (id int auto_increment primary key);

insert into t5 values (10);

select table_name from information_schema.TABLES

where table_schema = "mysqltest" and table_name like "t%";

select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";

show keys from t3 where Key_name = "a_data";

show tables like 't%';

--replace_column 8 # 12 # 13 #

show table status;

show full columns from t3 like "a%";

show full columns from mysql.db like "Insert%";

select * from information_schema.COLUMNS where table_name="t1"

and column_name= "a";

show columns from mysqltest.t1 where field like "%a%";

connect (user3,localhost,mysqltest_2,,);

connection user3;

select table_name, column_name, privileges from information_schema.columns 

where table_schema = 'mysqltest' and table_name = 't1';

show columns from mysqltest.t1;

connect (user4,localhost,mysqltest_3,,mysqltest);

connection user4;

connection default;

drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;

drop database mysqltest;

# Test for information_schema.CHARACTER_SETS &

# SHOW CHARACTER SET

select * from information_schema.CHARACTER_SETS

where CHARACTER_SET_NAME like 'latin1%';

SHOW CHARACTER SET LIKE 'latin1%';

SHOW CHARACTER SET WHERE charset like 'latin1%';

# Test for information_schema.COLLATIONS &

# SHOW COLLATION

--replace_column 5 #

select * from information_schema.COLLATIONS

where COLLATION_NAME like 'latin1%';

--replace_column 5 #

SHOW COLLATION LIKE 'latin1%';

--replace_column 5 #

SHOW COLLATION WHERE collation like 'latin1%';

select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY

where COLLATION_NAME like 'latin1%';

#

# Bug#7213: information_schema: redundant non-standard TABLE_NAMES table

#

--error 1109

select * from information_schema.table_names;

#

# Bug#2719 information_schema: errors in "columns"

#

select column_type from information_schema.columns

where table_schema="information_schema" and table_name="COLUMNS" and

(column_name="character_set_name" or column_name="collation_name");

#

# Bug#2718 information_schema: errors in "tables"

#

select TABLE_ROWS from information_schema.tables where 

table_schema="information_schema" and table_name="COLUMNS";

select table_type from information_schema.tables

where table_schema="mysql" and table_name="user";

# test for 'show open tables ... where'

show open tables where `table` like "user";

# test for 'show status ... where'

show status where variable_name like "%database%";

# test for 'show variables ... where'

show variables where variable_name like "skip_show_databas";

#

# Bug #7981:SHOW GLOBAL STATUS crashes server

#

# We don't actually care about the value, just that it doesn't crash.

--replace_column 2 #

show global status like "Threads_running";

#

# Bug #7215  information_schema: columns are longtext instead of varchar

# Bug #7217  information_schema: columns are varbinary() instead of timestamp

#

select table_schema,table_name, column_name from

information_schema.columns 

where data_type = 'longtext';

select table_name, column_name, data_type from information_schema.columns

where data_type = 'datetime';

#

# Bug #8164  subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU

#

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A

WHERE NOT EXISTS 

(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B

  WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA

  AND A.TABLE_NAME = B.TABLE_NAME);

#

# Bug #9344  INFORMATION_SCHEMA, wrong content, numeric columns

#

create table t1

( x_bigint BIGINT,

  x_integer INTEGER,

  x_smallint SMALLINT,

  x_decimal DECIMAL(5,3),

  x_numeric NUMERIC(5,3),

  x_real REAL,

  x_float FLOAT,

  x_double_precision DOUBLE PRECISION );

SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME= 't1';

drop table t1;

#

# Bug #9404  information_schema: Weird error messages

# with SELECT SUM() ... GROUP BY queries

#

SELECT table_schema, count(*) FROM information_schema.TABLES

WHERE table_name NOT LIKE 'ndb_%' AND table_name NOT LIKE 'falcon%' GROUP BY TABLE_SCHEMA;

#

# Bug #9434 SHOW CREATE DATABASE information_schema;

#

show create database information_schema;

#

# Bug #11057 information_schema: columns table has some questionable contents

# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns

#

create table t1(f1 LONGBLOB, f2 LONGTEXT);

select column_name,data_type,CHARACTER_OCTET_LENGTH,

       CHARACTER_MAXIMUM_LENGTH

from information_schema.columns

where table_name='t1';

drop table t1;

create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,

                f5 BIGINT, f6 BIT, f7 bit(64));

select column_name, NUMERIC_PRECISION, NUMERIC_SCALE

from information_schema.columns

where table_name='t1';

drop table t1;

#

# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set

#

create table t1 (a int not null, b int);

use information_schema;

select column_name, column_default from columns

  where table_schema='test' and table_name='t1';

use test;

show columns from t1;

drop table t1;

#

# Bug #12636: SHOW TABLE STATUS with where condition containing a subquery

#             over information schema

#

CREATE TABLE t1 (a int);

CREATE TABLE t2 (b int);

--replace_column 8 # 12 # 13 #

SHOW TABLE STATUS FROM test

  WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

                    WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');

DROP TABLE t1,t2;

#

# Bug #9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA'

#

--error ER_PARSE_ERROR

alter database information_schema;

--error ER_DBACCESS_DENIED_ERROR

drop database information_schema;

--error 1044

drop table information_schema.tables;

--error 1044

alter table information_schema.tables;

#

# Bug #9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB

#

use information_schema;

--error 1044

create temporary table schemata(f1 char(10));

#

#

# Bug#14089 FROM list subquery always fails when information_schema is current database

#

use test;

create table t1(id int);

insert into t1(id) values (1);

select 1 from (select 1 from test.t1) a;

use information_schema;

select 1 from (select 1 from test.t1) a;

use test;

drop table t1;

# Bug #14387 SHOW COLUMNS doesn't work on temporary tables

# Bug #15224 SHOW INDEX from temporary table doesn't work

# Bug #12770 DESC cannot display the info. about temporary table

#

create temporary table t1(f1 int, index(f1));

show columns from t1;

describe t1;

show indexes from t1;

drop table t1;

#

# Bug#14271 I_S: columns has no size for (var)binary columns

#

create table t1(f1 binary(32), f2 varbinary(64));

select character_maximum_length, character_octet_length

from information_schema.columns where table_name='t1';

drop table t1;

#

# Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema

#

select column_type, group_concat(table_schema, '.', table_name), count(*) as num

from information_schema.columns where

table_schema='information_schema' and

(column_type = 'varchar(7)' or column_type = 'varchar(20)'

 or column_type = 'varchar(27)')

group by column_type order by num;

#

# Bug#19236 bad COLUMNS.CHARACTER_MAXIMUM_LENGHT and CHARACTER_OCTET_LENGTH

#

create table t1(f1 char(1) not null, f2 char(9) not null)

default character set utf8;

select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from

information_schema.columns where table_schema='test' and table_name = 't1';

drop table t1;

#

# Bug#19599 duplication of information_schema column value in a CONCAT expr with user var

#

set @a:= '.';

create table t1(f1 char(5));

create table t2(f1 char(5));

select concat(@a, table_name), @a, table_name

from information_schema.tables where table_schema = 'test';

drop table t1,t2;

#

# Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA 

#

SELECT t.table_name, c1.column_name

  FROM information_schema.tables t

       INNER JOIN

       information_schema.columns c1

       ON t.table_schema = c1.table_schema AND

          t.table_name = c1.table_name

  WHERE t.table_schema = 'information_schema' AND

        c1.ordinal_position =

        ( SELECT COALESCE(MIN(c2.ordinal_position),1)

            FROM information_schema.columns c2

            WHERE c2.table_schema = t.table_schema AND

                  c2.table_name = t.table_name AND

                  c2.column_name LIKE '%SCHEMA%'

        )

  AND t.table_name NOT LIKE 'falcon%';

SELECT t.table_name, c1.column_name

  FROM information_schema.tables t

       INNER JOIN

       information_schema.columns c1

       ON t.table_schema = c1.table_schema AND

          t.table_name = c1.table_name

  WHERE t.table_schema = 'information_schema' AND

        c1.ordinal_position =

        ( SELECT COALESCE(MIN(c2.ordinal_position),1)

            FROM information_schema.columns c2

            WHERE c2.table_schema = 'information_schema' AND

                  c2.table_name = t.table_name AND

                  c2.column_name LIKE '%SCHEMA%'

        )

  AND t.table_name NOT LIKE 'falcon%';

#

# Bug#21231: query with a simple non-correlated subquery over

#            INFORMARTION_SCHEMA.TABLES 

#

SELECT MAX(table_name) FROM information_schema.tables;

SELECT table_name from information_schema.tables

  WHERE table_name=(SELECT MAX(table_name)

                      FROM information_schema.tables);

#

# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail

#

create table t1 (f1 int(11));

create table t2 (f1 int(11), f2 int(11));

select table_name from information_schema.tables

where table_schema = 'test' and table_name not in

(select table_name from information_schema.columns

 where table_schema = 'test' and column_name = 'f3');

drop table t1,t2;

#

# Bug#24630  Subselect query crashes mysqld

#

select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=

(select cast(table_name as char)  from information_schema.tables

 order by table_name limit 1) limit 1;

select t.table_name, group_concat(t.table_schema, '.', t.table_name),

       count(*) as num1

from information_schema.tables t

inner join information_schema.columns c1

on t.table_schema = c1.table_schema AND t.table_name = c1.table_name

where t.table_schema = 'information_schema' AND

      t.table_name not like 'falcon%' AND

        c1.ordinal_position =

        (select isnull(c2.column_type) -

         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +

         count(*) as num

         from information_schema.columns c2 where

         c2.table_schema='information_schema' and

         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')

          group by c2.column_type order by num limit 1)

group by t.table_name order by num1, t.table_name;

#

# Bug#25859    ALTER DATABASE works w/o parameters

#

--error ER_PARSE_ERROR

alter database;

--error ER_PARSE_ERROR

alter database test;

#

# Bug#27747 database metadata doesn't return sufficient column default info

#

create table t1 (

  f1 varchar(50),

  f2 varchar(50) not null,

  f3 varchar(50) default '',

  f4 varchar(50) default NULL,

  f5 bigint not null,

  f6 bigint not null default 10,

  f7 datetime not null,

  f8 datetime default '2006-01-01'

);

select column_default from information_schema.columns where table_name= 't1';

show columns from t1;

drop table t1;

#

# Bug#30079 A check for "hidden" I_S tables is flawed

#

--error 1109

show fields from information_schema.table_names;

--error 1109

show keys from information_schema.table_names;

SET max_heap_table_size = DEFAULT;

USE test;

--echo End of 5.0 tests.

#

# Bug#30795 Query on INFORMATION_SCHEMA.SCHEMATA, wrong result

#

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

WHERE SCHEMA_NAME ='information_schema';

#

# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA

#

SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';

#

# Bug#31633 Information schema = NULL queries crash the server

#

select * from information_schema.columns where table_schema = NULL;

select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;

select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;

select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;

select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;

select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;

select * from information_schema.schemata where schema_name = NULL;

select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;

select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;

select * from information_schema.tables where table_schema = NULL;

select * from information_schema.tables where table_catalog = NULL;

select * from information_schema.tables where table_name = NULL;

select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;

select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;