Bug description

Hello,

I've encountered an issue in Apache Superset's SQL Lab when executing particularly long queries. The query consistently fails with a timeout error. This issue can be reproduced with the following Python code snippet, which generates the query: print(f'select {" 1,"*10000}'[:-1])

i'm using clickhouse and i've tested the query in the superset_app container with clickhouse-connect and the sql request was exectuted in less than one second

when i check the database activity, the request is executed 20 seconds after clicking run sqlLab

How to reproduce the bug

in sqlab execute th request generate by this code : print(f'select {" 1,"*10000}'[:-1])

i've reproduced the error with another Clikhouse/Superset setup

thanks for your help

Screenshots/recordings

No response

Superset version

3.0.4

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

  • [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
  • [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
  • [X] 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.

Comment From: justinpark

@netchose First of all, the query that you raised looks unusual. Which means selecting 10000 columns.

print(f'select {" 1,"*10000}'[:-1])
=> select 1, 1, 1, 1, 1, 1, .....(x 10000)

_DEV__Superset

ClickHouse, as per my understanding, may also not provide support due to the excessive number of columns involved.

Comment From: netchose

a more representative example which better corresponds to my use case which consists of choosing individuals from a population :

print(f"select 1 where 1 in ({','.join([str(x) for x in range(1,10000)])})")

In the database the request is executed in less than 2 second but in Superset, in chart view or in sqllab more than 4 minutes

my intuition tells me that this is a parameter that is not optimal in the postgres database used by Superset internally.

I'll try to look at this on my own

Apache Superset Query Timeout Error in SQL Lab for Long Queries

Comment From: rusackas

@netchose are you still facing this issue? If nobody can corroborate it, it may be at risk of being closed as stale. Meanwhile, maybe @dosu-bot can help.

Comment From: dosubot[bot]

Hey @rusackas! I'm here to help you out while you're waiting for a human maintainer. I'm Dosu-bot, and I can assist you with bugs, questions, and becoming a contributor. Let me know how I can help!

The issue you're encountering with query timeout in Apache Superset's SQL Lab might be due to the SQLLAB_ASYNC_TIME_LIMIT_SEC configuration parameter. This parameter sets the soft time limit for asynchronous SQL Lab queries. If your query execution exceeds this limit, it might be causing the timeout.

To resolve this issue, you can try increasing the SQLLAB_ASYNC_TIME_LIMIT_SEC parameter in your Superset configuration. Here is how you can do it:

  1. Locate your Superset configuration file (usually superset_config.py).
  2. Add or update the SQLLAB_ASYNC_TIME_LIMIT_SEC parameter to a higher value.

For example:

# superset_config.py

# Increase the SQL Lab async time limit to 600 seconds (10 minutes)
SQLLAB_ASYNC_TIME_LIMIT_SEC = 600

Additionally, the SynchronousSqlJsonExecutor class in the superset/sqllab/sql_json_executer.py file uses a timeout mechanism for synchronous query execution. The _timeout_duration_in_seconds attribute is used to set the timeout duration for the query execution. If this timeout is too short, it could also be causing the issue.

To address this, ensure that the timeout duration is set appropriately in the SynchronousSqlJsonExecutor class. You might need to adjust the _timeout_duration_in_seconds parameter to a higher value if you are using synchronous execution.

After updating the configuration, restart your Superset instance to apply the changes.

This should help in resolving the query timeout issue for long-running queries in SQL Lab [1][2].

To continue talking to Dosu, mention @dosu.

Comment From: netchose

The SQL query I am trying to execute runs locally in a few seconds on several different databases. I have provided an example query that is easily reproducible with any database and within a Superset container. The issue is not related to a timeout duration that isn't long enough.

When your train takes 10 days instead of 3 hours to complete a journey, the solution is not for you to take more days off but simply for the company that sold you the ticket to ensure your train arrives on time, isn't it?

Comment From: betodealmeida

I think this is fixed after https://github.com/apache/superset/issues/26786, tested with Postgrres and it was fine.