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 ibis-project/ibis#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

• SNOW-1232488 Column names are converted to lowercase #157
• SNOW-1232488: Table reflection methods using .format do not respect quoted_name #276
• SNOW-1232488: metadata reflection fails for case sensitive (lower/mixed) case objects #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.

[tboddyspargo]

I saw what I believe is a flavor of this (cursor doesn't support parameter binding with quoted_name) when using an all uppercase schema name (without quotes) in inspector.reflect_table.

Traceback (most recent call last):
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1016, in execute
    kwargs["binding_params"] = self._connection._process_params_qmarks(
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/connection.py", line 1480, in _process_params_qmarks
    snowflake_type, snowflake_binding = get_type_and_binding(v)
                                        ^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/connection.py", line 1452, in _get_snowflake_type_and_binding
    self.converter.to_snowflake_bindings(snowflake_type, v),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/converter.py", line 367, in to_snowflake_bindings
    return getattr(self, f"_{type_name}_to_snowflake_bindings")(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/converter.py", line 660, in __getattr__
    raise ProgrammingError(
snowflake.connector.errors.ProgrammingError: 255001: 255001: Binding data in type (quoted_name) is not supported.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 774, in reflect_table
    for col_d in self.get_columns(
                 ^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 497, in get_columns
    col_defs = self.dialect.get_columns(
               ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/sqlalchemy/snowdialect.py", line 668, in get_columns
    schema_columns = self._get_schema_columns(connection, schema, **kw)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in _get_schema_columns
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
    ret = fn(self, con, *args, **kw)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/sqlalchemy/snowdialect.py", line 481, in _get_schema_columns
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "../.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1016, in execute
    kwargs["binding_params"] = self._connection._process_params_qmarks(
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/connection.py", line 1480, in _process_params_qmarks
    snowflake_type, snowflake_binding = get_type_and_binding(v)
                                        ^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/connection.py", line 1452, in _get_snowflake_type_and_binding
    self.converter.to_snowflake_bindings(snowflake_type, v),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/converter.py", line 367, in to_snowflake_bindings
    return getattr(self, f"_{type_name}_to_snowflake_bindings")(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "../.venv/lib/python3.11/site-packages/snowflake/connector/converter.py", line 660, in __getattr__
    raise ProgrammingError(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 255001: 255001: Binding data in type (quoted_name) is not supported.
[SQL: 
            SELECT /* sqlalchemy:_get_schema_columns */
                   ic.table_name,
                   ic.column_name,
                   ic.data_type,
                   ic.character_maximum_length,
                   ic.numeric_precision,
                   ic.numeric_scale,
                   ic.is_nullable,
                   ic.column_default,
                   ic.is_identity,
                   ic.comment,
                   ic.identity_start,
                   ic.identity_increment
              FROM information_schema.columns ic
             WHERE ic.table_schema=?
             ORDER BY ic.ordinal_position]
[parameters: ('PUBLIC',)]
(Background on this error at: https://sqlalche.me/e/14/f405)