From d6e42aace333c5bbb7d1b76835e6947d1dac916c Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 7 Mar 2008 17:33:07 +0100 Subject: WL#4203 Reorganize and fix the data dictionary tests of testsuite funcs_1 1. Fix the following bugs Bug#30440 "datadict" tests (all engines) fail: Character sets depend on configuration Solution: Test variants charset_collation_* adjusted to different builds Bug#32603 "datadict" tests (all engines) fail in "community" tree: "PROFILING" table Solution: Excluding "PROFILING" table from queries Bug#33654 "slow log" is missing a line Solution: Unify the content of the fields TABLES.TABLE_ROWS and STATISTICS.CARDINALITY within result sets Bug#34532 Some funcs_1 tests do not clean up at end of testing Solution: DROP objects/reset global server variables modified during testing + let tests missing implementation end before loading of tables Bug#31421 funcs_1: ndb__datadict fails, discrepancy between scripts and expected results Solution: Cut __datadict tests into smaller tests + generate new results. Bug#33599 INFORMATION_SCHEMA.STATISTICS got a new column INDEX_COMMENT: tests fail (2) Generation of new results during post merge fix Bug#33600 CHARACTER_OCTET_LENGTH is now CHARACTER_MAXIMUM_LENGTH * 4 Generation of new results during post merge fix Bug#33631 Platform-specific replace of CHARACTER_MAXIMUM_LENGTH broken by 4-byte encoding Generation of new results during post merge fix + removal of platform-specific replace routine (no more needed) 2. Restructure the tests - Test not more than one INFORMATION_SCHEMA view per testscript - Separate tests of I_S view layout+functionality from content related to the all time existing databases "information_schema", "mysql" and "test" - Avoid storage engine related variants of tests which are not sensible to storage engines at all. 3. Reimplement or add some subtests + cleanup There is a some probability that even the reviewed changeset - does not fix all bugs from above or - contains new bugs which show up on some platforms <> Linux or on one of the various build types 4. The changeset contains fixes according to - one code review - minor bugs within testing code found after code review (accepted by reviewer) - problems found during tests with 5.0.56 in build environment mysql-test/suite/funcs_1/t/innodb_storedproc_02.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_03.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_06.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_07.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_08.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_10.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_02.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_03.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_06.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_07.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_08.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_10.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_02.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_03.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_06.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_07.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_08.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_10.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_02.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_03.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_06.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_07.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_08.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_10.inc: Change mode to -rw-rw-r-- BitKeeper/deleted/.del-innodb__load.test: Delete: mysql-test/suite/funcs_1/t/innodb__load.test BitKeeper/deleted/.del-innodb__load.result: Delete: mysql-test/suite/funcs_1/r/innodb__load.result BitKeeper/deleted/.del-myisam__load.test: Delete: mysql-test/suite/funcs_1/t/myisam__load.test BitKeeper/deleted/.del-memory__load.test: Delete: mysql-test/suite/funcs_1/t/memory__load.test BitKeeper/deleted/.del-memory__load.result: Delete: mysql-test/suite/funcs_1/r/memory__load.result BitKeeper/deleted/.del-myisam__load.result: Delete: mysql-test/suite/funcs_1/r/myisam__load.result BitKeeper/deleted/.del-create_database.inc: Delete: mysql-test/suite/funcs_1/include/create_database.inc BitKeeper/deleted/.del-create_user_lowest_priv.inc: Delete: mysql-test/suite/funcs_1/include/create_user_lowest_priv.inc BitKeeper/deleted/.del-create_user_no_super.inc: Delete: mysql-test/suite/funcs_1/include/create_user_no_super.inc BitKeeper/deleted/.del-datadict_help_tables_build.result: Delete: mysql-test/suite/funcs_1/r/datadict_help_tables_build.result BitKeeper/deleted/.del-datadict_help_tables_build.test: Delete: mysql-test/suite/funcs_1/t/datadict_help_tables_build.test BitKeeper/deleted/.del-datadict_help_tables_dev.result: Delete: mysql-test/suite/funcs_1/r/datadict_help_tables_dev.result BitKeeper/deleted/.del-datadict_help_tables_dev.test: Delete: mysql-test/suite/funcs_1/t/datadict_help_tables_dev.test BitKeeper/deleted/.del-datadict_master.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_master.inc BitKeeper/deleted/.del-datadict_show_schema.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_show_schema.inc BitKeeper/deleted/.del-datadict_show_table_design.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_show_table_design.inc BitKeeper/deleted/.del-datadict_tables.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables.inc BitKeeper/deleted/.del-datadict_tables_error.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error.inc BitKeeper/deleted/.del-datadict_tables_error_1.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1.inc BitKeeper/deleted/.del-datadict_tables_error_1044.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc BitKeeper/deleted/.del-datadict_tables_error_1049.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1049.inc BitKeeper/deleted/.del-datadict_tables_error_1051.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1051.inc BitKeeper/deleted/.del-datadict_tables_error_1146.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1146.inc BitKeeper/deleted/.del-datadict_tables_error_1288.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1288.inc BitKeeper/deleted/.del-innodb__datadict.result: Delete: mysql-test/suite/funcs_1/r/innodb__datadict.result BitKeeper/deleted/.del-innodb__datadict.test: Delete: mysql-test/suite/funcs_1/t/innodb__datadict.test BitKeeper/deleted/.del-memory__datadict.test: Delete: mysql-test/suite/funcs_1/t/memory__datadict.test BitKeeper/deleted/.del-myisam__datadict.result: Delete: mysql-test/suite/funcs_1/r/myisam__datadict.result BitKeeper/deleted/.del-myisam__datadict.test: Delete: mysql-test/suite/funcs_1/t/myisam__datadict.test BitKeeper/deleted/.del-memory__datadict.result: Delete: mysql-test/suite/funcs_1/r/memory__datadict.result mysql-test/suite/funcs_1/README.txt: Cleanup in description mysql-test/suite/funcs_1/include/memory_tb1.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/memory_tb2.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/memory_tb3.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/memory_tb4.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/sp_tb.inc: Remove outdated comment mysql-test/suite/funcs_1/r/innodb_bitdata.result: Updated results mysql-test/suite/funcs_1/r/innodb_cursors.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_02.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_03.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_06.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_07.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_08.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_10.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_0102.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_03.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_0407.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_08.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_09.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_frkey.result: Updated results mysql-test/suite/funcs_1/r/innodb_views.result: Updated results mysql-test/suite/funcs_1/r/memory_bitdata.result: Updated results mysql-test/suite/funcs_1/r/memory_cursors.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_02.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_03.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_06.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_07.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_08.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_10.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_0102.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_03.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_0407.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_08.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_09.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_1011ext.result: Updated results mysql-test/suite/funcs_1/r/memory_views.result: Updated results mysql-test/suite/funcs_1/r/myisam_bitdata.result: Updated results mysql-test/suite/funcs_1/r/myisam_cursors.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_02.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_03.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_06.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_07.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_08.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_10.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_0102.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_03.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_0407.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_08.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_09.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result: Updated results mysql-test/suite/funcs_1/r/myisam_views.result: Updated results mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/t/innodb_bitdata.test: Cleanup mysql-test/suite/funcs_1/t/innodb_cursors.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_0102.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_03.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_0407.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_08.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_09.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_1011ext.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_frkey.test: Cleanup mysql-test/suite/funcs_1/t/innodb_views.test: Cleanup mysql-test/suite/funcs_1/t/memory_bitdata.test: Cleanup mysql-test/suite/funcs_1/t/memory_cursors.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_0102.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_03.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_0407.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_08.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_09.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_1011ext.test: Cleanup mysql-test/suite/funcs_1/t/memory_views.test: Cleanup mysql-test/suite/funcs_1/t/myisam_bitdata.test: Cleanup mysql-test/suite/funcs_1/t/myisam_cursors.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_0102.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_03.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_0407.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_08.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_09.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_1011ext.test: Cleanup mysql-test/suite/funcs_1/t/myisam_views.test: Cleanup mysql-test/suite/funcs_1/t/charset_collation_1.test: Check content of information_schema views character_sets, collations, collation_character_set_applicability Build variant 1 mysql-test/suite/funcs_1/t/charset_collation_2.test: Check content of information_schema views character_sets, collations, collation_character_set_applicability Build variant 2 mysql-test/suite/funcs_1/t/charset_collation_3.test: Check content of information_schema views character_sets, collations, collation_character_set_applicability Build variant 3 mysql-test/suite/funcs_1/t/is_basics_mixed.test: Test some basic properties which are independend of the storage engine used for the creation of tables. mysql-test/suite/funcs_1/t/is_character_sets.test: Tests related to functionality of information_schema.character_sets mysql-test/suite/funcs_1/t/is_collation_character_set_applicability.test: Tests related to functionality of information_schema.collation_character_set_applicability mysql-test/suite/funcs_1/t/is_collations.test: Tests related to functionality of information_schema.collations mysql-test/suite/funcs_1/t/is_column_privileges.test: Tests related to functionality of information_schema.column_privileges mysql-test/suite/funcs_1/t/is_column_privileges_is_mysql_test.test: Check content of information_schema.column_privileges about databases mysql and test mysql-test/suite/funcs_1/t/is_columns.test: Tests related to functionality of information_schema.columns mysql-test/suite/funcs_1/t/is_columns_innodb.test: Check content of information_schema.columns when some tables are preloaded Variant for InnoDB mysql-test/suite/funcs_1/t/is_columns_is.test: Check content of information_schema.columns about schema information_schema mysql-test/suite/funcs_1/t/is_columns_memory.test: Check content of information_schema.columns when some tables are preloaded Variant for MEMORY mysql-test/suite/funcs_1/t/is_columns_myisam.test: Check content of information_schema.columns when some tables are preloaded Variant for MyISAM mysql-test/suite/funcs_1/t/is_columns_mysql.test: Check content of information_schema.columns about schema mysql mysql-test/suite/funcs_1/t/is_columns_ndb.test: Check content of information_schema.columns when some tables are preloaded Variant for NDB mysql-test/suite/funcs_1/t/is_key_column_usage.test: Tests related to functionality of information_schema.key_column_usage mysql-test/suite/funcs_1/t/is_routines.test: Tests related to functionality of information_schema.routines mysql-test/suite/funcs_1/t/is_schema_privileges.test: Tests related to functionality of information_schema.schema_privileges mysql-test/suite/funcs_1/t/is_schema_privileges_is_mysql_test.test: Check content of information_schema.schema_privileges about schemas information_schema, mysql and test mysql-test/suite/funcs_1/t/is_schemata.test: Tests related to functionality of information_schema.schemata mysql-test/suite/funcs_1/t/is_schemata_is_mysql_test.test: Check content of information_schema.schemata about schemas information_schema, mysql and test mysql-test/suite/funcs_1/t/is_statistics.test: Tests related to functionality of information_schema.statistics mysql-test/suite/funcs_1/t/is_statistics_is.test: Check content of information_schema.statistics about schema information_schema mysql-test/suite/funcs_1/t/is_statistics_mysql.test: Check content of information_schema.statistics about schema mysql mysql-test/suite/funcs_1/t/is_table_constraints.test: Tests related to functionality of information_schema.table_constraints mysql-test/suite/funcs_1/t/is_table_constraints_is.test: Check content of information_schema.table_constraints about schema information_schema mysql-test/suite/funcs_1/t/is_table_constraints_mysql.test: Check content of information_schema.table_constraints about schema mysql mysql-test/suite/funcs_1/t/is_table_privileges.test: Tests related to functionality of information_schema.table_privileges mysql-test/suite/funcs_1/datadict/basics_mixed1.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/basics_mixed2.inc: Auxiliary script mysql-test/suite/funcs_1/include/cleanup.inc: Auxiliary script mysql-test/suite/funcs_1/r/charset_collation_1.result: Expected result for new test mysql-test/suite/funcs_1/r/charset_collation_2.result: Expected result for new test mysql-test/suite/funcs_1/r/charset_collation_3.result: Expected result for new test mysql-test/suite/funcs_1/r/is_basics_mixed.result: Expected result for new test mysql-test/suite/funcs_1/r/is_character_sets.result: Expected result for new test mysql-test/suite/funcs_1/r/is_collation_character_set_applicability.result: Expected result for new test mysql-test/suite/funcs_1/r/is_collations.result: Expected result for new test mysql-test/suite/funcs_1/r/is_column_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_column_privileges_is_mysql_test.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_innodb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_memory.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_myisam.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_ndb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_key_column_usage.result: Expected result for new test mysql-test/suite/funcs_1/r/is_routines.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schema_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schemata.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schemata_is_mysql_test.result: Expected result for new test mysql-test/suite/funcs_1/r/is_statistics.result: Expected result for new test mysql-test/suite/funcs_1/r/is_statistics_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_statistics_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_constraints.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_constraints_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_innodb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_memory.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_myisam.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_ndb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_triggers.result: Expected result for new test mysql-test/suite/funcs_1/r/is_user_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_views.result: Expected result for new test mysql-test/suite/funcs_1/t/is_tables.test: Tests related to functionality of information_schema.tables mysql-test/suite/funcs_1/t/is_tables_innodb.test: Check content of information_schema.tables when some tables are preloaded Variant for InnoDB mysql-test/suite/funcs_1/t/is_tables_is.test: Check content of information_schema.tables about schema information_schema mysql-test/suite/funcs_1/t/is_tables_memory.test: Check content of information_schema.tables when some tables are preloaded Variant for MEMORY mysql-test/suite/funcs_1/t/is_tables_myisam.test: Check content of information_schema.tables when some tables are preloaded Variant for MyISAM mysql-test/suite/funcs_1/t/is_tables_mysql.test: Check content of information_schema.tables about schema mysql mysql-test/suite/funcs_1/t/is_tables_ndb.test: Check content of information_schema.tables when some tables are preloaded Variant for NDB mysql-test/suite/funcs_1/t/is_triggers.test: Tests related to functionality of information_schema.triggers mysql-test/suite/funcs_1/t/is_user_privileges.test: Tests related to functionality of information_schema.user_privileges mysql-test/suite/funcs_1/t/is_views.test: Tests related to functionality of information_schema.views mysql-test/suite/funcs_1/datadict/basics_mixed3.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/charset_collation.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/columns.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/datadict.pre: Auxiliary script mysql-test/suite/funcs_1/datadict/is_table_query.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/statistics.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/table_constraints.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/tables1.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/tables2.inc: Auxiliary script --- .../suite/funcs_1/r/is_table_constraints.result | 310 +++++++++++++++++++++ 1 file changed, 310 insertions(+) create mode 100644 mysql-test/suite/funcs_1/r/is_table_constraints.result (limited to 'mysql-test/suite/funcs_1/r/is_table_constraints.result') diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result new file mode 100644 index 00000000000..d2ca58aade6 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result @@ -0,0 +1,310 @@ +SHOW TABLES FROM information_schema LIKE 'TABLE_CONSTRAINTS'; +Tables_in_information_schema (TABLE_CONSTRAINTS) +TABLE_CONSTRAINTS +####################################################################### +# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +####################################################################### +DROP VIEW IF EXISTS test.v1; +DROP PROCEDURE IF EXISTS test.p1; +DROP FUNCTION IF EXISTS test.f1; +CREATE VIEW test.v1 AS SELECT * FROM information_schema.TABLE_CONSTRAINTS; +CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLE_CONSTRAINTS; +CREATE FUNCTION test.f1() returns BIGINT +BEGIN +DECLARE counter BIGINT DEFAULT NULL; +SELECT COUNT(*) INTO counter FROM information_schema.TABLE_CONSTRAINTS; +RETURN counter; +END// +# Attention: The printing of the next result sets is disabled. +SELECT * FROM information_schema.TABLE_CONSTRAINTS; +SELECT * FROM test.v1; +CALL test.p1; +SELECT test.f1(); +DROP VIEW test.v1; +DROP PROCEDURE test.p1; +DROP FUNCTION test.f1; +######################################################################### +# Testcase 3.2.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout +######################################################################### +DESCRIBE information_schema.TABLE_CONSTRAINTS; +Field Type Null Key Default Extra +CONSTRAINT_CATALOG varchar(512) YES NULL +CONSTRAINT_SCHEMA varchar(64) NO +CONSTRAINT_NAME varchar(64) NO +TABLE_SCHEMA varchar(64) NO +TABLE_NAME varchar(64) NO +CONSTRAINT_TYPE varchar(64) NO +SHOW CREATE TABLE information_schema.TABLE_CONSTRAINTS; +Table Create Table +TABLE_CONSTRAINTS CREATE TEMPORARY TABLE `TABLE_CONSTRAINTS` ( + `CONSTRAINT_CATALOG` varchar(512) default NULL, + `CONSTRAINT_SCHEMA` varchar(64) NOT NULL default '', + `CONSTRAINT_NAME` varchar(64) NOT NULL default '', + `TABLE_SCHEMA` varchar(64) NOT NULL default '', + `TABLE_NAME` varchar(64) NOT NULL default '', + `CONSTRAINT_TYPE` varchar(64) NOT NULL default '' +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +SHOW COLUMNS FROM information_schema.TABLE_CONSTRAINTS; +Field Type Null Key Default Extra +CONSTRAINT_CATALOG varchar(512) YES NULL +CONSTRAINT_SCHEMA varchar(64) NO +CONSTRAINT_NAME varchar(64) NO +TABLE_SCHEMA varchar(64) NO +TABLE_NAME varchar(64) NO +CONSTRAINT_TYPE varchar(64) NO +SELECT constraint_catalog, constraint_schema, constraint_name, +table_schema, table_name +FROM information_schema.table_constraints +WHERE constraint_catalog IS NOT NULL; +constraint_catalog constraint_schema constraint_name table_schema table_name +######################################################################################### +# Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information +######################################################################################### +DROP DATABASE IF EXISTS db_datadict; +CREATE DATABASE db_datadict; +CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT, +f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2)) +ENGINE = ; +CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1); +CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3); +CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT, +f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2)) +ENGINE = ; +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT SELECT(f5) ON db_datadict.t1 TO 'testuser1'@'localhost'; +SHOW GRANTS FOR 'testuser1'@'localhost'; +Grants for testuser1@localhost +GRANT USAGE ON *.* TO 'testuser1'@'localhost' +GRANT SELECT (f5) ON `db_datadict`.`t1` TO 'testuser1'@'localhost' +SELECT * FROM information_schema.table_constraints +WHERE table_schema = 'db_datadict' +ORDER BY table_schema,table_name, constraint_name; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +NULL db_datadict my_idx1 db_datadict t1 UNIQUE +NULL db_datadict my_idx2 db_datadict t1 UNIQUE +NULL db_datadict PRIMARY db_datadict t1 PRIMARY KEY +NULL db_datadict PRIMARY db_datadict t2 PRIMARY KEY +SHOW INDEXES FROM db_datadict.t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 f1 ### ### ### ### ### ### ### +t1 0 PRIMARY 2 f2 ### ### ### ### ### ### ### +t1 0 my_idx1 1 f6 ### ### ### ### ### ### ### +t1 0 my_idx1 2 f1 ### ### ### ### ### ### ### +t1 0 my_idx2 1 f3 ### ### ### ### ### ### ### +SHOW INDEXES FROM db_datadict.t2; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t2 0 PRIMARY 1 f1 ### ### ### ### ### ### ### +t2 0 PRIMARY 2 f2 ### ### ### ### ### ### ### +# Establish connection testuser1 (user=testuser1) +SHOW GRANTS FOR 'testuser1'@'localhost'; +Grants for testuser1@localhost +GRANT USAGE ON *.* TO 'testuser1'@'localhost' +GRANT SELECT (f5) ON `db_datadict`.`t1` TO 'testuser1'@'localhost' +SELECT * FROM information_schema.table_constraints +WHERE table_schema = 'db_datadict' +ORDER BY table_schema,table_name, constraint_name; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +NULL db_datadict my_idx1 db_datadict t1 UNIQUE +NULL db_datadict my_idx2 db_datadict t1 UNIQUE +NULL db_datadict PRIMARY db_datadict t1 PRIMARY KEY +SHOW INDEXES FROM db_datadict.t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 f1 ### ### ### ### ### ### ### +t1 0 PRIMARY 2 f2 ### ### ### ### ### ### ### +t1 0 my_idx1 1 f6 ### ### ### ### ### ### ### +t1 0 my_idx1 2 f1 ### ### ### ### ### ### ### +t1 0 my_idx2 1 f3 ### ### ### ### ### ### ### +SHOW INDEXES FROM db_datadict.t2; +ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 't2' +# Switch to connection default and close connection testuser1 +DROP USER 'testuser1'@'localhost'; +DROP DATABASE db_datadict; +######################################################################################### +# Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS modifications +######################################################################################### +DROP DATABASE IF EXISTS db_datadict; +DROP TABLE IF EXISTS test.t1_my_table; +CREATE DATABASE db_datadict; +SELECT table_name FROM information_schema.table_constraints +WHERE table_name LIKE 't1_my_table%'; +table_name +CREATE TABLE test.t1_my_table +(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2)) +DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci +ENGINE = ; +SELECT constraint_name, table_schema, table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_table'; +constraint_name table_schema table_name constraint_type +PRIMARY test t1_my_table PRIMARY KEY +SELECT table_name FROM information_schema.table_constraints +WHERE table_name LIKE 't1_my_table%'; +table_name +t1_my_table +RENAME TABLE test.t1_my_table TO test.t1_my_tablex; +SELECT table_name FROM information_schema.table_constraints +WHERE table_name LIKE 't1_my_table%'; +table_name +t1_my_tablex +SELECT table_schema,table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_schema table_name +test t1_my_tablex +RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex; +SELECT table_schema,table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_schema table_name +db_datadict t1_my_tablex +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2); +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +DROP INDEX f2 ON db_datadict.t1_my_tablex; +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2); +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict f2 db_datadict t1_my_tablex UNIQUE +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +DROP INDEX f2 ON db_datadict.t1_my_tablex; +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,f1); +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict my_idx db_datadict t1_my_tablex UNIQUE +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +DROP INDEX my_idx ON db_datadict.t1_my_tablex; +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2); +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict my_idx db_datadict t1_my_tablex UNIQUE +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict my_idx db_datadict t1_my_tablex UNIQUE +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +ALTER TABLE db_datadict.t1_my_tablex +DROP COLUMN f2; +SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +constraint_schema constraint_name table_schema table_name constraint_type +db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY +SELECT table_name +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +t1_my_tablex +DROP TABLE db_datadict.t1_my_tablex; +SELECT table_name +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +SELECT table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +CREATE TABLE db_datadict.t1_my_tablex +ENGINE = AS +SELECT 1 AS f1; +SELECT table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1); +SELECT table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +t1_my_tablex +SELECT table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +t1_my_tablex +DROP DATABASE db_datadict; +SELECT table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +table_name +######################################################################## +# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and +# DDL on INFORMATION_SCHEMA tables are not supported +######################################################################## +DROP DATABASE IF EXISTS db_datadict; +DROP TABLE IF EXISTS db_datadict.t1; +CREATE DATABASE db_datadict; +CREATE TABLE db_datadict.t1 (f1 BIGINT, UNIQUE(f1)) +ENGINE = ; +INSERT INTO information_schema.table_constraints +(constraint_schema, constraint_name, table_name) +VALUES ( 'mysql', 'primary', 'db'); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +INSERT INTO information_schema.table_constraints +SELECT * FROM information_schema.table_constraints; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +UPDATE information_schema.table_constraints +SET table_name = 'db1' WHERE constraint_name = 'primary'; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DELETE FROM information_schema.table_constraints WHERE table_name = 't1'; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +TRUNCATE information_schema.table_constraints; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +CREATE INDEX i3 ON information_schema.table_constraints(table_name); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.table_constraints ADD f1 INT; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DROP TABLE information_schema.table_constraints; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.table_constraints +RENAME db_datadict.table_constraints; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.table_constraints +RENAME information_schema.xtable_constraints; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DROP TABLE db_datadict.t1; +DROP DATABASE db_datadict; -- cgit v1.2.1