SNOW-589556: sqlalchemy.func.sysdate() does not render parentheses

[csdev]

1. What version of Python are you using?

   Python 3.7.13

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

   Linux-5.4.0-109-generic-x86_64-with-debian-10.12

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

Note: our environment has a lot of packages installed, so I dumped the relevant parts using pipdeptree:

alembic==1.7.7
  - importlib-metadata [required: Any, installed: 4.2.0]
    - typing-extensions [required: >=3.6.4, installed: 4.1.1]
    - zipp [required: >=0.5, installed: 3.7.0]
  - importlib-resources [required: Any, installed: 5.6.0]
    - zipp [required: >=3.1.0, installed: 3.7.0]
  - Mako [required: Any, installed: 1.2.0]
    - importlib-metadata [required: Any, installed: 4.2.0]
      - typing-extensions [required: >=3.6.4, installed: 4.1.1]
      - zipp [required: >=0.5, installed: 3.7.0]
    - MarkupSafe [required: >=0.9.2, installed: 2.0.1]
  - SQLAlchemy [required: >=1.3.0, installed: 1.3.24]
snowflake-sqlalchemy==1.2.4
  - snowflake-connector-python [required: <3.0.0, installed: 2.7.6]
    - asn1crypto [required: >0.24.0,<2.0.0, installed: 1.5.1]
    - certifi [required: >=2017.4.17, installed: 2021.10.8]
    - cffi [required: >=1.9,<2.0.0, installed: 1.15.0]
      - pycparser [required: Any, installed: 2.21]
    - charset-normalizer [required: ~=2.0.0, installed: 2.0.12]
    - cryptography [required: >=3.1.0,<37.0.0, installed: 36.0.2]
      - cffi [required: >=1.12, installed: 1.15.0]
        - pycparser [required: Any, installed: 2.21]
    - idna [required: >=2.5,<4, installed: 3.3]
    - oscrypto [required: <2.0.0, installed: 1.3.0]
      - asn1crypto [required: >=1.5.1, installed: 1.5.1]
    - pycryptodomex [required: >=3.2,<4.0.0,!=3.5.0, installed: 3.14.1]
    - pyjwt [required: <3.0.0, installed: 1.7.1]
    - pyOpenSSL [required: >=16.2.0,<22.0.0, installed: 21.0.0]
      - cryptography [required: >=3.3, installed: 36.0.2]
        - cffi [required: >=1.12, installed: 1.15.0]
          - pycparser [required: Any, installed: 2.21]
      - six [required: >=1.5.2, installed: 1.16.0]
    - pytz [required: Any, installed: 2022.1]
    - requests [required: <3.0.0, installed: 2.27.1]
      - certifi [required: >=2017.4.17, installed: 2021.10.8]
      - charset-normalizer [required: ~=2.0.0, installed: 2.0.12]
      - idna [required: >=2.5,<4, installed: 3.3]
      - urllib3 [required: >=1.21.1,<1.27, installed: 1.26.9]
    - setuptools [required: >34.0.0, installed: 57.5.0]
  - sqlalchemy [required: <2.0.0, installed: 1.3.24]

4. What did you do?

Use sqlalchemy.func.sysdate in a query or table definition. For example, here is an alembic migration that creates a column with sysdate() as a default value:

revision = '4f93cae7a47b'
down_revision = '57e74ce2b6a3'

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'test_table',

        sa.Column('created', sa.DateTime, server_default=sa.func.sysdate(), nullable=False)
    )

def downgrade():
    op.drop_table('test_table')

5. What did you expect to see?
6. Can you set logging to DEBUG and collect the logs?

The generated SQL calls sysdate without the parentheses. This leads to an error because sysdate requires the parentheses (documentation).

alembic -c alembic/alembic.ini -n alembic_stats upgrade 57e74ce2b6a3:4f93cae7a47b --sql

2022-05-12T17:12:24Z DEBUG    snowflake.connector.ssl_wrap_socket Injecting ssl_wrap_socket_with_ocsp
2022-05-12T17:12:24Z DEBUG    snowflake.connector.auth cache directory: /root/.cache/snowflake
2022-05-12T17:12:24Z DEBUG    snowflake.connector.cursor Failed to import pyarrow. Cannot use pandas fetch API
2022-05-12T17:12:24Z INFO     alembic.runtime.migration Context impl SnowflakeImpl.
2022-05-12T17:12:24Z INFO     alembic.runtime.migration Generating static SQL
2022-05-12T17:12:24Z INFO     alembic.runtime.migration Will assume non-transactional DDL.
2022-05-12T17:12:24Z INFO     alembic.runtime.migration Running upgrade 57e74ce2b6a3 -> 4f93cae7a47b, create table
-- Running upgrade 57e74ce2b6a3 -> 4f93cae7a47b

CREATE TABLE test_table (
    created datetime NOT NULL DEFAULT sysdate
);

UPDATE alembic_version SET version_num='4f93cae7a47b' WHERE alembic_version.version_num = '57e74ce2b6a3';

This occurs because SQLAlchemy implements sysdate as an AnsiFunction. Based on this discussion with a SQLAlchemy maintainer, the dialect should implement an override with the correct behavior.

[github-actions]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response