tonycui opened a new issue, #69553: URL: https://github.com/apache/doris/issues/69553
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description **Version:** Doris 4.1.3 (compute-storage separation), `enable_sql_cache = true`. **What happens** The SQL cache is skipped whenever the session variable `sql_select_limit` is set to any value, even when the same SQL is run again on the same session with the same value. `CacheAnalyzer.commonCacheCondition` (fe-core, `qe/cache/CacheAnalyzer.java`) requires: ```java return sessionVariable.getSqlSelectLimit() < 0 && sessionVariable.getDefaultOrderByLimit() < 0 && ... ``` MySQL-protocol JDBC drivers implement `Statement.setMaxRows(n)` by sending `SET sql_select_limit = n` (MariaDB Connector/J and MySQL Connector/J both do this). BI tools call `setMaxRows` on every query to cap the rows they display. Metabase, for example, always passes a positive limit (2000 for UI results, at most 1048575), so with Doris the SQL cache is never used for any Metabase query. **How to reproduce** ```sql set enable_sql_cache = true; select k, sum(v) from t group by k; -- 1st run: miss select k, sum(v) from t group by k; -- 2nd run: hit (audit_log.hit_sql_cache = 1, scan_rows = 0) set sql_select_limit = 2000; select k, sum(v) from t group by k order by k; -- miss select k, sum(v) from t group by k order by k; -- miss again, every time ``` In our test the same query took ~0.04 s from the cache without `sql_select_limit` and ~0.35 s on every run with `sql_select_limit = 2000` (six runs, all `hit_sql_cache = 0`, no table change in between). **Why it matters** For dashboards that many people open within a few minutes, the SQL cache is the main way to avoid recomputing the same queries, but it is unusable from Metabase (and likely other JDBC-based BI tools) because of this one variable. ### Solution Treat `sql_select_limit` like the other variables that change the result, instead of disabling the cache: - `sql_select_limit` is already annotated `affectQueryResultInExecution = true` in `SessionVariable`, and `sql_mode` (`affectQueryResultInPlan = true`) is already handled by putting it into the cache key / `affectQueryResultVariables`. - Include the `sql_select_limit` value in the SQL cache key (or in the variables compared on lookup), and remove `getSqlSelectLimit() < 0` from `commonCacheCondition`. A query run with limit 2000 would then reuse only results cached with limit 2000, which keeps results correct. - The same could apply to `default_order_by_limit`, which is checked in the same condition. For comparison, the Snowflake JDBC driver keeps `setMaxRows` on the client side, so the server sees the plain SQL and its result cache works for BI tools. We have also asked the Metabase Doris driver project whether the driver can avoid sending `sql_select_limit`; I will link that issue here. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
