Summary

EXPLAIN incorrectly executes a stored function when it appears inside a derived table (e.g. SELECT * FROM (SELECT f()) AS t), while the same function is not invoked for EXPLAIN SELECT f();.

Environment

• Product: MariaDB (reproduced on 12.3.1)

Expected behaviour

• EXPLAIN should only produce a plan and must not evaluate stored functions (except where explicitly documented otherwise).
• Scalar and derived-table forms should behave consistently: neither should increment a side-effect counter during EXPLAIN.

Actual behaviour

Minimal reproducer (SQL)

DROP FUNCTION IF EXISTS m;

DELIMITER //

CREATE FUNCTION m() RETURNS INT

DETERMINISTIC

BEGIN

  SET @n = IFNULL(@n, 0) + 1;

  RETURN 1;

END//

DELIMITER ;

SET @n = 0;

EXPLAIN SELECT m();

SELECT @n AS n_after_explain_scalar;   -- expected: 0

SET @n = 0;

EXPLAIN SELECT * FROM (SELECT m()) AS t;

SELECT @n AS n_after_explain_derived; -- expected: 0; actual: 1

DROP FUNCTION m;