SQLAlchemy compliance: restore CTE test coverage

[laughingman7743]

Context

Parent issue: #736

The dialect reports ctes as supported but deletes the complete CTETest class.
Athena supports basic WITH queries, while recursive CTEs and CTEs combined with DML may differ.

Scope

• Run the SQLAlchemy CTE compliance tests against Athena engine v3.
• Re-enable the complete class and gate only unsupported variants.
• Resolve any compiler issue required for supported SELECT CTE behavior.
• Keep recursive or DML-specific limitations explicit.

Acceptance criteria

• Basic supported CTE tests run in the compliance suite.
• Each remaining exclusion names the Athena limitation it represents.
• Compiler regression tests cover any SQL rendering change.
• just lint passes.
• Athena tests are limited to CTETest nodes and run with -n 1.