One piece of information to be aware of. With Custom SQL, Tableau is wrapping the entire contents of the Custom SQL into a Select * statement to prevent a user from doing things like Drop in a statement.
Hopefully that helps a bit too.
Patrick is right, Custom SQL is wrapped in a Select statement.
I don't know much about SQL, but it may be that in PL there is exception handling for results that do not match the correct data type. The message you are receiving seems to indicate that because of the Select * wrapper, Tableau may not handle exceptions in the same way.
Consider maybe using CAST to force the datatype to return correctly.