SNOW-356205: lowercase table names don't work

[radix]

1. What version of Python are you using (python --version)?

3.7.10

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')?

Linux-4.19.128-microsoft-standard-x86_64-with-Ubuntu-20.04-focal

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

alabaster                     0.7.12
aniso8601                     7.0.0
appdirs                       1.4.4
asn1crypto                    1.4.0
attrs                         18.2.0
awscli                        1.19.71
azure-common                  1.1.26
azure-core                    1.12.0
azure-storage-blob            12.8.0
Babel                         2.9.1
bleach                        3.3.0
boto                          2.48.0
boto3                         1.17.71
botocore                      1.20.71
bump2version                  1.0.1
cachetools                    3.1.0
certifi                       2020.12.5
cffi                          1.14.5
cfgv                          3.2.0
chardet                       3.0.4
click                         7.1.2
colorama                      0.4.0
CommonMark                    0.5.4
coverage                      4.4.2
credstash                     1.17.1
cryptography                  3.4.7
datadiff                      1.1.5
dateparser                    1.0.0
decorator                     4.4.2
defusedxml                    0.7.1
dirsync                       2.2.3
distlib                       0.3.1
Django                        2.2.20
django-amazon-ses             2.1.1
django-braces                 1.14.0
django-cors-headers           3.2.1
django-crispy-forms           1.9.2
django-defender               0.6.2
django-enumfields             2.0.0
django-extensions             3.0.9
django-extra-views            0.7.1
django-filter                 2.2.0
django-health-check           3.12.1
django-imgix                  1.1.0
django-ipware                 2.1.0
django-jet                    1.0.8
django-model-utils            3.1.2
django-nose                   1.4.6
django-password-reset         2.0
django-redis-cache            2.1.1
django-registration-redux     2.9
django-session-security       2.6.6
django-storages               1.9.1
django-test-migrations        0.2.0
django-user-sessions          1.7.1
django-waffle                 2.1.0
djangorestframework           3.12.4
docutils                      0.15.2
dogpile.cache                 0.9.2
drf-jwt                       1.18.0    /venv/src/drf-jwt/src
et-xmlfile                    1.0.1
factory-boy                   2.12.0
Faker                         2.0.5
filelock                      3.0.12
flake8                        3.4.1
future                        0.18.2
geopatterns                   0.0.2     /venv/src/geopatterns
google-api-core               1.26.2
google-auth                   1.28.0
google-cloud-bigquery         1.18.0
google-cloud-core             1.6.0
google-crc32c                 1.1.2
google-resumable-media        1.2.0
googleapis-common-protos      1.53.0
graphene                      2.1.8
graphene-django               2.11.1
graphql-core                  2.3.2
graphql-relay                 2.0.1
hiredis                       0.2.0
identify                      2.2.4
idna                          2.10
imagesize                     1.2.0
imgix                         2.3.0
importlib-metadata            4.0.1
isodate                       0.6.0
jdcal                         1.4.1
Jinja2                        2.11.3
jira                          2.0.0
jmespath                      0.10.0
joblib                        1.0.1
lark-parser                   0.11.2
libsass                       0.10.1
MarkupSafe                    1.1.1
mccabe                        0.6.1
mistune                       2.0.0a4
mock                          1.0.1
more-itertools                8.7.0
msrest                        0.6.21
mysql-connector-python        8.0.23
nltk                          3.5
nodeenv                       1.6.0
nose                          1.3.7     /venv/src/nose/build/lib
numpy                         1.20.2
oauthlib                      3.1.0
openpyxl                      2.4.11
ordered-set                   4.0.2
oscrypto                      1.2.1
packaging                     20.9
pandas                        1.2.4
pbr                           5.5.1
Pillow                        8.1.2
pip                           21.0.1
pip-tools                     5.5.0
pluggy                        0.13.1
pre-commit                    2.12.1
promise                       2.3
protobuf                      3.15.6
psutil                        5.7.3
psycopg2                      2.8.6
py                            1.10.0
pyasn1                        0.4.8
pyasn1-modules                0.2.8
PyAthena                      1.4.3
pybigquery                    0.4.10
pycodestyle                   2.3.1
pycparser                     2.20
pycryptodomex                 3.10.1
pyflakes                      1.5.0
PyGithub                      1.37
Pygments                      2.1.3
PyJWT                         2.0.1
pyodbc                        3.0.10
pyOpenSSL                     19.1.0
pyparsing                     2.4.7
pytest                        5.4.3
python-dateutil               2.8.1
python3-openid                3.2.0
pytz                          2021.1
PyYAML                        5.4.1
recipe                        0.23.2
recipe-caching                0.6.0
recommonmark                  0.4.0
redis                         2.10.3
regex                         2021.3.17
requests                      2.23.0
requests-oauthlib             1.3.0
requests-toolbelt             0.9.1
rsa                           4.7.2
rules                         2.0.1
Rx                            1.6.1
s3transfer                    0.4.2
secretstash                   0.1.0     /venv/src/secretstash
sentry-sdk                    0.19.5
setuptools                    56.0.0
singledispatch                3.6.1
six                           1.15.0
snowballstemmer               2.1.0
snowflake-connector-python    2.4.3
snowflake-sqlalchemy          1.2.4
social-auth-app-django        4.0.0
social-auth-core              4.1.0
sorl-thumbnail                12.5.0
Sphinx                        1.7.5
sphinx-rtd-theme              0.4.0
sphinxcontrib-serializinghtml 1.1.4
sphinxcontrib-websupport      1.2.4
SQLAlchemy                    1.3.23
sqlalchemy-redshift           0.8.2
sqlparse                      0.3.1
stevedore                     3.3.0
stripe                        2.50.0
structlog                     19.2.0
structlog-sentry              1.2.2
sureberus                     0.14.0
tablib                        3.0.0
tenacity                      7.0.0
text-unidecode                1.3
textblob                      0.15.3
tinycss                       0.3
toml                          0.10.2
tqdm                          4.59.0
typing-extensions             3.7.4.3
tzlocal                       2.1
Unidecode                     1.2.0
urllib3                       1.25.11
uWSGI                         2.0.19.1
virtualenv                    20.4.4
wcwidth                       0.2.5
webencodings                  0.5.1
wheel                         0.36.2

