mirror of
https://github.com/MariaDB/server.git
synced 2026-01-25 04:41:47 +01:00
9057f741d7
MariaDB 11.8 and above allows to specify list of columns in the update trigger and such trigger is fired only when one of the specified columns is updated. The `INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS` table lists all such columns for a trigger. A column is shown only if the user has any non-SELECT privileges on the column specified in the trigger.
137 lines
3.8 KiB
Text
137 lines
3.8 KiB
Text
#
|
|
# Bug#18925: subqueries with MIN/MAX functions on INFORMATION_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 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%";
|
|
table_name column_name
|
|
ALL_PLUGINS PLUGIN_NAME
|
|
APPLICABLE_ROLES GRANTEE
|
|
CHARACTER_SETS CHARACTER_SET_NAME
|
|
CHECK_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
CLIENT_STATISTICS CLIENT
|
|
COLLATIONS COLLATION_NAME
|
|
COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME
|
|
COLUMNS TABLE_SCHEMA
|
|
COLUMN_PRIVILEGES TABLE_SCHEMA
|
|
ENABLED_ROLES ROLE_NAME
|
|
ENGINES ENGINE
|
|
EVENTS EVENT_SCHEMA
|
|
FILES TABLE_SCHEMA
|
|
GEOMETRY_COLUMNS F_TABLE_SCHEMA
|
|
GLOBAL_STATUS VARIABLE_NAME
|
|
GLOBAL_VARIABLES VARIABLE_NAME
|
|
INDEX_STATISTICS TABLE_SCHEMA
|
|
KEYWORDS WORD
|
|
KEY_CACHES KEY_CACHE_NAME
|
|
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
|
|
KEY_PERIOD_USAGE CONSTRAINT_SCHEMA
|
|
OPTIMIZER_COSTS ENGINE
|
|
PARAMETERS SPECIFIC_SCHEMA
|
|
PARTITIONS TABLE_SCHEMA
|
|
PERIODS TABLE_SCHEMA
|
|
PLUGINS PLUGIN_NAME
|
|
PROCESSLIST ID
|
|
PROFILING QUERY_ID
|
|
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
ROUTINES ROUTINE_SCHEMA
|
|
SCHEMATA SCHEMA_NAME
|
|
SCHEMA_PRIVILEGES TABLE_SCHEMA
|
|
SEQUENCES SEQUENCE_SCHEMA
|
|
SESSION_STATUS VARIABLE_NAME
|
|
SESSION_VARIABLES VARIABLE_NAME
|
|
SLAVE_STATUS Connection_name
|
|
SPATIAL_REF_SYS SRID
|
|
SQL_FUNCTIONS FUNCTION
|
|
STATISTICS TABLE_SCHEMA
|
|
SYSTEM_VARIABLES VARIABLE_NAME
|
|
TABLES TABLE_SCHEMA
|
|
TABLESPACES TABLESPACE_NAME
|
|
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
TABLE_PRIVILEGES TABLE_SCHEMA
|
|
TABLE_STATISTICS TABLE_SCHEMA
|
|
TRIGGERED_UPDATE_COLUMNS TRIGGER_SCHEMA
|
|
TRIGGERS TRIGGER_SCHEMA
|
|
USERS USER
|
|
USER_PRIVILEGES GRANTEE
|
|
USER_STATISTICS USER
|
|
VIEWS TABLE_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 = 'information_schema' AND
|
|
c2.table_name = t.table_name AND
|
|
c2.column_name LIKE '%SCHEMA%'
|
|
)
|
|
AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%";
|
|
table_name column_name
|
|
ALL_PLUGINS PLUGIN_NAME
|
|
APPLICABLE_ROLES GRANTEE
|
|
CHARACTER_SETS CHARACTER_SET_NAME
|
|
CHECK_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
CLIENT_STATISTICS CLIENT
|
|
COLLATIONS COLLATION_NAME
|
|
COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME
|
|
COLUMNS TABLE_SCHEMA
|
|
COLUMN_PRIVILEGES TABLE_SCHEMA
|
|
ENABLED_ROLES ROLE_NAME
|
|
ENGINES ENGINE
|
|
EVENTS EVENT_SCHEMA
|
|
FILES TABLE_SCHEMA
|
|
GEOMETRY_COLUMNS F_TABLE_SCHEMA
|
|
GLOBAL_STATUS VARIABLE_NAME
|
|
GLOBAL_VARIABLES VARIABLE_NAME
|
|
INDEX_STATISTICS TABLE_SCHEMA
|
|
KEYWORDS WORD
|
|
KEY_CACHES KEY_CACHE_NAME
|
|
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
|
|
KEY_PERIOD_USAGE CONSTRAINT_SCHEMA
|
|
OPTIMIZER_COSTS ENGINE
|
|
PARAMETERS SPECIFIC_SCHEMA
|
|
PARTITIONS TABLE_SCHEMA
|
|
PERIODS TABLE_SCHEMA
|
|
PLUGINS PLUGIN_NAME
|
|
PROCESSLIST ID
|
|
PROFILING QUERY_ID
|
|
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
ROUTINES ROUTINE_SCHEMA
|
|
SCHEMATA SCHEMA_NAME
|
|
SCHEMA_PRIVILEGES TABLE_SCHEMA
|
|
SEQUENCES SEQUENCE_SCHEMA
|
|
SESSION_STATUS VARIABLE_NAME
|
|
SESSION_VARIABLES VARIABLE_NAME
|
|
SLAVE_STATUS Connection_name
|
|
SPATIAL_REF_SYS SRID
|
|
SQL_FUNCTIONS FUNCTION
|
|
STATISTICS TABLE_SCHEMA
|
|
SYSTEM_VARIABLES VARIABLE_NAME
|
|
TABLES TABLE_SCHEMA
|
|
TABLESPACES TABLESPACE_NAME
|
|
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
|
|
TABLE_PRIVILEGES TABLE_SCHEMA
|
|
TABLE_STATISTICS TABLE_SCHEMA
|
|
TRIGGERED_UPDATE_COLUMNS TRIGGER_SCHEMA
|
|
TRIGGERS TRIGGER_SCHEMA
|
|
USERS USER
|
|
USER_PRIVILEGES GRANTEE
|
|
USER_STATISTICS USER
|
|
VIEWS TABLE_SCHEMA
|