Issue 1:
Describe the bug

custom_op usage leads to duplicated entries in query cache.

I have found this issue by discovering a memory leak in my application. We have big queries with size ~1 Mb and with default query_cache_size=500 it leads to cases when application consumes 500+ Mb after some time.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.6

DBAPI (i.e. the database driver)

any

Database Vendor and Major Version

any

Python Version

3.11

Operating system

any

To Reproduce

from sqlalchemy import select, literal, create_engine

engine = create_engine("postgresql://postgres:postgres@localhost:5432/postgres")

def main() -> None:
with engine.connect() as conn:
for _ in range(10):
query = select(literal(1).op("+")(literal(1)))

conn.execute(query)

print(len(engine._compiled_cache))

for entry in engine._compiled_cache.items():
print(entry)

if __name__ == "__main__":
main()

Error

Will print 10 instead of 1:

10
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c730e20>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c731360>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c7320e0>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c732bc0>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c733040>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c733220>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c733400>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c7335e0>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c7337c0>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1
(<sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x10c5a9250>, ('0', <class 'sqlalchemy.sql.selectable.Select'>, '_raw_columns', (('1', <class 'sqlalchemy.sql.elements.BinaryExpression'>, 'left', ('2', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '3', False), 'right', ('4', <class 'sqlalchemy.sql.elements.BindParameter'>, (<class 'sqlalchemy.sql.sqltypes.Integer'>,), '5', False), 'operator', <sqlalchemy.sql.operators.custom_op object at 0x10c7339a0>, 'type', (<class 'sqlalchemy.sql.sqltypes.Integer'>,)),), '_label_style', <SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY: 2>, '_compile_options', (<class 'sqlalchemy.sql.selectable.SelectState.default_select_compile_options'>, ())), (), False, False) SELECT %(param_1)s + %(param_2)s AS anon_1

Additional context

No response

Issue 2:
Migrated issue, originally created by Marcin Lulek (@ergo)

Hello,

I'm not sure if I was always using SA in wrong way or there is a bug in 1.2 version.

Setters need to be named same way as hybrid property - this test case works in 1.x and breaks in 1.2. I did notice that in the examples in docs the property name is same in both places, though.

Error I'm getting is:

File "/home/ergo/workspace/ziggurat_cms/.venv/lib/python3.6/site-packages/sqlalchemy/ext/hybrid.py", line 873, in set

raise AttributeError("can't set attribute")

AttributeError: can't set attribute

Attachments: test_hybrid.py