/*
Enum enum_locked_tables_mode and locked_tables_mode member are
used to indicate whether the so-called “locked tables mode” is on,
and what kind of mode is active.
Locked tables mode is used when it’s necessary to open and
lock many tables at once, for usage across multiple
(sub-)statements.
This may be necessary either for queries that use stored functions
and triggers, in which case the statements inside functions and
triggers may be executed many times, or for implementation of
LOCK TABLES, in which case the opened tables are reused by all
subsequent statements until a call to UNLOCK TABLES.
The kind of locked tables mode employed for stored functions and
triggers is also called “prelocked mode”.
In this mode, first open_tables() call to open the tables used
in a statement analyses all functions used by the statement
and adds all indirectly used tables to the list of tables to
open and lock.
It also marks the parse tree of the statement as requiring
prelocking. After that, lock_tables() locks the entire list
of tables and changes THD::locked_tables_modeto LTM_PRELOCKED.
All statements executed inside functions or triggers
use the prelocked tables, instead of opening their own ones.
Prelocked mode is turned off automatically once close_thread_tables()
of the main statement is called.
*/

Second question: What tables is it even trying to open? You’re doing a select on a function… I’m confused.

I also tried reproducing your results with MariaDB 10.3 and got similar results for func1 vs func2, but func3 performed somewhat better, relatively speaking: func1 x 1M: 1.739 sec, func2 x 1M: 2.249 sec, func3 x 1M: 3.054 sec.

I also tried reproducing the issue with actual stored procedures. The benchmark function doesn’t support calling stored procedures, so I wrote my own benchmark stored procedure. This appears to introduce some overhead compared to the built-in benchmark function, so the results are not directly comparable, but there appears to be a similar effect with stored procedures, i.e. that func2 is slower than func1, and that func3 is slower than func2.