SNOW-1232488: metadata reflection fails for case sensitive (lower/mixed) case objects

[michaelkwagner]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.9.9

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

   macOS-11.5.2-x86_64-i386-64bit

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

Relevant modules:

snowflake-connector-python==3.0.0
snowflake-sqlalchemy==1.4.6
SQLAlchemy==1.4.41

4. What did you do?

• Created database, schema, table with column names in uppercase / case insensitive
• Called:

metadata = MetaData(schema=schema)
metadata.reflect(bind=engine)

• Reflection executes as expected
• Created database, schema, table with column names in mixed/lower case / case sensitive
• Called:

metadata = MetaData(schema=schema)
metadata.reflect(bind=engine)

• Reflection fails (see output below)
• Also tried using quoted name attempting a way to have the reflect() method recognize the casing

Tests to recreate issue:
from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
from sqlalchemy.schema import MetaData
from sqlalchemy.sql.elements import quoted_name

account = "xxx"
user = "xxx"
password = "xxx"


def test_reflection_upper_case():
    """
    When database, schema, and table name are upper case / case insensitive
    reflection works.
    """
    validate_reflection(
        database="REFLECTION_UPPER_DB",
        schema="TEST_SCHEMA",
        table_name="TEST_TABLE",
        column_id="ID",
        name="NAME"
    )


def test_reflection_upper_case_quoted_name():
    """
    Using quoted_name to confirm works with upper case / case insensitive.
    Reflection works.

    This quoted_name test is included to verify that quoted_name, at worst, does not
    effect the validation. For tests below where reflection fails, a test using quoted_name is
    also run to attempt to see if reflection will work.
    """
    validate_reflection(
        database=quoted_name("REFLECTION_UPPER_DB", quote=True),
        schema=quoted_name("TEST_SCHEMA", quote=True),
        table_name="TEST_TABLE",
        column_id="ID",
        name="NAME"
    )


def test_reflection_mixed_case():
    """
    When database, schema, and table name are mixed case / case sensitive
    reflection does not work.

    Failing statement:
    metadata.reflect(bind=engine)
    """
    validate_reflection(
        database="Reflection_Mixed_Db",
        schema="Test_Schema",
        table_name="Test_Table",
        column_id="Id",
        name="Name"
    )


def test_reflection_mixed_case_quoted_name():
    """
    Tried using quoted_name to see if mixed case / case sensitive would work.
    It also fails on same statement:
    metadata.reflect(bind=engine)
    """
    validate_reflection(
        database=quoted_name("Reflection_Mixed_Db", quote=True),
        schema=quoted_name("Test_Schema", quote=True),
        table_name="Test_Table",
        column_id="Id",
        name="Name"
    )


def test_reflection_lower_case():
    """
    When database, schema, and table name are lower case / case sensitive
    reflection does not work.

    Failing statement:
    metadata.reflect(bind=engine)
    """
    validate_reflection(
        database="reflection_lower_db",
        schema="test_schema",
        table_name="test_table",
        column_id="id",
        name="name"
    )


def test_reflection_lower_case_quoted_name():
    """
    Tried using quoted_name to see if lower case / case sensitive would work.
    It also fails on same statement:
    metadata.reflect(bind=engine)
    """
    validate_reflection(
        database=quoted_name("reflection_lower_db", quote=True),
        schema=quoted_name("test_schema", quote=True),
        table_name="test_table",
        column_id="Id",
        name="Name"
    )


def validate_reflection(database, schema, table_name, column_id, name):
    url = URL(
        account=account,
        user=user,
        password=password)
    engine = create_engine(url=url)
    try:
        sql = f'CREATE DATABASE "{database}" '
        with engine.connect() as connection:
            connection.execute(sql)
            sql = f'CREATE SCHEMA "{database}"."{schema}" '
            connection.execute(sql)

            sql = (f'create table "{database}"."{schema}"."{table_name}" '
                   f'("{column_id}" int not null, '
                   f'"{name}" varchar(100));')
            connection.execute(sql)

            url = URL(
                account=account,
                user=user,
                password=password,
                database=database)
            engine = create_engine(url=url)

            metadata = MetaData(schema=schema)
            metadata.reflect(bind=engine)
    finally:
        with engine.connect() as connection:
            connection.execute(f'DROP DATABASE IF EXISTS "{database}"')

5. What did you expect to see?

metadata.reflect() works as expected for uppercase / case insensitive. However, fails for lower and mixed case / case sensitive. Would expect metadata.reflect() to work regardless of casing / case sensitivity.

Also, looked for a means to indicate case sensitivity, did not find one.

6. Can you set logging to DEBUG and collect the logs? Output from failing test:

connection = <snowflake.connector.connection.SnowflakeConnection object at 0x12b55c1c0>
cursor = <snowflake.connector.cursor.SnowflakeCursor object at 0x12b70cf70>
error_class = <class 'snowflake.connector.errors.ProgrammingError'>
error_value = {'done_format_msg': False, 'errno': 2043, 'msg': 'SQL compilation error:\nObject does not exist, or operation cannot be performed.', 'sfqid': '01aa4e30-0b04-2893-0000-adc1035551c2', ...}

    @staticmethod
    def default_errorhandler(
        connection: SnowflakeConnection,
        cursor: SnowflakeCursor,
        error_class: type[Error],
        error_value: dict[str, str],
    ) -> None:
        """Default error handler that raises an error.
    
        Args:
            connection: Connections in which the error happened.
            cursor: Cursor in which the error happened.
            error_class: Class of error that needs handling.
            error_value: A dictionary of the error details.
    
        Raises:
            A Snowflake error.
        """
>       raise error_class(
            msg=error_value.get("msg"),
            errno=error_value.get("errno"),
            sqlstate=error_value.get("sqlstate"),
            sfqid=error_value.get("sfqid"),
            done_format_msg=error_value.get("done_format_msg"),
            connection=connection,
            cursor=cursor,
        )
E       sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002043 (02000): SQL compilation error:
E       Object does not exist, or operation cannot be performed.
E       [SQL: SHOW /* sqlalchemy:get_table_names */ TABLES IN "Test_Schema"]
E       (Background on this error at: https://sqlalche.me/e/14/f405)

../../env/lib/python3.9/site-packages/snowflake/connector/errors.py:209: ProgrammingError

[cpcloud]

Check out https://github.com/ibis-project/ibis/pull/5741 for a possible workaround.

[sfc-gh-dszmolka]

hi folks - apologies for leaving this unanswered for so long; we're changing that going forward. for now, possibly

• https://github.com/snowflakedb/snowflake-sqlalchemy/issues/157
• https://github.com/snowflakedb/snowflake-sqlalchemy/issues/276
• https://github.com/snowflakedb/snowflake-sqlalchemy/issues/388

might be all originating from the same gap in snowflake-sqlalchemy. At this time, I cannot promise any timeline for taking care of this, but rest assured we're aware of the issue and i'll keep this thread posted.