Skip to content

Commit 8fc7d7c

Browse files
airborne12claude
andcommitted
[fix](nereids) Reject score() in correlated subqueries and in HAVING through aliases
### What problem does this PR solve? Issue Number: None Related PR: apache#69292 Problem Summary: Review of 2e999db found three gaps in the checks of BindScoreToOlapScan: - A correlated subquery could call score() with ORDER BY score() LIMIT, e.g. EXISTS (SELECT 1 FROM t a WHERE a.c1 = b.c1 AND a.c1 MATCH 'apple' AND score() > 0.6 ORDER BY score() DESC LIMIT 1). Decorrelation removes that LIMIT and ORDER BY (EliminateLimitUnderApply, EliminateSortUnderApply), so a predicate inferred from the outer query, such as an outer MATCH on b.c1, reaches the scored scan and adds to its score. score() is now rejected anywhere in a correlated subquery, also in a derived table in it, whose LIMIT can end up directly under the subquery once the derived table is merged. - HAVING and QUALIFY could read MATCH through an alias of the select list, e.g. c1 MATCH 'red' AS m ... QUALIFY m AND rn = 1 with PARTITION BY c1. Pushing that filter through the window moves the MATCH into the scan, where it adds to the score. Aliases of score(), MATCH and SEARCH in the select list or GROUP BY, and aliases of those aliases, are now treated like the expressions themselves. - A nested SELECT with HAVING on a column it does not return, e.g. ... WHERE c1 MATCH 'apple' HAVING c2 = 'sweet' ORDER BY s DESC LIMIT 2, was rejected: analysis restores the select list with a projection between LIMIT and ORDER BY, and the check required them to be adjacent. The check now skips projections of plain columns between them. ### Release note None ### Check List (For Author) - Test: Unit Test and Regression test - BindScoreToOlapScanTest: the reviewer's correlated EXISTS, QUALIFY alias and nested HAVING queries, plus a scored derived table in a correlated EXISTS, a correlated scalar subquery, HAVING and GROUP BY ... HAVING on a MATCH alias, and an uncorrelated EXISTS with the TopN. With the previous binder the five new tests fail; with this change all 73 pass. Checkstyle passes. - Regression tests: test_score_filter_usage gets the correlated EXISTS, the QUALIFY alias and the nested HAVING query, with its .out regenerated by the framework. The 19 suites that use score() pass on a private cluster. - Behavior changed: Yes. score() in a correlated subquery, and HAVING or QUALIFY that read score(), MATCH or SEARCH through an alias, are rejected. A nested scored SELECT with HAVING on a column it does not return is accepted. - Does this need documentation: No Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 2e999db commit 8fc7d7c

4 files changed

Lines changed: 96 additions & 13 deletions

File tree

‎fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/BindScoreToOlapScan.java‎

Lines changed: 34 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@
5656
import com.google.common.collect.ImmutableSet;
5757

