Full-Text Search SQL extension

[wombatu-kun]

Summary

This proposes a Full-Text Search (FTS) SQL extension that exposes full-text relevance as a WHERE predicate over Lance tables, complementing the existing SEARCH() / VECTOR_SEARCH() / HYBRID_SEARCH() table functions. It adds three predicate functions - lance_match, lance_phrase, lance_multi_match - a projectable _score relevance column, COUNT(*) evaluated through the FTS index, and a correct global ORDER BY _score ... LIMIT k ranked-retrieval path. It targets all supported Spark/Scala combinations (3.4, 3.5, 4.0, 4.1; Scala 2.12/2.13) and reuses existing infrastructure: the DataSource V2 pushdown hub, the optimizer-rule injection pattern already used by the blob rules, inline metadata columns, and Lance's native inverted-index FTS.

Sponsorship and collaboration

Following the Contribution Workflow, this asks @hamersaw to sponsor the design. @ivscheianu has prototyped much of the predicate surface described here and is invited to co-develop, so the work lands as one shared implementation rather than parallel efforts.

1. Motivation

Users want full-text relevance as an ordinary SQL predicate, not only as a dedicated search source. The two shapes are genuinely different:

• Search as the source (already available): SELECT ... FROM SEARCH('lance.db.docs', 'vector database', 10) returns the globally top-k most relevant rows. Ideal when ranking is the query.
• Search as a predicate (this proposal): SELECT ... FROM docs WHERE lance_match(body, 'vector database') AND year >= 2024 ... treats relevance as one filter among many, composable with joins, additional WHERE conjuncts, GROUP BY, COUNT(*), and window functions. The table-function surface cannot express "filter this pipeline by relevance" without forcing search to be the leaf operator.

Both shapes share the same Lance inverted index; this proposal adds the predicate shape that is currently missing.

2. How it works today

2.1 The search table functions

SEARCH, VECTOR_SEARCH, and HYBRID_SEARCH are registered via SparkSessionExtensions.injectTableFunction (org.lance.spark.search.LanceSearchTableFunctions). They build a LanceSearchQuery (SearchType.FULL_TEXT / VECTOR) and call the namespace API namespace.queryTable(QueryTableRequest), reading the Arrow IPC response. The scan is single-partition (LanceSearchScan returns one partition; LanceSearchColumnarPartitionReader issues one queryTable call), which is what makes the returned top-k a correct global ranking. SEARCH appends a _score column; VECTOR_SEARCH appends _distance; HYBRID_SEARCH appends _score, _distance, and a reciprocal-rank-fusion _relevance_score computed in Spark.

2.2 FTS index creation

The extension grammar already supports ALTER TABLE t CREATE INDEX name USING fts (col) WITH (...), executed by AddIndexExec against Lance IndexType.INVERTED, with options base_tokenizer, language, stem, remove_stop_words, ascii_folding, lower_case, max_token_length, and with_position (required for phrase queries). FTS index creation is therefore out of scope here; this proposal is purely query-side and assumes an inverted index exists on the searched column(s).

2.3 The gap

Nothing on main lets you write FTS in a WHERE clause: the only FTS surface is the table-function form, which forces search to be the leaf operator and cannot serve as one predicate among many.

3. Proposed design

3.1 SQL surface

Three Lance-namespaced scalar predicate functions, valid only inside a top-level AND conjunction of a WHERE over a Lance table:

lance_match(col, query [, options])               -- BM25 match on one indexed text column
lance_phrase(col, query [, slop])                 -- ordered phrase match (slop = max gap, default 0; needs with_position index)
lance_multi_match(query [, options], c1, c2, ...)  -- match across multiple columns (variadic tail)

The options argument is a key=value,key=value string mapped onto Lance's FullTextQuery.match(text, column, boost, Optional<fuzziness>, maxExpansions, Operator, prefixLength):

key	type	default	meaning
operator	AND | OR	OR	how query terms combine
fuzziness	int	auto	max edit distance per term
prefix_length	int	0	leading characters that must match exactly under fuzziness
max_expansions	int	50	fuzzy-term expansion budget
boost	float	1.0	score multiplier (affects _score, not which rows match)

lance_multi_match accepts operator and boost in its options and matches the query against each listed column. lance_phrase takes a positional slop integer (maximum token gap) rather than an options map.

