Move unary diagnostic logic from sql planner to analyzer

This checking is normally done as part of analysis (not the sql planner) (aka in an Analyzer rule) -- if we put it in sql planning then it won't apply to queries that don't come from SQL.

_Originally posted by @alamb in https://github.com/apache/datafusion/pull/21288#discussion_r3030504399_

Locate the unary diagnostic checking currently implemented in the SQL planner and trace the existing Analyzer rules and tests around it. Move the responsibility into analysis so the diagnostics also apply to queries that do not originate from SQL, then run the affected test suite to confirm both SQL and non-SQL paths behave consistently.

Written by the indexing model from the issue text.