This is an automated email from the ASF dual-hosted git repository.
pierrejeambrun pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git
The following commit(s) were added to refs/heads/main by this push:
new 6036de15b5b API: Fix prefix pattern search returning rows without the
prefix (#73520)
6036de15b5b is described below
commit 6036de15b5b075a932f83421e478ad3cad9e9563
Author: Y-C <[email protected]>
AuthorDate: Tue Sep 22 22:02:12 2026 +0800
API: Fix prefix pattern search returning rows without the prefix (#73520)
The *_prefix_pattern filters turn a prefix into an index-friendly range
scan. Whenever bumping the last character would leave the alphanumeric
range ('9', 'z', 'Z'), the upper bound is recomputed from a shorter
prefix, so the scanned range is far wider than the prefix the caller
asked for: searching "dag_9" also returns "dag_abc", searching "a9"
returns everything starting with "a", and searching "z" gets no upper
bound at all. This affects every *_prefix_pattern query parameter, so a
UI search box and a REST client both get results that do not match what
was typed.
The documented trade-off of stripping trailing non-alphanumeric
characters is deliberate and stays; only the undocumented widening is
addressed here, and the bounds themselves are left alone so the B-tree
index still drives the scan.
Co-authored-by: Eason09053360
<[email protected]>
---
.../api_fastapi/common/parameters/search.py | 44 +++++++-----
.../unit/api_fastapi/common/test_parameters.py | 80 +++++++++++++++++++++-
2 files changed, 104 insertions(+), 20 deletions(-)
diff --git a/airflow-core/src/airflow/api_fastapi/common/parameters/search.py
b/airflow-core/src/airflow/api_fastapi/common/parameters/search.py
index 642416d22a5..d9b9685bdba 100644
--- a/airflow-core/src/airflow/api_fastapi/common/parameters/search.py
+++ b/airflow-core/src/airflow/api_fastapi/common/parameters/search.py
@@ -26,7 +26,7 @@ from typing import (
)
from fastapi import HTTPException, Query, status
-from sqlalchemy import and_, or_, true as sql_true
+from sqlalchemy import and_, func, or_, true as sql_true
from airflow.api_fastapi.common.db.common import SessionDep
from airflow.api_fastapi.common.parameters.base import BaseParam
@@ -36,6 +36,7 @@ from airflow.typing_compat import Self
from airflow.utils.sqlalchemy import apply_regex_query_timeout
if TYPE_CHECKING:
+ from sqlalchemy.orm import InstrumentedAttribute
from sqlalchemy.sql import ColumnElement, Select
@@ -86,6 +87,26 @@ class _PrefixPatternParam(BaseParam[str], ABC):
term = term[:-1]
return term
+ @staticmethod
+ def _build_prefix_range_clauses(
+ column: ColumnElement | InstrumentedAttribute, lower: str, upper: str
| None
+ ) -> list[ColumnElement[bool]]:
+ """
+ Return the predicates matching values that start with ``lower`` on
``column``.
+
+ The bounds keep a B-tree index usable but are wider than the prefix:
bumping the last
+ character out of the alphanumeric range derives the upper bound from a
shorter prefix
+ (``"a9"`` yields ``< "b"``, ``"z"`` yields none at all). The substring
equality narrows
+ that back. It is an equality and not ``LIKE 'lower%'`` so it resolves
under the same
+ collation as the bounds — ``LIKE`` is case-insensitive on SQLite and
MySQL ``_ci``
+ columns, and would keep leaking case variants.
+ """
+ clauses: list[ColumnElement[bool]] = [column >= lower]
+ if upper is not None:
+ clauses.append(column < upper)
+ clauses.append(func.substr(column, 1, len(lower)) == lower)
+ return clauses
+
@abstractmethod
def _prefix_clause(self, term: str):
"""Return the SQL boolean for one prefix term (including empty string
after ``~`` alias)."""
@@ -195,9 +216,7 @@ class _PrefixSearchParam(_PrefixPatternParam):
if not lower:
return self.attribute.is_not(None)
upper = self._prefix_range_upper(term)
- if upper is None:
- return self.attribute >= lower
- return and_(self.attribute >= lower, self.attribute < upper)
+ return and_(*self._build_prefix_range_clauses(self.attribute, lower,
upper))
@classmethod
def depends(cls, *args: Any, **kwargs: Any) -> Self:
@@ -218,27 +237,14 @@ class
_TaskDisplayNamePrefixPatternParam(_PrefixPatternParam):
if not lower:
return sql_true()
upper = self._prefix_range_upper(term)
- if upper is None:
- return or_(
- and_(
- TaskInstance._task_display_property_value.is_(None),
- TaskInstance.task_id >= lower,
- ),
- and_(
- TaskInstance._task_display_property_value.is_not(None),
- TaskInstance._task_display_property_value >= lower,
- ),
- )
return or_(
and_(
TaskInstance._task_display_property_value.is_(None),
- TaskInstance.task_id >= lower,
- TaskInstance.task_id < upper,
+ *self._build_prefix_range_clauses(TaskInstance.task_id, lower,
upper),
),
and_(
TaskInstance._task_display_property_value.is_not(None),
- TaskInstance._task_display_property_value >= lower,
- TaskInstance._task_display_property_value < upper,
+
*self._build_prefix_range_clauses(TaskInstance._task_display_property_value,
lower, upper),
),
)
diff --git a/airflow-core/tests/unit/api_fastapi/common/test_parameters.py
b/airflow-core/tests/unit/api_fastapi/common/test_parameters.py
index 9cf0412b440..d6cb3c40a6a 100644
--- a/airflow-core/tests/unit/api_fastapi/common/test_parameters.py
+++ b/airflow-core/tests/unit/api_fastapi/common/test_parameters.py
@@ -25,7 +25,7 @@ from unittest import mock
import pytest
from fastapi import Depends, FastAPI, HTTPException
-from sqlalchemy import select
+from sqlalchemy import Column, MetaData, String, Table, create_engine, select
from airflow.api_fastapi.common.parameters import (
FilterParam,
@@ -453,6 +453,17 @@ class TestPrefixSearchParam:
# Range scan uses the full composite-key value as the lower bound.
assert "2026-01-01|us" in sql
+ def test_to_orm_narrows_the_range_with_a_leading_substring(self):
+ """The index-friendly bounds are wider than the prefix, so an equality
pins the prefix down."""
+ param = _PrefixSearchParam(DagModel.dag_id).set_value("dag_9")
+ statement = param.to_orm(select(DagModel))
+
+ sql = _compile(statement)
+ # '9' bumps to ':' (non-alphanumeric), so the upper bound comes from
the shorter 'dag'.
+ assert "dag_id >= 'dag_9'" in sql
+ assert "dag_id < 'dah'" in sql
+ assert "substr(dag.dag_id, 1, 5) = 'dag_9'" in sql
+
def test_to_orm_pipe_as_or_false_tilde_alias_still_works(self):
"""``pipe_as_or=False`` must not interfere with the ``~`` → empty
alias."""
param = _PrefixSearchParam(DagModel.dag_id, pipe_as_or=False)
@@ -464,6 +475,64 @@ class TestPrefixSearchParam:
assert "is not null" in sql
+class TestPrefixSearchParamOnARealEngine:
+ """
+ Run the predicate against SQLite so the rows it lets through are checked,
not just the SQL.
+
+ Every term below computes an upper bound wider than the term itself, so
the bounds alone
+ would also return the rows that are missing from each ``expected`` list.
+ """
+
+ NAMES = [
+ "dag_9",
+ "dag_99",
+ "dag_abc",
+ "dagz9",
+ "dagZ1",
+ "a9",
+ "aa",
+ "abz",
+ "abz1",
+ "ab~",
+ "z1",
+ "zz",
+ "{a",
+ ]
+
+ @pytest.fixture
+ def select_names(self):
+ table = Table("prefix_probe", MetaData(), Column("name", String(64),
primary_key=True))
+ engine = create_engine("sqlite://")
+ table.metadata.create_all(engine)
+ with engine.connect() as conn:
+ conn.execute(table.insert(), [{"name": name} for name in
self.NAMES])
+
+ def run(term: str) -> list[str]:
+ param = _PrefixSearchParam(table.c.name).set_value(term)
+ return sorted(row[0] for row in
conn.execute(param.to_orm(select(table.c.name))))
+
+ yield run
+
+ @pytest.mark.parametrize(
+ ("term", "expected"),
+ [
+ # 'dagz9' pins down that the user's '_' is literal, not a
single-character wildcard.
+ ("dag_9", ["dag_9", "dag_99"]),
+ # Differs from 'dagz9' only in case, which the bounds distinguish
and so must the
+ # predicate narrowing them.
+ ("dagZ", ["dagZ1"]),
+ ("a9", ["a9"]),
+ ("abz", ["abz", "abz1"]),
+ # 'z' cascades down to no upper bound at all.
+ ("z", ["z1", "zz"]),
+ # Documented trade-off, unchanged: a trailing '_' is stripped, so
'dag_' means 'dag'.
+ ("dag_", ["dagZ1", "dag_9", "dag_99", "dag_abc", "dagz9"]),
+ ],
+ )
+ def test_only_values_with_the_prefix_come_back(self, select_names, term,
expected):
+ assert select_names(term) == expected
+
+
class TestTaskDisplayNamePrefixPatternParam:
"""Prefix filter splits on NULL override so ``task_id`` can use indexes."""
@@ -478,6 +547,15 @@ class TestTaskDisplayNamePrefixPatternParam:
assert "task_id <" in sql
assert "task_display_name is not null" in sql
+ def test_to_orm_narrows_both_branches_with_a_leading_substring(self):
+ """Both sides of the ``IS NULL`` split need the prefix predicate, not
only the bounds."""
+ param = _TaskDisplayNamePrefixPatternParam().set_value("task_9")
+ statement = param.to_orm(select(TaskInstance))
+
+ sql = _compile(statement)
+ assert "substr(task_instance.task_id, 1, 6) = 'task_9'" in sql
+ assert "substr(task_instance.task_display_name, 1, 6) = 'task_9'" in
sql
+
def test_to_orm_empty_matches_all(self):
param = _TaskDisplayNamePrefixPatternParam().set_value("")
statement = select(TaskInstance)
