Failed to execute SQL with nested CTE table alias

[haribo0915]

Environment

1. Clean Python:3.12 docker container with only sqlalchemy-iris==0.18.0 and SQLAlchemy==2.0.43 installed
2. IRIS 2025.1 container

Example Code to Reproduce

The code below is executed in the Python container, which will fetch data from the IRIS 2025.1 container.

def sqlalchemy(sql):
  username = 'superuser'
  password = 'SYS'
  hostname = 'sc-ai-copilot-iris-1'
  port = '1972' 
  namespace = 'SC'
  CONNECTION_STRING = f"iris://{username}:{password}@{hostname}:{port}/{namespace}"

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

  result = connection.exec_driver_sql(sql).fetchall()

  print(result)

if __name__ == "__main__":
  sql = """
  WITH customer_cte AS (
      SELECT name,
              primarylocationid
      FROM sc_data.customer
  ),
  customer_location_cte AS (
      SELECT c.name AS customer_name,
              l.name AS location_name
      FROM sc_data.location l
      INNER JOIN customer_cte c
          ON c.primarylocationid = l.uid
  )
  SELECT customer_name, location_name
  FROM customer_location_cte;
  """

  sqlalchemy(sql)

Error Message

It's likely that the parser or rewriter failed to interpret the customer_cte c alias in the customer_location_cte CTE table because the SQL error message shown below stated an extra AS keyword in a subquery alias [%msg: < USING expected, IDENTIFIER (c) found ... AS customer_cte c>].

Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy_iris/base.py", line 1155, in do_execute
    cursor.execute(query, params)
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 276, in execute
    self._execute()
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 549, in _execute
    return exec_func()
           ^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 595, in _execute_query
    self._send_direct_query_request()
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 1169, in _send_direct_query_request
    self._process_sqlcode(sqlcode)
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 773, in _process_sqlcode
    super()._process_sqlcode(sqlcode, self._get_error_info(sqlcode))
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 370, in _process_sqlcode
    raise OperationalError(message)
intersystems_iris.dbapi._DBAPI.OperationalError: [SQLCODE: <-1>:<Invalid SQL statement>]
[Location: <Prepare>]
[%msg: < USING expected, IDENTIFIER (c) found ^SELECT customer_name , location_name FROM ( SELECT c . name AS customer_name , l . name AS location_name FROM sc_data . location l INNER JOIN ( SELECT name , primarylocationid FROM sc_data . customer ) AS customer_cte c>]

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/code/app/test.py", line 87, in <module>
    sqlalchemy(query1)
  File "/code/app/test.py", line 60, in sqlalchemy
    result = connection.exec_driver_sql(sql).fetchall()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1779, in exec_driver_sql
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy_iris/base.py", line 1155, in do_execute
    cursor.execute(query, params)
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 276, in execute
    self._execute()
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 549, in _execute
    return exec_func()
           ^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 595, in _execute_query
    self._send_direct_query_request()
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 1169, in _send_direct_query_request
    self._process_sqlcode(sqlcode)
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 773, in _process_sqlcode
    super()._process_sqlcode(sqlcode, self._get_error_info(sqlcode))
  File "/usr/local/lib/python3.12/site-packages/intersystems_iris/dbapi/_DBAPI.py", line 370, in _process_sqlcode
    raise OperationalError(message)
sqlalchemy.exc.OperationalError: (intersystems_iris.dbapi._DBAPI.OperationalError) [SQLCODE: <-1>:<Invalid SQL statement>]
[Location: <Prepare>]
[%msg: < USING expected, IDENTIFIER (c) found ^SELECT customer_name , location_name FROM ( SELECT c . name AS customer_name , l . name AS location_name FROM sc_data . location l INNER JOIN ( SELECT name , primarylocationid FROM sc_data . customer ) AS customer_cte c>]
[SQL: 
  WITH customer_cte AS (
      SELECT name,
              primarylocationid
      FROM sc_data.customer
  ),
  customer_location_cte AS (
      SELECT c.name AS customer_name,
              l.name AS location_name
      FROM sc_data.location l
      INNER JOIN customer_cte c
          ON c.primarylocationid = l.uid
  )
  SELECT customer_name, location_name
  FROM customer_location_cte;
  ]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Detailed Description

The query could be executed successfully if removing the nested CTE table alias customer_cte c like

WITH customer_cte AS (
    SELECT name,
            primarylocationid
    FROM sc_data.customer
),
customer_location_cte AS (
    SELECT customer_cte.name AS customer_name,
            l.name AS location_name
    FROM sc_data.location l
    INNER JOIN customer_cte
        ON customer_cte.primarylocationid = l.uid
)
  SELECT customer_name, location_name
  FROM customer_location_cte;

In addition, I've tested using another Python container with DB-API driver intersystems-irispython==5.2.0 installed only, and it works, so I'm not entirely sure if the problem is caused by sqlalchemy-iris or intersystems-irispython, or if I missed any important steps. Below is the code for the DB-API driver approach:

import iris

def db_api(sql):
  args = {
    'connectionstr': 'sc-ai-copilot-iris-1:1972/SC',
    'username':'superuser', 
    'password':'SYS',
    'sharedmemory': False
  }
  connection = iris.connect(**args)

  cursor = connection.cursor()

  try:
    # Prepare and execute a SQL  
    cursor.execute(sql)

    # Fetch next row of a query result set
    rows = cursor.fetchall()

    for row in rows:
      print(row)

  except Exception as ex:
    print(ex)
  finally:
    if cursor:
      cursor.close()
    if connection:
      connection.close()

if __name__ == "__main__":
  sql = """
  WITH customer_cte AS (
      SELECT name,
              primarylocationid
      FROM sc_data.customer
  ),
  customer_location_cte AS (
      SELECT c.name AS customer_name,
              l.name AS location_name
      FROM sc_data.location l
      INNER JOIN customer_cte c
          ON c.primarylocationid = l.uid
  )
  SELECT customer_name, location_name
  FROM customer_location_cte;
  """

  db_api(sql)

Thank you so much!