Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
csdev opened this issue May 12, 2022 · 1 comment · May be fixed by #394
Closed

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

csdev opened this issue May 12, 2022 · 1 comment · May be fixed by #394
Labels
bug Something isn't working needs triage Stale

Comments

@csdev
Copy link

csdev commented May 12, 2022

  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]
  1. 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')
  1. What did you expect to see?
  2. 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.
@csdev csdev added bug Something isn't working needs triage labels May 12, 2022
@github-actions github-actions bot changed the title sqlalchemy.func.sysdate() does not render parentheses SNOW-589556: sqlalchemy.func.sysdate() does not render parentheses May 12, 2022
@sjhewitt sjhewitt mentioned this issue Mar 15, 2023
Open
4 tasks
@github-actions github-actions bot added the Stale label Apr 5, 2023
@github-actions
Copy link

github-actions bot commented Apr 5, 2023

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

@github-actions github-actions bot closed this as not planned Won't fix, can't repro, duplicate, stale Apr 5, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working needs triage Stale
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

render func.sysdate as SYSDATE() sjhewitt/snowflake-sqlalchemy
1 participant
@csdev