In some cases I believe this problem can also be triggered by a query that only uses the Stored Aggregate Function once, but also has a 'HAVING' clause. I need to isolate an example.

Right. But in a way it's common to all stored routines — functions, procedures, triggers, anything.

The kind of error you've got is only possible in stored aggregate functions.

But even in a stored routine, if one would need to create a temporary table, one can never know whether it'll conflict with some existing table of the same name.

... but with those other stored routines the routine causing side effects is much more expected, isn't it? Indeed with triggers, often very much the intended use. Also with those it's always possible to work around the problem with a table naming convention.

For the stored aggregate functions it doesn't appear to be possible to work around the problem - here's an example only using the function once, but with a having clause that triggers the issue (again using the medi_int example from the documentation):

CREATE TABLE t1 (x int, y int);

INSERT INTO t1 values (1,1), (2,1), (3,2);

select y, medi_int(x), count(*) c from t1 group by 1 having c=1;

# fails due to "Table 'tt' already exists" error

[as an aside, interesting that if you switch x & y such that the count(*)s are all 0 then it doesn't trigger the problem].

In fact:

select y, medi_int(x) z, count(*) c from t1 group by 1 having c=1;

# triggers the problem

select y, z, c from (select y, medi_int(x) z, count(*) c from t1 group by 1) t where c=1;

# also triggers the problem

#

# yet:

select y, z, c from (select y, medi_int(x) z, count(*) c from t1 group by 1) t having c=1;

# works fine

# but leaves warnings:

# +-------+------+-----------------------------+

# | Level | Code | Message                     |

# +-------+------+-----------------------------+

# | Note  | 4094 | At line 18 in test.medi_int |

# | Note  | 4094 | At line 18 in test.medi_int |

# | Note  | 4094 | At line 18 in test.medi_int |

# +-------+------+-----------------------------+

Assigning to serg to decide what to do about it, if anything.