1# 2# Bug#18925: subqueries with MIN/MAX functions on INFORMATION_SCHEMA 3# 4SELECT t.table_name, c1.column_name 5FROM information_schema.tables t 6INNER JOIN 7information_schema.columns c1 8ON t.table_schema = c1.table_schema AND 9t.table_name = c1.table_name 10WHERE t.table_schema = 'information_schema' AND 11c1.ordinal_position = 12( SELECT COALESCE(MIN(c2.ordinal_position),1) 13FROM information_schema.columns c2 14WHERE c2.table_schema = t.table_schema AND 15c2.table_name = t.table_name AND 16c2.column_name LIKE '%SCHEMA%' 17 ) 18AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%"; 19table_name column_name 20ALL_PLUGINS PLUGIN_NAME 21APPLICABLE_ROLES GRANTEE 22CHARACTER_SETS CHARACTER_SET_NAME 23CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 24CLIENT_STATISTICS CLIENT 25COLLATIONS COLLATION_NAME 26COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME 27COLUMNS TABLE_SCHEMA 28COLUMN_PRIVILEGES TABLE_SCHEMA 29ENABLED_ROLES ROLE_NAME 30ENGINES ENGINE 31EVENTS EVENT_SCHEMA 32FILES TABLE_SCHEMA 33GEOMETRY_COLUMNS F_TABLE_SCHEMA 34GLOBAL_STATUS VARIABLE_NAME 35GLOBAL_VARIABLES VARIABLE_NAME 36INDEX_STATISTICS TABLE_SCHEMA 37KEYWORDS WORD 38KEY_CACHES KEY_CACHE_NAME 39KEY_COLUMN_USAGE CONSTRAINT_SCHEMA 40PARAMETERS SPECIFIC_SCHEMA 41PARTITIONS TABLE_SCHEMA 42PLUGINS PLUGIN_NAME 43PROCESSLIST ID 44PROFILING QUERY_ID 45REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA 46ROUTINES ROUTINE_SCHEMA 47SCHEMATA SCHEMA_NAME 48SCHEMA_PRIVILEGES TABLE_SCHEMA 49SESSION_STATUS VARIABLE_NAME 50SESSION_VARIABLES VARIABLE_NAME 51SPATIAL_REF_SYS SRID 52SQL_FUNCTIONS FUNCTION 53STATISTICS TABLE_SCHEMA 54SYSTEM_VARIABLES VARIABLE_NAME 55TABLES TABLE_SCHEMA 56TABLESPACES TABLESPACE_NAME 57TABLE_CONSTRAINTS CONSTRAINT_SCHEMA 58TABLE_PRIVILEGES TABLE_SCHEMA 59TABLE_STATISTICS TABLE_SCHEMA 60TRIGGERS TRIGGER_SCHEMA 61USER_PRIVILEGES GRANTEE 62USER_STATISTICS USER 63VIEWS TABLE_SCHEMA 64SELECT t.table_name, c1.column_name 65FROM information_schema.tables t 66INNER JOIN 67information_schema.columns c1 68ON t.table_schema = c1.table_schema AND 69t.table_name = c1.table_name 70WHERE t.table_schema = 'information_schema' AND 71c1.ordinal_position = 72( SELECT COALESCE(MIN(c2.ordinal_position),1) 73FROM information_schema.columns c2 74WHERE c2.table_schema = 'information_schema' AND 75c2.table_name = t.table_name AND 76c2.column_name LIKE '%SCHEMA%' 77 ) 78AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%"; 79table_name column_name 80ALL_PLUGINS PLUGIN_NAME 81APPLICABLE_ROLES GRANTEE 82CHARACTER_SETS CHARACTER_SET_NAME 83CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 84CLIENT_STATISTICS CLIENT 85COLLATIONS COLLATION_NAME 86COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME 87COLUMNS TABLE_SCHEMA 88COLUMN_PRIVILEGES TABLE_SCHEMA 89ENABLED_ROLES ROLE_NAME 90ENGINES ENGINE 91EVENTS EVENT_SCHEMA 92FILES TABLE_SCHEMA 93GEOMETRY_COLUMNS F_TABLE_SCHEMA 94GLOBAL_STATUS VARIABLE_NAME 95GLOBAL_VARIABLES VARIABLE_NAME 96INDEX_STATISTICS TABLE_SCHEMA 97KEYWORDS WORD 98KEY_CACHES KEY_CACHE_NAME 99KEY_COLUMN_USAGE CONSTRAINT_SCHEMA 100PARAMETERS SPECIFIC_SCHEMA 101PARTITIONS TABLE_SCHEMA 102PLUGINS PLUGIN_NAME 103PROCESSLIST ID 104PROFILING QUERY_ID 105REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA 106ROUTINES ROUTINE_SCHEMA 107SCHEMATA SCHEMA_NAME 108SCHEMA_PRIVILEGES TABLE_SCHEMA 109SESSION_STATUS VARIABLE_NAME 110SESSION_VARIABLES VARIABLE_NAME 111SPATIAL_REF_SYS SRID 112SQL_FUNCTIONS FUNCTION 113STATISTICS TABLE_SCHEMA 114SYSTEM_VARIABLES VARIABLE_NAME 115TABLES TABLE_SCHEMA 116TABLESPACES TABLESPACE_NAME 117TABLE_CONSTRAINTS CONSTRAINT_SCHEMA 118TABLE_PRIVILEGES TABLE_SCHEMA 119TABLE_STATISTICS TABLE_SCHEMA 120TRIGGERS TRIGGER_SCHEMA 121USER_PRIVILEGES GRANTEE 122USER_STATISTICS USER 123VIEWS TABLE_SCHEMA 124