MDEV-34391 SET PATH variable and resolution for stored routine invocations

[iqbal-rsec]

trafficstars

• [x] The Jira issue number for this PR is: MDEV-34391

Description

Implement the SET PATH statement. The grammar differs from the Jira ticket where the equal sign is required: SET PATH= 'schema1, schema2, schema3'; Schemas within the session PATH variable will be used to resolve unqualified and qualified routine invocations. To determine whether or not a particular routine exists within a schema/database lookups has to be performed on the proc table, so performance is impacted. To alleviate this, the PATH is empty by default - routines will be resolved using the current database as it was originally done before this implementation.

Original PR is #3566, this PR has some additional changes that fixes/simplify the parser:

• Refactored PATH parser code and fixed a bug where commas inside quotes are still treated as separator.
• Moved path cache to system_variables for consistency with other variables
• Changed path SET grammar to require = for consistency with other variables
• Changed path cache to be a simple static array both for easier memory management. Only 16 schemas are supported per session, we do not want too many schemas in the path anyway since it will impact performance.
• Add support for internal schemas (mariadb_schema, oracle_schema) in paths.
• Set default PATH to empty
• Make views invariant to current path
• Invalidate sp caches when session path has been changed so that stored routines are recompiled to use the new paths
• Disallow session path setting inside stored routines
• Add tests
  • Ensure that SUPER permission is needed to set GLOBAL PATH
  • Tests were added to allow database names that contains commas or double quotes
  • Test internal schemas in PATH
  • Test view invariance with respect to PATH

Release Notes

New system variable path has been added.

• https://mariadb.com/kb/en/full-list-of-mariadb-options-system-and-status-variables/
• https://mariadb.com/kb/en/server-system-variables/

How can this PR be tested?

mysql-test/mtr path_basic path_resolution

Basing the PR against the correct MariaDB version

• [x] This is a new feature or a refactoring, and the PR is based against the main branch.
• [ ] This is a bug fix, and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [x] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.