SNOW-1232488: Table reflection methods using .format do not respect quoted_name

machow commented 2 years ago

Hello--thanks for your work on this library! I maintain a tool called siuba that can execute SQL analyses across many different databases (using sqlalchemy). While working on snowflake support, I noticed that table reflection often does not work for case sensitive identifiers, even if they're quoted.

Below is an example that I think shows the root cause: queries in the dialect that are built using .format, do not perform the necessary escaping of quoted_name objects.

Example

from snowflake.sqlalchemy import __version__
from snowflake.sqlalchemy.snowdialect import SnowflakeDialect

__version__    # 1.3.3
dialect = SnowflakeDialect()

# far so good
nn_schema = dialect.normalize_name("some_schema")
type(nn_schema)   # sqlalchemy.sql.elements.quoted_name
nn_schema.quote  # True

# Does not correctly quote schema name
"SHOW TABLES IN SCHEMA {}".format(nn_schema)

# output is 'SHOW TABLES IN SCHEMA some_schema'

What did you expect to see?

SHOW TABLES IN SCHEMA "some_schema"

What did you see instead?

SHOW TABLES IN SCHEMA some_schema

(and an error because SOME_SCHEMA does not exist)

cpcloud commented 1 year ago

@machow We worked around this broken behavior in ibis in https://github.com/ibis-project/ibis/pull/5741 by effectively monkey patching dialect.normalize_name.

Yeah, it's hack, but it's not clear if anyone is fixing bugs in this project so feel free to crib from the above as needed.

sfc-gh-dszmolka commented 5 months ago

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

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.

snowflakedb / snowflake-sqlalchemy