Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 19d9296

Browse filesBrowse files
htyounghtyoung
authored
[fix](cache) fix same sql return wrong result when switch database with use db and enable sql cache (#44782) (#44852)
It will return wrong result when running same sql in different db with `enable_sql_cache=true` for example, the `db1` and `db2` has the same table `tbl` but the data are not equals, if execute the below sql in `db1` and cache the result, then execute it in `db2`, it will return the wrong result ```sql select count(*) from tbl ```
1 parent 9affc10 commit 19d9296
Copy full SHA for 19d9296

File tree

Expand file treeCollapse file tree

2 files changed

+105
-18
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+105
-18
lines changed

‎fe/fe-core/src/main/java/org/apache/doris/common/NereidsSqlCacheManager.java

Copy file name to clipboardExpand all lines: fe/fe-core/src/main/java/org/apache/doris/common/NereidsSqlCacheManager.java
+31-18Lines changed: 31 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -74,9 +74,11 @@
7474
import java.util.Optional;
7575
import java.util.Set;
7676

77-
/** NereidsSqlCacheManager */
77+
/**
78+
* NereidsSqlCacheManager
79+
*/
7880
public class NereidsSqlCacheManager {
79-
// key: <user>:<sql>
81+
// key: <ctl.db>:<user>:<sql>
8082
// value: SqlCacheContext
8183
private volatile Cache<String, SqlCacheContext> sqlCaches;
8284

@@ -110,7 +112,7 @@ private static Cache<String, SqlCacheContext> buildSqlCaches(int sqlCacheNum, lo
110112
// auto evict cache when jvm memory too low
111113
.softValues();
112114
if (sqlCacheNum > 0) {
113-
cacheBuilder = cacheBuilder.maximumSize(sqlCacheNum);
115+
cacheBuilder.maximumSize(sqlCacheNum);
114116
}
115117
if (expireAfterAccessSeconds > 0) {
116118
cacheBuilder = cacheBuilder.expireAfterAccess(Duration.ofSeconds(expireAfterAccessSeconds));
@@ -119,25 +121,28 @@ private static Cache<String, SqlCacheContext> buildSqlCaches(int sqlCacheNum, lo
119121
return cacheBuilder.build();
120122
}
121123

122-
/** tryAddFeCache */
124+
/**
125+
* tryAddFeCache
126+
*/
123127
public void tryAddFeSqlCache(ConnectContext connectContext, String sql) {
124128
Optional<SqlCacheContext> sqlCacheContextOpt = connectContext.getStatementContext().getSqlCacheContext();
125129
if (!sqlCacheContextOpt.isPresent()) {
126130
return;
127131
}
128132

129133
SqlCacheContext sqlCacheContext = sqlCacheContextOpt.get();
130-
UserIdentity currentUserIdentity = connectContext.getCurrentUserIdentity();
131134
String key = sqlCacheContext.getCacheKeyType() == CacheKeyType.SQL
132-
? currentUserIdentity.toString() + ":" + normalizeSql(sql.trim())
133-
: currentUserIdentity.toString() + ":" + DebugUtil.printId(sqlCacheContext.getOrComputeCacheKeyMd5());
135+
? generateCacheKey(connectContext, normalizeSql(sql))
136+
: generateCacheKey(connectContext, DebugUtil.printId(sqlCacheContext.getOrComputeCacheKeyMd5()));
134137
if (sqlCaches.getIfPresent(key) == null && sqlCacheContext.getOrComputeCacheKeyMd5() != null
135138
&& sqlCacheContext.getResultSetInFe().isPresent()) {
136139
sqlCaches.put(key, sqlCacheContext);
137140
}
138141
}
139142

140-
/** tryAddBeCache */
143+
/**
144+
* tryAddBeCache
145+
*/
141146
public void tryAddBeCache(ConnectContext connectContext, String sql, CacheAnalyzer analyzer) {
142147
Optional<SqlCacheContext> sqlCacheContextOpt = connectContext.getStatementContext().getSqlCacheContext();
143148
if (!sqlCacheContextOpt.isPresent()) {
@@ -147,10 +152,9 @@ public void tryAddBeCache(ConnectContext connectContext, String sql, CacheAnalyz
147152
return;
148153
}
149154
SqlCacheContext sqlCacheContext = sqlCacheContextOpt.get();
150-
UserIdentity currentUserIdentity = connectContext.getCurrentUserIdentity();
151155
String key = sqlCacheContext.getCacheKeyType() == CacheKeyType.SQL
152-
? currentUserIdentity.toString() + ":" + normalizeSql(sql.trim())
153-
: currentUserIdentity.toString() + ":" + DebugUtil.printId(sqlCacheContext.getOrComputeCacheKeyMd5());
156+
? generateCacheKey(connectContext, normalizeSql(sql))
157+
: generateCacheKey(connectContext, DebugUtil.printId(sqlCacheContext.getOrComputeCacheKeyMd5()));
154158
if (sqlCaches.getIfPresent(key) == null && sqlCacheContext.getOrComputeCacheKeyMd5() != null) {
155159
SqlCache cache = (SqlCache) analyzer.getCache();
156160
sqlCacheContext.setSumOfPartitionNum(cache.getSumOfPartitionNum());
@@ -167,23 +171,23 @@ public void tryAddBeCache(ConnectContext connectContext, String sql, CacheAnalyz
167171
}
168172
}
169173

170-
/** tryParseSql */
174+
/**
175+
* tryParseSql
176+
*/
171177
public Optional<LogicalSqlCache> tryParseSql(ConnectContext connectContext, String sql) {
172-
UserIdentity currentUserIdentity = connectContext.getCurrentUserIdentity();
173-
String key = currentUserIdentity + ":" + normalizeSql(sql.trim());
178+
String key = generateCacheKey(connectContext, normalizeSql(sql.trim()));
174179
SqlCacheContext sqlCacheContext = sqlCaches.getIfPresent(key);
175180
if (sqlCacheContext == null) {
176181
return Optional.empty();
177182
}
178183

179184
// LOG.info("Total size: " + GraphLayout.parseInstance(sqlCacheContext).totalSize());
180-
185+
UserIdentity currentUserIdentity = connectContext.getCurrentUserIdentity();
181186
List<Variable> currentVariables = resolveUserVariables(sqlCacheContext);
182187
if (usedVariablesChanged(currentVariables, sqlCacheContext)) {
183188
String md5 = DebugUtil.printId(
184189
sqlCacheContext.doComputeCacheKeyMd5(Utils.fastToImmutableSet(currentVariables)));
185-
186-
String md5CacheKey = currentUserIdentity + ":" + md5;
190+
String md5CacheKey = generateCacheKey(connectContext, md5);
187191
SqlCacheContext sqlCacheContextWithVariable = sqlCaches.getIfPresent(md5CacheKey);
188192

189193
// already exist cache in the fe, but the variable is different to this query,
@@ -203,6 +207,15 @@ public Optional<LogicalSqlCache> tryParseSql(ConnectContext connectContext, Stri
203207
}
204208
}
205209

210+
private String generateCacheKey(ConnectContext connectContext, String sqlOrMd5) {
211+
CatalogIf<?> currentCatalog = connectContext.getCurrentCatalog();
212+
String currentCatalogName = currentCatalog != null ? currentCatalog.getName() : "";
213+
String currentDatabase = connectContext.getDatabase();
214+
String currentDatabaseName = currentDatabase != null ? currentDatabase : "";
215+
return currentCatalogName + "." + currentDatabaseName + ":" + connectContext.getCurrentUserIdentity().toString()
216+
+ ":" + sqlOrMd5;
217+
}
218+
206219
private String normalizeSql(String sql) {
207220
return NereidsParser.removeCommentAndTrimBlank(sql);
208221
}
@@ -402,7 +415,7 @@ private boolean usedVariablesChanged(List<Variable> currentVariables, SqlCacheCo
402415
Variable cachedVariable = cachedUsedVariables.get(i);
403416
if (!Objects.equals(currentVariable, cachedVariable)
404417
|| cachedVariable.getRealExpression().anyMatch(
405-
expr -> !((ExpressionTrait) expr).isDeterministic())) {
418+
expr -> !((ExpressionTrait) expr).isDeterministic())) {
406419
return true;
407420
}
408421
}

‎regression-test/suites/nereids_p0/cache/parse_sql_from_sql_cache.groovy

Copy file name to clipboardExpand all lines: regression-test/suites/nereids_p0/cache/parse_sql_from_sql_cache.groovy
+74Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -827,6 +827,80 @@ suite("parse_sql_from_sql_cache") {
827827
def result = sql "select FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss')"
828828
assertNotEquals("yyyy-MM-dd HH:mm:ss", result[0][0])
829829
}
830+
}),
831+
extraThread("test_same_sql_with_different_db", {
832+
def dbName1 = "test_db1"
833+
def dbName2 = "test_db2"
834+
def tableName = "test_cache_table"
835+
836+
sql "CREATE DATABASE IF NOT EXISTS ${dbName1}"
837+
sql "DROP TABLE IF EXISTS ${dbName1}.${tableName}"
838+
sql """
839+
CREATE TABLE IF NOT EXISTS ${dbName1}.${tableName} (
840+
`k1` date NOT NULL COMMENT "",
841+
`k2` int(11) NOT NULL COMMENT ""
842+
) ENGINE=OLAP
843+
DUPLICATE KEY(`k1`, `k2`)
844+
COMMENT "OLAP"
845+
PARTITION BY RANGE(`k1`)
846+
(PARTITION p202411 VALUES [('2024-11-01'), ('2024-12-01')))
847+
DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 1
848+
PROPERTIES (
849+
"replication_allocation" = "tag.location.default: 1",
850+
"in_memory" = "false",
851+
"storage_format" = "V2"
852+
)
853+
"""
854+
sql "CREATE DATABASE IF NOT EXISTS ${dbName2}"
855+
sql "DROP TABLE IF EXISTS ${dbName2}.${tableName}"
856+
sql """
857+
CREATE TABLE IF NOT EXISTS ${dbName2}.${tableName} (
858+
`k1` date NOT NULL COMMENT "",
859+
`k2` int(11) NOT NULL COMMENT ""
860+
) ENGINE=OLAP
861+
DUPLICATE KEY(`k1`, `k2`)
862+
COMMENT "OLAP"
863+
PARTITION BY RANGE(`k1`)
864+
(PARTITION p202411 VALUES [('2024-11-01'), ('2024-12-01')))
865+
DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 1
866+
PROPERTIES (
867+
"replication_allocation" = "tag.location.default: 1",
868+
"in_memory" = "false",
869+
"storage_format" = "V2"
870+
)
871+
"""
872+
873+
sql """
874+
INSERT INTO ${dbName1}.${tableName} VALUES
875+
("2024-11-29",0),
876+
("2024-11-30",0)
877+
"""
878+
// after partition changed 10s, the sql cache can be used
879+
sleep(10000)
880+
sql """
881+
INSERT INTO ${dbName2}.${tableName} VALUES
882+
("2024-11-29",0)
883+
"""
884+
// after partition changed 10s, the sql cache can be used
885+
sleep(10000)
886+
887+
sql "set enable_sql_cache=true"
888+
sql "use ${dbName1}"
889+
List<List<Object>> result1 = sql """
890+
SELECT COUNT(*) FROM ${tableName}
891+
"""
892+
assertEquals(result1[0][0],2)
893+
894+
sql "use ${dbName2}"
895+
List<List<Object>> result2 = sql """
896+
SELECT COUNT(*) FROM ${tableName}
897+
"""
898+
assertEquals(result2[0][0],1)
899+
900+
sql "DROP TABLE IF EXISTS ${dbName1}.${tableName}"
901+
sql "DROP TABLE IF EXISTS ${dbName2}.${tableName}"
902+
sql "DROP DATABASE IF EXISTS ${dbName1}"
903+
sql "DROP DATABASE IF EXISTS ${dbName2}"
830904
})
831905
).get()
832906
}

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.