It appears that without the CAST to VARCHAR(1000) there is some sort of assumption about the length of the result.

So if there is an explicit CAST that assumption is dropped and the query works.