~drizzle-trunk/drizzle/development

# Test for data_dictionary.schemas &
# show databases

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

--replace_column 1 #
--sorted_result
select count(*) from data_dictionary.SCHEMAS where schema_name > 'm';
--replace_column 1 #
--sorted_result
select count(*) from data_dictionary.schemas;
show databases like 't%';
show databases;

# Test for data_dictionary.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);

--sorted_result
select table_name from data_dictionary.TABLES
where table_schema = "mysqltest" and table_name like "t%";

show tables like 't%';
--replace_column 1 #  6 # 7 # 8 # 9 # 10 #
show table status;
show columns from t3 like "a%";
--sorted_result
select * from data_dictionary.COLUMNS where table_name="t1"
and column_name= "a";

connect (user3,localhost,mysqltest_2,,);
connection user3;
--sorted_result
select table_name, column_name from data_dictionary.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 data_dictionary.CHARACTER_SETS &
--sorted_result
select * from data_dictionary.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';

# Test for data_dictionary.COLLATIONS &

--replace_column 5 #
--sorted_result
select * from data_dictionary.COLLATIONS
where COLLATION_NAME like 'latin1%';

#
# Bug#2719 data_dictionary: errors in "columns"
#
--sorted_result
select DATA_TYPE from data_dictionary.columns
where table_schema="data_dictionary" and table_name="COLUMNS" and
(column_name="character_set_name" or column_name="collation_name");

#
# Bug#2718 data_dictionary: errors in "tables"
#
--sorted_result
select TABLE_TYPE from data_dictionary.tables where 
table_schema="data_dictionary" and table_name="COLUMNS";
--sorted_result
select table_type from data_dictionary.tables
where table_schema="mysql" and table_name="user";

#
# Bug #7215  data_dictionary: columns are longtext instead of varchar
# Bug #7217  data_dictionary: columns are varbinary() instead of timestamp
#
--sorted_result
select table_schema,table_name, column_name from
data_dictionary.columns 
where DATA_TYPE = 'longtext';
--sorted_result
select table_name, column_name, DATA_TYPE from data_dictionary.columns
where DATA_TYPE = 'datetime';

#
# Bug #8164  subquery with DATA_DICTIONARY.COLUMNS, 100 % CPU
#
--sorted_result
SELECT COUNT(*) FROM DATA_DICTIONARY.TABLES A
WHERE NOT EXISTS 
(SELECT * FROM DATA_DICTIONARY.COLUMNS B
  WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
  AND A.TABLE_NAME = B.TABLE_NAME);

#
# Bug #9344  DATA_DICTIONARY, wrong content, numeric columns
#

create table t1
( x_bigint BIGINT,
  x_integer INTEGER,
  x_int int,
  x_decimal DECIMAL(5,3),
  x_numeric NUMERIC(5,3),
  x_real REAL,
  x_float FLOAT,
  x_double_precision DOUBLE PRECISION );
--sorted_result
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM DATA_DICTIONARY.COLUMNS
WHERE TABLE_NAME= 't1';
drop table t1;

#
# Bug #9404  data_dictionary: Weird error messages
# with SELECT SUM() ... GROUP BY queries
#
--sorted_result
SELECT table_schema, count(*) FROM data_dictionary.TABLES
WHERE table_name NOT LIKE 'ndb_%' AND 
table_name NOT LIKE 'falcon%' AND
ENGINE IS NULL
GROUP BY TABLE_SCHEMA;

#
# Bug #9434 SHOW CREATE DATABASE data_dictionary;
#
#show create database data_dictionary;

#
# Bug #11057 data_dictionary: columns table has some questionable contents
# Bug #12301 data_dictionary: NUMERIC_SCALE must be 0 for integer columns
#
create table t1(f1 LONGBLOB, f2 LONGTEXT);
--sorted_result
select column_name, DATA_TYPE, CHARACTER_OCTET_LENGTH,
       CHARACTER_MAXIMUM_LENGTH
from data_dictionary.columns
where table_name='t1';
drop table t1;
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
                f5 BIGINT, f6 int, f7 int);
--sorted_result
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
from data_dictionary.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 data_dictionary;
--sorted_result
select column_name, column_default,column_default_is_null from columns
  where table_schema='test' and table_name='t1';
use test;
show columns from t1;
drop table t1;

