Visitar URL original
fix: SQL -> combining = and LIKE does return 0 results on timeseries … · ArcadeData/arcadedb@c407438 · GitHub
Skip to content

Commit c407438

Browse files
lvcalvca
committed
fix: SQL -> combining = and LIKE does return 0 results on timeseries type
Fixed Issue #4128
1 parent 40bc98c commit c407438

2 files changed

Lines changed: 137 additions & 12 deletions

File tree

‎engine/src/main/java/com/arcadedb/query/sql/executor/SelectExecutionPlanner.java‎

Lines changed: 49 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -2247,15 +2247,24 @@ private static long toEpochMs(final Object value) {
22472247

22482248
/**
22492249
* Extracts a TagFilter from the flattened WHERE clause by matching equality predicates on TAG columns.
2250-
* Only simple equality conditions (column = 'value') on TAG columns are extracted.
2250+
* <p>
2251+
* The flattened WHERE is in DNF form: a list of AndBlocks where the overall predicate is the OR of
2252+
* each block. A row qualifies for push-down only if every block constrains the tag column to a
2253+
* (possibly different) literal via equality; in that case we can safely push the union of those
2254+
* values down as an IN filter (the residual FilterStep eliminates any false positives).
2255+
* <p>
2256+
* If even one block has no equality on a given tag column, that block could match a row with any
2257+
* tag value, so push-down on that column would be unsound and is skipped.
22512258
*/
22522259
private static TagFilter extractTagFilter(final List<AndBlock> flattenedWhere, final List<ColumnDefinition> columns,
22532260
final String timestampColumn, final CommandContext context) {
2254-
if (flattenedWhere == null)
2261+
if (flattenedWhere == null || flattenedWhere.isEmpty())
22552262
return null;
22562263

2257-
TagFilter filter = null;
2264+
final int tagCount = columns.size();
2265+
final List<Map<Integer, Set<Object>>> perBlockEqualities = new ArrayList<>(flattenedWhere.size());
22582266
for (final AndBlock andBlock : flattenedWhere) {
2267+
final Map<Integer, Set<Object>> blockMap = new HashMap<>();
22592268
for (final BooleanExpression expr : andBlock.getSubBlocks()) {
22602269
if (!(expr instanceof BinaryCondition binary))
22612270
continue;
@@ -2265,12 +2274,11 @@ private static TagFilter extractTagFilter(final List<AndBlock> flattenedWhere, f
22652274
final String rightStr = binary.right != null ? binary.right.toString().trim() : null;
22662275
if (leftStr == null || rightStr == null)
22672276
continue;
2268-
// Skip timestamp predicates — already handled by time range extraction
2277+
// Skip timestamp predicates, already handled by time range extraction
22692278
if (timestampColumn.equals(leftStr) || timestampColumn.equals(rightStr))
22702279
continue;
22712280

2272-
// Determine which side is the column name and which is the value
2273-
for (int i = 0; i < columns.size(); i++) {
2281+
for (int i = 0; i < tagCount; i++) {
22742282
final ColumnDefinition col = columns.get(i);
22752283
if (col.getRole() != ColumnDefinition.ColumnRole.TAG)
22762284
continue;
@@ -2282,19 +2290,48 @@ private static TagFilter extractTagFilter(final List<AndBlock> flattenedWhere, f
22822290
final Object value = valueExpr.execute((Identifiable) null, context);
22832291
if (value == null)
22842292
continue;
2285-
// Column index for TagFilter is the non-timestamp column index
2286-
int nonTsIdx = -1;
2287-
for (int j = 0; j <= i; j++)
2288-
if (columns.get(j).getRole() != ColumnDefinition.ColumnRole.TIMESTAMP)
2289-
nonTsIdx++;
2290-
filter = filter == null ? TagFilter.eq(nonTsIdx, value.toString()) : filter.and(nonTsIdx, value.toString());
2293+
final int nonTsIdx = nonTsIndexOf(columns, i);
2294+
blockMap.computeIfAbsent(nonTsIdx, k -> new HashSet<>()).add(value.toString());
2295+
break;
2296+
}
2297+
}
2298+
perBlockEqualities.add(blockMap);
2299+
}
2300+
2301+
// For each tag column, push down only if every block constrains it; values are unioned across blocks.
2302+
TagFilter filter = null;
2303+
for (int i = 0; i < tagCount; i++) {
2304+
final ColumnDefinition col = columns.get(i);
2305+
if (col.getRole() != ColumnDefinition.ColumnRole.TAG)
2306+
continue;
2307+
final int nonTsIdx = nonTsIndexOf(columns, i);
2308+
2309+
final Set<Object> unionValues = new HashSet<>();
2310+
boolean inEveryBlock = true;
2311+
for (final Map<Integer, Set<Object>> blockMap : perBlockEqualities) {
2312+
final Set<Object> values = blockMap.get(nonTsIdx);
2313+
if (values == null || values.isEmpty()) {
2314+
inEveryBlock = false;
22912315
break;
22922316
}
2317+
unionValues.addAll(values);
22932318
}
2319+
if (!inEveryBlock || unionValues.isEmpty())
2320+
continue;
2321+
2322+
filter = filter == null ? TagFilter.in(nonTsIdx, unionValues) : filter.andIn(nonTsIdx, unionValues);
22942323
}
22952324
return filter;
22962325
}
22972326

2327+
private static int nonTsIndexOf(final List<ColumnDefinition> columns, final int columnIndex) {
2328+
int nonTsIdx = -1;
2329+
for (int j = 0; j <= columnIndex; j++)
2330+
if (columns.get(j).getRole() != ColumnDefinition.ColumnRole.TIMESTAMP)
2331+
nonTsIdx++;
2332+
return nonTsIdx;
2333+
}
2334+
22982335
/**
22992336
* Returns true if the WHERE clause contains conditions that are NOT consumed by time-series
23002337
* push-down (i.e., not time-range predicates and not tag equality filters).

‎engine/src/test/java/com/arcadedb/engine/timeseries/TimeSeriesSQLTest.java‎

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,94 @@ void insertWithSET() {
9595
assertThat(((Number) row.getProperty("cpu")).doubleValue()).isEqualTo(75.5);
9696
}
9797

98+
/**
99+
* Regression test for issue #4128: combining "=" and LIKE on a TAG column with OR
100+
* was returning 0 results because the time-series planner pushed down the equality
101+
* branch as if it were a global AND condition, dropping rows that should have matched
102+
* any of the LIKE branches.
103+
*/
104+
@Test
105+
void selectTagsEqualityOrLikeCsv() {
106+
database.command("sql",
107+
"CREATE TIMESERIES TYPE TaggedMetric TIMESTAMP ts TAGS (tags STRING) FIELDS (value DOUBLE)");
108+
109+
database.transaction(() -> {
110+
database.command("sql", "INSERT INTO TaggedMetric SET ts = 1000, tags = 'CI', value = 1.0");
111+
database.command("sql", "INSERT INTO TaggedMetric SET ts = 2000, tags = 'CI,slow', value = 2.0");
112+
database.command("sql", "INSERT INTO TaggedMetric SET ts = 3000, tags = '1m,CI,fast', value = 3.0");
113+
database.command("sql", "INSERT INTO TaggedMetric SET ts = 4000, tags = 'fast,CI', value = 4.0");
114+
database.command("sql", "INSERT INTO TaggedMetric SET ts = 5000, tags = 'other', value = 5.0");
115+
});
116+
117+
// Mixing = and LIKE in OR must return all 4 rows tagged with CI in any position.
118+
final ResultSet rs = database.query("sql",
119+
"SELECT FROM TaggedMetric WHERE ts BETWEEN 0 AND 10000 AND ("
120+
+ "tags = 'CI' OR tags LIKE 'CI,%' OR tags LIKE '%,CI' OR tags LIKE '%,CI,%')");
121+
final List<Result> results = new ArrayList<>();
122+
while (rs.hasNext())
123+
results.add(rs.next());
124+
125+
assertThat(results).hasSize(4);
126+
127+
// Only-LIKE form (the workaround in the bug report) should also return 4 rows.
128+
final ResultSet rs2 = database.query("sql",
129+
"SELECT FROM TaggedMetric WHERE ts BETWEEN 0 AND 10000 AND ("
130+
+ "tags LIKE 'CI' OR tags LIKE 'CI,%' OR tags LIKE '%,CI' OR tags LIKE '%,CI,%')");
131+
final List<Result> results2 = new ArrayList<>();
132+
while (rs2.hasNext())
133+
results2.add(rs2.next());
134+
135+
assertThat(results2).hasSize(4);
136+
}
137+
138+
/**
139+
* Push-down sanity: when the WHERE has a single equality on a tag, the engine
140+
* still uses the optimized tag filter and returns the correct row.
141+
*/
142+
@Test
143+
void selectSingleTagEqualityPushDown() {
144+
database.command("sql",
145+
"CREATE TIMESERIES TYPE PushDownMetric TIMESTAMP ts TAGS (host STRING) FIELDS (value DOUBLE)");
146+
147+
database.transaction(() -> {
148+
database.command("sql", "INSERT INTO PushDownMetric SET ts = 1000, host = 'h1', value = 1.0");
149+
database.command("sql", "INSERT INTO PushDownMetric SET ts = 2000, host = 'h2', value = 2.0");
150+
database.command("sql", "INSERT INTO PushDownMetric SET ts = 3000, host = 'h1', value = 3.0");
151+
});
152+
153+
final ResultSet rs = database.query("sql",
154+
"SELECT FROM PushDownMetric WHERE ts BETWEEN 0 AND 10000 AND host = 'h1'");
155+
final List<Result> results = new ArrayList<>();
156+
while (rs.hasNext())
157+
results.add(rs.next());
158+
159+
assertThat(results).hasSize(2);
160+
}
161+
162+
/**
163+
* Push-down for OR of equalities on the same tag column: must return the union.
164+
*/
165+
@Test
166+
void selectTagOrEqualitiesPushDown() {
167+
database.command("sql",
168+
"CREATE TIMESERIES TYPE OrEqMetric TIMESTAMP ts TAGS (host STRING) FIELDS (value DOUBLE)");
169+
170+
database.transaction(() -> {
171+
database.command("sql", "INSERT INTO OrEqMetric SET ts = 1000, host = 'h1', value = 1.0");
172+
database.command("sql", "INSERT INTO OrEqMetric SET ts = 2000, host = 'h2', value = 2.0");
173+
database.command("sql", "INSERT INTO OrEqMetric SET ts = 3000, host = 'h3', value = 3.0");
174+
database.command("sql", "INSERT INTO OrEqMetric SET ts = 4000, host = 'h1', value = 4.0");
175+
});
176+
177+
final ResultSet rs = database.query("sql",
178+
"SELECT FROM OrEqMetric WHERE ts BETWEEN 0 AND 10000 AND (host = 'h1' OR host = 'h2')");
179+
final List<Result> results = new ArrayList<>();
180+
while (rs.hasNext())
181+
results.add(rs.next());
182+
183+
assertThat(results).hasSize(3);
184+
}
185+
98186
@Test
99187
void timeBucketFunction() {
100188
database.command("sql",

0 commit comments

Comments
 (0)