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