#
# Bug #9846 Inappropriate error displayed while dropping table from
# 'DATA_DICTIONARY'
#
--error ER_PARSE_ERROR
alter database data_dictionary;
use data_dictionary;
#--error ER_DBACCESS_DENIED_ERROR
#drop table tables;
#--error ER_DBACCESS_DENIED_ERROR
#alter table tables;
#
# Bug #9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB
#
use data_dictionary;
--error ER_DBACCESS_DENIED_ERROR
create temporary table schemas(f1 char(10));
#
#
# Bug#14089 FROM list subquery always fails when data_dictionary is current database
#
use test;
create table t1(id int);
insert into t1(id) values (1);
--sorted_result
select 1 from (select 1 from test.t1) a;
use data_dictionary;
--sorted_result
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 varbinary(32), f2 varbinary(64));
--sorted_result
select character_maximum_length, character_octet_length
from data_dictionary.columns where table_name='t1';
drop table t1;

#
# Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on data_dictionary
#
--replace_column 2 # 3 #
select DATA_TYPE, group_concat(table_schema, '.', table_name) as a, count(*) as num
from data_dictionary.columns where
table_schema='data_dictionary' and
(DATA_TYPE = 'varchar' or DATA_TYPE = 'varchar'
 or DATA_TYPE = 'varchar')
group by DATA_TYPE order by DATA_TYPE, 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);
--sorted_result
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
data_dictionary.columns where table_schema='test' and table_name = 't1';
drop table t1;

#
# Bug#19599 duplication of data_dictionary column value in a CONCAT expr with user var
#
set @a:= '.';
create table t1(f1 char(5));
create table t2(f1 char(5));
--sorted_result
select concat(@a, table_name), @a, table_name
from data_dictionary.tables where table_schema = 'test';
drop table t1,t2;

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

--sorted_result
SELECT t.table_name, c1.column_name
  FROM data_dictionary.tables t
       INNER JOIN
       data_dictionary.columns c1
       ON t.table_schema = c1.table_schema AND
          t.table_name = c1.table_name
  WHERE t.table_schema = 'data_dictionary' AND
        c1.ordinal_position =
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
            FROM data_dictionary.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%'
  AND t.ENGINE IS NULL;
--sorted_result
SELECT t.table_name, c1.column_name
  FROM data_dictionary.tables t
       INNER JOIN
       data_dictionary.columns c1
       ON t.table_schema = c1.table_schema AND
          t.table_name = c1.table_name
  WHERE t.table_schema = 'data_dictionary' AND
        c1.ordinal_position =
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
            FROM data_dictionary.columns c2
            WHERE c2.table_schema = 'data_dictionary' AND
                  c2.table_name = t.table_name AND
                  c2.column_name LIKE '%SCHEMA%'
        )
  AND t.table_name NOT LIKE 'falcon%'
  AND t.ENGINE IS NULL;

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

--sorted_result
SELECT MAX(table_name) FROM data_dictionary.tables;
--sorted_result
SELECT table_name from data_dictionary.tables
  WHERE table_name=(SELECT MAX(table_name)
                      FROM data_dictionary.tables);
#
# Bug#23299 Some queries against DATA_DICTIONARY with subqueries fail
#
create table t1 (f1 int);
create table t2 (f1 int, f2 int);

--sorted_result
select table_name from data_dictionary.tables
where table_schema = 'test' and table_name not in
(select table_name from data_dictionary.columns
 where table_schema = 'test' and column_name = 'f3');
drop table t1,t2;


#
# Bug#24630  Subselect query crashes mysqld
#
--sorted_result
select 1 as f1 from data_dictionary.tables  where "CHARACTER_SETS"=
(select cast(table_name as char)  from data_dictionary.tables WHERE TABLE_SCHEMA=schema()
 AND TABLE_NAME = "CHARACTER_SETS"
 order by table_name limit 1) AND TABLE_SCHEMA=schema() limit 1;

--replace_column 2 #
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
       count(*) as num1
from data_dictionary.tables t
inner join data_dictionary.columns c1
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
where t.table_schema = 'data_dictionary' AND
      t.table_name not like 'falcon%' AND
      t.ENGINE IS NULL AND
        c1.ordinal_position =
        (select isnull(c2.DATA_TYPE) -
         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
         count(*) as num
         from data_dictionary.columns c2 where
         c2.table_schema='data_dictionary' and
         (c2.DATA_TYPE = 'varchar' or c2.DATA_TYPE = 'varchar')
          group by c2.DATA_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'
);
--sorted_result
select column_name,column_default,column_default_is_null from data_dictionary.columns where table_name= 't1';
show columns from t1;
drop table t1;