Examples:

SELECT id, body FROM docs WHERE lance_match(body, 'apache spark');
SELECT id, body FROM docs WHERE lance_match(body, 'sparrk', 'fuzziness=1,operator=AND') AND year >= 2024;
SELECT id, body, _score FROM docs WHERE lance_match(body, 'vector database') ORDER BY _score DESC LIMIT 10;
SELECT count(*) FROM docs WHERE lance_match(body, 'deep learning');
SELECT id FROM docs WHERE lance_phrase(body, 'machine learning', 1);
SELECT id FROM docs WHERE lance_multi_match('lance', 'operator=AND', title, body);

Hard constraints, enforced fail-fast at planning time: at most one FTS predicate per query, in the top-level AND (FTS under OR/NOT is rejected, because Lance applies one FTS query per scan); col/query are string literals; _score is selectable only when an FTS predicate is active (3.4).

3.2 Predicate functions and the optimizer rule

The functions are UnboundFunction sentinels: they resolve and type-check as real catalog functions so the analyzer accepts WHERE lance_match(body,'x'), but their produceResult(...) throws ("lance_match must be pushed down; enable the Lance SQL extension and keep the predicate in a top-level AND"). They are markers consumed by an optimizer rule and never evaluate row-by-row; if a predicate is not stripped, the user gets a precise error instead of silently-wrong results. This is the same sentinel contract the existing SEARCH() table functions rely on.

A single Catalyst optimizer rule does the rewrite. Walking the entire condition tree, it validates fail-fast at planning time (rejecting any FTS call under OR/NOT, enforcing the single-predicate and literal-argument rules, and validating _score co-occurrence), builds a Lance FullTextQuery from the function and its options, serializes it into the relation's options map, removes the FTS conjunct from the Filter (residual non-FTS conjuncts stay and flow normally to SupportsPushDownV2Filters), and rewrites the relation via relation.copy(options = ...). Using the named-argument copy(options = ...) form (the same approach the merged blob-context rules use) keeps the rule portable across the Spark-version differences in the DataSourceV2Relation API.

The rule is injected with injectOptimizerRule, landing in the preCBO batch after V2ScanRelationPushDown (matching the precedent set by the merged blob rules). Encoding FTS into the relation's options rather than into a transient Filter shape is deliberate: Spark runs optimizer batches to a fixed point, so the subsequent pushdown re-run re-creates the scan with FTS attached, and column pruning, limit, top-N, aggregate pushdown, and metadata-column resolution all observe it.

End-to-end flow for SELECT id, body, _score FROM docs WHERE lance_match(body, 'vector database', 'operator=AND') ORDER BY _score DESC LIMIT 10:

Analyze
  lance_match -> UnboundFunction sentinel (FunctionCatalog); _score -> LanceDataset.metadataColumns()
  plan: GlobalLimit(Sort(_score DESC, Project(id, body, _score, Filter(lance_match(...), Relation[LanceDataset]))))
