Support with_hint for tables

[c-thiel]

with_hint currently seems to be not supported.
The following snippet raises an assertion error:

from sqlalchemy import table, column, select
import ibm_db_sa

users = table("users", column("a"))
dialect = ibm_db_sa.dialect()

q = (
    select(users)
    .select_from(users)
    .where(users.c.a == 1)
    .with_hint(
        users,
        "FOR SYSTEM_TIME BETWEEN '0001-01-01-00.00.00.000000' AND '9999-12-31-00.00.00.000000'",
    )
)
q_hist = q.compile(dialect=dialect)
print(q_hist)
assert "FOR SYSTEM_TIME BETWEEN" in str(q_hist)

Is there any known workaround or plan to implement it?

[bchoudhary6415]

Hello @c-thiel

The with_hint() method is used to add query hints (such as index hints), but in this case, the FOR SYSTEM_TIME clause is a special clause for temporal tables in IBM Db2, and it's not recognized by with_hint() in the same way regular query hints (e.g., index hints) are.

As a workaround, you can manually construct the query with the FOR SYSTEM_TIME clause, You can use the text() function to construct and execute your query.

q_with_system_time = """
SELECT users.a 
FROM users 
FOR SYSTEM_TIME BETWEEN '0001-01-01-00.00.00.000000' AND '9999-12-31-00.00.00.000000' 
WHERE users.a = 1
"""
# Execute the query using raw SQL
result = session.execute(text(q_with_system_time))

Also, you can try the below approach

metadata = MetaData()
users = Table(
    'users', metadata,
    Column('a', Integer),
)
with engine.connect() as conn:
    metadata.create_all(conn)
    conn.execute(users.insert(), [
        {'a': 1},
        {'a': 2},
        {'a': 3}
    ])
    q = select(users).where(users.c.a == 1)

    q_with_hint = q.with_hint(
        users,
        "FOR SYSTEM_TIME BETWEEN '0001-01-01-00.00.00.000000' AND '9999-12-31-00.00.00.000000'"
    )

    q_hist = q_with_hint.compile()
    print("Compiled query with with_hint:", q_hist)
    assert "SELECT" in str(q_hist)
    print(f"q_with_hint = {q_with_hint}")
    result = conn.execute(q_with_hint)
    for row in result:
        print(row)

I have tried giving index in with_hint(), see below code snippet

with engine.connect() as conn:
    metadata.create_all(conn)
    conn.execute(users.insert(), [
        {'a': 1},
        {'a': 2},
        {'a': 3}
    ])
    q = select(users).where(users.c.a == 1)
    q_with_hint = q.with_hint(users, "USE INDEX (idx_a)")
    q_hist = q_with_hint.compile()
    print("Compiled query with with_hint:", q_hist)
    assert "SELECT" in str(q_hist)

    result = conn.execute(q_with_hint)
    for row in result:
        print(row)

Please try the approach mentioned above and let me know if you're encountering any issues or if I might be missing something in my explanation.

Thank you!