GitHub user FrancescoCastaldi added a comment to the discussion: Recommended
IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris)
When migrating to Superset 6.x with InterSystems IRIS, the `SELECT TOP n`
syntax error occurs because older dialect versions did not translate standard
SQLAlchemy 1.4/2.0 limit clauses appropriately for Superset's query execution
engine.
### Recommended Version Matrix
For Superset 6.x, update your packages to:
- `sqlalchemy-iris>=0.20.1`
- `intersystems-irispython>=5.4.0`
In `sqlalchemy-iris` 0.20.1, the compiler introduces updated handling in
`get_select_precolumns()` and `limit_clause()`. If your IRIS database server
supports modern SQL pagination, it uses `LIMIT ... OFFSET ...`; otherwise, it
prepends `TOP <n>` to the column list.
### Superset Configuration (Custom Engine Spec)
Because Superset does not bundle a dedicated `IrisEngineSpec` in core, SQL Lab
wraps queries in subqueries for row limiting unless configured. You can define
a lightweight engine spec in `superset_config.py` to instruct Superset how to
handle limits:
```python
# superset_config.py
from superset.db_engine_specs.base import BaseEngineSpec
class IrisEngineSpec(BaseEngineSpec):
engine = "iris"
engine_name = "InterSystems IRIS"
allows_subqueries = True
supports_simple_limit = False
@classmethod
def apply_limit_to_sql(cls, sql: str, limit: int, database) -> str:
# Prevent appending LIMIT if IRIS compiler expects TOP
if "select top" in sql.lower():
return sql
return super().apply_limit_to_sql(sql, limit, database)
```
In the Superset UI (Databases -> Edit -> Advanced -> Other -> Engine
Parameters), pass:
```json
{
"connect_args": {
"auto_commit": true
}
}
```
This configuration provides stable pagination across SQL Lab and Explore in
Superset 6.x.
GitHub link:
https://github.com/apache/superset/discussions/44346#discussioncomment-19164452
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
