`Ordered` without `nulls_first` set should not be treated as if it's set to `False`

[stnguyen]

sqlglot/sqlglot/generator.py

Line 2608 in 7d485c7

nulls_last = not nulls_first

Shouldn't we check if nulls_first is None then nulls_last is also None instead of True?

The problem can be seen with Databricks dialect:

In [1]: sqlglot.select('c1').from_('t1').order_by(sqlglot.exp.Ordered(this='c1')).sql()
Out[1]: 'SELECT c1 FROM t1 ORDER BY c1 NULLS LAST'

[georgesittas]

This behavior is expected, you should rely on the builders to build the ASTs or ensure you respect what the Parser emits. All of these variants work, for example:

>>> sqlglot.select('c1').from_('t1').order_by("c1", dialect="databricks").sql(dialect="databricks")
'SELECT c1 FROM t1 ORDER BY c1'
>>> sqlglot.select('c1').from_('t1').order_by("c1", dialect="databricks")
Select(
  expressions=[
    Column(
      this=Identifier(this=c1, quoted=False))],
  from_=From(
    this=Table(
      this=Identifier(this=t1, quoted=False))),
  order=Order(
    expressions=[
      Ordered(
        this=Column(
          this=Identifier(this=c1, quoted=False)),
        nulls_first=True)]))
>>> sqlglot.select('c1').from_('t1').order_by("c1").sql()
'SELECT c1 FROM t1 ORDER BY c1'

[stnguyen]

sorry I don't understand how is this expected? looks like depends on dialect and ASC/DESC it still adds NULLS FIRST/LAST:

In [46]: sqlglot.select('c1').from_('t1').order_by("c1").sql(dialect='clickhouse')
Out[46]: 'SELECT c1 FROM t1 ORDER BY c1 NULLS FIRST'

In [47]: sqlglot.select('c1').from_('t1').order_by("c1 DESC").sql(dialect='clickhouse')
Out[47]: 'SELECT c1 FROM t1 ORDER BY c1 DESC'


In [48]: sqlglot.select('c1').from_('t1').order_by("c1").sql(dialect='snowflake')
Out[48]: 'SELECT c1 FROM t1 ORDER BY c1 NULLS FIRST'

In [49]: sqlglot.select('c1').from_('t1').order_by("c1 DESC").sql(dialect='snowflake')
Out[49]: 'SELECT c1 FROM t1 ORDER BY c1 DESC NULLS LAST'


In [50]: sqlglot.select('c1').from_('t1').order_by("c1").sql(dialect='duckdb')
Out[50]: 'SELECT c1 FROM t1 ORDER BY c1 NULLS FIRST'

In [51]: sqlglot.select('c1').from_('t1').order_by("c1 DESC").sql(dialect='duckdb')
Out[51]: 'SELECT c1 FROM t1 ORDER BY c1 DESC'

[georgesittas]

You need to set the dialect in order_by, it expects it as a kwarg. Otherwise the parsed "orderable" won't be dialect-aware to set the appropriate value to the null ordering child argument.

[stnguyen]

thank you!