Repository: getredash/redash
Pull request: Add default limit (1000) to SQL queries
## What type of PR is this? (check all applicable)

- [x] Feature

## Description

Add a default limit 1000 for query execution, now it contains both the frontend changes(add a checkbox) and backend changes.
## Related Tickets & Documents

REDASH-112, Closes #976.

## Mobile & Desktop Screenshots/Recordings (if there are UI changes)
This is for data source that currently support auto limit
<img width="861" alt="Screen Shot 2020-08-27 at 10 12 49 AM" src="https://user-images.githubusercontent.com/25569264/91473627-03f98a00-e84e-11ea-8838-244ccd9f3369.png">


This is for data source that currently not support auto limit
<img width="858" alt="Screen Shot 2020-08-27 at 10 13 05 AM" src="https://user-images.githubusercontent.com/25569264/91473638-078d1100-e84e-11ea-8a36-c121fd2f4126.png">




Problem statement:
Limit number of records returned by a query
I typically run `SELECT * FROM ...` just to get a peak on the data and in many cases forget to add limit (yeah I know I know....) .

It would be nice if REDASH would have tunable default limit e.g. 10,000 rows (same way most of the desktop tools like TOAD or Navicat have). Not sure if this is easy to do for any data sources, its certainly trivial for postgres (it executes the limit as a last step).

Ideally displayed on the new query page right next to "refresh schedule". This way user knows about it and can easily change it.

Hints from issue discussion:
Sounds like a good feature, but need some research on how it will work with the different data source types. Also need to make sure not to apply it to non SELECT queries.

Please find below an example:
`SELECT * FROM table LIMIT 5;`
any development yet for this feature ? 
We get very high celery CPU load occasionally, which locks up redash. Likely due to users running queries against large datasets without a LIMIT. Zeppelin has a similar feature (Row count limit per datasource, and data length limit as well). Would be great if this was added. We might just build this out when we get some time.
try 
`set rowcount 10000;`
`select * FROM table;`
The problem with this matter is that the command highly depends on the DB that it's running the query.

@ninja-ken your solution may not work on all data-sources.

@arikfr What about add in the data-source implementation a way to determine the row count limit for itself? That way, redash could call each function of each data-source. Do you think that can work?
Hey, anything on this feature request?
Hi, Any update on this feature request? This will help us out a lot!
I think I will try superset for now. They have this feature and many other features(scheduling etc.) out of the box. Will keep a look on this thread though.

Commit message: Add frontend changes and connect to backend

Patch to review:
diff --git a/client/app/components/queries/QueryEditor/AutoLimitCheckbox.jsx b/client/app/components/queries/QueryEditor/AutoLimitCheckbox.jsx
new file mode 100644
index 0000000000..4ac0330299
--- /dev/null
+++ b/client/app/components/queries/QueryEditor/AutoLimitCheckbox.jsx
@@ -0,0 +1,37 @@
+import React, { useCallback } from "react";
+import PropTypes from "prop-types";
+import recordEvent from "@/services/recordEvent";
+import Checkbox from "antd/lib/checkbox";
+import Tooltip from "antd/lib/tooltip";
+
+export default function AutoLimitCheckbox({ available, checked, onChange }) {
+  const handleClick = useCallback(() => {
+    recordEvent("checkbox_auto_limit", "screen", "query_editor", { state: !checked });
+    onChange(!checked);
+  }, [checked, onChange]);
+
+  let tooltipMessage = null;
+  if (!available) {
+    tooltipMessage = "Auto limiting is not available for this Data Source type.";
+  } else {
+    tooltipMessage = "Auto limit results to first 1000 rows.";
+  }
+
+  return (
+    <Tooltip placement="top" title={tooltipMessage}>
+      <Checkbox
+        className="query-editor-controls-checkbox"
+        disabled={!available}
+        onClick={handleClick}
+        checked={available && checked}>
+        LIMIT 1000
+      </Checkbox>
+    </Tooltip>
+  );
+}
+
+AutoLimitCheckbox.propTypes = {
+  available: PropTypes.bool,
+  checked: PropTypes.bool.isRequired,
+  onChange: PropTypes.func.isRequired,
+};
diff --git a/client/app/components/queries/QueryEditor/QueryEditorControls.jsx b/client/app/components/queries/QueryEditor/QueryEditorControls.jsx
index cf02c8535e..9507c5a11e 100644
--- a/client/app/components/queries/QueryEditor/QueryEditorControls.jsx
+++ b/client/app/components/queries/QueryEditor/QueryEditorControls.jsx
@@ -8,6 +8,7 @@ import KeyboardShortcuts, { humanReadableShortcut } from "@/services/KeyboardSho
 
 import AutocompleteToggle from "./AutocompleteToggle";
 import "./QueryEditorControls.less";
