Error message not found when I'm trying to run custom SQL on Tableau Desktop using AWS Athena Query which runs fine on Athena.

Such an error can be caused by various reasons, for example old JDBC driver version or incorrect JDBC connection properties.

Can you try following steps and see if they help:

1. Make sure download and use the latest "JDBC driver with AWS SDK", you can download the driver from link https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html
2. If the issue persists with the newest driver, please enable JDBC driver debug logging. Please refer to https://kb.tableau.com/articles/HowTo/how-to-enable-driver-logging-for-amazon-athena-using-a-properties-file
3. Restart Tableau and rerun the problematic query to generate driver logs. Check the logs for any specific stacktrace error messages, which normally indicates the root cause of the issue. If you need help to further troubleshoot the driver logs, please open a support ticket from AWS console. Please avoid sharing raw logs in public posts because JDBC driver logs might contain sensitive information of your account.
4. Besides that, I'd suggest you to check this similar thread in Tableau forum: https://community.tableau.com/s/question/0D58b00009zBZgaCAG/athenaclienterror , it mentioned another possible cause related to local proxy server (if you have one), which can be overcome by specifying NonProxyHosts=*.amazonaws.com in Tableau properties file.