5858
import java.util.ArrayList;
59+
import java.util.HashSet;
5960
import java.util.List;
6061
import java.util.Optional;
6162
import java.util.Set;
@@ -74,7 +75,8 @@
7475
* <li>in the select list, window functions and ORDER BY of a SELECT that does not aggregate.</li>
7576
* </ul>
7677
* A SELECT in a subquery, view, CTE or set operation has to ORDER BY score() with a LIMIT, which keeps
77-
* the predicates of the outer query away from its scan. Any other use of score() is rejected.
78+
* the predicates of the outer query away from its scan. A correlated subquery cannot call score(), because
79+
* decorrelation can remove that LIMIT. Any other use of score() is rejected.
7880
*
7981
* <p>This rule runs before subquery aliases are removed. It adds a score column to the scan, which
8082
* scores every row the scan returns, and replaces the score() calls of the SELECT with it.
@@ -86,6 +88,7 @@ public class BindScoreToOlapScan implements CustomRewriter {
8688
+ "table and has a MATCH or SEARCH predicate in its WHERE clause";
8789
public static final String NESTED_SELECT_ERROR = "score() in a subquery, view, CTE or set operation "
8890
+ "requires ORDER BY score() with LIMIT";
91+
public static final String CORRELATED_SUBQUERY_ERROR = "score() cannot be used in a correlated subquery";
8992
public static final String AGGREGATE_ERROR = "score() can only be used in the WHERE clause of a SELECT "
9093
+ "with GROUP BY, aggregate functions or DISTINCT";
9194
public static final String HAVING_OR_QUALIFY_ERROR = "score(), MATCH and SEARCH cannot be used in HAVING "
@@ -130,6 +133,11 @@ private Plan bindNestedSelects(Plan plan) {
130133
boolean changed = false;
131134
for (int i = 0; i < plan.arity(); i++) {
132135
Plan child = plan.child(i);
136+
// Decorrelation can remove the LIMIT and ORDER BY of a SELECT in a correlated subquery.
137+
if (plan instanceof LogicalApply && i == 1 && ((LogicalApply<?, ?>) plan).isCorrelated()
138+
&& child.anyMatch(node -> callsScore((Plan) node))) {
139+
throw new AnalysisException(CORRELATED_SUBQUERY_ERROR);
140+
}
133141
Plan newChild = startsSelect(plan, i) ? bindSelect(child, false) : bindNestedSelects(child);
134142
changed |= newChild != child;
135143
newChildren.add(newChild);
@@ -211,16 +219,21 @@ private static void check(List<Plan> path, boolean outermost) {
211219

212220
List<Plan> aboveWhere = path.subList(0, clause + 1);
213221
boolean aggregates = aboveWhere.stream().anyMatch(LogicalAggregate.class::isInstance);
214-
Set<Slot> scoreAliases = aboveWhere.stream()
215-
.filter(LogicalProject.class::isInstance)
216-
.flatMap(project -> ((LogicalProject<?>) project).getProjects().stream())
217-
.filter(project -> project.containsType(Score.class))
218-
.map(NamedExpression::toSlot)
219-
.collect(ImmutableSet.toImmutableSet());
222+
// HAVING and QUALIFY can also read score(), MATCH and SEARCH through aliases of the SELECT.
223+
Set<Slot> scoringAliases = new HashSet<>();
224+
for (int i = aboveWhere.size() - 1; i >= 0; i--) {
225+
for (Expression expression : aboveWhere.get(i).getExpressions()) {
226+
if (expression instanceof NamedExpression
227+
&& (expression.containsType(Score.class, Match.class, Search.class)
228+
|| expression.getInputSlots().stream().anyMatch(scoringAliases::contains))) {
229+
scoringAliases.add(((NamedExpression) expression).toSlot());
230+
}
231+
}
232+
}
220233
for (Plan node : aboveWhere) {
221234
if (node instanceof LogicalFilter && ((LogicalFilter<?>) node).getConjuncts().stream().anyMatch(
222235
conjunct -> conjunct.containsType(Score.class, Match.class, Search.class)
223-
|| conjunct.getInputSlots().stream().anyMatch(scoreAliases::contains))) {
236+
|| conjunct.getInputSlots().stream().anyMatch(scoringAliases::contains))) {
224237
throw new AnalysisException(HAVING_OR_QUALIFY_ERROR);
225238
}
226239
if (aggregates && callsScore(node)) {
@@ -259,11 +272,20 @@ private static boolean isConstant(Expression expression) {
259272
return expression.getInputSlots().isEmpty() && !expression.containsType(Score.class);
260273
}
261274

262-
// A TopN on the score above the SELECT, so that no outer predicate is pushed into the scan.
275+
// A TopN on the score above the SELECT, so that no outer predicate is pushed into the scan. Analysis
276+
// restores the select list between LIMIT and ORDER BY when HAVING or ORDER BY reads another column.
263277
private static boolean ordersByScoreWithLimit(List<Plan> aboveWhere) {
264-
for (int i = 0; i + 1 < aboveWhere.size(); i++) {
265-
if (aboveWhere.get(i) instanceof LogicalLimit && aboveWhere.get(i + 1) instanceof LogicalSort) {
266-
List<OrderKey> orderKeys = ((LogicalSort<?>) aboveWhere.get(i + 1)).getOrderKeys();
278+
for (int i = 0; i < aboveWhere.size(); i++) {
279+
if (!(aboveWhere.get(i) instanceof LogicalLimit)) {
280+
continue;
281+
}
282+
int sort = i + 1;
283+
while (sort < aboveWhere.size() && aboveWhere.get(sort) instanceof LogicalProject
284+
&& ((LogicalProject<?>) aboveWhere.get(sort)).isAllSlots()) {
285+
sort++;
286+
}
287+
if (sort < aboveWhere.size() && aboveWhere.get(sort) instanceof LogicalSort) {
288+
List<OrderKey> orderKeys = ((LogicalSort<?>) aboveWhere.get(sort)).getOrderKeys();
267289
return orderKeys.size() == 1 && isScoreOrAlias(orderKeys.get(0).getExpr(), aboveWhere);
268290
}
269291
}

‎fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/BindScoreToOlapScanTest.java‎

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -188,14 +188,37 @@ void testTableAlias() {
188188
"SELECT * FROM (%s) x",
189189
"WITH c AS (%s) SELECT * FROM c",
190190
"SELECT * FROM (%s) x UNION ALL SELECT id, 0 FROM score_usage_V3",
191-
"SELECT id FROM score_usage_V3 WHERE id IN (SELECT id FROM (%s) x)"})
191+
"SELECT id FROM score_usage_V3 WHERE id IN (SELECT id FROM (%s) x)",
192+
"SELECT id FROM score_usage_V3 WHERE EXISTS (%s)"})
192193
void testNestedScoreRequiresTopN(String query) {
193194
String scored = "SELECT id, score() AS s FROM score_usage_V2 WHERE c1 MATCH 'apple' AND score() > 0.00001";
194195
assertScoreUsageError(String.format(query, scored), BindScoreToOlapScan.NESTED_SELECT_ERROR);
195196
Assertions.assertDoesNotThrow(() -> PlanChecker.from(connectContext).checkPlannerResult(
196197
String.format(query, scored + " ORDER BY s DESC LIMIT 2")));
197198
}
198199

200+
@Test
201+
void testNestedScoreTopNAboveHaving() {
202+
// HAVING reads c2, which the SELECT does not return, so analysis restores the select list between
203+
// LIMIT and ORDER BY.
204+
Assertions.assertDoesNotThrow(() -> PlanChecker.from(connectContext).checkPlannerResult(
205+
"SELECT * FROM (SELECT id, score() AS s FROM score_usage_V2 WHERE c1 MATCH 'apple'"
206+
+ " HAVING c2 = 'sweet' ORDER BY s DESC LIMIT 2) x"));
207+
}
208+
209+
@ParameterizedTest
210+
@ValueSource(strings = {
211+
"SELECT id FROM score_usage_V3 b WHERE (b.c1 MATCH 'red' OR b.c1 MATCH 'green') AND EXISTS ("
212+
+ "SELECT 1 FROM score_usage_V2 a WHERE a.c1 = b.c1 AND a.c1 MATCH 'apple'"
213+
+ " AND score() > 0.6 ORDER BY score() DESC LIMIT 1)",
214+
"SELECT id FROM score_usage_V3 b WHERE EXISTS (SELECT 1 FROM (SELECT c1, score() AS s"
215+
+ " FROM score_usage_V2 WHERE c1 MATCH 'apple' ORDER BY s DESC LIMIT 1) a WHERE a.c1 = b.c1)",
216+
"SELECT id, (SELECT max(a.id) FROM score_usage_V2 a WHERE a.id = b.id AND a.c1 MATCH 'apple'"
217+
+ " AND score() > 0.6) FROM score_usage_V3 b"})
218+
void testCorrelatedSubqueryIsRejected(String query) {
219+
assertScoreUsageError(query, BindScoreToOlapScan.CORRELATED_SUBQUERY_ERROR);
220+
}
221+
199222
@Test
200223
void testViewWithScoreTopN() {
201224
PlanChecker.from(connectContext).checkPlannerResult("SELECT * FROM score_usage_top WHERE id > 0",
@@ -229,6 +252,17 @@ void testHavingAndQualifyAreRejected() {
229252
assertScoreUsageError("SELECT id, score() AS s, row_number() OVER (ORDER BY id) AS rn"
230253
+ " FROM score_usage_V2 WHERE c1 MATCH 'apple' QUALIFY rn = 1 AND s > 0.5",
231254
BindScoreToOlapScan.HAVING_OR_QUALIFY_ERROR);
255+
// MATCH through an alias of the select list.
256+
assertScoreUsageError("SELECT id, score() AS s, c1 MATCH 'red' AS m,"
257+
+ " row_number() OVER (PARTITION BY c1 ORDER BY id) AS rn"
258+
+ " FROM score_usage_V2 WHERE c1 MATCH 'apple' QUALIFY m AND rn = 1",
259+
BindScoreToOlapScan.HAVING_OR_QUALIFY_ERROR);
260+
assertScoreUsageError("SELECT id, score() AS s, c1 MATCH 'red' AS m"
261+
+ " FROM score_usage_V2 WHERE c1 MATCH 'apple' HAVING m",
262+
BindScoreToOlapScan.HAVING_OR_QUALIFY_ERROR);
263+
assertScoreUsageError("SELECT c1 MATCH 'red' AS m, count(*) FROM score_usage_V2"
264+
+ " WHERE c1 MATCH 'apple' AND score() > 0.00001 GROUP BY m HAVING m",
265+
BindScoreToOlapScan.HAVING_OR_QUALIFY_ERROR);
232266
}
233267

234268
@ParameterizedTest

‎regression-test/data/inverted_index_p0/test_score_filter_usage.out‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,9 @@
5757
sour 1
5858
sweet 1
5959

60+
-- !nested_score_topn_having --
61+
1 0.4700036
62+
6063
-- !nested_score_topn --
6164
1 0.4700036 sweet
6265
2 0.4700036 sour

‎regression-test/suites/inverted_index_p0/test_score_filter_usage.groovy‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,13 @@ suite("test_score_filter_usage", "p0") {
172172
"""
173173
exception "score(), MATCH and SEARCH cannot be used in HAVING or QUALIFY"
174174
}
175+
test {
176+
sql """
177+
SELECT id, score() AS s, c1 MATCH 'red' AS m, row_number() OVER (PARTITION BY c1 ORDER BY id) AS rn
178+
FROM test_score_filter_usage WHERE c1 MATCH 'apple' QUALIFY m AND rn = 1
179+
"""
180+
exception "score(), MATCH and SEARCH cannot be used in HAVING or QUALIFY"
181+
}
175182

176183
// score() is the relevance for the MATCH and SEARCH predicates of its own SELECT on one table.
177184
test {
@@ -249,6 +256,23 @@ suite("test_score_filter_usage", "p0") {
249256
"""
250257
exception nestedSelect
251258
}
259+
// Decorrelation removes ORDER BY score() LIMIT from a correlated subquery.
260+
test {
261+
sql """
262+
SELECT id FROM test_score_filter_usage b
263+
WHERE (b.c1 MATCH 'red' OR b.c1 MATCH 'green') AND EXISTS (
264+
SELECT 1 FROM test_score_filter_usage a
265+
WHERE a.c1 = b.c1 AND a.c1 MATCH 'apple' AND score() > 0.6
266+
ORDER BY score() DESC LIMIT 1)
267+
"""
268+
exception "score() cannot be used in a correlated subquery"
269+
}
270+
order_qt_nested_score_topn_having """
271+
SELECT * FROM (
272+
SELECT id, score() AS s FROM test_score_filter_usage
273+
WHERE c1 MATCH 'apple' HAVING c2 = 'sweet' ORDER BY s DESC LIMIT 2
274+
) x
275+
"""
252276
order_qt_nested_score_topn """
253277
SELECT x.id, x.s, b.c2 FROM (
254278
SELECT id, score() AS s FROM test_score_filter_usage

0 commit comments

Comments
 (0)