Joining CTEs inside a function fails with error code 1146 despite the tables existing

When I wrap a statement containing two or more Common Table Expressions in a function, and one of those CTEs is JOINed to another CTE, executing the function will result in error code 1146, reporting that the first table that the CTE interacts with doesn't exist - even though it does.

Extracting the failing query and running it standalone works fine, the query will return the expected results.

I have attached sample SQL that replicates this behaviour.