Option to always emit NULLS FIRST / NULLS LAST for an Ordered node #8904

Is your feature request related to a problem? Please describe.

We build ORDER BY clauses as exp.Order / exp.Ordered nodes and render them for Snowflake, Databricks and BigQuery. When a sort field carries an explicit null placement, we want it to reach the warehouse as written. The generator instead leaves out a NULLS FIRST / NULLS LAST clause whenever it matches the dialect's NULL_ORDERING:

import sqlglot
from sqlglot import exp

print(sqlglot.__version__)
col = exp.column("c")
for desc in (False, True):
    for nulls_first in (False, True):
        node = exp.Order(expressions=[exp.Ordered(this=col.copy(), desc=desc, nulls_first=nulls_first)])
        print(f"desc={desc!s:5} nulls_first={nulls_first!s:5} ->", node.sql(dialect="snowflake"))

30.29.0
desc=False nulls_first=False -> ORDER BY c ASC
desc=False nulls_first=True  -> ORDER BY c ASC NULLS FIRST
desc=True  nulls_first=False -> ORDER BY c DESC NULLS LAST
desc=True  nulls_first=True  -> ORDER BY c DESC

That is correct for a warehouse that always uses the default sqlglot assumes, but Snowflake lets an account or session change it with the DEFAULT_NULL_ORDERING parameter. On an account set to FIRST, the first and last lines above sort the opposite way from what the AST says: nulls_first=False on an ascending sort returns NULLs first, and nulls_first=True on a descending sort returns them last. The SQL text no longer carries the intent, and the generator has no way to know the session's setting.

The same applies to any tool that generates SQL for someone else's warehouse and wants the result order to be independent of server-side defaults.

Describe the solution you'd like

A generator option, alongside identify and normalize, that always writes the null placement an Ordered node carries when nulls_first is set, for dialects whose NULL_ORDERING_SUPPORTED is True. For example:

node.sql(dialect="snowflake", explicit_null_ordering=True)
# desc=False nulls_first=False -> ORDER BY c ASC NULLS LAST
# desc=True  nulls_first=True  -> ORDER BY c DESC NULLS FIRST

The default stays as today, so existing output is unchanged.

Describe alternatives you've considered

• Setting NULL_ORDERING on a dialect subclass: no value makes the generator write both clauses (nulls_are_last writes NULLS FIRST but never NULLS LAST), and it means maintaining a subclass per dialect.
• Overriding ordered_sql in a generator subclass per dialect: works, but duplicates the window-function and simulation logic in ordered_sql.
• Rendering the direction and NULLS clause as text after the generated sort key: works, but gives up the AST for that part of the clause.

Additional context

The decision to omit is in Generator.ordered_sql, which compares nulls_first with self.dialect.NULL_ORDERING. Happy to open a PR if the option shape works for you.

Drafted with Claude; the output above was produced by running the snippet.