Rendering alias in a subquery is resulting in error

[prdpsvs]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Which packages are affected?

• dbt-adapters
• dbt-tests-adapter
• dbt-athena
• dbt-athena-community
• dbt-bigquery
• dbt-postgres
• dbt-redshift
• dbt-snowflake
• dbt-spark

Current Behavior

It looks like subquery requires alias. https://github.com/dbt-labs/dbt-adapters/blob/999dd7fd8238259538e271938cb5d1fc7e65c335/dbt-adapters/src/dbt/adapters/base/relation.py#L225C17-L225C30

Although, it is made optional, can it be controlled by an adapter?
Please see the issue from a user using dbt-fabric adapter, microsoft/dbt-fabric#255

Expected Behavior

Adapter should able to control subquery alias.

Steps To Reproduce

microsoft/dbt-fabric#255

Relevant log output

Environment

- OS: Windows
- Python: > 3.8
- dbt-adapters: 1.11.0
- <adapter>: dbt-fabric 1.9.0

Additional Context

No response