This is an automated email from the ASF dual-hosted git repository.
mattcasters pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new f550576756 Issue #8629 : Let pgvector search skip filters whose stream
value is empty (#8630)
f550576756 is described below
commit f5505767567c65924120abe41623ef51bb83fd80
Author: Bart Maertens <[email protected]>
AuthorDate: Thu Oct 1 11:35:27 2026 +0200
Issue #8629 : Let pgvector search skip filters whose stream value is empty
(#8630)
Every pgvector search filter became "column = ?", so a row whose filter
field was null or empty matched nothing and quietly returned no results.
Filters get a "Skip if empty" option (skip_if_empty, default N, so
existing pipelines are unchanged). When set, that filter is left out of
the WHERE clause for rows where its value is empty. It is per filter so
required filters, such as a tenant or access-control column, keep
applying and can never widen a search.
Each combination of active filters gets its own prepared statement,
created on first use and cached, which keeps plain equality predicates.
The statement with every filter is still prepared up front, so a bad
table or column name fails on the first row as before.
Also document which settings win when Language Model Chat uses an AI
Provider: the provider's type, base URL, API key, chat model, timeout and
temperature override the inline values, which are only fallbacks for
empty provider fields. Correct the AI Provider page, which said the
transform can override the model; that holds for the embedding
transforms, not for Language Model Chat.
---
.../ROOT/pages/metadata-types/ai-provider.adoc | 8 +-
.../pipeline/transforms/languagemodelchat.adoc | 38 +++++-
.../pages/pipeline/transforms/pgvector-search.adoc | 13 ++
.../pgvector/transforms/search/PgVectorSearch.java | 98 +++++++++++----
.../transforms/search/PgVectorSearchData.java | 15 ++-
.../transforms/search/PgVectorSearchDialog.java | 19 ++-
.../transforms/search/PgVectorSearchMeta.java | 4 +-
.../hop/pgvector/util/PgVectorSearchFilter.java | 16 ++-
.../search/messages/messages_en_US.properties | 3 +
.../transforms/search/PgVectorSearchMetaTest.java | 28 ++++-
.../transforms/search/PgVectorSearchTest.java | 134 ++++++++++++++++++++-
.../hop/pgvector/util/PgVectorSqlBuilderTest.java | 12 ++
12 files changed, 354 insertions(+), 34 deletions(-)
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/metadata-types/ai-provider.adoc
b/docs/hop-user-manual/modules/ROOT/pages/metadata-types/ai-provider.adoc
index 3f78748fa2..b8432a3ea5 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/metadata-types/ai-provider.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/metadata-types/ai-provider.adoc
@@ -40,8 +40,10 @@ The same pattern applies to every other secret in the
project: never hard-code i
See
xref:hop-gui/perspective-ai-advisor.adoc#secrets-and-personal-information[Secrets
and personal information].
The xref:pipeline/transforms/languagemodelchat.adoc[Language model chat]
transform can optionally select a named AI Provider.
-Connection fields from the provider overlay the transform's inline values;
empty provider fields keep the inline values.
+The provider's type, base URL, API key, chat model, timeout and temperature
then take precedence over the transform's inline values; an inline value is
only used when the provider leaves that field empty.
+In particular, a temperature set on the provider overrides the temperature on
every transform that uses it, so leave *Temperature* empty here if transforms
need their own.
Input/output mapping, mock mode, proxy and retries stay on the transform.
+See xref:pipeline/transforms/languagemodelchat.adoc#ai-provider-precedence[AI
Provider and inline settings] for the details.
== Provider types
@@ -116,7 +118,7 @@ A GitHub Copilot-style OAuth provider is an extension
point; it is not bundled.
|HTTP timeout for completions.
|Temperature
-|Sampling temperature, when the provider supports it.
+|Sampling temperature, when the provider supports it. When set, it overrides
the temperature configured on a Language Model Chat transform that uses this
provider.
|===
When Language Model Chat points at an AI Provider, extra transform options
(proxy, retries, mock, I/O fields) are not taken from the provider.
@@ -148,7 +150,7 @@ Each row pairs a role with a model name:
A transform never asks which role to use: it needs one kind of model and looks
up that role. Point a chat transform and an embedding transform at the same
provider and each finds its own model.
-Use at most one row per role, since that is what makes the lookup unambiguous.
A transform that needs a different model than the provider's default for its
role can override it on the transform itself.
+Use at most one row per role, since that is what makes the lookup unambiguous.
An embedding transform that needs a different model than the provider's default
for its role can override it on the transform itself. Language Model Chat works
the other way round: the provider's `CHAT` model wins, and the model name on
the transform is only used when the provider has none.
=== Relationship to Model name
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/languagemodelchat.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/languagemodelchat.adoc
index 65f416f1b1..eae2a61172 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/languagemodelchat.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/languagemodelchat.adoc
@@ -41,7 +41,7 @@ The following parameters are applicable to all language model
selections.
| Name of the transform. Must be unique within a single pipeline.
| AI Provider (optional)
-| Named xref:metadata-types/ai-provider.adoc[AI Provider] metadata from the
Metadata perspective. Connection fields (provider type, base URL, API key,
model name, timeout, temperature) overlay the inline values below. Empty
provider fields keep the inline values. Input/output mapping, mock mode, proxy
and retries stay on this transform. Leave blank to use only the inline fields.
Requires the `hop-tech-ai` plugin. Do not paste live API keys on this transform
or on the provider: use an env [...]
+| Named xref:metadata-types/ai-provider.adoc[AI Provider] metadata from the
Metadata perspective. When set, the provider's connection settings (provider
type, base URL, API key, chat model, timeout, temperature) take precedence over
the inline values below, which are only used for fields the provider leaves
empty. See <<ai-provider-precedence>>. Input/output mapping, mock mode, proxy
and retries stay on this transform. Leave blank to use only the inline fields.
Requires the `hop-tech-ai` [...]
| Input JSON Chat
| When enabled, uses the chat completion JSON format (system, assistant, user
messages). When disabled, treats the input as plain text. See "Chat Completion"
section for more details.
@@ -71,6 +71,42 @@ The following parameters are applicable to all language
model selections.
| Defines the API used for communication with the endpoint (e.g., via OpenAI,
Anthropic, Ollama, Hugging Face, Mistral). **Note:** The Open AI API option can
be used with any Open AI compatible endpoint/server. For example, vLLM (Kwon
et al., 2023).
|===
+[[ai-provider-precedence]]
+=== AI Provider and inline settings
+
+When *AI Provider* is set, the transform starts from its own settings and then
copies the provider's connection settings over them. The provider wins wherever
it has a value; an inline value is only a fallback for a field the provider
leaves empty.
+
+[cols="2,4",options="header"]
+|===
+| Setting | Which value is used
+
+| Model API
+| Always the provider's type. The *Model API* selected on the transform is
ignored. OpenAI, Grok, Gemini and Custom providers use the OpenAI API.
+
+| Base URL
+| The provider's *Base URL* if set, otherwise the inline base URL (for Ollama,
the endpoint) of the provider's model type.
+
+| API key
+| The provider's *API key* if set, otherwise the inline key (for Hugging Face,
the access token) of the provider's model type.
+
+| Model name
+| The provider's `CHAT` model: its `CHAT` row under *Models per role*, or else
its *Model name*. If both are empty, the inline model name of the provider's
model type. For Hugging Face, the provider's *Base URL* is used as the model id
when it has no model name.
+
+| Temperature
+| The provider's *Temperature* if set, otherwise the inline temperature. A
provider temperature always overrides the inline one, even when the inline
value was chosen on purpose. Once the provider field holds anything, including
a variable that resolves to nothing, it counts as set: a value that is not a
number is sent as `0.3`, not replaced by the inline temperature.
+
+| Timeout
+| The provider's *Timeout (seconds)* if it resolves to a whole number,
otherwise the inline timeout.
+|===
+
+Variables in provider fields are resolved first, so apart from *Temperature*,
a field holding a variable that resolves to an empty value counts as empty.
Empty provider fields fall back to the inline values, not to the defaults
listed for each provider type on the xref:metadata-types/ai-provider.adoc[AI
Provider] page.
+
+The inline fallbacks come from the provider's model type, not from the *Model
API* selected on the transform. With an Anthropic provider, for example, an
empty provider *Model name* falls back to the inline Anthropic model name, even
when the transform itself was set up for OpenAI.
+
+All other options always come from the transform: mock mode, parallelism and
the input and output fields, and the model-specific options such as Top P, Top
K, maximum tokens, response format, proxy, retries and logging. The
model-specific ones are read from the transform's fields for the provider's
model type.
+
+To tune the temperature per transform while sharing one provider, leave
*Temperature* empty on the provider and set it on each transform.
+
=== Model-Specific Options
Below is a table detailing parameters specific to individual language model
APIs that are currently supported with the Language model chat transform plugin.
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/pgvector-search.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/pgvector-search.adoc
index 37d2ea9a97..de5eab2a9c 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/pgvector-search.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/pgvector-search.adoc
@@ -80,6 +80,19 @@ The similarity score is normalised so that higher is better
for every metric, bu
The *Filters* tab restricts the search to rows where a table column equals the
value of a stream field, which lets one index serve several document sets. The
columns come from the mappings configured in pgvector upsert.
+[options="header"]
+|===
+|Column |Description
+
+|Table column|Column in the pgvector table to compare.
+|Stream field|Input field whose value the column must equal.
+|Skip if empty|`N` (the default) always applies the filter. An empty stream
value is then compared as is and matches nothing, so the row finds no matches.
`Y` leaves the filter out of the search for any row whose stream field is null
or empty, so that row is searched without it.
+|===
+
+Use *Skip if empty* for optional narrowing, such as a category the user may or
may not have picked. Leave it off for filters that must always hold, such as a
tenant or access-control column: with it on, a row that arrives without a value
would search every tenant's documents.
+
+Each combination of skipped filters gets its own prepared statement, created
the first time a row needs it, so the query never carries a placeholder for a
filter it does not use.
+
== Notes
*Top K* and *Minimum score* accept variables, so they can be set per
environment rather than per pipeline.
diff --git
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearch.java
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearch.java
index a5917134db..d1389c71b1 100644
---
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearch.java
+++
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearch.java
@@ -16,8 +16,10 @@
*/
package org.apache.hop.pgvector.transforms.search;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
+import java.util.BitSet;
import java.util.List;
import org.apache.hop.core.Const;
import org.apache.hop.core.exception.HopException;
@@ -119,8 +121,11 @@ public class PgVectorSearch extends
BaseTransform<PgVectorSearchMeta, PgVectorSe
int matches = 0;
try {
- bindSearchParameters(row, embedding);
- try (ResultSet resultSet = data.searchStatement.executeQuery()) {
+ String[] filterValues = readFilterValues(row);
+ BitSet activeFilters = activeFilters(filterValues);
+ PreparedStatement statement = searchStatement(activeFilters);
+ bindSearchParameters(statement, embedding, filterValues, activeFilters);
+ try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
double score = resultSet.getDouble("similarity");
// The minimum score is applied after the top-k limit, so a run can
legitimately
@@ -181,17 +186,70 @@ public class PgVectorSearch extends
BaseTransform<PgVectorSearchMeta, PgVectorSe
data.resultScoreFieldIndex =
data.outputRowMeta.indexOfValue(meta.getResultScoreField());
}
- private void bindSearchParameters(Object[] row, Object embedding) throws
Exception {
+ private String[] readFilterValues(Object[] row) throws HopException {
+ String[] values = new String[data.filterBindings.size()];
+ for (int i = 0; i < values.length; i++) {
+ values[i] = data.inputRowMeta.getString(row,
data.filterBindings.get(i).streamFieldIndex);
+ }
+ return values;
+ }
+
+ /**
+ * Works out which filters go into the WHERE clause for this row. A filter
always applies unless
+ * it is set to be skipped when its stream value is empty and that value is
null or empty.
+ */
+ private BitSet activeFilters(String[] filterValues) {
+ BitSet active = new BitSet(filterValues.length);
+ for (int i = 0; i < filterValues.length; i++) {
+ PgVectorSearchFilter filter = data.filterBindings.get(i).filter;
+ if (filter.isSkipIfEmpty() && Utils.isEmpty(filterValues[i])) {
+ if (isRowLevel()) {
+ logRowlevel(
+ BaseMessages.getString(
+ PKG,
+ "PgVectorSearch.Log.SkippedEmptyFilter",
+ filter.getColumnName(),
+ filter.getStreamField()));
+ }
+ continue;
+ }
+ active.set(i);
+ }
+ return active;
+ }
+
+ /**
+ * Returns the statement for this combination of filters, preparing it on
first use. Leaving a
+ * skipped filter out of the SQL altogether, rather than binding {@code (?
IS NULL OR column =
+ * ?)}, gives the planner a plain predicate to plan the vector index scan
around.
+ */
+ private PreparedStatement searchStatement(BitSet activeFilters) throws
Exception {
+ PreparedStatement statement = data.searchStatements.get(activeFilters);
+ if (statement == null) {
+ List<PgVectorSearchFilter> filters =
+ activeFilters.stream().mapToObj(i ->
data.filterBindings.get(i).filter).toList();
+ statement =
+ data.database
+ .getConnection()
+ .prepareStatement(
+ PgVectorSqlBuilder.searchSql(data.qualifiedTable,
data.metric, filters));
+ data.searchStatements.put(activeFilters, statement);
+ }
+ return statement;
+ }
+
+ private void bindSearchParameters(
+ PreparedStatement statement, Object embedding, String[] filterValues,
BitSet activeFilters)
+ throws Exception {
String vectorLiteral = EmbeddingJsonParser.toPgVectorLiteral(embedding);
int parameterIndex = 1;
// Placeholder order must match searchSql(): score expression, filters,
ORDER BY, LIMIT.
- data.searchStatement.setString(parameterIndex++, vectorLiteral);
- for (PgVectorSearchData.FilterBinding binding : data.filterBindings) {
- String filterValue = data.inputRowMeta.getString(row,
binding.streamFieldIndex);
- data.searchStatement.setString(parameterIndex++, filterValue);
+ statement.setString(parameterIndex++, vectorLiteral);
+ for (int i = activeFilters.nextSetBit(0); i >= 0; i =
activeFilters.nextSetBit(i + 1)) {
+ statement.setString(parameterIndex++, filterValues[i]);
}
- data.searchStatement.setString(parameterIndex++, vectorLiteral);
- data.searchStatement.setInt(parameterIndex, data.topK);
+ statement.setString(parameterIndex++, vectorLiteral);
+ statement.setInt(parameterIndex, data.topK);
}
private void resolveFilterBindings() throws HopException {
@@ -219,18 +277,16 @@ public class PgVectorSearch extends
BaseTransform<PgVectorSearchMeta, PgVectorSe
try {
data.database =
PgVectorDatabase.connect(this, this, getMetadataProvider(),
meta.getConnection());
- VectorDistanceMetric metric =
+ data.metric =
meta.getDistanceMetric() != null ? meta.getDistanceMetric() :
VectorDistanceMetric.COSINE;
- String qualifiedTable =
+ data.qualifiedTable =
PgVectorSqlBuilder.qualifiedTable(
resolve(meta.getSchemaName()), resolve(meta.getTableName()));
- List<PgVectorSearchFilter> activeFilters =
- data.filterBindings.stream().map(b -> b.filter).toList();
- data.searchStatement =
- data.database
- .getConnection()
- .prepareStatement(
- PgVectorSqlBuilder.searchSql(qualifiedTable, metric,
activeFilters));
+ // Prepare the statement holding every filter up front, so a bad table
or column name fails
+ // on the first row as before. Statements that leave out skipped filters
follow on demand.
+ BitSet allFilters = new BitSet();
+ allFilters.set(0, data.filterBindings.size());
+ searchStatement(allFilters);
} catch (Exception e) {
closeDatabase();
throw new HopException(BaseMessages.getString(PKG,
"PgVectorSearch.Error.Initializing"), e);
@@ -244,14 +300,14 @@ public class PgVectorSearch extends
BaseTransform<PgVectorSearchMeta, PgVectorSe
}
private void closeDatabase() {
- if (data.searchStatement != null) {
+ for (PreparedStatement statement : data.searchStatements.values()) {
try {
- data.searchStatement.close();
+ statement.close();
} catch (Exception e) {
logError(BaseMessages.getString(PKG,
"PgVectorSearch.Error.ClosingStatement"), e);
}
- data.searchStatement = null;
}
+ data.searchStatements.clear();
if (data.database != null) {
data.database.disconnect();
data.database = null;
diff --git
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchData.java
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchData.java
index 8586b23284..d5416fc9ed 100644
---
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchData.java
+++
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchData.java
@@ -18,9 +18,13 @@ package org.apache.hop.pgvector.transforms.search;
import java.sql.PreparedStatement;
import java.util.ArrayList;
+import java.util.BitSet;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.pgvector.util.PgVectorSearchFilter;
+import org.apache.hop.pgvector.util.VectorDistanceMetric;
import org.apache.hop.pipeline.transform.BaseTransformData;
import org.apache.hop.pipeline.transform.ITransformData;
@@ -29,7 +33,16 @@ public class PgVectorSearchData extends BaseTransformData
implements ITransformD
public IRowMeta inputRowMeta;
public IRowMeta outputRowMeta;
public org.apache.hop.core.database.Database database;
- public PreparedStatement searchStatement;
+
+ /**
+ * Prepared search statements keyed by the filters they contain: bit {@code
i} is set when {@code
+ * filterBindings.get(i)} is part of the WHERE clause. Without "skip if
empty" filters there is
+ * only ever the one statement holding every filter.
+ */
+ public Map<BitSet, PreparedStatement> searchStatements = new HashMap<>();
+
+ public String qualifiedTable;
+ public VectorDistanceMetric metric;
/** Resolved once in init, so variables are not re-resolved per row. */
public int topK;
diff --git
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchDialog.java
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchDialog.java
index fc5d82b4a7..4507555b83 100644
---
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchDialog.java
+++
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchDialog.java
@@ -45,6 +45,8 @@ import org.eclipse.swt.widgets.TableItem;
public class PgVectorSearchDialog extends BaseTransformDialog {
private static final Class<?> PKG = PgVectorSearchMeta.class;
+ private static final String CONST_COMBO_YES = "System.Combo.Yes";
+ private static final String CONST_COMBO_NO = "System.Combo.No";
private final PgVectorSearchMeta input;
private GuiCompositeWidgets widgets;
@@ -123,8 +125,18 @@ public class PgVectorSearchDialog extends
BaseTransformDialog {
BaseMessages.getString(PKG,
"PgVectorSearchDialog.Filters.Column.Stream"),
ColumnInfo.COLUMN_TYPE_CCOMBO,
new String[] {},
+ true),
+ new ColumnInfo(
+ BaseMessages.getString(PKG,
"PgVectorSearchDialog.Filters.Column.SkipIfEmpty"),
+ ColumnInfo.COLUMN_TYPE_CCOMBO,
+ new String[] {
+ BaseMessages.getString(PKG, CONST_COMBO_YES),
+ BaseMessages.getString(PKG, CONST_COMBO_NO)
+ },
true)
};
+ columns[2].setToolTip(
+ BaseMessages.getString(PKG,
"PgVectorSearchDialog.Filters.Column.SkipIfEmpty.Tooltip"));
int rows = input.getFilters() != null ? input.getFilters().size() : 0;
wFilters =
@@ -189,6 +201,9 @@ public class PgVectorSearchDialog extends
BaseTransformDialog {
TableItem item = new TableItem(wFilters.table, SWT.NONE);
item.setText(1, Const.NVL(filter.getColumnName(), ""));
item.setText(2, Const.NVL(filter.getStreamField(), ""));
+ item.setText(
+ 3,
+ BaseMessages.getString(PKG, filter.isSkipIfEmpty() ? CONST_COMBO_YES
: CONST_COMBO_NO));
}
wFilters.removeEmptyRows();
wFilters.setRowNums();
@@ -202,8 +217,10 @@ public class PgVectorSearchDialog extends
BaseTransformDialog {
for (TableItem item : wFilters.getNonEmptyItems()) {
String columnName = item.getText(1);
String streamField = item.getText(2);
+ boolean skipIfEmpty =
+ BaseMessages.getString(PKG,
CONST_COMBO_YES).equalsIgnoreCase(item.getText(3));
if (!Utils.isEmpty(columnName) && !Utils.isEmpty(streamField)) {
- filters.add(new PgVectorSearchFilter(columnName, streamField));
+ filters.add(new PgVectorSearchFilter(columnName, streamField,
skipIfEmpty));
}
}
return filters;
diff --git
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMeta.java
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMeta.java
index da519a4623..b110cf54e6 100644
---
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMeta.java
+++
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMeta.java
@@ -258,7 +258,9 @@ public class PgVectorSearchMeta extends
BaseTransformMeta<PgVectorSearch, PgVect
copy.filters = new ArrayList<>();
if (filters != null) {
for (PgVectorSearchFilter filter : filters) {
- copy.filters.add(new PgVectorSearchFilter(filter.getColumnName(),
filter.getStreamField()));
+ copy.filters.add(
+ new PgVectorSearchFilter(
+ filter.getColumnName(), filter.getStreamField(),
filter.isSkipIfEmpty()));
}
}
return copy;
diff --git
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/util/PgVectorSearchFilter.java
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/util/PgVectorSearchFilter.java
index d8cdcb353a..d2fd0cabb6 100644
---
a/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/util/PgVectorSearchFilter.java
+++
b/plugins/tech/pgvector/src/main/java/org/apache/hop/pgvector/util/PgVectorSearchFilter.java
@@ -20,7 +20,13 @@ import lombok.Getter;
import lombok.Setter;
import org.apache.hop.metadata.api.HopMetadataProperty;
-/** Filters search results where a table column equals a value from an input
stream field. */
+/**
+ * Filters search results where a table column equals a value from an input
stream field.
+ *
+ * <p>By default a filter always applies, so an empty stream value is compared
as is and matches
+ * nothing. With {@link #skipIfEmpty} set, the filter is left out of the query
for any row whose
+ * stream value is empty, which makes it an optional narrowing rather than a
required one.
+ */
@Getter
@Setter
public class PgVectorSearchFilter {
@@ -31,10 +37,18 @@ public class PgVectorSearchFilter {
@HopMetadataProperty(key = "stream", injectionKey = "STREAM")
private String streamField;
+ @HopMetadataProperty(key = "skip_if_empty", injectionKey = "SKIP_IF_EMPTY")
+ private boolean skipIfEmpty;
+
public PgVectorSearchFilter() {}
public PgVectorSearchFilter(String columnName, String streamField) {
+ this(columnName, streamField, false);
+ }
+
+ public PgVectorSearchFilter(String columnName, String streamField, boolean
skipIfEmpty) {
this.columnName = columnName;
this.streamField = streamField;
+ this.skipIfEmpty = skipIfEmpty;
}
}
diff --git
a/plugins/tech/pgvector/src/main/resources/org/apache/hop/pgvector/transforms/search/messages/messages_en_US.properties
b/plugins/tech/pgvector/src/main/resources/org/apache/hop/pgvector/transforms/search/messages/messages_en_US.properties
index fe9508ecf4..bcfc4c44a0 100644
---
a/plugins/tech/pgvector/src/main/resources/org/apache/hop/pgvector/transforms/search/messages/messages_en_US.properties
+++
b/plugins/tech/pgvector/src/main/resources/org/apache/hop/pgvector/transforms/search/messages/messages_en_US.properties
@@ -42,6 +42,8 @@ PgVectorSearch.Tab.Main=Main
PgVectorSearchDialog.Filters.Label=Filters
PgVectorSearchDialog.Filters.Column.Table=Table column
PgVectorSearchDialog.Filters.Column.Stream=Stream field
+PgVectorSearchDialog.Filters.Column.SkipIfEmpty=Skip if empty
+PgVectorSearchDialog.Filters.Column.SkipIfEmpty.Tooltip=Y leaves this filter
out of the search for any row whose stream field is empty, so the row is
searched without it. N (the default) always applies the filter, and an empty
stream value then matches nothing.
PgVectorSearch.Validation.ConnectionRequired=A database connection is required
PgVectorSearch.Validation.TableRequired=A table name is required
@@ -56,6 +58,7 @@ PgVectorSearch.Error.Searching=Error searching the pgvector
table
PgVectorSearch.Error.ClosingStatement=Error closing the pgvector search
statement
PgVectorSearch.eatingRowOnNoMatch.Label=Do not pass the row if the search
finds nothing
PgVectorSearch.eatingRowOnNoMatch.Tooltip=When off, a row that matches nothing
is still passed on with empty match fields. When on, the row is dropped. Rows
with an empty embedding field are treated the same way.
+PgVectorSearch.Log.SkippedEmptyFilter=Filter on column ''{0}'' skipped: stream
field ''{1}'' is empty
PgVectorSearch.Log.EmptyEmbedding=Field ''{0}'' contains no query vector;
treated as no match
PgVectorSearchMeta.Injection.EAT_ROW_ON_NO_MATCH=Do not pass the row if the
search finds nothing
diff --git
a/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMetaTest.java
b/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMetaTest.java
index ec628a28b3..4838b47eb7 100644
---
a/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMetaTest.java
+++
b/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchMetaTest.java
@@ -61,7 +61,10 @@ class PgVectorSearchMetaTest {
original.setResultChunkIndexField("hit_idx");
original.setResultContentField("hit_content");
original.setResultScoreField("hit_score");
- original.setFilters(List.of(new PgVectorSearchFilter("source_type",
"wanted_type")));
+ original.setFilters(
+ List.of(
+ new PgVectorSearchFilter("source_type", "wanted_type"),
+ new PgVectorSearchFilter("category", "wanted_category", true)));
PgVectorSearchMeta copy = roundTrip(original);
@@ -78,9 +81,12 @@ class PgVectorSearchMetaTest {
assertEquals(original.getResultChunkIndexField(),
copy.getResultChunkIndexField());
assertEquals(original.getResultContentField(),
copy.getResultContentField());
assertEquals(original.getResultScoreField(), copy.getResultScoreField());
- assertEquals(1, copy.getFilters().size());
+ assertEquals(2, copy.getFilters().size());
assertEquals("source_type", copy.getFilters().get(0).getColumnName());
assertEquals("wanted_type", copy.getFilters().get(0).getStreamField());
+ assertEquals(false, copy.getFilters().get(0).isSkipIfEmpty());
+ assertEquals("category", copy.getFilters().get(1).getColumnName());
+ assertEquals(true, copy.getFilters().get(1).isSkipIfEmpty());
}
/** The matched chunk index is a number in the table, so it must be a number
in the stream too. */
@@ -159,6 +165,24 @@ class PgVectorSearchMetaTest {
XmlHandler.getSubNode(document, "transform"),
PgVectorSearchMeta.class, null);
}
+ @Test
+ void cloneKeepsTheSkipIfEmptyFlag() {
+ PgVectorSearchMeta meta = new PgVectorSearchMeta();
+ meta.setDefault();
+ meta.setFilters(List.of(new PgVectorSearchFilter("category",
"wanted_category", true)));
+
+ PgVectorSearchMeta copy = (PgVectorSearchMeta) meta.clone();
+
+ assertTrue(copy.getFilters().get(0).isSkipIfEmpty());
+ }
+
+ /** Filters written before the option existed keep applying to every row. */
+ @Test
+ void filtersAreNotSkippedByDefault() {
+ assertEquals(false, new PgVectorSearchFilter().isSkipIfEmpty());
+ assertEquals(false, new PgVectorSearchFilter("category",
"wanted_category").isSkipIfEmpty());
+ }
+
/** Matches Hop's Database Lookup: rows are passed on unless the option is
explicitly enabled. */
@Test
void defaultsToPassingRowsThroughOnNoMatch() {
diff --git
a/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchTest.java
b/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchTest.java
index 1144d1503a..e159ab8f71 100644
---
a/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchTest.java
+++
b/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/transforms/search/PgVectorSearchTest.java
@@ -17,25 +17,35 @@
package org.apache.hop.pgvector.transforms.search;
import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.ArgumentMatchers.anyInt;
import static org.mockito.ArgumentMatchers.anyString;
import static org.mockito.Mockito.doAnswer;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;
+import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
+import java.util.BitSet;
import java.util.Iterator;
+import java.util.LinkedHashMap;
import java.util.List;
+import java.util.Map;
import org.apache.hop.core.HopClientEnvironment;
+import org.apache.hop.core.database.Database;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.RowMeta;
import org.apache.hop.core.row.value.ValueMetaString;
import org.apache.hop.core.variables.Variables;
+import org.apache.hop.pgvector.util.PgVectorSearchFilter;
+import org.apache.hop.pgvector.util.VectorDistanceMetric;
import org.apache.hop.pipeline.transforms.mock.TransformMockHelper;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeAll;
@@ -48,6 +58,9 @@ class PgVectorSearchTest {
private List<Object[]> output;
private IRowMeta outputRowMeta;
+ /** SQL prepared on the stubbed connection, in order, with the statement
handed out for it. */
+ private Map<String, PreparedStatement> prepared;
+
@BeforeAll
static void setUpClass() throws Exception {
HopClientEnvironment.init();
@@ -61,6 +74,7 @@ class PgVectorSearchTest {
when(helper.logChannelFactory.create(any(),
any())).thenReturn(helper.iLogChannel);
when(helper.pipeline.isRunning()).thenReturn(true);
output = new ArrayList<>();
+ prepared = new LinkedHashMap<>();
}
@AfterEach
@@ -138,6 +152,75 @@ class PgVectorSearchTest {
assertNull(output.get(0)[outputRowMeta.indexOfValue("match_id")]);
}
+ /** Without "skip if empty" an empty filter value is still bound, as it
always has been. */
+ @Test
+ void bindsAnEmptyFilterValueWhenSkippingIsOff() throws Exception {
+ runFiltered(false, new Object[] {"[0.1,0.2]", "q1", "acme", null});
+
+ assertEquals(1, prepared.size());
+ String sql = prepared.keySet().iterator().next();
+ assertTrue(sql.contains("WHERE \"tenant\" = ? AND \"category\" = ?"), sql);
+ PreparedStatement statement = prepared.get(sql);
+ verify(statement).setString(2, "acme");
+ verify(statement).setString(3, null);
+ verify(statement).setInt(5, 5);
+ }
+
+ @Test
+ void leavesAnEmptySkippableFilterOutOfTheQuery() throws Exception {
+ runFiltered(true, new Object[] {"[0.1,0.2]", "q1", "acme", ""});
+
+ String sql = lastPreparedSql();
+ assertTrue(sql.contains("WHERE \"tenant\" = ? ORDER BY"), sql);
+ assertFalse(sql.contains("\"category\""), sql);
+ PreparedStatement statement = prepared.get(sql);
+ verify(statement).setString(2, "acme");
+ verify(statement).setString(3, "[0.1,0.2]");
+ verify(statement).setInt(4, 5);
+ assertEquals(1, output.size());
+ assertEquals("doc-0",
output.get(0)[outputRowMeta.indexOfValue("match_document_id")]);
+ }
+
+ @Test
+ void appliesASkippableFilterWhenItHasAValue() throws Exception {
+ runFiltered(true, new Object[] {"[0.1,0.2]", "q1", "acme", "manuals"});
+
+ assertEquals(1, prepared.size(), "only the statement with every filter is
needed");
+ String sql = lastPreparedSql();
+ assertTrue(sql.contains("WHERE \"tenant\" = ? AND \"category\" = ?"), sql);
+ PreparedStatement statement = prepared.get(sql);
+ verify(statement).setString(2, "acme");
+ verify(statement).setString(3, "manuals");
+ }
+
+ /** Skipping only applies to filters that ask for it: an empty required
filter still binds. */
+ @Test
+ void neverSkipsAFilterThatIsNotMarkedSkippable() throws Exception {
+ runFiltered(true, new Object[] {"[0.1,0.2]", "q1", null, null});
+
+ String sql = lastPreparedSql();
+ assertTrue(sql.contains("WHERE \"tenant\" = ? ORDER BY"), sql);
+ verify(prepared.get(sql)).setString(2, null);
+ }
+
+ /** Each combination of filters is prepared once and reused by later rows. */
+ @Test
+ void preparesEachFilterCombinationOnce() throws Exception {
+ runFiltered(
+ true,
+ new Object[] {"[0.1,0.2]", "q1", "acme", "manuals"},
+ new Object[] {"[0.1,0.2]", "q2", "acme", null},
+ new Object[] {"[0.1,0.2]", "q3", "acme", "faq"},
+ new Object[] {"[0.1,0.2]", "q4", "acme", ""});
+
+ assertEquals(2, prepared.size());
+ assertEquals(4, output.size());
+ }
+
+ private String lastPreparedSql() {
+ return new ArrayList<>(prepared.keySet()).get(prepared.size() - 1);
+ }
+
private static PgVectorSearchMeta newMeta() {
PgVectorSearchMeta meta = new PgVectorSearchMeta();
meta.setDefault();
@@ -147,9 +230,51 @@ class PgVectorSearchTest {
}
private void run(PgVectorSearchMeta meta, String embedding, int matchCount)
throws Exception {
+ PgVectorSearchData data = newData(meta, new String[0]);
+ data.searchStatements.put(new BitSet(), stubStatement(matchCount));
+ process(meta, data, List.<Object[]>of(new Object[] {embedding, "q1"}));
+ }
+
+ /**
+ * Runs rows of {@code embedding, marker, tenant, category} through a
transform filtering on
+ * tenant (always applied) and category (skipped when empty, if {@code
skipEmptyCategory}), with
+ * statements prepared on a stubbed connection.
+ */
+ private void runFiltered(boolean skipEmptyCategory, Object[]... rows) throws
Exception {
+ PgVectorSearchMeta meta = newMeta();
+ meta.setFilters(
+ List.of(
+ new PgVectorSearchFilter("tenant", "tenant"),
+ new PgVectorSearchFilter("category", "category",
skipEmptyCategory)));
+ PgVectorSearchData data = newData(meta, new String[] {"tenant",
"category"});
+ for (int i = 0; i < meta.getFilters().size(); i++) {
+ data.filterBindings.add(
+ new PgVectorSearchData.FilterBinding(2 + i,
meta.getFilters().get(i)));
+ }
+ data.metric = VectorDistanceMetric.COSINE;
+ data.qualifiedTable = "\"public\".\"chunks\"";
+ Connection connection = mock(Connection.class);
+ when(connection.prepareStatement(anyString()))
+ .thenAnswer(
+ invocation -> {
+ PreparedStatement statement = stubStatement(1);
+ prepared.put(invocation.getArgument(0), statement);
+ return statement;
+ });
+ data.database = mock(Database.class);
+ when(data.database.getConnection()).thenReturn(connection);
+
+ process(meta, data, List.of(rows));
+ }
+
+ private PgVectorSearchData newData(PgVectorSearchMeta meta, String[]
extraFields)
+ throws Exception {
IRowMeta inputRowMeta = new RowMeta();
inputRowMeta.addValueMeta(new ValueMetaString("embedding"));
inputRowMeta.addValueMeta(new ValueMetaString("marker"));
+ for (String field : extraFields) {
+ inputRowMeta.addValueMeta(new ValueMetaString(field));
+ }
PgVectorSearchData data = new PgVectorSearchData();
data.inputRowMeta = inputRowMeta;
@@ -162,18 +287,21 @@ class PgVectorSearchTest {
data.resultContentFieldIndex =
data.outputRowMeta.indexOfValue("match_content");
data.resultScoreFieldIndex =
data.outputRowMeta.indexOfValue("match_score");
data.filterBindings = new ArrayList<>();
- data.searchStatement = stubStatement(matchCount);
+ return data;
+ }
+ private void process(PgVectorSearchMeta meta, PgVectorSearchData data,
List<Object[]> input)
+ throws Exception {
PgVectorSearch transform =
spy(
new PgVectorSearch(
helper.transformMeta, meta, data, 0, helper.pipelineMeta,
helper.pipeline));
transform.init();
- transform.setInputRowMeta(inputRowMeta);
+ transform.setInputRowMeta(data.inputRowMeta);
// The database is already stubbed, so skip the one-time setup branch in
processRow().
transform.first = false;
- Iterator<Object[]> rows = List.<Object[]>of(new Object[] {embedding,
"q1"}).iterator();
+ Iterator<Object[]> rows = input.iterator();
doAnswer(invocation -> rows.hasNext() ? rows.next() :
null).when(transform).getRow();
doAnswer(
invocation -> {
diff --git
a/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/util/PgVectorSqlBuilderTest.java
b/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/util/PgVectorSqlBuilderTest.java
index 68132e757b..9cf3eb3deb 100644
---
a/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/util/PgVectorSqlBuilderTest.java
+++
b/plugins/tech/pgvector/src/test/java/org/apache/hop/pgvector/util/PgVectorSqlBuilderTest.java
@@ -17,6 +17,7 @@
package org.apache.hop.pgvector.util;
import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
import java.util.List;
@@ -103,6 +104,17 @@ class PgVectorSqlBuilderTest {
assertTrue(sql.endsWith("LIMIT ?"));
}
+ /** With every filter skipped for a row, the search runs without a WHERE
clause. */
+ @Test
+ void searchSqlWithoutFiltersHasNoWhereClause() {
+ String sql =
+ PgVectorSqlBuilder.searchSql(
+ "\"public\".\"chunks\"", VectorDistanceMetric.COSINE, List.of());
+
+ assertFalse(sql.contains("WHERE"));
+ assertEquals(3, sql.chars().filter(c -> c == '?').count(), "score vector +
order-by + limit");
+ }
+
/** Identifiers are quoted and embedded quotes are doubled, so a crafted
name cannot break out. */
@Test
void quotesAndEscapesIdentifiers() {
