Percent in string literal incorrectly double encoded

[coltnz]

Describe the bug

Percent in string literal incorrectly double encoded with sqlachemy interface.

Steps to reproduce

1. Make a sql call via sqlalchemy query with a 'Format' in a literal string e.g. SELECT formatDateTime(toDate('2010-01-04'), '%g')

Result:

┌─formatDateTime(toDate('2010-01-04'), '%%g')─┐
│ %g                                          │
└─────────────────────────────────────────────┘

Expected behaviour

┌─formatDateTime(toDate('2010-01-04'), '%g')─┐
│ 10                                         │
└────────────────────────────────────────────┘

Configuration

On a slightly older version, don't see the necessary fix on master.

Analysis

Various places in code has:

        if self.preparer._double_percents:
            text = text.replace("%", "%%")
        return text

class ChIdentifierPreparer(IdentifierPreparer)

doesn't override the assignment in its base class:

        self._double_percents = self.dialect.paramstyle in (
            "format",
            "pyformat",
        )

Workaround

I monkey patched:

original_init = ChIdentifierPreparer.__init__

def new_init(self, *args, **kwargs):
    original_init(self, *args, **kwargs)
    self._double_percents = False

ChIdentifierPreparer.__init__ = new_init

[genzgd]

Thanks for the analysis and including a workaround!

I think you're the first person to open an issue about SQLAlchemy that's not related to Superset. I'd honestly recommend against trying to use clickhouse-connect SQLAlchemy for anything but Superset, since support is incomplete and tests are minimal. Nevertheless it's possible this will get fixed when we take a look at SQLAlchemy 2.0 support.

[coltnz]

Streamlit uses sqlachemy for its caching so don't think switching is an easy option unfortunately.

[genzgd]

Thanks for the context -- I don't see sqlalchemy as a requirement for the latest streamlit install, do you happen to know if it's still required or what else I'm missing? That would help us decide the priority in fixing this.

[coltnz]

From https://docs.streamlit.io/library/advanced-features/connecting-to-data

Step 1: Install prerequisite library - SQLAlchemy All SQLConnections in Streamlit use SQLAlchemy.

[genzgd]

Thanks for the response! We'll take a look at Streamlit usage when determining the priorities for SQLAlchemy improvements and bug fixes!