ilike is not supported

[kabulov]

code

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.dialects import registry
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

registry.register("yql.ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("yql", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")

engine = create_engine("yql+ydb://localhost:myport/mydb")
Base = declarative_base()
Session = sessionmaker(bind=engine)

class Table(Base):
    __tablename__ = "myexampletable"
    cstr = Column(String(), primary_key=True)
    cint = Column(Integer(), primary_key=True)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session = Session()

values_list = [
    Table(cstr="aaa", cint=111),
    Table(cstr="ccc", cint=111),
    Table(cstr="bbb", cint=222),
]

session.add_all(values_list)
session.commit()

q = session.query(Table).filter(Table.cstr.ilike("a%")).all()
for c in q:
    print(c.cstr, c.cint)

results in following error:

sqlalchemy.exc.DataError: (ydb_sqlalchemy.dbapi.errors.DataError) position { row: 3 column: 39 } message: "Unknown builtin: lower" end_position { row: 3 column: 39 } severity: 1 ,position { row: 3 column: 7 } message: "Unknown builtin: lower" end_position { row: 3 column: 7 } severity: 1 (server_code: 400080)
[SQL: SELECT myexampletable.cstr AS myexampletable_cstr, myexampletable.cint AS myexampletable_cint 
FROM myexampletable 
WHERE lower(myexampletable.cstr) LIKE lower(%(cstr_1)s)]
[parameters: {'cstr_1': 'a%'}]

i suggest it happens somewhere here: https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/compiler.py#L3451

maybe should be rewritten like its done for postgresql: https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/postgresql/base.py#L1791

there is lower function support in ydb: https://ydb.tech/docs/ru/yql/reference/udf/list/unicode

[rekby]

As I understand: postgres simple skip ilike?

What about use YDB lower function like:

    def visit_ilike_case_insensitive_operand(self, element, **kw):
        return f"Unicode::ToLower({element.element._compiler_dispatch(self, **kw)})"

?