clickhouse-sqlalchemy icon indicating copy to clipboard operation
clickhouse-sqlalchemy copied to clipboard

'inherit_cache' attribute warning when executing a query

Open AviSarmani opened this issue 7 months ago • 1 comments

To Reproduce In database:

CREATE TABLE test_01
( `first_col` String)
ENGINE = Log;
CREATE TABLE test_02
( `first_col` String)
ENGINE = Log;

Code:

from clickhouse_sqlalchemy import Table
from clickhouse_sqlalchemy import select as chselect

test_01 = Table(
    "test_01",
    metadata,
    autoload_with=engine,
)

test_02 = Table(
    "test_02",
    metadata,
    autoload_with=engine,
)

sel = (
    chselect(test_01.c.first_col)
    .select_from(test_01.join(test_02, test_01.c.first_col == test_02.c.first_col))
    .limit(1)

connection = engine.connect()
result = connection.execute(sel)

This gives the following warning: SAWarning: Class Select will not make use of SQL compilation caching as it does not set the 'inherit_cache' attribute to True. This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions. Set this attribute to True if this object can make use of the cache key generated by the superclass. Alternatively, this attribute may be set to False which will disable this warning. (Background on this warning at: https://sqlalche.me/e/20/cprf)

If I set the attribute on our Select object: https://github.com/xzkostyan/clickhouse-sqlalchemy/blob/a5390b9ba79191cccaad008df59d50f1ba145d2e/clickhouse_sqlalchemy/sql/selectable.py#L84 to select.inherit_cache = False, the warning disappears but we don't use the cache

If I set it to True we get a an error and would be nice if we could use the caching mechanism:

File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 700, in _compile_w_cache
    compiled_sql = compiled_cache.get(key)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/_collections.py", line 535, in get
    item = self._data.get(key)
TypeError: unhashable type: 'dict

If we can't use the cache, we should set it to false to get rid of the warning.

Versions Clickhouse: 23.7.1.2470 Sqlalchemy: 2.0.23 clickhouse-sqlalchemy: 0.3.0

AviSarmani avatar Nov 22 '23 16:11 AviSarmani