is_columns.test

这个文件是windows mysql源码

SELECT table_schema,table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modification of COLUMN_NAME
SELECT table_name, column_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
SELECT table_name, column_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modification of COLUMN size
SELECT table_name, column_name, character_maximum_length,
       character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(20);
SELECT table_name, column_name, character_maximum_length,
       character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modification of COLUMN type
SELECT table_name, column_name, character_maximum_length,
       character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col VARCHAR(20);
SELECT table_name, column_name, character_maximum_length,
       character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modify COLUMN DEFAULT
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello';
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modify IS_NULLABLE
SELECT table_name, column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) NOT NULL;
SELECT table_name, column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modify COLLATION
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs';
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modify CHARACTER SET
SELECT table_name, column_name, character_maximum_length,
       character_octet_length, character_set_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8;
SELECT table_name, column_name, character_maximum_length,
       character_octet_length, character_set_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check modify COLUMN_COMMENT
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello';
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check ADD COLUMN
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
ADD COLUMN second_col CHAR(10);
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check switch ordinal position of column
SELECT table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN second_col CHAR(10) FIRST;
SELECT table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
#
# Check DROP COLUMN
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
DROP COLUMN first_col;
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check set COLUMN UNIQUE
SELECT table_name, column_name, column_key
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
ALTER TABLE db_datadict.t1_my_tablex
ADD UNIQUE INDEX IDX(second_col);
SELECT table_name, column_name, column_key
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check impact of DROP TABLE
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
DROP TABLE db_datadict.t1_my_tablex;
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check a VIEW
CREATE VIEW test.t1_my_tablex
AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2";
--vertical_results
SELECT * FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
--horizontal_results
DROP VIEW test.t1_my_tablex;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
#
# Check impact of DROP SCHEMA
--replace_result $engine_type <engine_type>
eval
CREATE TABLE db_datadict.t1_my_tablex
ENGINE = $engine_type AS
SELECT 1;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';


--echo ########################################################################
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
--echo #           DDL on INFORMATION_SCHEMA table are not supported
--echo ########################################################################
# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.8:  Ensure that no user may create an index on an
#           INFORMATION_SCHEMA table.
# 3.2.1.9:  Ensure that no user may alter the definition of an
#           INFORMATION_SCHEMA table.
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
#           other database.
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
#           in an INFORMATION_SCHEMA table.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
DROP TABLE IF EXISTS test.t1;
--enable_warnings
CREATE DATABASE db_datadict;
CREATE TABLE test.t1 (f1 BIGINT);

--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.columns (table_schema,table_name,column_name)
VALUES('test','t1', 'f2');
--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.columns (table_schema,table_name,column_name)
VALUES('test','t2', 'f1');

--error ER_DBACCESS_DENIED_ERROR
UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1';

--error ER_DBACCESS_DENIED_ERROR
DELETE FROM information_schema.columns WHERE table_name = 't1';
--error ER_DBACCESS_DENIED_ERROR
TRUNCATE information_schema.columns;

--error ER_DBACCESS_DENIED_ERROR
CREATE INDEX i3 ON information_schema.columns(table_name);

--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.columns ADD f1 INT;

--error ER_DBACCESS_DENIED_ERROR
DROP TABLE information_schema.columns;

--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.columns RENAME db_datadict.columns;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.columns RENAME information_schema.xcolumns;

# Cleanup
DROP TABLE test.t1;
DROP DATABASE db_datadict;