Reserved words are not quoted correctly

[izzudinhafiz]

When using PyAthena with Sqlalchemy, reserved keywords aren't being quoted correctly. The test case is also failing to catch this as it's compiling with the default dialect instead of PyAthena's Dialect

I have a minimum reproducible snippet here

from pyathena.sqlalchemy.base import AthenaDialect
from sqlalchemy import Column, MetaData, String, Table

dialect = AthenaDialect()

# this is from the test code
test_case = Table("select", MetaData(), Column("current_timestamp", String()))
should_quote = Table("select", MetaData(), Column("current_timestamp", String()))

test_case_query = str(test_case.select().where(test_case.c.current_timestamp == "a"))
should_quote_query = (
    should_quote.select()
    .where(should_quote.c.current_timestamp == "a")
    .compile(dialect=dialect)
    .string
)

assert '"select"' in test_case_query, test_case_query
assert '"select"' in should_quote_query, should_quote_query

This will fail the assertion on should_quote_query

The query from the test case outputs to

SELECT "select"."current_timestamp" 
FROM "select" 
WHERE "select"."current_timestamp" = :current_timestamp_1

Where as when using the dialect

SELECT select.current_timestamp 
FROM select 
WHERE select.current_timestamp = %(current_timestamp_1)s

The impacted test_case is here

class TestSQLAlchemyAthena:
    def test_reserved_words(self):
        """Presto uses double quotes, not backticks"""
        fake_table = Table("select", MetaData(), Column("current_timestamp", types.String()))
        query = str(fake_table.select().where(fake_table.c.current_timestamp == "a"))
        assert '"select"' in query
        assert '"current_timestamp"' in query
        assert "`select`" not in query
        assert "`current_timestamp`" not in query

I'll be opening a PR to fix this issue

[izzudinhafiz]

Issue coming from sqlalchemy.sql.compiler.IdentifierPreparer._requires_quote function which downcases the value before checking against the reserved_words

class IdentifierPreparer:
    def _requires_quotes(self, value: str) -> bool:
        """Return True if the given identifier requires quoting."""
        lc_value = value.lower()
        return (
            lc_value in self.reserved_words
            or value[0] in self.illegal_initial_characters
            or not self.legal_characters.match(str(value))
            or (lc_value != value)
        )

The reserved words in the default dialect are in lower case

# from sqlalchemy.sql.compiler
RESERVED_WORDS = {
    "all",
    "analyse",
    "analyze",
    ...
}

Whereas PyAthena is defining this in uppercase

# from pyathena.sqlalchemy.constants

DDL_RESERVED_WORDS: Set[str] = {
    "ALL",
    "ALTER",
    "AND",
    "ARRAY",
    "AS",
    ...
}

[laughingman7743]

Thank you for your contribution. For the test, please refer to the following documentation. (You must have an AWS account.)
https://laughingman7743.github.io/PyAthena/testing.html

I'll review the PR over the weekend.