I create a simple table and populate it with 100 records:

CREATE OR REPLACE TABLE t1 (a int);

DELIMITER $$

FOR i IN 0..99 DO

  INSERT INTO t1 VALUES (i);

END FOR;

$$

DELIMITER ;

Testing simple constant subquery

Now I run these tree queries, using a simple subquery:

SELECT (SELECT SLEEP(0.01)) AS x,a FROM t1;

SELECT (SELECT SLEEP(0.01)) AS x,a FROM t1 GROUP BY x,a;

SELECT (SELECT SLEEP(0.01)) AS x,a FROM t1 GROUP BY x,a WITH ROLLUP;

All queries spent nearly one second. Which means "(SELECT SLEEP(0.01)" was evaluated nearly 100 times.

Testing simple constant stored function

Now I replace the subquery to a slow constant expression. A deterministic stored function with SLEEP is good for test purposes:

CREATE OR REPLACE FUNCTION f1() RETURNS INT DETERMINISTIC NO SQL RETURN SLEEP(0.01);

Now I rerun the same three queries using f1() instead of the subquery:

SELECT f1() AS x,a FROM t1;

SELECT f1() AS x,a FROM t1 GROUP BY x,a;

SELECT f1() AS x,a FROM t1 GROUP BY x,a WITH ROLLUP;

The first and the second query still spend one second.

The third query becomes much slower:

MariaDB [test]> SELECT f1() AS x,a FROM t1 GROUP BY x,a WITH ROLLUP;

+------+------+

| x    | a    |

+------+------+

|    0 |    0 |

...

|    0 |   99 |

|    0 | NULL |

| NULL | NULL |

+------+------+

102 rows in set (3.08 sec)

Notice, it spent 3 seconds, which means f1() was evaluated nearly 300 times.

Looks weird. Why an SP returning an expression vs a subquery returning the same expression would be 3 times slower?

Testing simple stored function wrapped to a subquery

Now I replace f1() to a subquery (SELECT f1()) and rerun the same queries:

SELECT (SELECT f1()) AS x,a FROM t1;

SELECT (SELECT f1()) AS x,a FROM t1 GROUP BY x,a;

SELECT (SELECT f1()) AS x,a FROM t1 GROUP BY x,a WITH ROLLUP;

All three queries spend one second again. Looks even weirder. It's not clear why wrapping an expression EXPR into a subquery (SELECT EXPR) makes the last query three times faster!

Perhaps Item_func_rollup_const could cache args[0]->val_xxx() to avoid this.