SNOW-1637096: When using order_by, the resulting query does not use the correct defaults for NULL [FIRST | LAST] as per the docs

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.10.4 (main, May 26 2022, 13:33:07) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)

2. What operating system and processor architecture are you using?

   Linux-3.10.0-1127.19.1.el7.x86_64-x86_64-with-glibc2.17

3. What are the component versions in the environment (pip freeze)?

   asn1crypto==1.5.1
   astroid==2.15.4
   asttokens==2.4.0
   async-timeout==4.0.2
   backcall==0.2.0
   boto3==1.26.126
   botocore==1.29.126
   cachetools==5.3.3
   certifi==2022.12.7
   cffi==1.15.1
   charset-normalizer==3.1.0
   click==7.1.2
   cloudpickle==2.2.1
   colorama==0.4.6
   comm==0.1.4
   coverage==7.2.5
   cryptography==3.4.8
   debugpy==1.8.0
   decorator==5.1.1
   Deprecated==1.2.13
   dill==0.3.6
   exceptiongroup==1.1.1
   executing==2.0.0
   factory-boy==3.3.0
   Faker==25.8.0
   filelock==3.15.1
   frozendict==2.4.4
   greenlet==2.0.2
   hvac==2.2.0
   idna==3.4
   iniconfig==2.0.0
   ipykernel==6.25.2
   ipython==8.16.1
   isort==5.12.0
   jedi==0.19.1
   jmespath==1.0.1
   jupyter_client==8.3.1
   jupyter_core==5.3.2
   lazy-object-proxy==1.9.0
   lxml==4.9.2
   Mako==1.2.4
   MarkupSafe==2.0.1
   marshmallow_dataclass==7.6.0
   marshmallow-enum==1.5.1
   marshmallow==3.21.3
   matplotlib-inline==0.1.6
   mccabe==0.7.0
   multimethod==1.10
   mypy-extensions==1.0.0
   mypy==1.2.0
   nest-asyncio==1.5.8
   numpy==1.24.3
   oauthlib==3.2.2
   packaging==23.1
   pandas==2.2.2
   parso==0.8.3
   pexpect==4.8.0
   pickleshare==0.7.5
   platformdirs==3.5.0
   pluggy==1.0.0
   prompt-toolkit==3.0.39
   psutil==5.9.5
   ptyprocess==0.7.0
   pure-eval==0.2.2
   pyarrow==10.0.1
   pycparser==2.21
   pydantic==1.10.13
   Pygments==2.16.1
   PyJWT==2.0.1
   pylint==2.17.3
   pyOpenSSL==21.0.0
   pytest-cov==2.12.1
   pytest-mock==2.0.0
   pytest==7.3.1
   python-dateutil==2.8.2
   python-gnupg==0.5.0
   python-json-logger==2.0.7
   pytz==2021.3
   PyYAML==6.0.1
   pyzmq==25.1.1
   redis==4.5.4
   requests-oauthlib==1.3.1
   requests==2.29.0
   s3transfer==0.6.0
   six==1.16.0
   snowflake-connector-python==3.12.1
   snowflake-snowpark-python==1.18.0
   sortedcontainers==2.4.0
   stack-data==0.6.3
   stringcase==1.2.0
   structlog==21.5.0
   tabulate==0.9.0
   tenacity==8.2.3
   toml==0.10.2
   tomli==2.0.1
   tomlkit==0.11.8
   tornado==6.3.3
   traitlets==5.11.1
   typeguard==4.0.0
   types-click==7.1.8
   types-python-dateutil==2.8.19.12
   types-requests==2.29.0.0
   types-tabulate==0.9.0.20240106
   types-urllib3==1.26.25.12
   typing_extensions==4.5.0
   typing-inspect==0.8.0
   tzdata==2024.1
   urllib3==1.26.15
   wcwidth==0.2.8
   Werkzeug==1.0.1
   wrapt==1.15.0

4. What did you do?
   -- this query generates: SELECT "A", "B" FROM TEST_TABLE ORDER BY "A" ASC NULLS FIRST
   asc_query = session.table("TEST_TABLE").select("A", "B").order_by(col("A").asc())

   -- this query generates: SELECT "A", "B" FROM TEST_TABLE ORDER BY "A" DESC NULLS LAST
   desc_query = session.table("TEST_TABLE").select("A", "B").order_by(col("a").desc())

5. What did you expect to see?
   I expected the ASC sort to either use no option for the ordering of null values or use NULLS LAST to keep the default sorting semantics of Snowflake.

I expected the DESC sort to either use no option for the ordering of null values or use NULLS LAST to keep the default sorting semantics of Snowflake.

Expectations based on the docs: Order By

• Unless specified otherwise, NULL values are considered to be higher than any non-NULL values. As a result, the ordering for NULLS depends on the sort order:
  * If the sort order is ASC, NULLS are returned last; to force NULLS to be first, use NULLS FIRST.
  * If the sort order is DESC, NULLS are returned first; to force NULLS to be last, use NULLS LAST.