It looks like the setting:
spark.sql.ansi.enabled true
Must be set on non-serverless job/non SQL warehouse clusters for the behaviour to be consistent. This is on by default in those clusters, but not others.