Optimize
  V2ScanRelationPushDown builds an initial scan (no FTS yet)
  LanceFtsPredicateRule (preCBO, runs after pushdown):
    - validate: single FTS conjunct, top-level AND, literal args, _score co-occurs with FTS
    - build FullTextQuery.match('vector database', 'body', operator=AND, ...)
    - rel' = rel.copy(options = rel.options + { __lance_fts_query : encode(query) })
    - remove lance_match from the Filter -> Project(..., rel')
    - fixed point: V2ScanRelationPushDown re-runs on rel'
  newScanBuilder(options) -> LanceScanBuilder decodes the FTS query
    - pruneColumns({id, body, _score})  (_score allowed: FTS active)
    - pushTopN([_score DESC], 10)        (recognized as score ordering, 3.5)
    - build() -> LanceScan(..., ftsQuery, topN=[_score DESC], limit=10)
Execute
  LanceFragmentScanner.create():
    scanOptions.fullTextQuery(FullTextQuery.match(...))  -- Lance autoprojects _score
    scanOptions.columns([id, body]); limit(10); setColumnOrderings([_score DESC])
    fragment.newScan(scanOptions) -> Arrow batch (id, body, _score)
  Spark merges per-fragment results -> Sort(_score DESC) + GlobalLimit(10)

The FTS query is threaded as Optional<FtsQuerySpec> through LanceScanBuilder -> LanceScan -> LanceInputPartition -> LanceFragmentScanner, and applied via the Lance API ScanOptions.Builder.fullTextQuery(org.lance.ipc.FullTextQuery).

3.3 _score as a metadata column

_score is exposed as an inline static MetadataColumn on LanceDataset (name _score, FloatType, nullable), alongside the existing ROW_ID_COLUMN / ROW_ADDRESS_COLUMN / FRAGMENT_ID_COLUMN, and included in metadataColumns(). As a metadata column it is hidden from SELECT *, preserving backward compatibility. The value is produced by Lance's scanner: setting ScanOptions.fullTextQuery(...) makes Lance autoproject the relevance column, and the reader maps it into the _score output slot - there is no Spark-side BM25. Because _score is meaningful only with an active FTS query, metadataColumns() always advertises it (so the analyzer can resolve it), and the optimizer rule validates that any projected _score co-occurs with an FTS predicate on the same relation, otherwise raising a clear AnalysisException.

3.4 COUNT(*) through the FTS index

LanceScanBuilder.pushAggregation today short-circuits COUNT(*) to a manifest metadata fast-path only when there are no pushed predicates. With an active FTS query the manifest total ignores FTS selectivity, so the fast-path is bypassed and the count is computed through the index. Preferred path: a driver-side count using the Lance primitives Dataset.countIndexedRows(column, query, Optional<fragmentIds>) / Dataset.countRows(filter), returned as a single-row local scan with no executors. Fallback (partial or untrained index, where countIndexedRows would undercount): a per-fragment scan-based count in LanceCountStarPartitionReader, which sets fullTextQuery(...) and uses scanner.countRows() per fragment, summed by Spark. The driver chooses based on index coverage.

3.5 Ranked retrieval and global top-k

Target: SELECT ..., _score FROM t WHERE lance_match(col,'q') ORDER BY _score DESC LIMIT k. This is the capability no current implementation provides, and the one with a real correctness subtlety.

Mechanism. LanceScanBuilder already implements SupportsPushDownTopN. We extend it: when FTS is active and the sole sort key is _score DESC, accept the push, ask each fragment for its local top-k by score (limit(k) + setColumnOrderings([_score DESC])), and let Spark merge the at-most k x numFragments rows into the global top-k (isPartiallyPushed() stays true, so Spark keeps the global sort/limit above the scan). The merge input is bounded and cheap.

Correctness across fragments (an explicit Lance-core dependency). BM25 score is sum over terms of IDF(t) * tf-saturation, and IDF(t) depends on corpus-level document count N and document frequency df(t). If each fragment's scan computes IDF from only its own documents, per-fragment _score values are not comparable, and a raw k x numFragments merge is not a correct global top-k. A correct distributed merge requires Lance to score each fragment's rows against dataset-global corpus statistics carried by the dataset-level inverted index. We propose to state this as a Lance-core invariant (C1): per-fragment FTS scans must score using global IDF.

Default that is correct today. Until C1 is guaranteed, ORDER BY _score DESC LIMIT k defaults to a single-partition, dataset-level ranked scan (Dataset.newScan with fullTextQuery, limit=k, score ordering) - structurally the same single-partition shape the SEARCH() functions already use, so it is correct by construction. The distributed per-fragment top-k (the parallel path above) is opt-in (fts.distributed_topn=true) and gated on a Lance-core capability check for global IDF. This gives correct ranking out of the box with a clean, flag-gated upgrade to distributed ranking once Lance core confirms C1.

Robustness of ORDER BY _score. A naive predicate-path implementation fails on ORDER BY _score DESC: Spark inserts a range-partitioning exchange whose RangePartitioner.sketch runs a sampling scan to choose bounds, and that sampling scan re-plans the relation outside the path that attached FTS, so the scan is rebuilt without the FTS query and _score is "requested without an active FTS query". The fix follows from 3.2: because the FTS query is encoded in the relation options and stored on LanceScan (and included in equals/hashCode), it is intrinsic to the scan and re-materialized identically by every newScanBuilder call, including the sampling scan, ReusedExchange, and AQE re-optimization. No rule needs to re-fire during sampling. For the unbounded ORDER BY _score case, the single-partition global scan also removes the range exchange entirely.

3.6 Complementarity with the search table functions

	Predicate path (this proposal)	SEARCH() table functions
Parallelism	full per-fragment scan	single partition
Global top-k correctness	needs C1 for the distributed merge; default uses a single-partition global scan	correct by construction
Composability	composes with joins, extra WHERE, GROUP BY, windows; FTS is one predicate among many	search is the leaf; you compose around its output
Needs a namespace	no (works on dataset paths)	yes (queryTable)
Best for	filtering a pipeline by relevance, COUNT(*) of matches, hybrid SQL	"give me the global top-k most relevant rows"

The two stay complementary. For WHERE lance_match(...) without score ordering (the filter use case) the distributed path is always used - the unique value this adds. For WHERE lance_match(...) ORDER BY _score DESC LIMIT k the V2 scan builder internally chooses the single-partition global ranked Lance scan, but does not rewrite the query into a SEARCH() table function, which would change semantics for residual predicates and conflate the two surfaces.

3.7 Design decisions and rejected alternatives

• Sentinel functions + optimizer rule, not a parser/grammar extension. Catalog functions resolve and type-check for free and need no grammar changes; the rule does the rewrite. (Same approach as the existing search functions.)
• Relation rewrite via relation.copy(options = ...). The named-argument form is portable across the Spark-version differences in the DataSourceV2Relation API, as already proven by the merged blob-context rules.
• _score as a metadata column, not a schema decorator. A decorator (as used for _distance via LanceVirtualColumnsTable) would promote _score into the base schema() and regress SELECT *. SupportsMetadataColumns keeps it hidden by default.
• Fail-fast, not fallback substring eval. There is no correct row-wise BM25 without the index, so silently evaluating FTS as a generic UDF would produce wrong relevance. Unsupported shapes raise a clear AnalysisException at planning time.
• Ranked retrieval defaults to a single-partition global scan, not distributed-by-default. Correct out of the box; distributed top-k is opt-in and gated on the Lance-core global-IDF guarantee.

4. Who is affected

• SQL users: gain lance_match / lance_phrase / lance_multi_match, a projectable _score, FTS-aware COUNT(*), and ranked retrieval. Fully backward compatible: SELECT * is unchanged (_score is hidden), and existing queries are unaffected.
• Lance core: the distributed ranked top-k depends on global-IDF scoring for per-fragment FTS scans (C1). The default ranking path does not depend on it. C1 would be confirmed with the core team before enabling the distributed path.
• Docs: a new docs/src/operations/dql/fts.md, mirroring search.md, cross-linked with the FTS index page and with search.md (when to use predicate FTS vs SEARCH()).

5. Risks and mitigations

Risk	Mitigation
Cross-fragment BM25 comparability (highest)	Default to the single-partition global ranked scan (correct by construction); make the distributed merge opt-in and gated on the declared Lance-core global-IDF guarantee (C1).
Optimizer-rule fragility across Spark versions	Confine mutation to relation.copy(options = ...) (named arg), proven version-portable by the merged blob-context rules; compile/test matrix on 3.4/3.5/4.0/4.1.
Rule ordering vs V2ScanRelationPushDown	Encode FTS into relation options so the fixed-point pushdown re-run re-creates the scan with FTS; assert via tests that pruning/limit/topN/aggregate observe FTS.
Sentinel leakage (predicate not stripped)	Tree-wide planning-time validation converts unsupported shapes into precise AnalysisExceptions; sentinel produceResult throws rather than silently mis-evaluating.
_score in sampling / reused / AQE plans	FTS intrinsic to LanceScan with equals/hashCode; regression test reproducing the RangePartitioner.sketch failure.
Interaction with other pushdowns (limit/offset/aggregate)	LIMIT without order returns arbitrary k matches (documented); keep isPartiallyPushed() true; bypass count fast-path under FTS; residual predicates combine with FTS in one ScanOptions.
Blob v2 / ACCEPT_ANY_SCHEMA columns	Validate the FTS column is a normal indexed text field, not a blob descriptor column; reject with a clear message.
Cost-based optimizer blind to FTS selectivity	Near-term, document; optionally feed an FTS-aware row estimate (from countIndexedRows) into statistics later. Out of scope for the first PRs.
Untrained / partial FTS index	Detect index coverage on the driver; force scan-based count/eval and warn when coverage is partial.

6. Incremental implementation plan

Small, independently reviewable PRs, ordered so each lands value and de-risks the next.

1. FTS predicate plumbing (no scoring/ranking): the lance_match sentinel, option parsing, the optimizer rule (strip + validate + serialize into options), and threading Optional<FtsQuerySpec> to LanceFragmentScanner.fullTextQuery(...); register the function and rule in every LanceSparkSessionExtensions.
2. lance_phrase + lance_multi_match with slop and per-column matching.
3. _score metadata column (unordered): inline SCORE_COLUMN, conditional exposure, map Lance's autoprojected score; verify SELECT * excludes it and standalone _score is rejected.
4. COUNT(*) through the FTS index: bypass the metadata fast-path under FTS; prefer countIndexedRows/countRows, fallback to per-fragment count.
5. Global ranked top-k via the dataset-level scan (correct by construction): route ORDER BY _score DESC LIMIT k to a single-partition ranked Lance scan.
6. RangePartitioner.sketch robustness + unbounded ORDER BY _score: prove FTS survives sampling/reused/AQE because it is intrinsic to LanceScan; add the regression test.
7. Distributed per-fragment top-k push (opt-in, C1-gated): extend pushTopN for _score; gate behind fts.distributed_topn and a Lance-core capability check. Last and flag-gated.
8. Docs + examples: docs/src/operations/dql/fts.md, cross-linked with the FTS index and search pages.

7. Open questions for sponsors and reviewers

1. Global IDF (C1): does Lance core score per-fragment fullTextQuery scans against dataset-global corpus statistics today, or per-fragment? This decides whether the distributed top-k push is "enable a flag" or "blocked on upstream".
2. Scoring autoprojection: when _score is not projected (a pure filter), can scoring be turned off with guaranteed-identical matching, to skip score computation?
3. Default ranking path: agree that ORDER BY _score DESC LIMIT k should default to the single-partition global scan (correct, lower parallelism) rather than distributed-by-default with a caveat?
4. _score exposure model: advertise _score always and validate co-occurrence in the rule (simpler analysis), or advertise it only once the rule has attached FTS (stricter, couples metadataColumns() to optimizer state)?
5. Boolean composition: ever support multiple FTS predicates by mapping AND/OR onto Lance's FullTextQuery.booleanQuery / Occur{SHOULD,MUST,MUST_NOT}, or hard-cap at one for v1?
6. Function namespacing: register lance_match only under the Lance catalog (lance.lance_match), or also session-wide for the bare name (collision risk with user UDFs)?
7. Ranked predicate-FTS vs SEARCH(): is internal delegation to a dataset-level ranked scan acceptable, or should ranked queries be documented as "use SEARCH()" and the predicate surface stay filter-only?

[hamersaw]

Thanks for putting this together! I think the proposal makes sense, but my biggest concern is introducing an orthogonal API to perform these operations. We're not a large project and I fear the maintenance burden of maintaining two APIs that solve the same problem. Can we succinctly summarize the advantages of this over the existing TVF approach?

From what I can tell most of the advantages of this approach can be implementing using the existing TVF, they just have not yet been. The exception is using FTS as a scalar boolean in arbitrary expressions (ex. OR, HAVING, etc). IMO moving forward boils down to determining if this is a real user need that's worth maintaining another API. The only concrete example for something the TVF can not provide is something like:

SELECT * FROM X WHERE (A OR lance_match) AND C;

[ivscheianu]

The way I think about it: both paths hit the same FTS index, but they answer different questions. The TVF says "give me the top-k most relevant rows", so it's a ranking primitive. The predicate says "give me all rows matching this text condition", so it's a filter. One is a source, the other is a WHERE clause.

Where this shows up in practice:

-- Predicate path: reads naturally
SELECT department, COUNT(*) FROM docs
WHERE lance_match(body, 'compliance') AND year >= 2024
GROUP BY department

-- TVF path: what k is "big enough"?
SELECT department, COUNT(*) FROM SEARCH('docs', 'compliance', 100000)
WHERE year >= 2024
GROUP BY department

The TVF version works, but the user has to guess a k, it runs single-partition through queryTable, and it's computing relevance scores nobody asked for. The predicate version is just a normal table scan with a pushed-down filter, and Spark parallelizes it across fragments automatically.

Beyond the semantics, there are structural differences that would be hard to retrofit into the TVF:

1. Parallelism: the predicate path gets one Spark task per fragment through normal DataSource V2 partition planning. The TVF goes through a single queryTable call, so one partition, one task. Making the TVF parallel would mean either a two-phase pipeline (fetch row IDs, then fan out reads) or switching it to use the native scanner internally. Either way we will end up rebuilding the predicate path inside the TVF.

2. Direct scanner path: the predicate path opens the dataset by URI and scans fragments directly. The TVF routes through the namespace queryTable RPC. Both still need a namespace-backed catalog for table resolution and index creation, but the predicate path skips the queryTable round-trip at scan time.

3. SQL composability (within AND): the predicate is just a filter on a regular table scan, so joins, GROUP BY, window functions, additional WHERE conjuncts, and Catalyst optimizations all work normally. The TVF forces search to be the leaf node. One limitation though: the predicate only composes as a top-level AND conjunct. OR/NOT/CASE wrapping is rejected at planning time since Lance-core supports one FTS query per scan.

On OR composition: this is one thing the TVF structurally can't provide (lance-core limitation), but if that's lifted upstream, the predicate path is where it would naturally land.

I know two text-search entry points adds cognitive load, but my thinking is that clear docs solve it: "Use SEARCH() for ranked results by relevance. Use lance_match() to filter a table by text match as one condition among many."

[hamersaw]

The TVF says "give me the top-k most relevant rows", so it's a ranking primitive

I guess this is where I would challenge it. For the points presented:

Parallelism

Currently the TVF implements its planInputPartitions() to just return a single partition, but that is not a fundamental restriction, rather a connector choice. We could update this to serve multiple partitions in the same way the predicate proposal does.

Direct scanner path

The reader implementation for TVF at LanceSearchColumnarPartitionReader uses the namespace.queryTable function, but could easily use a scanner directory if the namespace doesn't exist. This is the pattern we have used elsewhere in the connector and I would argue for the predicate approach we would want to update to allow for namespace use.

SQL composability

For the AND example here I think we can make TVF work with a full_match_mode with something like:

SELECT department, COUNT(*)
FROM SEARCH(table => 'docs', query => 'compliance', full_match => true)
WHERE year >= 2024
GROUP BY department

IIUC this would get the same functionality with a little implementation massaging.

I'm not at all saying we shouldn't do this predicate approach, I just want to make sure we're doing it for the right reasons. I think most of the arguments for this can be solved by the TVF approach, but just have not been currently. If we do decide to implement predicate-based queries, I think for maintainability it would be useful to fuse the internal implementation as much as possible. In my quick spike over the weekend it feels like 40-50% of the code can be the same and then the predicate implementation can be catalyst rules / etc over the top of that. That way, using TVF or predicates is less of a "what is your use-case" and more so a syntactic decision -- which admittedly, I would vote for the predicates TBH.

[ivscheianu]

Ok, I understand. I'm ok with sharing the internals. Looking at the code, I think the convergence works cleanly for filtering: the predicate path already goes through LanceScan -> LanceFragmentScanner -> fragment.newScan() with a FullTextQuery. A TVF full_match mode could feed into the same path instead of its own LanceSearchScan -> namespace.queryTable, getting parallelism for free.

Happy to create a design and iterate on it, I'll post it here once I have it.

[hamersaw]

@ivscheianu I had some time today, so I hope you don't mind I did a little dive into this and put together a brief design of this below.

[hamersaw]

Had a little time today to look at this and I actually quite like the direction this is going. Apologies for the Claude plan output, but I made sure it was concise enough (but hopefully actionable) that it doesn't read too heavily. Basically, I think the goals are to:

• Unify the backend of TVF + predicate
• Enable namespace usage (similar to existing Spark connector APIs)

This way the existing TVF becomes a very lightweight implementation and the proposed predicate approach provides a little more flexibility in terms of syntax. Would appreciate any feedback, and from there we can decide a bit on how this gets done. I should have some cycles coming up and would like to contribute where I can.

---

The main divergence from the proposal is the execution model. The proposal keeps the predicate path local-only (JNI fragment.newScan) — distributed for filters, a single-partition local Dataset.newScan for ranked retrieval, and "no namespace required" as an advantage. Where this discussion landed: unify the existing SEARCH TVF and the new predicate functions onto one shared front-end, and dispatch execution by capability — prefer the namespace queryTable endpoint when present, fall back to the local dataset path otherwise. That mirrors how the rest of the connector already resolves namespace-or-path (resolve (datasetUri, credentials) via describeTable, then operate on the URI; LanceFragmentScanner / LanceCountStarPartitionReader already work this way).

Unchanged from the proposal

• lance_match / lance_phrase / lance_multi_match sentinels + optimizer-rule pushdown.
• _score as a metadata column (hidden from SELECT *).
• FTS-aware COUNT(*) via countIndexedRows, bypassing the manifest fast-path.
• Ranked ORDER BY _score DESC LIMIT k, single-partition by default.
• Planning-time validation (single FTS predicate, top-level AND, no OR/NOT).

Where it changed

	Original proposal	Where we landed
Scope	new predicate surface alongside the TVF	both surfaces share one front-end: query model, arg parser, scan spec, planning
Execution	predicate = local JNI only	dispatch by capability: namespace endpoint (queryTable) → local via vended creds → error
Namespace	"not required" (a predicate advantage)	preferred when present (correct global ranking by construction); local is the fallback
Query model	predicate builds ipc.FullTextQuery; TVF builds StringFtsQuery	one model (FullTextQuery) + converter to namespace structured FtsQuery (already supported as of namespace 0.7.5)
TVF internals	bespoke LanceSearchTable scan stack	relation over the real LanceDataset, sharing the scan with the predicate path
Ranked top-k correctness	distributed merge gated on global IDF (C1)	single-partition queryTable is correct by construction; C1 only needed for the distributed local path
Distribution	per-fragment local	namespaced is single-partition until QueryTableRequest gains a fragments param (mirroring countIndexedRows); local stays per-fragment

Concrete shape

1. One query model + converter. Keep org.lance.ipc.FullTextQuery as canonical (the predicate rule already builds it; the TVF moves off StringFtsQuery). Add a FullTextQuery → namespace StructuredFtsQuery converter next to FullTextQueryUtils. The mapping is mechanical — fields line up 1:1 (verified against namespace 0.7.5):

• MatchQuery(queryText, column, boost, fuzziness, maxExpansions, operator, prefixLength) → namespace MatchQuery (same fields; queryText→terms)
• PhraseQuery(queryText, column, slop) → namespace PhraseQuery
• MultiMatchQuery(queryText, columns, boosts, operator) → namespace MultiMatchQuery = one MatchQuery per column
• wrap in StructuredFtsQuery → QueryTableRequestFullTextQuery.structuredQuery

2. One scan spec, carried by the existing options plumbing. The predicate rule already serializes the query into relation options under LanceSparkReadOptions.CONFIG_FULL_TEXT_QUERY (AbstractLanceFtsPredicateRule → FullTextQueryUtils.fullTextQueryToString). Extend that with k/offset and reuse the existing column/filter pushdown. Both front-ends produce the same spec:

• Predicate: the rule already does this — minimal change.
• TVF: LanceSearchTableFunctions.search builds a DataSourceV2Relation over the real LanceDataset with the spec in options, instead of constructing LanceSearchTable. The FTS-specific LanceSearch{Table,Scan,ScanBuilder,InputPartition,PartitionReaderFactory,Columnar/RowReader} classes are then deleted (vector/hybrid keep theirs).

3. Backend dispatch in the scan/partition reader. Selector is the getNamespaceImpl != null style check already used for resolution, refined by endpoint availability:

• Namespace path: build QueryTableRequest from the spec → namespace.queryTable → decode Arrow IPC. The decode already exists in LanceSearchColumnarPartitionReader; extract it into a shared reader. _score comes from the response.
• Local path: the existing LanceFragmentScanner — scanOptions.fullTextQuery(...) then fragment.newScan(...). _score from Lance's autoprojected relevance column.

4. Filters + limit (correctness). Reuse FilterPushDown.compileFiltersToSqlWhereClause(pushedPredicates) (already used to build the local ScanOptions.filter at LanceScanBuilder.java:258) to populate QueryTableRequest.filter. Pushing k to queryTable is only safe when no residual filter sits above the scan — the existing hasResidualPredicates flag (LanceScanBuilder :300-313, already gating topN :358 and aggregate :380) is exactly that gate; when set, push filter-only and let Spark's global limit trim.

5. COUNT(*). Two concrete edits: extend the metadata-fast-path guard in LanceScanBuilder.pushAggregation (:387-399) to also exclude FTS (the manifest total ignores selectivity), and switch LanceCountStarPartitionReader.computeCount from batch-counting to Dataset.countIndexedRows(column, query, Optional<fragmentIds>). countIndexedRows is already fragment-scoped, so FTS count distributes today. countTableRows has no FTS field, so per the dispatch rule count falls to the local path (search uses the namespace endpoint, count uses local-via-credentials).

Suggested build order

Each step is independently reviewable; the _2.13 modules compile the _2.12 sources, so most edits are single-module — only LanceFtsPredicateRule.scala (the copy() arity shim) is per Spark version.

1. Shared scan spec + FullTextQuery → StructuredFtsQuery converter (+ unit tests). Pure addition.
2. TVF builds FullTextQuery/structuredQuery instead of StringFtsQuery (gains fuzziness/boost/operator/phrase/multi_match). Existing TVF tests stay green.
3. Backend dispatch: keep local LanceFragmentScanner; add the queryTable branch + extracted Arrow reader; implement the k-gate.
4. FTS COUNT(*): the two edits above.
5. Route the TVF through the real LanceDataset relation; delete the FTS LanceSearch* scan classes; migrate BaseSparkSearchTableFunctionTest.
6. (Upstream-gated) distributed namespaced query once QueryTableRequest carries fragments; distributed local ranked behind a global-IDF (C1) check.

Upstream dependency

Only one, and only for distributing the namespaced query path: a fragment subset on QueryTableRequest (mirroring countIndexedRows's Optional<fragmentIds>). Until then namespaced FTS is single-partition, which is also the correct-ranking path, so it's a fine default. Distributed local ranked top-k still needs per-fragment scores against dataset-global corpus stats (the C1/global-IDF item already raised). FTS count needs nothing upstream.

Notes / open

• ... WHERE (A OR lance_match) AND C is rejected under the proposal's validation (FTS can't sit under OR), so the predicate win is AND-composition + joins/aggregates/windows, not OR. The namespace BooleanQuery type (must/should/mustNot) is a plausible future path to real OR/NOT.
• k/LIMIT/offset semantics across shapes (WHERE lance_match(...) no limit, LIMIT n no order, ORDER BY _score LIMIT k, SEARCH(..., k, offset)) still need to be pinned down per backend.
• _score exposure: since metadataColumns() is resolved before the optimizer rule runs, advertise _score always and have the rule raise AnalysisException when it's projected without a co-occurring FTS predicate — net behavior is "only usable with FTS."

[hamersaw]

@ivscheianu I put up a PR at http://github.com/ivscheianu/lance-spark/pull/1 into your branch that should (1) route TVF through the same query path as predicates and (2) enable namespace queries using predicates. Can you take a look when you get the chance and we'll get this all moving forward!

[ivscheianu]

Thank you a lot for the work! Approved the PR and left some comments, I can address them myself after you merge this if you think they're worth it.

[hamersaw]

@ivscheianu @wombatu-kun thanks for all the work on this! Just merged #501. If I recall the plan is to follow up on some of the remaining work like surfacing score and supporting COUNT(*). What do we think this looks like?

[ivscheianu]

For _score, the auto-projection we rely on seems to be deprecated in lance-core, so the fix is to explicitly include _score in the projection list when FTS is active (lance-core resolves it from the FTS execution node at plan time). When selected without FTS we handle it spark side with a null constant vector.

For COUNT(*): there's a bug where the optimizer rule strips the FTS predicate before pushAggregation runs, so it sees no predicates and returns total table rows from metadata. I'd thread the query into computeCount using scanner.countRows

We'll need _score first, then COUNT(*), I can take care of them if you're ok with that.

[ivscheianu]

Created #779

For COUNT(*), there's a fix in #766 that adds the getFullTextQuery() == null guard to the metadata shortcut and threads the FTS query into LanceCountStarPartitionReader.computeCount. That should be ready to merge independently.

After both land, the remaining items from the proposal are:

• Pushed ranked retrieval (ORDER BY _score DESC LIMIT k -> single-partition scan, correct by construction)
• Distributed per-fragment top-k (gated on C1 / global-IDF confirmation from lance-core)