+import AutoLimitCheckbox from "@/components/queries/QueryEditor/AutoLimitCheckbox";
 
 export function ButtonTooltip({ title, shortcut, ...props }) {
   shortcut = humanReadableShortcut(shortcut, 1); // show only primary shortcut
@@ -38,6 +39,7 @@ export default function EditorControl({
   saveButtonProps,
   executeButtonProps,
   autocompleteToggleProps,
+  autoLimitCheckboxProps,
   dataSourceSelectorProps,
 }) {
   useEffect(() => {
@@ -84,6 +86,7 @@ export default function EditorControl({
           onToggle={autocompleteToggleProps.onToggle}
         />
       )}
+      {autoLimitCheckboxProps !== false && <AutoLimitCheckbox {...autoLimitCheckboxProps} />}
       {dataSourceSelectorProps === false && <span className="query-editor-controls-spacer" />}
       {dataSourceSelectorProps !== false && (
         <Select
@@ -153,6 +156,7 @@ EditorControl.propTypes = {
       onToggle: PropTypes.func,
     }),
   ]),
+  autoLimitCheckboxProps: PropTypes.oneOfType([PropTypes.shape(AutoLimitCheckbox.propTypes)]),
   dataSourceSelectorProps: PropTypes.oneOfType([
     PropTypes.bool, // `false` to hide
     PropTypes.shape({
@@ -175,5 +179,6 @@ EditorControl.defaultProps = {
   saveButtonProps: false,
   executeButtonProps: false,
   autocompleteToggleProps: false,
+  autoLimitCheckboxProps: false,
   dataSourceSelectorProps: false,
 };
diff --git a/client/app/components/queries/QueryEditor/QueryEditorControls.less b/client/app/components/queries/QueryEditor/QueryEditorControls.less
index f81c152ae5..118698dc79 100644
--- a/client/app/components/queries/QueryEditor/QueryEditorControls.less
+++ b/client/app/components/queries/QueryEditor/QueryEditorControls.less
@@ -21,6 +21,12 @@
     }
   }
 
+  .query-editor-controls-checkbox {
+    display: inline-block;
+    white-space: nowrap;
+    margin: auto 5px;
+  }
+
   .query-editor-controls-spacer {
     flex: 1 1 auto;
     height: 35px; // same as Antd <Select>
diff --git a/client/app/pages/queries/QuerySource.jsx b/client/app/pages/queries/QuerySource.jsx
index 2f97c50662..48473d886c 100644
--- a/client/app/pages/queries/QuerySource.jsx
+++ b/client/app/pages/queries/QuerySource.jsx
@@ -26,6 +26,7 @@ import { getEditorComponents } from "@/components/queries/editor-components";
 import useQuery from "./hooks/useQuery";
 import useVisualizationTabHandler from "./hooks/useVisualizationTabHandler";
 import useAutocompleteFlags from "./hooks/useAutocompleteFlags";
+import useAutoLimitFlags from "./hooks/useAutoLimitFlags";
 import useQueryExecute from "./hooks/useQueryExecute";
 import useQueryResultData from "@/lib/useQueryResultData";
 import useQueryDataSources from "./hooks/useQueryDataSources";
@@ -77,6 +78,7 @@ function QuerySource(props) {
 
   const editorRef = useRef(null);
   const [autocompleteAvailable, autocompleteEnabled, toggleAutocomplete] = useAutocompleteFlags(schema);
+  const [autoLimitAvailable, autoLimitChecked, setAutoLimit] = useAutoLimitFlags(dataSource, query, setQuery);
 
   const [handleQueryEditorChange] = useDebouncedCallback(queryText => {
     setQuery(extend(query.clone(), { query: queryText }));
@@ -306,6 +308,11 @@ function QuerySource(props) {
                         enabled: autocompleteEnabled,
                         onToggle: toggleAutocomplete,
                       }}
+                      autoLimitCheckboxProps={{
+                        available: autoLimitAvailable,
+                        checked: autoLimitChecked,
+                        onChange: setAutoLimit,
+                      }}
                       dataSourceSelectorProps={
                         dataSource
                           ? {
diff --git a/client/app/pages/queries/hooks/useAutoLimitFlags.js b/client/app/pages/queries/hooks/useAutoLimitFlags.js
new file mode 100644
index 0000000000..92688e1882
--- /dev/null
+++ b/client/app/pages/queries/hooks/useAutoLimitFlags.js
@@ -0,0 +1,24 @@
+import { useCallback, useState } from "react";
+import localOptions from "@/lib/localOptions";
+import { get, extend } from "lodash";
+
+function isAutoLimitAvailable(dataSource) {
+  return get(dataSource, "apply_auto_limit", false);
+}
+
+export default function useAutoLimitFlags(dataSource, query, setQuery) {
+  const isAvailable = isAutoLimitAvailable(dataSource);
+  const [isChecked, setIsChecked] = useState(localOptions.get("applyAutoLimit", true));
+  query.options.applyAutoLimit = isAvailable && isChecked;
+
+  const setAutoLimit = useCallback(
+    state => {
+      setIsChecked(state);
+      localOptions.set("applyAutoLimit", state);
+      setQuery(extend(query.clone(), { options: { ...query.options, applyAutoLimit: isAvailable && state } }));
+    },
+    [query, setQuery, isAvailable]
+  );
+
+  return [isAvailable, isChecked, setAutoLimit];
+}
diff --git a/client/app/services/query-result.js b/client/app/services/query-result.js
index 0f39778c0c..dc4874da86 100644
--- a/client/app/services/query-result.js
+++ b/client/app/services/query-result.js
@@ -435,11 +435,11 @@ class QueryResult {
     return `${queryName.replace(/ /g, "_") + moment(this.getUpdatedAt()).format("_YYYY_MM_DD")}.${fileType}`;
   }
 
-  static getByQueryId(id, parameters, maxAge) {
+  static getByQueryId(id, parameters, applyAutoLimit, maxAge) {
     const queryResult = new QueryResult();
 
     axios
-      .post(`api/queries/${id}/results`, { id, parameters, max_age: maxAge })
+      .post(`api/queries/${id}/results`, { id, parameters, apply_auto_limit: applyAutoLimit, max_age: maxAge })
       .then(response => {
         queryResult.update(response);
 
@@ -454,13 +454,14 @@ class QueryResult {
     return queryResult;
   }
 
-  static get(dataSourceId, query, parameters, maxAge, queryId) {
+  static get(dataSourceId, query, parameters, applyAutoLimit, maxAge, queryId) {
     const queryResult = new QueryResult();
 
     const params = {
       data_source_id: dataSourceId,
       parameters,
       query,
+      apply_auto_limit: applyAutoLimit,
       max_age: maxAge,
     };
 
diff --git a/client/app/services/query.js b/client/app/services/query.js
index 66ba2be6b8..a756114d39 100644
--- a/client/app/services/query.js
+++ b/client/app/services/query.js
@@ -130,7 +130,8 @@ export class Query {
   }
 
   getQueryResult(maxAge) {
-    const execute = () => QueryResult.getByQueryId(this.id, this.getParameters().getExecutionValues(), maxAge);
+    const execute = () =>
+      QueryResult.getByQueryId(this.id, this.getParameters().getExecutionValues(), this.getAutoLimit(), maxAge);
     return this.prepareQueryResultExecution(execute, maxAge);
   }
 
@@ -141,7 +142,8 @@ export class Query {
     }
 
     const parameters = this.getParameters().getExecutionValues({ joinListValues: true });
-    const execute = () => QueryResult.get(this.data_source_id, queryText, parameters, maxAge, this.id);
+    const execute = () =>
+      QueryResult.get(this.data_source_id, queryText, parameters, this.getAutoLimit(), maxAge, this.id);
     return this.prepareQueryResultExecution(execute, maxAge);
   }
 
@@ -184,6 +186,10 @@ export class Query {
     return this.$parameters;
   }
 
+  getAutoLimit() {
+    return this.options.applyAutoLimit;
+  }
+
   getParametersDefs(update = true) {
     return this.getParameters().get(update);
   }
diff --git a/redash/handlers/queries.py b/redash/handlers/queries.py
index 5511d43a3e..266092e4d8 100644
--- a/redash/handlers/queries.py
+++ b/redash/handlers/queries.py
@@ -488,9 +488,9 @@ def post(self, query_id):
 
         parameter_values = collect_parameters_from_request(request.args)
         parameterized_query = ParameterizedQuery(query.query_text, org=self.current_org)
-
+        apply_auto_limit = query.options.get("apply_auto_limit", False)
         return run_query(
-            parameterized_query, parameter_values, query.data_source, query.id
+            parameterized_query, parameter_values, query.data_source, query.id, apply_auto_limit
         )
 
 
diff --git a/redash/handlers/query_results.py b/redash/handlers/query_results.py
index 4b9e189cce..8b6dbb6628 100644
--- a/redash/handlers/query_results.py
+++ b/redash/handlers/query_results.py
@@ -61,7 +61,7 @@ def error_response(message, http_status=400):
 }
 
 
-def run_query(query, parameters, data_source, query_id, max_age=0):
+def run_query(query, parameters, data_source, query_id, apply_auto_limit, max_age=0):
     if data_source.paused:
         if data_source.pause_reason:
             message = "{} is paused ({}). Please try later.".format(
@@ -77,6 +77,8 @@ def run_query(query, parameters, data_source, query_id, max_age=0):
     except (InvalidParameterError, QueryDetachedFromDataSourceError) as e:
         abort(400, message=str(e))
 
+    query_text = data_source.query_runner.apply_auto_limit(query.text, apply_auto_limit)
+
     if query.missing_params:
         return error_response(
             "Missing parameter value for: {}".format(", ".join(query.missing_params))
@@ -85,7 +87,7 @@ def run_query(query, parameters, data_source, query_id, max_age=0):
     if max_age == 0:
         query_result = None
     else:
-        query_result = models.QueryResult.get_latest(data_source, query.text, max_age)
+        query_result = models.QueryResult.get_latest(data_source, query_text, max_age)
 
     record_event(
         current_user.org,
@@ -95,7 +97,7 @@ def run_query(query, parameters, data_source, query_id, max_age=0):
             "cache": "hit" if query_result else "miss",
             "object_id": data_source.id,
             "object_type": "data_source",
-            "query": query.text,
+            "query": query_text,
             "query_id": query_id,
             "parameters": parameters,
         },
@@ -109,7 +111,7 @@ def run_query(query, parameters, data_source, query_id, max_age=0):
         }
     else:
         job = enqueue_query(
-            query.text,
+            query_text,
             data_source,
             current_user.id,
             current_user.is_api_user(),
@@ -180,6 +182,7 @@ def post(self):
         )
 
         parameterized_query = ParameterizedQuery(query, org=self.current_org)
+        apply_auto_limit = params.get("apply_auto_limit", False)
 
         data_source_id = params.get("data_source_id")
         if data_source_id:
@@ -193,7 +196,7 @@ def post(self):
             return error_messages["no_permission"]
 
         return run_query(
-            parameterized_query, parameters, data_source, query_id, max_age
+            parameterized_query, parameters, data_source, query_id, apply_auto_limit, max_age
         )
 
 
@@ -286,6 +289,7 @@ def post(self, query_id):
         )
 
         allow_executing_with_view_only_permissions = query.parameterized.is_safe
+        apply_auto_limit = params.get("apply_auto_limit", False)
 
         if has_access(
             query, self.current_user, allow_executing_with_view_only_permissions
@@ -295,6 +299,7 @@ def post(self, query_id):
                 parameter_values,
                 query.data_source,
                 query_id,
+                apply_auto_limit,
                 max_age,
             )
         else:
diff --git a/redash/models/__init__.py b/redash/models/__init__.py
index 1d5528aaca..c109fe43d4 100644
--- a/redash/models/__init__.py
+++ b/redash/models/__init__.py
@@ -122,6 +122,7 @@ def to_dict(self, all=False, with_permissions_for=None):
             "syntax": self.query_runner.syntax,
             "paused": self.paused,
             "pause_reason": self.pause_reason,
+            "apply_auto_limit": self.query_runner.supports_auto_limit
         }
 
         if all:
diff --git a/redash/query_runner/__init__.py b/redash/query_runner/__init__.py
index 9f92ffe637..2a2b4e5388 100644
--- a/redash/query_runner/__init__.py
+++ b/redash/query_runner/__init__.py
@@ -10,7 +10,7 @@
 from six import text_type
 from sshtunnel import open_tunnel
 from redash import settings
-from redash.utils import json_loads
+from redash.utils import json_loads, query_is_select_no_limit, add_limit_to_query
 from rq.timeouts import JobTimeoutException
 
 from redash.utils.requests_session import requests, requests_session
@@ -83,7 +83,7 @@ def host(self):
         """Returns this query runner's configured host.
         This is used primarily for temporarily swapping endpoints when using SSH tunnels to connect to a data source.
 
-        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port` 
+        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port`
         configuration values. If your query runner uses a different schema (e.g. a web address), you should override this function.
         """
         if "host" in self.configuration:
@@ -96,7 +96,7 @@ def host(self, host):
         """Sets this query runner's configured host.
         This is used primarily for temporarily swapping endpoints when using SSH tunnels to connect to a data source.
 
-        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port` 
+        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port`
         configuration values. If your query runner uses a different schema (e.g. a web address), you should override this function.
         """
         if "host" in self.configuration:
@@ -109,7 +109,7 @@ def port(self):
         """Returns this query runner's configured port.
         This is used primarily for temporarily swapping endpoints when using SSH tunnels to connect to a data source.
 
-        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port` 
+        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port`
         configuration values. If your query runner uses a different schema (e.g. a web address), you should override this function.
         """
         if "port" in self.configuration:
@@ -122,7 +122,7 @@ def port(self, port):
         """Sets this query runner's configured port.
         This is used primarily for temporarily swapping endpoints when using SSH tunnels to connect to a data source.
 
-        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port` 
+        `BaseQueryRunner`'s naïve implementation supports query runner implementations that store endpoints using `host` and `port`
         configuration values. If your query runner uses a different schema (e.g. a web address), you should override this function.
         """
         if "port" in self.configuration:
@@ -190,6 +190,13 @@ def to_dict(cls):
             **({"deprecated": True} if cls.deprecated else {}),
         }
 
+    @property
+    def supports_auto_limit(self):
+        return False
+
+    def apply_auto_limit(self, query_text, set_auto_limit):
+        return query_text
+
 
 class BaseSQLQueryRunner(BaseQueryRunner):
     def get_schema(self, get_stats=False):
@@ -208,6 +215,22 @@ def _get_tables_stats(self, tables_dict):
                 res = self._run_query_internal("select count(*) as cnt from %s" % t)
                 tables_dict[t]["size"] = res[0]["cnt"]
 
+    @property
+    def supports_auto_limit(self):
+        return True
+
+    def apply_auto_limit(self, query_text, set_auto_limit):
+        if set_auto_limit:
+            from redash.query_runner.databricks import split_sql_statements
+            query_list = split_sql_statements(query_text)
+            # we only check for last one in the list because it is the one that we show result
+            last_query = query_list[-1]
+            if query_is_select_no_limit(last_query):
+                query_list[-1] = add_limit_to_query(last_query)
+            return ";\n".join(query_list)
+        else:
+            return query_text
+
 
 def is_private_address(url):
     hostname = urlparse(url).hostname
diff --git a/redash/tasks/queries/maintenance.py b/redash/tasks/queries/maintenance.py
index c54cd05aa3..92e37f5547 100644
--- a/redash/tasks/queries/maintenance.py
+++ b/redash/tasks/queries/maintenance.py
@@ -76,6 +76,11 @@ class RefreshQueriesError(Exception):
     pass
 
 
+def _apply_auto_limit(query_text, query):
+    set_auto_limit = query.options.get("apply_auto_limit", False)
+    return query.data_source.query_runner.apply_auto_limit(query_text, set_auto_limit)
+
+
 def refresh_queries():
     logger.info("Refreshing queries...")
     enqueued = []
@@ -84,8 +89,10 @@ def refresh_queries():
             continue
 
         try:
+            query_text = _apply_default_parameters(query)
+            query_text = _apply_auto_limit(query_text, query)
             enqueue_query(
-                _apply_default_parameters(query),
+                query_text,
                 query.data_source,
                 query.user_id,
                 scheduled_query=query,
diff --git a/redash/utils/__init__.py b/redash/utils/__init__.py
index 533bc96100..c0930d899c 100644
--- a/redash/utils/__init__.py
+++ b/redash/utils/__init__.py
@@ -13,6 +13,7 @@
 import pystache
 import pytz
 import simplejson
+import sqlparse
 from flask import current_app
 from funcy import select_values
 from redash import settings
@@ -70,8 +71,8 @@ def generate_token(length):
 
 class JSONEncoder(simplejson.JSONEncoder):
     """Adapter for `simplejson.dumps`."""
-    
-    
+
+
     def default(self, o):
         # Some SQLAlchemy collections are lazy.
         if isinstance(o, Query):
@@ -213,3 +214,32 @@ def render_template(path, context):
     function decorated with the `context_processor` decorator, which is not explicitly required for rendering purposes.
     """
     current_app.jinja_env.get_template(path).render(**context)
+
+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
+
+def find_last_keyword_idx(parsed_query):
+    for i in reversed(range(len(parsed_query.tokens))):
+        if parsed_query.tokens[i].ttype in sqlparse.tokens.Keyword:
+            return i
+    return -1
+
+def add_limit_to_query(query):
+    # this function will only be called if query_is_select_no_limit returns True
+    parsed_query = sqlparse.parse(query)[0]
+    limit_tokens = sqlparse.parse(" LIMIT 1000")[0].tokens
+    length = len(parsed_query.tokens)
+    if parsed_query.tokens[length - 1].ttype == sqlparse.tokens.Punctuation:
+        parsed_query.tokens[length - 1:length - 1] = limit_tokens
+    else:
+        parsed_query.tokens += limit_tokens
+    return str(parsed_query)
+
diff --git a/tests/handlers/test_query_results.py b/tests/handlers/test_query_results.py
index 57a9e3dbc7..be448d51d5 100644
--- a/tests/handlers/test_query_results.py
+++ b/tests/handlers/test_query_results.py
@@ -1,3 +1,4 @@
+from redash.query_runner import BaseSQLQueryRunner, BaseQueryRunner
 from tests import BaseTestCase
 
 from redash.models import db
@@ -39,7 +40,7 @@ def test_supports_unicode(self):
         try:
             rv.headers['Content-Disposition'].encode('ascii')
         except Exception as e:
-            self.fail(repr(e))            
+            self.fail(repr(e))
 
 
 class TestQueryResultListAPI(BaseTestCase):
@@ -76,6 +77,47 @@ def test_execute_new_query(self):
         self.assertNotIn("query_result", rv.json)
         self.assertIn("job", rv.json)
 
+    def test_add_limit_change_query_sql(self):
+        ds = self.factory.create_data_source(
+            group=self.factory.org.default_group, type="databricks"
+        )
+        query = self.factory.create_query(query_text="SELECT 2", data_source=ds)
+        query_result = self.factory.create_query_result(data_source=ds, query_hash=query.query_hash)
+
+        rv = self.make_request(
+            "post",
+            "/api/query_results",
+            data={
+                "data_source_id": ds.id,
+                "query": query.query_text,
+                "apply_auto_limit": True
+            },
+        )
+
+        self.assertEqual(rv.status_code, 200)
+        self.assertNotIn("query_result", rv.json)
+        self.assertIn("job", rv.json)
+
+    def test_add_limit_no_change_for_nonsql(self):
+        ds = self.factory.create_data_source(
+            group=self.factory.org.default_group, type="druid"
+        )
+        query = self.factory.create_query(query_text="SELECT 5", data_source=ds)
+        query_result = self.factory.create_query_result(data_source=ds, query_hash=query.query_hash)
+
+        rv = self.make_request(
+            "post",
+            "/api/query_results",
+            data={
+                "data_source_id": ds.id,
+                "query": query.query_text,
+                "apply_auto_limit": True
+            },
+        )
+
+        self.assertEqual(rv.status_code, 200)
+        self.assertEqual(query_result.id, rv.json["query_result"]["id"])
+
     def test_execute_query_without_access(self):
         group = self.factory.create_group()
         db.session.commit()
diff --git a/tests/query_runner/test_basesql_queryrunner.py b/tests/query_runner/test_basesql_queryrunner.py
new file mode 100644
index 0000000000..2a76277d1c
--- /dev/null
+++ b/tests/query_runner/test_basesql_queryrunner.py
@@ -0,0 +1,88 @@
+import unittest
+
+from redash.query_runner import BaseSQLQueryRunner, BaseQueryRunner
+
+
+class TestBaseSQLQueryRunner(unittest.TestCase):
+
+    def setUp(self):
+        self.query_runner = BaseSQLQueryRunner({})
+
+    def test_apply_auto_limit_origin_no_limit_1(self):
+        origin_query_text = "SELECT 2"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual("SELECT 2 LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_origin_have_limit_1(self):
+        origin_query_text = "SELECT 2 LIMIT 100"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text, query_text)
+
+    def test_apply_auto_limit_origin_have_limit_2(self):
+        origin_query_text = "SELECT * FROM fake WHERE id IN (SELECT id FROM fake_2 LIMIT 200) LIMIT 200"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text, query_text)
+
+    def test_apply_auto_limit_origin_no_limit_2(self):
+        origin_query_text = "SELECT * FROM fake WHERE id IN (SELECT id FROM fake_2 LIMIT 200)"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text + " LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_non_select_query(self):
+        origin_query_text = ("create table execution_times as "
+                             "(select id, retrieved_at, data_source_id, query, runtime, query_hash "
+                             "from query_results order by 1 desc)")
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text, query_text)
+
+    def test_apply_auto_limit_error_query(self):
+        origin_query_text = "dklsk jdhsajhdiwc kkdsakjdwi mdklsjal"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text, query_text)
+
+    def test_apply_auto_limit_multi_query_add_limit_1(self):
+        origin_query_text = ("insert into execution_times (id, retrieved_at, data_source_id, query, runtime, query_hash) "
+                            "select id, retrieved_at, data_source_id, query, runtime, query_hash from query_results "
+                            "where id > (select max(id) from execution_times);\n"
+                            "select max(id), 'execution_times' as table_name from execution_times "
+                            "union all "
+                            "select max(id), 'query_results' as table_name from query_results")
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text + " LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_multi_query_add_limit_2(self):
+        origin_query_text = "use database demo;\n" \
+                            "select * from data"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text + " LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_multi_query_end_with_punc(self):
+        origin_query_text = ("select * from table1;\n"
+                             "select * from table2")
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual("select * from table1;\nselect * from table2 LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_multi_query_last_not_select(self):
+        origin_query_text = ("select * from table1;\n"
+                             "CREATE TABLE Persons (PersonID int)")
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual(origin_query_text, query_text)
+
+    def test_apply_auto_limit_last_command_comment(self):
+        origin_query_text = "select * from raw_events; # comment"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual("select * from raw_events LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_last_command_comment_2(self):
+        origin_query_text = "select * from raw_events; -- comment"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual("select * from raw_events LIMIT 1000", query_text)
+
+    def test_apply_auto_limit_inline_comment(self):
+        origin_query_text = "select * from raw_events -- comment"
+        query_text = self.query_runner.apply_auto_limit(origin_query_text, True)
+        self.assertEqual("select * from raw_events LIMIT 1000", query_text)
+
+
+if __name__ == '__main__':
+    unittest.main()
diff --git a/tests/tasks/test_refresh_queries.py b/tests/tasks/test_refresh_queries.py
index 452c1b7702..918421d74c 100644
--- a/tests/tasks/test_refresh_queries.py
+++ b/tests/tasks/test_refresh_queries.py
@@ -7,14 +7,15 @@
 
 
 class TestRefreshQuery(BaseTestCase):
-    def test_enqueues_outdated_queries(self):
+    def test_enqueues_outdated_queries_for_sqlquery(self):
         """
         refresh_queries() launches an execution task for each query returned
         from Query.outdated_queries().
         """
-        query1 = self.factory.create_query()
+        query1 = self.factory.create_query(options={"apply_auto_limit": True})
         query2 = self.factory.create_query(
-            query_text="select 42;", data_source=self.factory.create_data_source()
+            query_text="select 42;", data_source=self.factory.create_data_source(),
+            options={"apply_auto_limit": True}
         )
         oq = staticmethod(lambda: [query1, query2])
         with patch(ENQUEUE_QUERY) as add_job_mock, patch.object(
@@ -25,12 +26,50 @@ def test_enqueues_outdated_queries(self):
             add_job_mock.assert_has_calls(
                 [
                     call(
-                        query1.query_text,
+                        query1.query_text + " LIMIT 1000",
                         query1.data_source,
                         query1.user_id,
                         scheduled_query=query1,
                         metadata=ANY,
                     ),
+                    call(
+                        "select 42 LIMIT 1000",
+                        query2.data_source,
+                        query2.user_id,
+                        scheduled_query=query2,
+                        metadata=ANY,
+                    ),
+                ],
+                any_order=True,
+            )
+
+    def test_enqueues_outdated_queries_for_non_sqlquery(self):
+        """
+        refresh_queries() launches an execution task for each query returned
+        from Query.outdated_queries().
+        """
+        ds = self.factory.create_data_source(
+            group=self.factory.org.default_group, type="phoenix"
+        )
+        query1 = self.factory.create_query(data_source=ds, options={"apply_auto_limit": True})
+        query2 = self.factory.create_query(
+            query_text="select 42;", data_source=ds, options={"apply_auto_limit": True}
+        )
+        oq = staticmethod(lambda: [query1, query2])
+        with patch(ENQUEUE_QUERY) as add_job_mock, patch.object(
+            Query, "outdated_queries", oq
+        ):
+            refresh_queries()
+            self.assertEqual(add_job_mock.call_count, 2)
+            add_job_mock.assert_has_calls(
+                [
+                    call(
+                        query1.query_text,
+                        query1.data_source,
+                        query1.user_id,
+                        scheduled_query=query1,
+                        metadata=ANY,
+                        ),
                     call(
                         query2.query_text,
                         query2.data_source,
@@ -42,12 +81,12 @@ def test_enqueues_outdated_queries(self):
                 any_order=True,
             )
 
-    def test_doesnt_enqueue_outdated_queries_for_paused_data_source(self):
+    def test_doesnt_enqueue_outdated_queries_for_paused_data_source_for_sqlquery(self):
         """
         refresh_queries() does not launch execution tasks for queries whose
         data source is paused.
         """
-        query = self.factory.create_query()
+        query = self.factory.create_query(options={"apply_auto_limit": True})
         oq = staticmethod(lambda: [query])
         query.data_source.pause()
         with patch.object(Query, "outdated_queries", oq):
@@ -60,14 +99,42 @@ def test_doesnt_enqueue_outdated_queries_for_paused_data_source(self):
             with patch(ENQUEUE_QUERY) as add_job_mock:
                 refresh_queries()
                 add_job_mock.assert_called_with(
-                    query.query_text,
+                    query.query_text + " LIMIT 1000",
                     query.data_source,
                     query.user_id,
                     scheduled_query=query,
                     metadata=ANY,
                 )
 
-    def test_enqueues_parameterized_queries(self):
+    def test_doesnt_enqueue_outdated_queries_for_paused_data_source_for_non_sqlquery(self):
+        """
+        refresh_queries() does not launch execution tasks for queries whose
+        data source is paused.
+        """
+        ds = self.factory.create_data_source(
+            group=self.factory.org.default_group, type="phoenix"
+        )
+        query = self.factory.create_query(data_source=ds, options={"apply_auto_limit": True})
+        oq = staticmethod(lambda: [query])
+        query.data_source.pause()
+        with patch.object(Query, "outdated_queries", oq):
+            with patch(ENQUEUE_QUERY) as add_job_mock:
+                refresh_queries()
+                add_job_mock.assert_not_called()
+
+            query.data_source.resume()
+
+            with patch(ENQUEUE_QUERY) as add_job_mock:
+                refresh_queries()
+                add_job_mock.assert_called_with(
+                    query.query_text,
+                    query.data_source,
+                    query.user_id,
+                    scheduled_query=query,
+                    metadata=ANY,
+                    )
+
+    def test_enqueues_parameterized_queries_for_sqlquery(self):
         """
         Scheduled queries with parameters use saved values.
         """
@@ -82,10 +149,48 @@ def test_enqueues_parameterized_queries(self):
                         "value": "42",
                         "title": "n",
                     }
-                ]
+                ],
+                "apply_auto_limit": True
             },
         )
         oq = staticmethod(lambda: [query])
+        with patch(ENQUEUE_QUERY) as add_job_mock, patch.object(
+            Query, "outdated_queries", oq
+        ):
+            refresh_queries()
+            add_job_mock.assert_called_with(
+                "select 42 LIMIT 1000",
+                query.data_source,
+                query.user_id,
+                scheduled_query=query,
+                metadata=ANY,
+            )
+
+    def test_enqueues_parameterized_queries_for_non_sqlquery(self):
+        """
+        Scheduled queries with parameters use saved values.
+        """
+        ds = self.factory.create_data_source(
+            group=self.factory.org.default_group, type="phoenix"
+        )
+        query = self.factory.create_query(
+            query_text="select {{n}}",
+            options={
+                "parameters": [
+                    {
+                        "global": False,
+                        "type": "text",
+                        "name": "n",
+                        "value": "42",
+                        "title": "n",
+                    }
+                ],
+                "apply_auto_limit": True
+
+            },
+            data_source=ds,
+        )
+        oq = staticmethod(lambda: [query])
         with patch(ENQUEUE_QUERY) as add_job_mock, patch.object(
             Query, "outdated_queries", oq
         ):
@@ -113,7 +218,8 @@ def test_doesnt_enqueue_parameterized_queries_with_invalid_parameters(self):
                         "value": 42,  # <-- should be text!
                         "title": "n",
                     }
-                ]
+                ],
+                "apply_auto_limit": True
             },
         )
         oq = staticmethod(lambda: [query])
@@ -140,7 +246,8 @@ def test_doesnt_enqueue_parameterized_queries_with_dropdown_queries_that_are_det
                         "queryId": 100,
                         "title": "n",
                     }
-                ]
+                ],
+                "apply_auto_limit": True
             },
         )
 
diff --git a/tests/utils/test_query_limit.py b/tests/utils/test_query_limit.py
new file mode 100644
index 0000000000..089d5592f1
--- /dev/null
+++ b/tests/utils/test_query_limit.py
@@ -0,0 +1,41 @@
+import unittest
+
+from redash.utils import query_is_select_no_limit, add_limit_to_query
+
+
+class TestQueryLimit(unittest.TestCase):
+    def test_check_query_limit_no_limit(self):
+        query = "SELECT *"
+        self.assertEqual(True, query_is_select_no_limit(query))
+
+    def test_check_query_limit_non_select(self):
+        query = "Create Table (PersonID INT)"
+        self.assertEqual(False, query_is_select_no_limit(query))
+
+    def test_check_query_limit_invalid_1(self):
+        query = "OFFSET 5"
+        self.assertEqual(False, query_is_select_no_limit(query))
+
+    def test_check_query_limit_invalid_2(self):
+        query = "TABLE A FROM TABLE B"
+        self.assertEqual(False, query_is_select_no_limit(query))
+
+    def test_check_query_with_limit(self):
+        query = "SELECT * LIMIT 5"
+        self.assertEqual(False, query_is_select_no_limit(query))
+
+    def test_check_query_with_offset(self):
+        query = "SELECT * LIMIT 5 OFFSET 3"
+        self.assertEqual(False, query_is_select_no_limit(query))
+
+    def test_add_limit_query_no_limit(self):
+        query = "SELECT *"
+        self.assertEqual("SELECT * LIMIT 1000", add_limit_to_query(query))
+
+    def test_add_limit_query_with_punc(self):
+        query = "SELECT *;"
+        self.assertEqual("SELECT * LIMIT 1000;", add_limit_to_query(query))
+
+
+if __name__ == '__main__':
+    unittest.main()


Code review comments:
diff_hunk: @@ -865,10 +867,14 @@ def dashboard_api_keys(self):
         return [api_key[0] for api_key in api_keys]
 
 
-@listens_for(Query.query_text, "set")
-def gen_query_hash(target, val, oldval, initiator):
-    target.query_hash = utils.gen_query_hash(val)
-    target.schedule_failures = 0
+@listens_for(Query, "before_insert")
path: redash/models/__init__.py
text: @user2:
I'm pretty sure you can apply both decorator to a single method:

```python
@user1(Query, "before_insert")
@user1(Query, "before_update")
def update_query_hash(...):
    ...
```

diff_hunk: @@ -122,6 +123,7 @@ def to_dict(self, all=False, with_permissions_for=None):
             "syntax": self.query_runner.syntax,
             "paused": self.paused,
             "pause_reason": self.pause_reason,
+            "apply_auto_limit": self.query_runner.supports_auto_limit
path: redash/models/__init__.py
text: @user1:
```suggestion
            "supports_auto_limit": self.query_runner.supports_auto_limit
```

This should be `supports_auto_limit` too. 

Sorry for the nits, it just that these things are very hard to change later.

diff_hunk: @@ -0,0 +1,24 @@
+import { useCallback, useState } from "react";
+import localOptions from "@/lib/localOptions";
+import { get, extend } from "lodash";
+
+function isAutoLimitAvailable(dataSource) {
+  return get(dataSource, "apply_auto_limit", false);
+}
+
+export default function useAutoLimitFlags(dataSource, query, setQuery) {
+  const isAvailable = isAutoLimitAvailable(dataSource);
+  const [isChecked, setIsChecked] = useState(localOptions.get("applyAutoLimit", true));
+  query.options.applyAutoLimit = isAvailable && isChecked;
path: client/app/pages/queries/hooks/useAutoLimitFlags.js
text: @user1:
Is this to guarantee the `applyAutoLimit` is set to false when not available? If so, how about we put this line into a

```jsx
useEffect(() => {
  query.options.applyAutoLimit = isAvailable && isChecked;
}, [isAvailable, isChecked]);
```
? This way it will update that value only when one the vars change and not on every render.

@user2:
Maybe I'm missing something, but it's a simple operation between two boolean values which most of the time yields the same value (so no change in rendering). Why complicate the code with a `useEffect`?

@user1:
I thought it mostly not to be reassigning a value uneedlessly, it just felt a bit weird to mutate such value on every re-render, so my take was to limit it with `useEffect`. It is a boolean value, so it won't trigger any rerender indeed, it's more on the case it was updated out of this hook's context. Which thinking now, doesn't happen 😅



@user2:
:-)

We need to be careful with such optimizations, because in this case it's likely making the code less optimized (instead of boolean assignment, we get functions calls, function definitions, etc) but what's even worse is that makes the code less readable.