xinyiZzz opened a new issue, #31297: URL: https://github.com/apache/doris/issues/31297
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description ## Goal Compatible with Mysql grammar in new optimizer Nereids, support grammar and impl: 1. `SHOW PROCEDURE STATUS` 2. `SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'sys'` only support grammar but do nothing: 1. `SHOW FUNCTION STATUS` TODO: 1. `SHOW CREATE PROCEDURE` ## Current Behavior `SHOW PROCEDURE STATUS` and `SHOW FUNCTION STATUS` will fall back to old parser using cup and `ShowProcedureStmt` does nothing, so it returns empty. ``` sql_parser.cup show_param ::= KW_WHITELIST procedure_or_function KW_STATUS opt_wild_where {: RESULT = new ShowProcedureStmt(); :} ``` ## Expected 1. use mysql client, refer to Mysql: ``` mysql> SHOW PROCEDURE STATUS; |Db|Name|Type|Definer|Modified|Created|Security_type|Comment|character_set_client|collation_connection|DatabaseCollation| | sys | create_synonym_db | PROCEDURE | mysql.sys@localhost | 2022-12-17 18:01:31 | 2022-12-17 18:01:31 | INVOKER | Description ... | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | ``` ``` mysql> SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE' limit 1; |SPECIFIC_NAME|ROUTINE_CATALOG|ROUTINE_SCHEMA|ROUTINE_NAME|ROUTINE_TYPE|DATA_TYPE|CHARACTER_MAXIMUM_LENGTH|CHARACTER_OCTET_LENGTH|NUMERIC_PRECISION|NUMERIC_SCALE|DATETIME_PRECISION|CHARACTER_SET_NAME|COLLATION_NAME|DTD_IDENTIFIER|ROUTINE_BODY|ROUTINE_DEFINITION|EXTERNAL_NAME|EXTERNAL_LANGUAGE|PARAMETER_STYLE|IS_DETERMINISTIC|SQL_DATA_ACCESS|SQL_PATH|SECURITY_TYPE|CREATED|LAST_ALTERED|SQL_MODE|ROUTINE_COMMENT|DEFINER|CHARACTER_SET_CLIENT|COLLATION_CONNECTION|DATABASE_COLLATION| | create_synonym_db | def | sys | create_synonym_db | PROCEDURE | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SQL | BEGIN DECLARE v_done bool DEFAULT FALSE; DECLARE v_db_name_check VARCHAR(64); ... SELECT CONCAT( 'Created ', v_views_created, ' view', IF(v_views_created != 1, 's', ''), ' in the ', sys.quote_identifier(in_synonym), ' database' ) AS summary; END | NULL | SQL | SQL | NO | MODIFIES SQL DATA | NULL | INVOKER | 2022-12-17 18:01:31 | 2022-12-17 18:01:31 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | Description ... | mysql.sys@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | ``` 4. use Navicat, refer to Mysql:  In Mysql log:  In Doris FE audit log:  ## References Mysql: list all stored procedure: ``` 1. SHOW PROCEDURE STATUS; 2. SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE' limit 1; show a procedure by name: 3. SHOW CREATE PROCEDURE Command ``` refer to: https://database.guide/2-ways-to-list-all-stored-procedures-in-mysql/ https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html https://dev.mysql.com/doc/refman/8.0/en/show-procedure-status.html https://dev.mysql.com/doc/refman/8.0/en/show-procedure-code.html https://soft-builder.com/how-to-list-stored-procedures-and-functions-in-mysql-database/ Oracle: list all stored procedure: ``` 1. SELECT object_name FROM user_procedures WHERE object_type = 'PROCEDURE'; 2. SELECT owner, object_name FROM all_procedures 3. SELECT owner, object_name FROM dba_procedures WHERE object_type = 'PROCEDURE'; 4. SELECT * FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MY_STORED_PROCEDURE' ``` refer to: https://database.guide/how-to-list-all-stored-procedures-in-oracle-database/ PostgreSQL: list all stored procedure: ``` 1. information_schema.routines View specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | routine_type | module_catalog | module_schema | module_name | udt_catalog | udt_schema | udt_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | type_udt_catalog | type_udt_schema | type_udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | routine_body | routine_definition | external_name | external_language | parameter_style | is_deterministic | sql_data_access | is_null_call | sql_path | schema_level_routine | max_dynamic_result_sets | is_user_defined_cast | is_implicitly_invocable | security_type | to_sql_specific_catalog | to_sql_specific_schema | to_sql_specific_name | as_l ocator | created | last_altered | new_savepoint_level | is_udt_dependent | result_cast_from_data_type | result_cast_as_locator | result_cast_char_max_length | result_cast_char_octet_length | result_cast_char_set_catalog | result_cast_char_set_schema | result_cast_char_set_name | result_cast_collation_catalog | result_cast_collation_schema | result_cast_collation_name | result_cast_numeric_precision | result_cast_numeric_precision_radix | result_cast_numeric_scale | result_cast_datetime_precision | result_cast_interval_type | result_cast_interval_precision | result_cast_type_udt_catalog | result_cast_type_udt_schema | result_cast_type_udt_name | result_cast_scope_catalog | result_cast_scope_schema | result_cast_scope_name | result_cast_maximum_cardinality | result_cast_dtd_identifier 2. pg_proc Catalog 3. \df Command ``` refer to: https://database.guide/3-ways-to-list-all-stored-procedures-in-a-postgresql-database/ https://database.guide/3-ways-to-list-all-stored-procedures-that-reference-a-table-in-postgresql/ https://soft-builder.com/how-to-list-procedures-in-postgresql-database/ ### Use case _No response_ ### Related issues _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
