Incorrect number of parameters because of unused CTEs

[bdeboe]

I'm running into trouble trying to craft an easy-to-read and reusable query using CTEs (in fact for this hybrid search demo). As long as all CTEs that have parameters are used, things work OK, but when I leave out a CTE that includes a parameter and still supply it, I get "Incorrect number of parameters", and when I leave it out I get a weird "Unable to allocate a license" error.

I'm not sure whether this sits at the SQLAlchemy-iris level or one level underneath, but I'm sure you can help me figure that out :-)

To reproduce:

from sqlalchemy import create_engine, text

username = 'demo'
password = 'demo'
hostname = 'localhost'
port = '1972' 
namespace = 'USER'
CONNECTION_STRING = f"iris://{username}:{password}@{hostname}:{port}/{namespace}"

engine = create_engine(CONNECTION_STRING)
connection = engine.connect()

sql = text("""
    WITH cte AS (
        SELECT 123 as n, :param as message
    ), 
    cte2 AS (
        SELECT *, :param2 as message2
        FROM cte
    )
    SELECT * FROM cte
""")

result = connection.execute(sql, { "param": "hello", "param2": "hello2" }).fetchall()
print(result)

you can easily play around selecting from cte or cte2, and include/exclude parameters