4. What did you do?

I tried doing a trivial query against a database that has lowercase table names.

Here's a reproduction script, trying to query article.article_id.

from sqlalchemy import MetaData, Table, create_engine, select

eng = create_engine("snowflake://...")

meta = MetaData()
article = Table("article", meta, autoload_with=eng)

# it's doing *some* things right, like it clearly got the columns:

print("Columns:")
print(list(article.c)) # this prints a list of columns that it clearly introspected properly, including `article_id`.

# but this is blowing up:
eng.execute(select([article.c.article_id]).limit(1))

# with an error like:
# sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002003 (42S02): SQL compilation error:
# Object 'ARTICLE' does not exist or not authorized.
# [SQL: SELECT article."article_id"
# FROM article
#  LIMIT %(param_1)s]
# [parameters: {'param_1': 1}]
# (Background on this error at: http://sqlalche.me/e/13/f405)

5. What did you expect to see?

I expected to get a row of results

6. What did you see instead?

I got an error about about "ARTICLE" not existing, especially given that it was able to introspect that table correctly.

7. Can you set logging to DEBUG and collect the logs?

This didn't really give any more useful information as far as I can tell.

[radix]

If anyone has any suggestions for how to work around this issue and interact with a table with a lowercase name, I would appreciate it.

[radix]

I discovered that I can pass quote=True when constructing a Table instance.

[sfc-gh-hkapre]

Using this parameter should be the correct way to force case sensitivity. Since the object name is lowercase on the Snowflake side, quotes need to be used to force the identifier to be treated as lower case. Otherwise, Snowflake will treat the identifier as upper case (which is the default).