[6.0.0] Superset SQLab+starrocks many statement does't support

[ywill3]

Bug description

Superset SQLab+starrocks many statement does't support
use starrocks databases connection like "starrocks://bigdata:xxx@xx.xx.xx.xx:9030/"
there are list of statement does't support:
. show tables;
. show databases;
. show create table test.will_test1 ;
. refresh external table hive.test.will_test1 ;
. drop table test.will_test FORCE;

Screenshots/recordings

No response

Superset version

6.0.0

Python version

3.11

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hi @ywill3! I'm Dosu and I’m helping the superset team.

Superset's support for StarRocks SQL statements in SQL Lab depends on several factors: the StarRocks SQLAlchemy driver, Superset's SQL parsing, and your connection configuration. There are no explicit limitations or support for statements like SHOW TABLES, SHOW DATABASES, SHOW CREATE TABLE, REFRESH EXTERNAL TABLE, or DROP TABLE ... FORCE documented in Superset itself—support for these comes from the StarRocks SQLAlchemy dialect, not Superset's core code.

Common reasons these statements fail in SQL Lab include:

• Using an incompatible StarRocks driver version (Superset 6.0.0 requires starrocks==1.0.6; newer versions need SQLAlchemy v2, which is not supported)
• Impersonation settings causing authentication errors (try disabling impersonation in your database connection settings)
• Insufficient privileges for the StarRocks user
• Connection string format issues (should be starrocks://<User>:<Password>@<Host>:<Port>/<Catalog>.<Database>)
• The SQL parser (sqlglot) in Superset may fail to recognize some statements or dialect-specific syntax, causing them to be rejected even if they work in other clients

If you have already checked driver version, impersonation, privileges, and connection string, and still see issues, it's likely a limitation in the StarRocks SQLAlchemy dialect or a parsing issue in Superset's SQL Lab. As a workaround, you may need to run these statements directly in a StarRocks client or check if the StarRocks dialect supports them via SQLAlchemy.

For more troubleshooting steps and context, see these discussions:

• Driver and impersonation issues
• SQL parsing and DDL/DML limitations
• Driver compatibility and connection string format

If you have Superset logs or error messages for specific statements, sharing those may help pinpoint the root cause.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[ywill3]

1.My StarRocks driver version is starrocks==1.0.6
2.My connect string is correct. I can run many select statement.
3.My starrocks user is root. so don't have privileges problem.

[dosubot]

Superset sends your SQL statements to StarRocks as-is and relies on the StarRocks SQLAlchemy driver and the database itself to support statements like SHOW TABLES, SHOW DATABASES, SHOW CREATE TABLE, REFRESH EXTERNAL TABLE, and DROP TABLE ... FORCE—there’s no extra filtering or blocking in Superset’s code for these statements¹²³. If these statements fail in SQL Lab but SELECT works, it’s likely due to either the StarRocks driver not supporting them via SQLAlchemy, or the Superset SQL parser (sqlglot) not recognizing the syntax and rejecting the query before execution⁴⁵.

To narrow it down, try running the same statements directly using a SQLAlchemy session or the StarRocks client. If they work there but not in Superset, check for any error messages in Superset’s logs when you run these statements—those details will help pinpoint if the issue is with SQL parsing or the driver. If you see a parsing error, it’s likely sqlglot not handling the statement; if you see a driver/database error, it’s likely a limitation of the StarRocks SQLAlchemy dialect.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

Footnotes

1. StarRocks engine spec does not block these statements ↩

2. Superset SQL Lab executes SQL as-is, no statement filtering ↩

3. SQL Lab executor sends rendered SQL to the driver ↩

4. Superset uses sqlglot to parse and classify statements; unrecognized syntax can cause rejection ↩

5. Statements like SHOW/REFRESH are parsed as Command nodes; if not recognized, Superset may reject them ↩

[ywill3]

1. Use show tables; show databases; show create table test.will_test1 ;below is exception in superset
   starrocks error: Getting syntax error at line 1, column 12. Detail message: Unexpected input 'LIMIT', the most similar input is {, ';'}.

2. Use refresh external table hive.test.will_test1 ; below is exception in superset
   Unable to parse SQL
   Error parsing near 'table' at line 1:22
   refresh external table hive.test.will_test1

But I use these code in the same python env, it's work;
from sqlalchemy import create_engine, text
engine = create_engine("starrocks://bigdata:xxx@xx.xx.xx.xx:9030/")

with engine.connect() as conn:
conn.execute(text(""" refresh external table hive.test.will_test1 ;"""))

3. Use drop table test.will_test1 FORCE; is same as refresh external table hive.test.will_test1 ; below is exception in superset
   Unable to parse SQL
   Error parsing near 'FORCE' at line 1:32
   drop table test.will_test1 FORCE

[dosubot]

The root cause is that Superset 6.0.0 uses the sqlglot parser to validate and classify all SQL statements before sending them to the database. StarRocks-specific statements like refresh external table, drop table ... FORCE, and some show commands aren't recognized by sqlglot's StarRocks dialect, so Superset blocks them with parsing errors before they ever reach your database—even though they work fine when run directly via SQLAlchemy in Python, which doesn't do this extra parsing or validation example.

There's currently no configuration option to bypass or relax this SQL parsing in SQL Lab. The only reliable workaround is to run unsupported statements directly via SQLAlchemy or another client outside of Superset example. You could try patching Superset to use a more permissive dialect (like MySQL) for StarRocks, but this is not robust and may break other features example.

The long-term fix is for the StarRocks dialect in sqlglot to add support for these statements. If you want to help, you can contribute to sqlglot or request support for these StarRocks-specific commands upstream.

Let me know if you want details on patching the dialect mapping or contributing to sqlglot!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[ywill3]

how about this issue?
Use show tables; show databases; show create table test.will_test1 ;below is exception in superset
starrocks error: Getting syntax error at line 1, column 12. Detail message: Unexpected input 'LIMIT', the most similar input is {, ';'}.

[dosubot]

This error happens because Superset automatically appends a LIMIT clause to non-SELECT statements like SHOW TABLES, SHOW DATABASES, and SHOW CREATE TABLE when running them in SQL Lab. StarRocks does not support LIMIT on these statements, so you get a syntax error about 'Unexpected input LIMIT'—even though the same statements work fine when run directly via SQLAlchemy, which does not add LIMITs or parse the SQL in this way.

This behavior is due to Superset's SQL parsing and limit application logic, which was updated in version 6.0.0 to use the SQLScript class for parsing and applying limits. The logic is supposed to only add LIMIT to SELECT-like queries, but it can misclassify SHOW statements and add LIMIT anyway, causing the error you see in StarRocks (details in PR #36599).

There is currently no configuration option to disable this automatic LIMIT for SHOW statements. The only workaround is to run these statements outside of Superset (for example, directly in a StarRocks client or via SQLAlchemy in Python). If you need to use these statements in Superset, you would have to patch the code to avoid applying LIMIT to SHOW statements, but this is not recommended for production.

This is a known limitation and would require an upstream fix in Superset's SQL parsing logic to properly detect and skip LIMIT for SHOW statements in StarRocks.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}