This is by no means an official statement but just a mere conclusion that I deduct from the way I know JPA providers implement that kind of stuff. I suppose EclipseLink either has a special handling for such unknown type cases, does some kind of function discovery or you were simply lucky because it defaults to a String type.
Could you try with a function that returns an Integer and check the result type?

Anyway, this is about Hibernate and from what I know of the internals, it must know the type of the expression to work. The type of the expression is used for type checks and is also responsible for actually reading the value from a JDBC result set. By registering the function with it's return type in the dialect, you are essentially telling Hibernate which getXXX method of the JDBC result set to use for retrieving the value. Using the ResultSet.getObject method in case a type is not known might be an enhancement that could be made but is definitely not something that can be done easily.