AUTO LIMIT not working with queries containing CTE

Issue Summary

We have seen queries not honoring the LIMIT 1000

image

Analysis

When redash attempts to apply the limit in the python backend it only add the LIMIT when the query_is_select_no_limit return true. For queries that Start with a CTE statement
example

with redshift_tables as
(select table_id, schema, name from de.tables) 
select table_id, schema, name 
from redshift_tables

The function below is returning false since parsed_query.tokens[0].value.upper() != "SELECT is returning True

.def query_is_select_no_limit(query):
    parsed_query = sqlparse.parse(query)[0]
    last_keyword_idx = find_last_keyword_idx(parsed_query)
    # Either invalid query or query that is not select
    if last_keyword_idx == -1 or parsed_query.tokens[0].value.upper() != "SELECT":
        return False

    no_limit = parsed_query.tokens[last_keyword_idx].value.upper() != "LIMIT" \
               and parsed_query.tokens[last_keyword_idx].value.upper() != "OFFSET"
    return no_limit

For queries that don’t start with a CTE (normal select) the LIMIT works

Workaround

I am not sure there is even if we create multiple statement the last statement is honored. Changing the function to parsed_query.tokens[0].value.upper() not in ("SELECT","WITH") fixes the issue but I have not done any rigorous testing or even run the unit tests

Technical details:

  • Redash Version: 10
  • Browser/OS: Chrome
  • How did you install Redash: in AWS ECS (Docker image)
1 Like

Great post :ok_hand:

I will follow-up on it in a couple weeks after we hit the EOL for hosted. Most of my time is spent running migrations right now :hot_face:

1 Like

Good luck @jesse I am sure it is going to be some crazy times

1 Like

I have also noticed that the LIMIT checkbox is not honored if my query begins with a comment. For example:
– select * from one_data_table
select * from another_data_table

When I run this query, the LIMIT does not apply, but if I delete the first line that is commented, I get the top 1000 rows returned.

1 Like

Hey there, can you check whether the fix in this PR soulves the issue for you? Fix not working auto limit by tnk-ysk · Pull Request #5617 · getredash/redash · GitHub

The unit tests in this PR give me confidence as they are using the same tests I used when reporting this. Thank you

1 Like