#
# Bug#30079 A check for "hidden" I_S tables is flawed
#
#--error ER_UNKNOWN_TABLE
#show fields from data_dictionary.table_names;
#--error ER_UNKNOWN_TABLE
#show keys from data_dictionary.table_names;

SET max_heap_table_size = DEFAULT;
USE test;

--echo End of 5.0 tests.

#
# Bug#30795 Query on DATA_DICTIONARY.SCHEMAS, wrong result
#
--sorted_result
SELECT SCHEMA_NAME FROM DATA_DICTIONARY.SCHEMAS
WHERE SCHEMA_NAME ='data_dictionary';
                                                                                                 
#
# Bug#31381 Error in retrieving Data from DATA_DICTIONARY
#
--sorted_result
SELECT TABLE_COLLATION FROM DATA_DICTIONARY.TABLES
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';

#
# Bug#31633 Information schema = NULL queries crash the server
#
--sorted_result
select * from data_dictionary.columns where table_schema = NULL;
--sorted_result
select * from `data_dictionary`.`COLUMNS` where `TABLE_NAME` = NULL;
--sorted_result
select * from `data_dictionary`.`INDEXES` where `TABLE_SCHEMA` = NULL;
--sorted_result
select * from `data_dictionary`.`INDEXES` where `TABLE_NAME` = NULL;
#select * from `data_dictionary`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
#select * from `data_dictionary`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
--sorted_result
select * from data_dictionary.schemas where schema_name = NULL;
--sorted_result
select * from data_dictionary.tables where table_schema = NULL;
--sorted_result
select * from data_dictionary.tables where table_name = NULL;
#select * from `data_dictionary`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
#select * from `data_dictionary`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;

--echo #
--echo # Test that the query is visible to self and others.
--echo #

--sorted_result
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();

#
# test that SHOW PROCESSLIST works correctly
#
--replace_column 1 # 2 # 3 # 6 # 7 #
#show processlist;

#
# do a query on the PROCESSLIST table in I_S to ensure it works correctly
#
#--replace_column 1 # 2 # 3
#SELECT info, command, db FROM data_dictionary.processlist WHERE id = CONNECTION_ID();

#
# do a query on the CHARACTER_SET table in I_S 
#
SELECT * FROM data_dictionary.character_sets ORDER BY character_set_name;

#
# perform a query on the COLLATIONS table
#
SELECT * FROM data_dictionary.collations ORDER BY collation_name;

#
# perform a query on COLUMNS
#
SELECT table_name, column_name
FROM data_dictionary.columns
WHERE table_name IN
(SELECT table_name FROM data_dictionary.tables 
 WHERE ENGINE IS NULL)
ORDER BY table_name;

#
# perform a query on REFERENTIAL_CONSTRAINTS
#
#SELECT * FROM data_dictionary.referential_constraints;

#
# query the SCHEMAS table
#
--replace_column 1 #
SELECT count(schema_name) FROM data_dictionary.schemas ORDER BY schema_name;

#
# Query the STATUS and VARIABLES related
# I_S related tables. 
#
--replace_column 1 #
SELECT count(*) FROM data_dictionary.session_status ORDER BY variable_name;

--replace_column 1 #
SELECT count(*) FROM data_dictionary.session_variables ORDER BY variable_name;

--replace_column 1 #
SELECT count(*) FROM data_dictionary.global_status ORDER BY variable_name;

--replace_column 1 #
SELECT count(*) FROM data_dictionary.global_variables ORDER BY variable_name;

#
# query TABLE_CONSTRAINTS table
#
#--replace_column 1 #
#SELECT count(*) FROM data_dictionary.table_constraints; 

#
# query TABLES
#
SELECT table_schema, table_name FROM data_dictionary.tables WHERE ENGINE IS NULL ORDER BY table_name;

# 
# do a query on the PLUGINS table in I_S to ensure it works correctly
# how do we test for this if the contents of this table can change
# depend on what plugins are configured for use?
#
--replace_column 1 #
--sorted_result
SELECT count(*) FROM data_dictionary.plugins;