FYI, it seems I was mistaken about the behaviour of '::' on Spark 4.0.1. It does indeed work like CAST on both DBR 17.3 and Spark 4.0.1 and raises an exception on '123X'::int. The '?::' operator seems to be a Databricks only extension at the moment (like '::' used to be).

In any case, it is not feasible to protect all possible sites where an implicit cast occurs with try_casts or by some other means, because there is no way of knowing where such conversions might occur. You will know only after you get an unexpected exception. And since there's no exception handling mechanism in SQL, there's no way to automatically recover from it and requires manual intervention to fix. And it can be extremely difficult to find where the exception occurred, since the error message doesn't say and since a LIMIT clause might make a failing query run just fine as the offending value might not occur in the limited dataset. Disabling spark.sql.ansi.enabled seems to be the only viable option.