fix(node): accept Arrow metadata across JavaScript realms (#3904)

## Summary

- accept genuine Arrow metadata maps created in another JavaScript realm
- validate every metadata entry and clone it into a local Map
- cover an Arrow 15 VM-realm table through the public fromDataToBuffer
boundary
- retain structural typing for nested and dictionary Arrow data

## Root cause

The sanitizer used a local-realm instanceof Map check for schema and
field metadata. A genuine Map created in another JavaScript realm has
the required internal Map state but fails that identity check, so
fromDataToBuffer rejected the foreign table before serializing its rows.

## Scope

This fixes the distinct JavaScript-realm sanitizer failure identified
during review. It does not establish the cause of the S3/compaction
panic reported in #1525, so that issue remains open.

## Validation

- pnpm test --runInBand (707 passed, 5 skipped)
- pnpm test --runInBand __test__/arrow.test.ts (189 passed)
- pnpm build
- pnpm lint
- pnpm run docs

Related to #1525

<!-- lance-gatekeeper-fix:v1 agent=b522628ad3bae914eb7266ccd899d508
generation=1 -->

---------

Co-authored-by: Gatefixer <313497061+lancedb-gatefixer[bot]@users.noreply.github.com>
Co-authored-by: Xuanwo <github@xuanwo.io>

.bumpversion.toml

@@ -1,5 +1,5 @@
 [tool.bumpversion]
-current_version = "0.38.0-beta.10"
+current_version = "0.38.0-beta.11"
 parse = """(?x)
     (?P<major>0|[1-9]\\d*)\\.
     (?P<minor>0|[1-9]\\d*)\\.

Cargo.lock

@@ -5402,7 +5402,7 @@ dependencies = [
 
 [[package]]
 name = "lancedb"
-version = "0.38.0-beta.10"
+version = "0.38.0-beta.11"
 dependencies = [
  "ahash",
  "anyhow",
@@ -5490,7 +5490,7 @@ dependencies = [
 
 [[package]]
 name = "lancedb-nodejs"
-version = "0.38.0-beta.10"
+version = "0.38.0-beta.11"
 dependencies = [
  "arrow-array",
  "arrow-buffer",
@@ -5515,7 +5515,7 @@ dependencies = [
 
 [[package]]
 name = "lancedb-python"
-version = "0.38.0-beta.10"
+version = "0.38.0-beta.11"
 dependencies = [
  "arrow",
  "async-trait",

docs/src/java/java.md

@@ -14,7 +14,7 @@ Add the following dependency to your `pom.xml`:
 <dependency>
     <groupId>com.lancedb</groupId>
     <artifactId>lancedb-core</artifactId>
-    <version>0.38.0-beta.10</version>
+    <version>0.38.0-beta.11</version>
 </dependency>
 ```
 

java/lancedb-core/pom.xml

@@ -8,7 +8,7 @@
     <parent>
       <groupId>com.lancedb</groupId>
       <artifactId>lancedb-parent</artifactId>
-      <version>0.38.0-beta.10</version>
+      <version>0.38.0-beta.11</version>
       <relativePath>../pom.xml</relativePath>
     </parent>
 

java/pom.xml

@@ -6,7 +6,7 @@
 
     <groupId>com.lancedb</groupId>
     <artifactId>lancedb-parent</artifactId>
-    <version>0.38.0-beta.10</version>
+    <version>0.38.0-beta.11</version>
     <packaging>pom</packaging>
     <name>${project.artifactId}</name>
     <description>LanceDB Java SDK Parent POM</description>

nodejs/Cargo.toml

@@ -1,7 +1,7 @@
 [package]
 name = "lancedb-nodejs"
 edition.workspace = true
-version = "0.38.0-beta.10"
+version = "0.38.0-beta.11"
 publish = false
 license.workspace = true
 description.workspace = true

nodejs/__test__/arrow.test.ts

@@ -1,11 +1,16 @@
 // SPDX-License-Identifier: Apache-2.0
 // SPDX-FileCopyrightText: Copyright The LanceDB Authors
+import * as fs from "node:fs";
+import * as vm from "node:vm";
 import * as arrow15 from "apache-arrow-15";
 import * as arrow16 from "apache-arrow-16";
 import * as arrow17 from "apache-arrow-17";
 import * as arrow18 from "apache-arrow-18";
 
 import {
+  Field as CurrentField,
+  LargeBinary as CurrentLargeBinary,
+  Schema as CurrentSchema,
   Vector as CurrentVector,
   convertToTable,
   tableFromIPC as currentTableFromIPC,
@@ -36,6 +41,59 @@ function sampleRecords(): Array<Record<string, any>> {
     },
   ];
 }
+
+it("serializes an Arrow Table created in another JavaScript realm", async () => {
+  const context = vm.createContext({
+    TextDecoder,
+    TextEncoder,
+    console,
+    setTimeout,
+    clearTimeout,
+  });
+  vm.runInContext(
+    fs.readFileSync(
+      require.resolve("apache-arrow-15/Arrow.es2015.min"),
+      "utf8",
+    ),
+    context,
+  );
+  const foreignTable: unknown = vm.runInContext(
+    "Arrow.tableFromArrays({ id: new Int32Array([1, 2, 3]), text: ['foo', 'bar', 'baz'] })",
+    context,
+  );
+
+  const foreignMetadata = (
+    foreignTable as { schema: { metadata: Map<string, string> } }
+  ).schema.metadata;
+  expect(foreignMetadata).not.toBeInstanceOf(Map);
+
+  const buf = await fromDataToBuffer(
+    foreignTable as Parameters<typeof fromDataToBuffer>[0],
+  );
+  const actual = currentTableFromIPC(buf);
+
+  expect(actual.numRows).toBe(3);
+  expect(actual.getChild("id")?.toJSON()).toEqual([1, 2, 3]);
+  expect(actual.getChild("text")?.toJSON()).toEqual(["foo", "bar", "baz"]);
+});
+
+it("preserves field metadata from a provided schema", async function () {
+  const jsonMetadata = new Map([["ARROW:extension:name", "lance.json"]]);
+  const schema = new CurrentSchema([
+    new CurrentField("meta", new CurrentLargeBinary(), true, jsonMetadata),
+  ]);
+
+  const table = makeArrowTable(
+    [{ meta: Buffer.from(JSON.stringify({ source: "test" })) }],
+    { schema },
+  );
+
+  expect(table.schema.fields[0].metadata).toEqual(jsonMetadata);
+
+  const roundTripped = currentTableFromIPC(await fromTableToBuffer(table));
+  expect(roundTripped.schema.fields[0].metadata).toEqual(jsonMetadata);
+});
+
 describe.each([arrow15, arrow16, arrow17, arrow18])(
   "Arrow",
   (

nodejs/__test__/embedding.test.ts

@@ -187,6 +187,58 @@ describe("embedding functions", () => {
     const vector0 = JSON.parse(JSON.stringify(arr[0].vector));
     expect(vector0).toEqual([1, 2, 3]);
   });
+  it("should append multiple Python embeddings with the same alias", async () => {
+    @register("python-mock")
+    // biome-ignore lint/correctness/noUnusedVariables: the decorator registers this class
+    class MockEmbeddingFunction extends EmbeddingFunction<string> {
+      ndims() {
+        return 3;
+      }
+      embeddingDataType(): Float {
+        return new Float32();
+      }
+      async computeQueryEmbeddings(_data: string) {
+        return [1, 2, 3];
+      }
+      async computeSourceEmbeddings(data: string[]) {
+        return data.map((value) =>
+          value === "hello world" ? [1, 2, 3] : [4, 5, 6],
+        );
+      }
+    }
+
+    const metadata = new Map([
+      [
+        "embedding_functions",
+        '[{"source_column":"text1","vector_column":"vector1","name":"python-mock","model":{}},{"source_column":"text2","vector_column":"vector2","name":"python-mock","model":{}}]',
+      ],
+    ]);
+    const schema = new Schema(
+      [
+        new Field("text1", new Utf8(), true),
+        new Field("text2", new Utf8(), true),
+        new Field(
+          "vector1",
+          new FixedSizeList(3, new Field("item", new Float32(), true)),
+          true,
+        ),
+        new Field(
+          "vector2",
+          new FixedSizeList(3, new Field("item", new Float32(), true)),
+          true,
+        ),
+      ],
+      metadata,
+    );
+
+    const db = await connect(tmpDir.name);
+    const table = await db.createEmptyTable("test", schema);
+    await table.add([{ text1: "hello world", text2: "goodbye world" }]);
+
+    const rows = await table.query().toArray();
+    expect(JSON.parse(JSON.stringify(rows[0].vector1))).toEqual([1, 2, 3]);
+    expect(JSON.parse(JSON.stringify(rows[0].vector2))).toEqual([4, 5, 6]);
+  });
 
   it("should append generated vectors to a non-nullable schema", async () => {
     @register("non_nullable_schema_test")

nodejs/__test__/table.test.ts

@@ -3561,6 +3561,27 @@ describe("when creating an empty table", () => {
     expect((actualSchema.fields[1].type as Float64).precision).toBe(2);
   });
 
+  it("can add and query JSON data", async () => {
+    const schema = new Schema([
+      new Field("id", new Int32(), true),
+      new Field(
+        "meta",
+        new Utf8(),
+        true,
+        new Map([["ARROW:extension:name", "arrow.json"]]),
+      ),
+    ]);
+    const table = await con.createEmptyTable("json", schema);
+    const meta = JSON.stringify({ x: 1 });
+
+    await table.add([{ id: 1, meta }]);
+
+    const rows = await table.query().toArray();
+    expect(rows).toHaveLength(1);
+    expect(rows[0].id).toBe(1);
+    expect(rows[0].meta).toBe(meta);
+  });
+
   it("can create an empty table from schema that specifies field types by name", async () => {
     const schemaLike = {
       fields: [

nodejs/lancedb/arrow.ts

@@ -72,8 +72,7 @@ export type FieldLike =
     };
 
 export type DataLike =
-  // biome-ignore lint/suspicious/noExplicitAny: <explanation>
-  | import("apache-arrow").Data<Struct<any>>
+  | import("apache-arrow").Data
   | {
       // biome-ignore lint/suspicious/noExplicitAny: <explanation>
       type: any;
@@ -82,6 +81,7 @@ export type DataLike =
       stride: number;
       nullable: boolean;
       children: DataLike[];
+      dictionary?: { data: readonly DataLike[] };
       get nullCount(): number;
       // biome-ignore lint/suspicious/noExplicitAny: <explanation>
       values: Buffers<any>[BufferType.DATA];

nodejs/lancedb/sanitize.ts

@@ -94,17 +94,24 @@ export function sanitizeMetadata(
   if (metadataLike === undefined || metadataLike === null) {
     return undefined;
   }
-  if (!(metadataLike instanceof Map)) {
+
+  let entries: IterableIterator<[unknown, unknown]>;
+  try {
+    entries = Map.prototype.entries.call(metadataLike);
+  } catch {
     throw Error("Expected metadata, if present, to be a Map<string, string>");
   }
-  for (const item of metadataLike) {
-    if (typeof item[0] !== "string" || typeof item[1] !== "string") {
+
+  const metadata = new Map<string, string>();
+  for (const [key, value] of entries) {
+    if (typeof key !== "string" || typeof value !== "string") {
       throw Error(
         "Expected metadata, if present, to be a Map<string, string> but it had non-string keys or values",
       );
     }
+    metadata.set(key, value);
   }
-  return metadataLike as Map<string, string>;
+  return metadata;
 }
 
 export function sanitizeInt(typeLike: object) {

nodejs/lancedb/schema.ts

@@ -406,10 +406,11 @@ function matchingFields(fields: Field[], tree: FieldTree): Field[] {
           field.name,
           new Struct(matchingFields(struct.children, value)),
           field.nullable,
+          field.metadata,
         ),
       );
     } else {
-      matches.push(new Field(field.name, value as DataType, field.nullable));
+      matches.push(field);
     }
   }
   return matches;

nodejs/npm/darwin-arm64/package.json

@@ -1,6 +1,6 @@
 {
 	"name": "@lancedb/lancedb-darwin-arm64",
-	"version": "0.38.0-beta.10",
+	"version": "0.38.0-beta.11",
 	"os": ["darwin"],
 	"cpu": ["arm64"],
 	"main": "lancedb.darwin-arm64.node",

nodejs/npm/linux-arm64-gnu/package.json

@@ -1,6 +1,6 @@
 {
 	"name": "@lancedb/lancedb-linux-arm64-gnu",
-	"version": "0.38.0-beta.10",
+	"version": "0.38.0-beta.11",
 	"os": ["linux"],
 	"cpu": ["arm64"],
 	"main": "lancedb.linux-arm64-gnu.node",

nodejs/npm/linux-arm64-musl/package.json

@@ -1,6 +1,6 @@
 {
 	"name": "@lancedb/lancedb-linux-arm64-musl",
-	"version": "0.38.0-beta.10",
+	"version": "0.38.0-beta.11",
 	"os": ["linux"],
 	"cpu": ["arm64"],
 	"main": "lancedb.linux-arm64-musl.node",

nodejs/npm/linux-x64-gnu/package.json

@@ -1,6 +1,6 @@
 {
 	"name": "@lancedb/lancedb-linux-x64-gnu",
-	"version": "0.38.0-beta.10",
+	"version": "0.38.0-beta.11",
 	"os": ["linux"],
 	"cpu": ["x64"],
 	"main": "lancedb.linux-x64-gnu.node",

nodejs/npm/linux-x64-musl/package.json

@@ -1,6 +1,6 @@
 {
 	"name": "@lancedb/lancedb-linux-x64-musl",
-	"version": "0.38.0-beta.10",
+	"version": "0.38.0-beta.11",
 	"os": ["linux"],
 	"cpu": ["x64"],
 	"main": "lancedb.linux-x64-musl.node",

nodejs/npm/win32-arm64-msvc/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@lancedb/lancedb-win32-arm64-msvc",
-  "version": "0.38.0-beta.10",
+  "version": "0.38.0-beta.11",
   "os": [
     "win32"
   ],

nodejs/npm/win32-x64-msvc/package.json

@@ -1,6 +1,6 @@
 {
 	"name": "@lancedb/lancedb-win32-x64-msvc",
-	"version": "0.38.0-beta.10",
+	"version": "0.38.0-beta.11",
 	"os": ["win32"],
 	"cpu": ["x64"],
 	"main": "lancedb.win32-x64-msvc.node",

nodejs/package-lock.json

@@ -1,12 +1,12 @@
 {
   "name": "@lancedb/lancedb",
-  "version": "0.38.0-beta.10",
+  "version": "0.38.0-beta.11",
   "lockfileVersion": 3,
   "requires": true,
   "packages": {
     "": {
       "name": "@lancedb/lancedb",
-      "version": "0.38.0-beta.10",
+      "version": "0.38.0-beta.11",
       "cpu": [
         "x64",
         "arm64"

nodejs/package.json

@@ -11,7 +11,7 @@
     "ann"
   ],
   "private": false,
-  "version": "0.38.0-beta.10",
+  "version": "0.38.0-beta.11",
   "main": "dist/index.js",
   "exports": {
     ".": "./dist/index.js",

python/Cargo.toml

@@ -1,6 +1,6 @@
 [package]
 name = "lancedb-python"
-version = "0.38.0-beta.10"
+version = "0.38.0-beta.11"
 publish = false
 edition.workspace = true
 description = "Python bindings for LanceDB"

python/python/lancedb/_blob.py

@@ -270,7 +270,8 @@ def _iter_projection_pairs(
             if isinstance(expr, str):
                 yield name, expr
             elif isinstance(expr, Expr):
-                yield name, expr.to_sql()
+                source = expr._column_name()
+                yield name, source if source is not None else expr.to_sql()
         return
     for column in projection:
         if isinstance(column, str):
@@ -280,7 +281,8 @@ def _iter_projection_pairs(
             if isinstance(expr, str):
                 yield name, expr
             elif isinstance(expr, Expr):
-                yield name, expr.to_sql()
+                source = expr._column_name()
+                yield name, source if source is not None else expr.to_sql()
 
 
 def _set_blob_column(tbl: pa.Table, output_name: str, blobs: pa.Array) -> pa.Table:

python/python/lancedb/_lancedb.pyi

@@ -87,6 +87,7 @@ class PyExpr:
     def contains(self, substr: "PyExpr") -> "PyExpr": ...
     def isin(self, values: List["PyExpr"]) -> "PyExpr": ...
     def cast(self, data_type: pa.DataType) -> "PyExpr": ...
+    def column_name(self) -> Optional[str]: ...
     def to_sql(self) -> str: ...
 
 def expr_col(name: str) -> PyExpr: ...
@@ -605,10 +606,10 @@ class FullTextQuery:
 class PyQueryRequest:
     limit: Optional[int]
     offset: Optional[int]
-    take_offsets: Optional[List[int]]
     filter: Optional[Union[str, bytes]]
     full_text_search: Optional[FullTextQuery]
     select: Optional[Union[str, List[str]]]
+    select_source_columns: Optional[Dict[str, str]]
     fast_search: Optional[bool]
     with_row_id: Optional[bool]
     use_lsm: Optional[bool]

python/python/lancedb/expr.py

@@ -249,6 +249,10 @@ class Expr:
 
     # ── utilities ────────────────────────────────────────────────────────────
 
+    def _column_name(self) -> str | None:
+        """Return the source name when this is a bare column expression."""
+        return self._inner.column_name()
+
     def to_sql(self) -> str:
         """Render the expression as a SQL string (useful for debugging)."""
         return self._inner.to_sql()
@@ -312,7 +316,7 @@ def func(name: str, *args: ExprLike) -> Expr:
     --------
     >>> from lancedb.expr import col, func
     >>> func("lower", col("name"))
-    Expr(lower(name))
+    Expr(lower(`name`))
     """
     inner_args = [_coerce(a)._inner for a in args]
     return Expr(expr_func(name, inner_args))

python/python/lancedb/query.py

@@ -109,7 +109,6 @@ def _query_is_plain_scan(query: Query) -> bool:
     return (
         query.vector is None
         and query.full_text_query is None
-        and query.take_offsets is None
         and not query.postfilter
         and not query.order_by
     )
@@ -168,6 +167,12 @@ def _projection_to_scanner_kwargs(columns: QueryProjection) -> Dict[str, Any]:
     return {"columns": projection}
 
 
+def _query_request_projection(req: "PyQueryRequest") -> QueryProjection:
+    if req.select_source_columns is not None:
+        return req.select_source_columns
+    return req.select
+
+
 def _scanner_kwargs_for_query(
     query: Query,
     blob_mode: BlobMode,
@@ -799,10 +804,6 @@ class Query(pydantic.BaseModel):
     # offset to start fetching results from
     offset: Optional[int] = None
 
-    # Dataset offsets whose duplicate occurrences must be restored after lookup.
-    # This is populated when a take query is converted to this serializable form.
-    take_offsets: Optional[List[int]] = None
-
     # if true, will only search the indexed data
     fast_search: Optional[bool] = None
 
@@ -824,7 +825,6 @@ class Query(pydantic.BaseModel):
         query = cls()
         query.limit = req.limit
         query.offset = req.offset
-        query.take_offsets = req.take_offsets
         query.filter = req.filter
         query.full_text_query = req.full_text_search
         query.columns = req.select
@@ -2805,15 +2805,16 @@ class AsyncQueryBase(object):
 
         req = self._inner.to_query_request()
         schema = await self._table.schema()
+        projection = _query_request_projection(req)
         self._blob_auto_row_id = blob_auto_row_id_for_scan(
             schema,
-            req.select,
+            projection,
             with_row_id=self._with_row_id,
         )
         if not self._blob_auto_row_id:
             self._blob_paths = ()
             return
-        self._blob_paths = tuple(blob_v2_projection_sources(schema, req.select).keys())
+        self._blob_paths = tuple(blob_v2_projection_sources(schema, projection).keys())
         self._inner.with_row_id()
 
     def select(self, columns: Union[List[str], dict[str, str]]) -> Self:
@@ -3900,14 +3901,15 @@ class AsyncHybridQuery(AsyncStandardQuery, AsyncVectorQueryBase):
         blob_paths: tuple[str, ...] = ()
         if self._table is not None:
             schema = await self._table.schema()
+            projection = _query_request_projection(req)
             blob_auto_row_id = blob_auto_row_id_for_scan(
                 schema,
-                req.select,
+                projection,
                 with_row_id=self._with_row_id,
             )
             if blob_auto_row_id:
                 blob_paths = tuple(
-                    blob_v2_projection_sources(schema, req.select).keys()
+                    blob_v2_projection_sources(schema, projection).keys()
                 )
         self._blob_auto_row_id = blob_auto_row_id
         self._blob_paths = blob_paths

python/python/lancedb/remote/table.py

@@ -36,6 +36,7 @@ from lancedb._lancedb import (
     UpdateResult,
 )
 from lancedb.embeddings.base import EmbeddingFunctionConfig
+from lancedb.expr import Expr
 from lancedb.index import (
     FTS,
     BTree,
@@ -863,7 +864,7 @@ class RemoteTable(Table):
 
     def update(
         self,
-        where: Optional[str] = None,
+        where: Optional[Union[str, Expr]] = None,
         values: Optional[dict] = None,
         *,
         values_sql: Optional[Dict[str, str]] = None,
@@ -874,9 +875,11 @@ class RemoteTable(Table):
 
         Parameters
         ----------
-        where: str, optional
-            The SQL where clause to use when updating rows. For example, 'x = 2'
-            or 'x IN (1, 2, 3)'. The filter must not be empty, or it will error.
+        where: str or [Expr][lancedb.expr.Expr], optional
+            The filter condition. Can be a SQL string or a type-safe
+            [Expr][lancedb.expr.Expr] built with [col][lancedb.expr.col] and
+            [lit][lancedb.expr.lit]. The filter must not be empty, or it will
+            error.
         values: dict, optional
             The values to update. The keys are the column names and the values
             are the values to set.

python/python/lancedb/table.py

@@ -1504,9 +1504,9 @@ class Table(ABC):
         Offsets are mostly useful for sampling as the set of all valid offsets is easily
         known in advance to be [0, len(table)).
 
-        No guarantees are made regarding the order in which results are returned.
-        Repeated offsets produce repeated rows, which makes this method suitable for
-        sampling with replacement.
+        No guarantees are made regarding the order in which results are returned.  If
+        you desire an output order that matches the order of the given offsets, you will
+        need to add the row offset column to the output and align it yourself.
 
         Parameters
         ----------
@@ -1744,7 +1744,7 @@ class Table(ABC):
     @abstractmethod
     def update(
         self,
-        where: Optional[str] = None,
+        where: Optional[Union[str, Expr]] = None,
         values: Optional[dict] = None,
         *,
         values_sql: Optional[Dict[str, str]] = None,
@@ -1759,9 +1759,11 @@ class Table(ABC):
 
         Parameters
         ----------
-        where: str, optional
-            The SQL where clause to use when updating rows. For example, 'x = 2'
-            or 'x IN (1, 2, 3)'. The filter must not be empty, or it will error.
+        where: str or [Expr][lancedb.expr.Expr], optional
+            The filter condition. Can be a SQL string or a type-safe
+            [Expr][lancedb.expr.Expr] built with [col][lancedb.expr.col] and
+            [lit][lancedb.expr.lit]. The filter must not be empty, or it will
+            error.
         values: dict, optional
             The values to update. The keys are the column names and the values
             are the values to set.
@@ -1779,6 +1781,7 @@ class Table(ABC):
         Examples
         --------
         >>> import lancedb
+        >>> from lancedb.expr import col
         >>> import pandas as pd
         >>> data = pd.DataFrame({"x": [1, 2, 3], "vector": [[1.0, 2], [3, 4], [5, 6]]})
         >>> db = lancedb.connect("./.lancedb")
@@ -1788,7 +1791,7 @@ class Table(ABC):
         0  1  [1.0, 2.0]
         1  2  [3.0, 4.0]
         2  3  [5.0, 6.0]
-        >>> table.update(where="x = 2", values={"vector": [10.0, 10]})
+        >>> table.update(where=col("x") == 2, values={"vector": [10.0, 10]})
         UpdateResult(rows_updated=1, version=2)
         >>> table.to_pandas()
            x        vector
@@ -3841,7 +3844,7 @@ class LanceTable(Table):
 
     def update(
         self,
-        where: Optional[str] = None,
+        where: Optional[Union[str, Expr]] = None,
         values: Optional[dict] = None,
         *,
         values_sql: Optional[Dict[str, str]] = None,
@@ -3852,9 +3855,11 @@ class LanceTable(Table):
 
         Parameters
         ----------
-        where: str, optional
-            The SQL where clause to use when updating rows. For example, 'x = 2'
-            or 'x IN (1, 2, 3)'. The filter must not be empty, or it will error.
+        where: str or [Expr][lancedb.expr.Expr], optional
+            The filter condition. Can be a SQL string or a type-safe
+            [Expr][lancedb.expr.Expr] built with [col][lancedb.expr.col] and
+            [lit][lancedb.expr.lit]. The filter must not be empty, or it will
+            error.
         values: dict, optional
             The values to update. The keys are the column names and the values
             are the values to set.
@@ -3872,6 +3877,7 @@ class LanceTable(Table):
         Examples
         --------
         >>> import lancedb
+        >>> from lancedb.expr import col
         >>> import pandas as pd
         >>> data = pd.DataFrame({"x": [1, 2, 3], "vector": [[1.0, 2], [3, 4], [5, 6]]})
         >>> db = lancedb.connect("./.lancedb")
@@ -3881,7 +3887,7 @@ class LanceTable(Table):
         0  1  [1.0, 2.0]
         1  2  [3.0, 4.0]
         2  3  [5.0, 6.0]
-        >>> table.update(where="x = 2", values={"vector": [10.0, 10]})
+        >>> table.update(where=col("x") == 2, values={"vector": [10.0, 10]})
         UpdateResult(rows_updated=1, version=2)
         >>> table.to_pandas()
            x        vector
@@ -3908,7 +3914,6 @@ class LanceTable(Table):
             )
             and not self._route_pushdown_to_rust
             and self.current_branch() is None
-            and query.take_offsets is None
         ):
             from lancedb.namespace import _execute_server_side_query
 
@@ -5800,23 +5805,7 @@ class AsyncTable:
 
     def _sync_query_to_async(
         self, query: Query
-    ) -> (
-        AsyncHybridQuery
-        | AsyncFTSQuery
-        | AsyncVectorQuery
-        | AsyncQuery
-        | AsyncTakeQuery
-    ):
-        if query.take_offsets is not None:
-            take_query = self.take_offsets(query.take_offsets)
-            if query.columns:
-                take_query = take_query.select(query.columns)
-            if query.use_lsm is not None:
-                take_query = take_query.use_lsm(query.use_lsm)
-            if query.with_row_id:
-                take_query = take_query.with_row_id()
-            return take_query
-
+    ) -> AsyncHybridQuery | AsyncFTSQuery | AsyncVectorQuery | AsyncQuery:
         async_query = self.query()
         if query.limit is not None:
             async_query = async_query.limit(query.limit)
@@ -5881,7 +5870,6 @@ class AsyncTable:
                 self._namespace_client, self._pushdown_operations
             )
             and not self._route_pushdown_to_rust
-            and query.take_offsets is None
         ):
             from lancedb.namespace import _execute_server_side_query
 
@@ -6013,7 +6001,7 @@ class AsyncTable:
         self,
         updates: Optional[Dict[str, Any]] = None,
         *,
-        where: Optional[str] = None,
+        where: Optional[Union[str, Expr]] = None,
         updates_sql: Optional[Dict[str, str]] = None,
     ) -> UpdateResult:
         """
@@ -6028,9 +6016,11 @@ class AsyncTable:
             The updates to apply.  The keys should be the name of the column to
             update.  The values should be the new values to assign.  This is
             required unless updates_sql is supplied.
-        where: str, optional
-            An SQL filter that controls which rows are updated. For example, 'x = 2'
-            or 'x IN (1, 2, 3)'.  Only rows that satisfy this filter will be udpated.
+        where: str or [Expr][lancedb.expr.Expr], optional
+            The filter condition. Can be a SQL string or a type-safe
+            [Expr][lancedb.expr.Expr] built with [col][lancedb.expr.col] and
+            [lit][lancedb.expr.lit]. Only rows that satisfy this filter will
+            be updated.
         updates_sql: dict, optional
             The updates to apply, expressed as SQL expression strings.  The keys should
             be column names. The values should be SQL expressions.  These can be SQL
@@ -6048,13 +6038,14 @@ class AsyncTable:
         --------
         >>> import asyncio
         >>> import lancedb
+        >>> from lancedb.expr import col
         >>> import pandas as pd
         >>> async def demo_update():
         ...     data = pd.DataFrame({"x": [1, 2], "vector": [[1, 2], [3, 4]]})
         ...     db = await lancedb.connect_async("./.lancedb")
         ...     table = await db.create_table("my_table", data)
         ...     # x is [1, 2], vector is [[1, 2], [3, 4]]
-        ...     await table.update({"vector": [10, 10]}, where="x = 2")
+        ...     await table.update({"vector": [10, 10]}, where=col("x") == 2)
         ...     # x is [1, 2], vector is [[1, 2], [10, 10]]
         ...     await table.update(updates_sql={"x": "x + 1"})
         ...     # x is [2, 3], vector is [[1, 2], [10, 10]]
@@ -6068,7 +6059,8 @@ class AsyncTable:
         if updates is not None:
             updates_sql = {k: value_to_sql(v) for k, v in updates.items()}
 
-        return await self._inner.update(updates_sql, where)
+        predicate = where.to_sql() if isinstance(where, Expr) else where
+        return await self._inner.update(updates_sql, predicate)
 
     async def add_columns(
         self,
@@ -6372,9 +6364,6 @@ class AsyncTable:
         Offsets are mostly useful for sampling as the set of all valid offsets is easily
         known in advance to be [0, len(table)).
 
-        No guarantees are made regarding the order in which results are returned.
-        Repeated offsets produce repeated rows.
-
         Parameters
         ----------
         offsets: list[int]

python/python/tests/test_blob.py

@@ -8,7 +8,12 @@ import pyarrow.compute as pc
 import pytest
 
 import lancedb
-from lancedb._blob import read_row_ids_from_hits, stash_auto_row_ids
+from lancedb._blob import (
+    blob_v2_projection_sources,
+    read_row_ids_from_hits,
+    stash_auto_row_ids,
+)
+from lancedb.expr import col
 from lancedb.index import FTS
 from lancedb.schema import blob_column_paths, blob_v2_column_paths
 
@@ -70,6 +75,14 @@ def test_blob_v2_column_paths_include_list_children():
     ]
 
 
+def test_blob_v2_projection_sources_use_typed_column_name():
+    schema = pa.schema([lancedb.blob("blob")])
+
+    assert blob_v2_projection_sources(schema, {"blob_alias": col("blob")}) == {
+        "blob_alias": "blob"
+    }
+
+
 def _legacy_v1_table(name):
     db = lancedb.connect("memory:///")
     schema = pa.schema(
@@ -166,6 +179,20 @@ async def test_async_table_to_pandas_descriptions_mode_omits_row_id():
     assert set(descriptor.keys()) == {"kind", "position", "size", "blob_id", "blob_uri"}
 
 
+@pytest.mark.asyncio
+async def test_async_typed_blob_projection_preserves_source_column():
+    db = await lancedb.connect_async("memory:///typed_blob_projection")
+    schema = pa.schema([pa.field("id", pa.int64()), lancedb.blob("blob")])
+    table = await db.create_table("typed_blob_projection", schema=schema)
+    await table.add([{"id": 1, "blob": b"alpha"}])
+
+    hits = await table.query().select({"blob_alias": col("blob")}).to_arrow()
+
+    assert "_lance_row_id" in hits.schema.field("blob_alias").type.names
+    blobs = await table.fetch_blobs("blob", hits)
+    assert blobs.to_pylist() == [b"alpha"]
+
+
 def test_fetch_blobs_round_trip():
     table = _blob_table(
         "round_trip",
@@ -403,6 +430,50 @@ async def test_blob_v2_hybrid_fetch_blobs_async():
     assert {blobs[i].as_py() for i in range(len(blobs))} == {b"alpha", b"beta"}
 
 
+@pytest.mark.asyncio
+async def test_async_hybrid_typed_blob_projection_preserves_source_column():
+    db = await lancedb.connect_async("memory:///hybrid_typed_blob")
+    schema = pa.schema(
+        [
+            pa.field("id", pa.int64()),
+            pa.field("text", pa.utf8()),
+            pa.field("vector", pa.list_(pa.float32(), list_size=2)),
+            lancedb.blob("blob"),
+        ]
+    )
+    table = await db.create_table("hybrid_typed_blob", schema=schema)
+    await table.add(
+        [
+            {
+                "id": 1,
+                "text": "hello alpha",
+                "vector": [1.0, 0.0],
+                "blob": b"alpha",
+            },
+            {
+                "id": 2,
+                "text": "hello beta",
+                "vector": [0.9, 0.1],
+                "blob": b"beta",
+            },
+        ]
+    )
+    await table.create_index("text", config=FTS(with_position=False))
+
+    hits = await (
+        table.query()
+        .nearest_to([1.0, 0.0])
+        .nearest_to_text("hello")
+        .select({"blob_alias": col("blob")})
+        .limit(2)
+        .to_arrow()
+    )
+
+    assert "_lance_row_id" in hits.schema.field("blob_alias").type.names
+    blobs = await table.fetch_blobs("blob", hits)
+    assert {blobs[i].as_py() for i in range(len(blobs))} == {b"alpha", b"beta"}
+
+
 def test_blob_file_seek_read_and_read_range():
     payload = _identifiable_payload(1024)
     table = _blob_table("seek_read", [{"id": 1, "image": payload}])

python/python/tests/test_expr.py

@@ -52,7 +52,7 @@ class TestExprConstruction:
     def test_func(self):
         e = func("lower", col("name"))
         assert isinstance(e, Expr)
-        assert e.to_sql() == "lower(name)"
+        assert e.to_sql() == "lower(`name`)"
 
     def test_func_unknown_raises(self):
         with pytest.raises(Exception):
@@ -115,7 +115,7 @@ class TestExprOperators:
     def test_and_operator(self):
         e = (col("age") > lit(18)) & (col("status") == lit("active"))
         assert isinstance(e, Expr)
-        assert e.to_sql() == "((age > 18) AND (status = 'active'))"
+        assert e.to_sql() == "((age > 18) AND (`status` = 'active'))"
 
     def test_or_operator(self):
         e = (col("a") == lit(1)) | (col("b") == lit(2))
@@ -166,7 +166,7 @@ class TestExprOperators:
     def test_coerce_plain_str(self):
         e = col("name") == "alice"
         assert isinstance(e, Expr)
-        assert e.to_sql() == "(name = 'alice')"
+        assert e.to_sql() == "(`name` = 'alice')"
 
     def test_reflexive_comparisons(self):
         # 10 < col("age") swaps to col("age") > 10
@@ -198,85 +198,85 @@ class TestExprBytesLiteral:
 
     def test_bytes_equality_expr_sql(self):
         e = col("data") == lit(b"\xca\xfe")
-        assert e.to_sql() == "(data = X'CAFE')"
+        assert e.to_sql() == "(`data` = X'CAFE')"
 
     def test_bytes_ne_expr_sql(self):
         e = col("data") != lit(b"\xff")
-        assert e.to_sql() == "(data <> X'FF')"
+        assert e.to_sql() == "(`data` <> X'FF')"
 
     def test_bytes_compound_expr_sql(self):
         e = (col("data") == lit(b"\x01")) & (col("id") > lit(5))
-        assert e.to_sql() == "((data = X'01') AND (id > 5))"
+        assert e.to_sql() == "((`data` = X'01') AND (id > 5))"
 
     def test_bytes_in_function_call(self):
         # Regression test: binary literals inside scalar function calls
         # used to fail because DataFusion's unparser does not support Binary
         # scalars.  Now handled via a placeholder-substitution rewrite.
         e = func("contains", col("data"), lit(b"\xff"))
-        assert e.to_sql() == "contains(data, X'FF')"
+        assert e.to_sql() == "contains(`data`, X'FF')"
 
     def test_bytes_in_not(self):
         e = ~(col("data") == lit(b"\xff"))
-        assert e.to_sql() == "NOT (data = X'FF')"
+        assert e.to_sql() == "NOT (`data` = X'FF')"
 
 
 class TestExprStringMethods:
     def test_lower(self):
         e = col("name").lower()
         assert isinstance(e, Expr)
-        assert e.to_sql() == "lower(name)"
+        assert e.to_sql() == "lower(`name`)"
 
     def test_upper(self):
         e = col("name").upper()
         assert isinstance(e, Expr)
-        assert e.to_sql() == "upper(name)"
+        assert e.to_sql() == "upper(`name`)"
 
     def test_contains(self):
         e = col("text").contains(lit("hello"))
         assert isinstance(e, Expr)
-        assert e.to_sql() == "contains(text, 'hello')"
+        assert e.to_sql() == "contains(`text`, 'hello')"
 
     def test_contains_with_str_coerce(self):
         e = col("text").contains("hello")
         assert isinstance(e, Expr)
-        assert e.to_sql() == "contains(text, 'hello')"
+        assert e.to_sql() == "contains(`text`, 'hello')"
 
     def test_chained_lower_eq(self):
         e = col("name").lower() == lit("alice")
         assert isinstance(e, Expr)
-        assert e.to_sql() == "(lower(name) = 'alice')"
+        assert e.to_sql() == "(lower(`name`) = 'alice')"
 
 
 class TestExprCast:
     def test_cast_string(self):
         e = col("id").cast("string")
         assert isinstance(e, Expr)
-        assert e.to_sql() == "CAST(id AS VARCHAR)"
+        assert e.to_sql() == "arrow_cast(id, 'Utf8')"
 
     def test_cast_int32(self):
         e = col("score").cast("int32")
         assert isinstance(e, Expr)
-        assert e.to_sql() == "CAST(score AS INTEGER)"
+        assert e.to_sql() == "arrow_cast(score, 'Int32')"
 
     def test_cast_float64(self):
         e = col("val").cast("float64")
         assert isinstance(e, Expr)
-        assert e.to_sql() == "CAST(val AS DOUBLE)"
+        assert e.to_sql() == "arrow_cast(val, 'Float64')"
 
     def test_cast_pyarrow_type(self):
         e = col("score").cast(pa.int32())
         assert isinstance(e, Expr)
-        assert e.to_sql() == "CAST(score AS INTEGER)"
+        assert e.to_sql() == "arrow_cast(score, 'Int32')"
 
     def test_cast_pyarrow_float64(self):
         e = col("val").cast(pa.float64())
         assert isinstance(e, Expr)
-        assert e.to_sql() == "CAST(val AS DOUBLE)"
+        assert e.to_sql() == "arrow_cast(val, 'Float64')"
 
     def test_cast_pyarrow_string(self):
         e = col("id").cast(pa.string())
         assert isinstance(e, Expr)
-        assert e.to_sql() == "CAST(id AS VARCHAR)"
+        assert e.to_sql() == "arrow_cast(id, 'Utf8')"
 
     def test_cast_pyarrow_and_string_equivalent(self):
         # pa.int32() and "int32" should produce equivalent SQL
@@ -597,14 +597,14 @@ class TestExprIsin:
     def test_isin_strs(self):
         assert (
             col("status").isin(["active", "pending"]).to_sql()
-            == "status IN ('active', 'pending')"
+            == "`status` IN ('active', 'pending')"
         )
 
     def test_isin_coerces_and_mixes(self):
         assert col("id").isin([lit(1), 2]).to_sql() == "id IN (1, 2)"
 
     def test_isin_empty(self):
-        assert col("id").isin([]).to_sql() == "id IN ()"
+        assert col("id").isin([]).to_sql() == "false"
 
     def test_isin_filter(self, simple_table):
         result = simple_table.search().where(col("id").isin([1, 3, 5])).to_arrow()

python/python/tests/test_query.py

@@ -675,6 +675,21 @@ def test_distance_range(table: lancedb.table.Table):
     assert res["_distance"].to_pylist() == [min_dist, max_dist]
 
 
+@pytest.mark.parametrize("expression", ["1 - _distance", "1.0 - _distance"])
+def test_select_arithmetic_with_distance(table, expression):
+    result = (
+        table.search([10, 10])
+        .select({"similarity": expression, "_distance": "_distance"})
+        .distance_type("cosine")
+        .to_arrow()
+    )
+
+    assert result.schema.field("similarity").type == pa.float32()
+    assert result["similarity"].to_pylist() == pytest.approx(
+        [1 - distance for distance in result["_distance"].to_pylist()]
+    )
+
+
 @pytest.mark.asyncio
 async def test_distance_range_async(table_async: AsyncTable):
     q = [0, 0]
@@ -1908,21 +1923,6 @@ def test_take_queries(tmp_path):
         17,
     ]
 
-    # Duplicate offsets are occurrences, not set members. Ordering is unspecified.
-    assert sorted(table.take_offsets([5, 2, 5, 17]).to_pandas()["idx"].to_list()) == [
-        2,
-        5,
-        5,
-        17,
-    ]
-
-    # Converting a take builder to its serializable query representation must
-    # retain occurrence metadata and execute with the same multiplicity.
-    query = table.take_offsets([5, 2, 5, 17]).select(["idx"]).to_query_object()
-    assert query.take_offsets == [5, 2, 5, 17]
-    converted = table._execute_query(query).read_all()
-    assert sorted(converted["idx"].to_pylist()) == [2, 5, 5, 17]
-
     # Take by row id
     assert list(
         sorted(table.take_row_ids([5, 2, 17]).to_pandas()["idx"].to_list())

python/python/tests/test_remote_db.py

@@ -479,49 +479,24 @@ def test_remote_permutation_is_picklable():
                 match = re.search(
                     r"_rowoffset\s+in\s+\((.*?)\)", body["filter"], re.IGNORECASE
                 )
-                offsets = list(
-                    dict.fromkeys(int(o.strip()) for o in match.group(1).split(","))
-                )
+                offsets = [int(o.strip()) for o in match.group(1).split(",")]
             else:
                 offsets = list(range(len(rows)))
-            columns = body.get("columns") or ["a"]
-            table = pa.table(
-                {
-                    column: (
-                        [rows[offset] for offset in offsets]
-                        if column == "a"
-                        else offsets
-                    )
-                    for column in columns
-                }
-            )
+            table = pa.table({"a": [rows[offset] for offset in offsets]})
 
             request.send_response(200)
             request.send_header("Content-Type", "application/vnd.apache.arrow.file")
             request.end_headers()
             with pa.ipc.new_file(request.wfile, schema=table.schema) as writer:
-                writer.write_table(table, max_chunksize=2)
+                writer.write_table(table)
         else:
             request.send_response(404)
             request.end_headers()
 
     with mock_lancedb_connection(handler) as db:
-        table = db.open_table("test")
-        assert table.take_offsets([0, 2, 0, 4]).to_list() == [
-            {"a": 0},
-            {"a": 0},
-            {"a": 2},
-            {"a": 4},
-        ]
-
-        permutation = Permutation.identity(table)
+        permutation = Permutation.identity(db.open_table("test"))
         restored = pickle.loads(pickle.dumps(permutation))
-        assert restored.__getitems__([0, 2, 0, 4]) == [
-            {"a": 0},
-            {"a": 2},
-            {"a": 0},
-            {"a": 4},
-        ]
+        assert restored.__getitems__([0, 2, 4]) == [{"a": 0}, {"a": 2}, {"a": 4}]
 
 
 def test_create_table_exist_ok():

python/python/tests/test_table.py

@@ -11,6 +11,7 @@ import warnings
 import weakref
 from concurrent.futures import ThreadPoolExecutor
 from datetime import date, datetime, timedelta
+from decimal import Decimal
 from time import sleep
 from typing import List
 from unittest.mock import patch
@@ -336,6 +337,21 @@ async def test_update_async(mem_db_async: AsyncConnection):
     assert await table.count_rows("id == 10") == 1
 
 
+@pytest.mark.asyncio
+async def test_update_expr_filter_literals_async(mem_db_async: AsyncConnection):
+    values = ["5", "4.66e-84", "it's"]
+    table = await mem_db_async.create_table(
+        "update_expr_literals",
+        data=[{"field": value, "result": "original"} for value in values],
+    )
+
+    for value in values:
+        update_res = await table.update({"result": value}, where=col("field") == value)
+        assert update_res.rows_updated == 1
+
+    assert (await table.to_arrow())["result"].to_pylist() == values
+
+
 def test_create_table(mem_db: DBConnection):
     schema = pa.schema(
         {
@@ -2343,6 +2359,148 @@ def test_update(mem_db: DBConnection):
     assert np.allclose(v, np.array([[1.2, 1.9], [1.1, 1.1]]))
 
 
+def test_update_expr_filter_literals(mem_db: DBConnection):
+    values = ["5", "4.66e-84", "it's"]
+    table = mem_db.create_table(
+        "update_expr_literals",
+        data=[{"field": value, "result": "original"} for value in values],
+    )
+
+    for value in values:
+        update_res = table.update(where=col("field") == value, values={"result": value})
+        assert update_res.rows_updated == 1
+
+    assert table.to_arrow()["result"].to_pylist() == values
+
+
+def test_update_expr_filter_preserves_typed_semantics(mem_db: DBConnection):
+    low = Decimal("1.234567890123456789")
+    high = Decimal("1.234567890123456790")
+    decimal_schema = pa.schema(
+        [("val", pa.decimal128(19, 18)), ("result", pa.string())]
+    )
+    decimal_table = mem_db.create_table(
+        "update_expr_decimal",
+        pa.table(
+            {"val": [low, high], "result": ["old", "old"]},
+            schema=decimal_schema,
+        ),
+    )
+    predicate = col("val") < lit(high)
+    assert decimal_table.search().where(predicate).to_arrow().num_rows == 1
+    result = decimal_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 1
+
+    keyword_table = mem_db.create_table(
+        "update_expr_keyword", [{"null": 1, "result": "old"}]
+    )
+    predicate = col("null") == 1
+    assert keyword_table.search().where(predicate).to_arrow().num_rows == 1
+    result = keyword_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 1
+
+    empty_in_table = mem_db.create_table(
+        "update_expr_empty_in", [{"id": 1, "result": "old"}]
+    )
+    predicate = col("id").isin([])
+    assert empty_in_table.search().where(predicate).to_arrow().num_rows == 0
+    result = empty_in_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 0
+
+    marker = "__lancedb_binary_placeholder_0__"
+    binary_schema = pa.schema(
+        [("payload", pa.binary()), ("text", pa.string()), ("result", pa.string())]
+    )
+    binary_table = mem_db.create_table(
+        "update_expr_binary",
+        pa.table(
+            {
+                "payload": [b"\x01", b"\x02"],
+                "text": ["other", marker],
+                "result": ["old", "old"],
+            },
+            schema=binary_schema,
+        ),
+    )
+    predicate = (col("payload") == lit(b"\x01")) | (col("text") == marker)
+    assert binary_table.search().where(predicate).to_arrow().num_rows == 2
+    result = binary_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 2
+
+    nonfinite_table = mem_db.create_table(
+        "update_expr_nonfinite",
+        [{"x": 1.0, "result": "old"}, {"x": 2.0, "result": "old"}],
+    )
+    predicate = col("x") < float("inf")
+    assert nonfinite_table.search().where(predicate).to_arrow().num_rows == 2
+    result = nonfinite_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 2
+
+    float16_table = mem_db.create_table(
+        "update_expr_float16",
+        [{"x": 1.0, "result": "old"}, {"x": 3.0, "result": "old"}],
+    )
+    predicate = col("x").cast(pa.float16()) < 2.0
+    assert float16_table.search().where(predicate).to_arrow().num_rows == 1
+    result = float16_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 1
+
+    string_cast_table = mem_db.create_table(
+        "update_expr_string_cast",
+        [{"x": 1, "result": "old"}, {"x": 2, "result": "old"}],
+    )
+    predicate = col("x").cast("string") == "1"
+    assert string_cast_table.search().where(predicate).to_arrow().num_rows == 1
+    result = string_cast_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 1
+
+    quoted_identifier_schema = pa.schema(
+        [("payload", pa.binary()), ("odd'name", pa.int64()), ("result", pa.string())]
+    )
+    quoted_identifier_table = mem_db.create_table(
+        "update_expr_quoted_identifier",
+        pa.table(
+            {"payload": [b"\x01"], "odd'name": [1], "result": ["old"]},
+            schema=quoted_identifier_schema,
+        ),
+    )
+    predicate = (col("payload") == lit(b"\x01")) & (col("odd'name") == 1)
+    assert quoted_identifier_table.search().where(predicate).to_arrow().num_rows == 1
+    result = quoted_identifier_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 1
+
+    decimal256_schema = pa.schema(
+        [("val", pa.decimal256(40, 2)), ("result", pa.string())]
+    )
+    decimal256_table = mem_db.create_table(
+        "update_expr_decimal256",
+        pa.table(
+            {
+                "val": [Decimal("1.00"), Decimal("3.00")],
+                "result": ["old", "old"],
+            },
+            schema=decimal256_schema,
+        ),
+    )
+    predicate = col("val") < lit(Decimal("2.00")).cast(pa.decimal256(40, 2))
+    assert decimal256_table.search().where(predicate).to_arrow().num_rows == 1
+    result = decimal256_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 1
+
+    binary_empty_table = mem_db.create_table(
+        "update_expr_binary_empty",
+        pa.table(
+            {"payload": [b"\x01", b"\x02"], "result": ["old", "old"]},
+            schema=pa.schema([("payload", pa.binary()), ("result", pa.string())]),
+        ),
+    )
+    predicate = (col("payload") == lit(b"\x01")).isin([])
+    assert binary_empty_table.search().where(predicate).to_arrow().num_rows == 0
+    assert predicate.to_sql() == "false"
+    result = binary_empty_table.update(where=predicate, values={"result": "new"})
+    assert result.rows_updated == 0
+
+
 def test_update_with_arrow_scalar(mem_db: DBConnection):
     schema = pa.schema({"id": pa.int64(), "vector": pa.list_(pa.float32(), 4)})
     table = mem_db.create_table("my_table", schema=schema)

python/src/expr.rs

@@ -130,6 +130,14 @@ impl PyExpr {
 
     // ── utilities ────────────────────────────────────────────────────────────
 
+    /// Return the referenced column name for a bare column expression.
+    fn column_name(&self) -> Option<String> {
+        match &self.0 {
+            DfExpr::Column(column) if column.relation.is_none() => Some(column.name.clone()),
+            _ => None,
+        }
+    }
+
     /// Render the expression as a SQL string (useful for debugging).
     fn to_sql(&self) -> PyResult<String> {
         lancedb::expr::expr_to_sql_string(&self.0).map_err(|e| PyValueError::new_err(e.to_string()))

python/src/query.rs

@@ -1,6 +1,7 @@
 // SPDX-License-Identifier: Apache-2.0
 // SPDX-FileCopyrightText: Copyright The LanceDB Authors
 
+use std::collections::HashMap;
 use std::sync::Arc;
 use std::time::Duration;
 
@@ -322,10 +323,10 @@ impl<'py> IntoPyObject<'py> for PyQueryVectors {
 pub struct PyQueryRequest {
     pub limit: Option<usize>,
     pub offset: Option<usize>,
-    pub take_offsets: Option<Vec<u64>>,
     pub filter: Option<PyQueryFilter>,
     pub full_text_search: Option<PyLanceDB<FtsQuery>>,
     pub select: PySelect,
+    pub select_source_columns: Option<HashMap<String, String>>,
     pub fast_search: Option<bool>,
     pub with_row_id: Option<bool>,
     pub use_lsm: Option<bool>,
@@ -352,11 +353,11 @@ impl From<AnyQuery> for PyQueryRequest {
             AnyQuery::Query(query_request) => Self {
                 limit: query_request.limit,
                 offset: query_request.offset,
-                take_offsets: query_request.take_offsets,
                 filter: query_request.filter.map(PyQueryFilter),
                 full_text_search: query_request
                     .full_text_search
                     .map(|fts| PyLanceDB(fts.query)),
+                select_source_columns: PySelect::source_columns(&query_request.select),
                 select: PySelect(query_request.select),
                 fast_search: Some(query_request.fast_search),
                 with_row_id: Some(query_request.with_row_id),
@@ -380,9 +381,9 @@ impl From<AnyQuery> for PyQueryRequest {
             AnyQuery::VectorQuery(vector_query) => Self {
                 limit: vector_query.base.limit,
                 offset: vector_query.base.offset,
-                take_offsets: vector_query.base.take_offsets,
                 filter: vector_query.base.filter.map(PyQueryFilter),
                 full_text_search: None,
+                select_source_columns: PySelect::source_columns(&vector_query.base.select),
                 select: PySelect(vector_query.base.select),
                 fast_search: Some(vector_query.base.fast_search),
                 with_row_id: Some(vector_query.base.with_row_id),
@@ -415,6 +416,25 @@ impl From<AnyQuery> for PyQueryRequest {
 #[derive(Clone)]
 pub struct PySelect(Select);
 
+impl PySelect {
+    fn source_columns(select: &Select) -> Option<HashMap<String, String>> {
+        match select {
+            Select::Expr(pairs) => Some(
+                pairs
+                    .iter()
+                    .filter_map(|(output, expr)| match expr {
+                        lancedb::expr::DfExpr::Column(column) if column.relation.is_none() => {
+                            Some((output.clone(), column.name.clone()))
+                        }
+                        _ => None,
+                    })
+                    .collect(),
+            ),
+            _ => None,
+        }
+    }
+}
+
 impl<'py> IntoPyObject<'py> for PySelect {
     type Target = PyAny;
     type Output = Bound<'py, Self::Target>;

rust/lancedb/Cargo.toml

@@ -1,6 +1,6 @@
 [package]
 name = "lancedb"
-version = "0.38.0-beta.10"
+version = "0.38.0-beta.11"
 edition.workspace = true
 description = "LanceDB: A serverless, low-latency vector database for AI applications"
 license.workspace = true

rust/lancedb/src/dataloader/permutation/reader.rs

@@ -31,7 +31,7 @@ use lance::io::RecordBatchStream;
 use lance_arrow::RecordBatchExt;
 use lance_core::ROW_ID;
 use lance_core::error::LanceOptionExt;
-use std::collections::{HashMap, HashSet};
+use std::collections::HashMap;
 use std::sync::Arc;
 
 /// Reads a permutation of a source table based on row IDs stored in a separate table
@@ -234,14 +234,7 @@ impl PermutationReader {
             .expect_ok()?
             .values();
 
-        let mut unique_row_ids = HashSet::with_capacity(num_rows);
-        let in_list: Vec<Expr> = row_ids
-            .iter()
-            .copied()
-            .filter(|row_id| unique_row_ids.insert(*row_id))
-            .map(lit)
-            .collect();
-        let num_unique_row_ids = unique_row_ids.len();
+        let in_list: Vec<Expr> = row_ids.iter().map(|id| lit(*id)).collect();
 
         let base_query = QueryRequest {
             filter: Some(QueryFilter::Datafusion(col(ROW_ID).in_list(in_list, false))),
@@ -254,7 +247,7 @@ impl PermutationReader {
             .query(
                 &AnyQuery::Query(base_query),
                 QueryExecutionOptions {
-                    max_batch_length: num_unique_row_ids as u32,
+                    max_batch_length: num_rows as u32,
                     ..Default::default()
                 },
             )
@@ -269,9 +262,9 @@ impl PermutationReader {
             });
         }
 
-        if batches.iter().map(|b| b.num_rows()).sum::<usize>() != num_unique_row_ids {
+        if batches.iter().map(|b| b.num_rows()).sum::<usize>() != num_rows {
             return Err(Error::InvalidInput {
-                message: "Base table returned a different number of rows than the number of unique row IDs"
+                message: "Base table returned different number of rows than the number of row IDs"
                     .to_string(),
             });
         }
@@ -511,7 +504,6 @@ impl PermutationReader {
             let table = Table::from(self.base_table.clone());
             let batches = table
                 .take_offsets(offsets.to_vec())
-                .preserve_order()
                 .select(selection.clone())
                 .execute()
                 .await?
@@ -811,10 +803,10 @@ mod tests {
         .unwrap();
 
         // Take offsets in reverse order and verify returned rows match that order
-        let offsets = vec![5, 3, 5, 1, 0];
+        let offsets = vec![5, 3, 1, 0];
         let batch = reader.take_offsets(&offsets, Select::All).await.unwrap();
 
-        assert_eq!(batch.num_rows(), 5);
+        assert_eq!(batch.num_rows(), 4);
 
         let idx_values = batch
             .column(0)
@@ -828,52 +820,6 @@ mod tests {
         assert_eq!(idx_values, expected);
     }
 
-    #[tokio::test]
-    async fn test_take_offsets_preserves_repeated_rows_in_permutation() {
-        let base_table = lance_datagen::gen_batch()
-            .col("idx", lance_datagen::array::step::<Int32Type>())
-            .into_mem_table("tbl", RowCount::from(5), BatchCount::from(1))
-            .await;
-        let base_row_ids = collect_column::<UInt64Type>(&base_table, "_rowid").await;
-        let permutation_row_ids = vec![
-            base_row_ids[3],
-            base_row_ids[1],
-            base_row_ids[3],
-            base_row_ids[2],
-        ];
-        let permutation_batch = RecordBatch::try_new(
-            Arc::new(Schema::new(vec![
-                Field::new("row_id", DataType::UInt64, false),
-                Field::new(SPLIT_ID_COLUMN, DataType::UInt64, false),
-            ])),
-            vec![
-                Arc::new(UInt64Array::from(permutation_row_ids)),
-                Arc::new(UInt64Array::from(vec![0; 4])),
-            ],
-        )
-        .unwrap();
-        let permutation_table = virtual_table("row_ids", &permutation_batch).await;
-        let reader = PermutationReader::try_from_tables(
-            base_table.base_table().clone(),
-            permutation_table.base_table().clone(),
-            0,
-        )
-        .await
-        .unwrap();
-
-        let batch = reader
-            .take_offsets(&[0, 1, 2, 3], Select::All)
-            .await
-            .unwrap();
-        let idx_values = batch
-            .column(0)
-            .as_primitive::<Int32Type>()
-            .values()
-            .to_vec();
-
-        assert_eq!(idx_values, vec![3, 1, 3, 2]);
-    }
-
     #[tokio::test]
     async fn test_take_offsets_with_column_selection() {
         let (base_table, row_ids_table, row_ids) = setup_permutation_tables(10).await;
@@ -937,17 +883,17 @@ mod tests {
             .unwrap();
 
         // With no permutation table, take_offsets uses the base table directly
-        let offsets = vec![0, 2, 0, 4, 6];
+        let offsets = vec![0, 2, 4, 6];
         let batch = reader.take_offsets(&offsets, Select::All).await.unwrap();
 
-        assert_eq!(batch.num_rows(), 5);
+        assert_eq!(batch.num_rows(), 4);
 
         let idx_values = batch
             .column(0)
             .as_primitive::<Int32Type>()
             .values()
             .to_vec();
-        assert_eq!(idx_values, vec![0, 2, 0, 4, 6]);
+        assert_eq!(idx_values, vec![0, 2, 4, 6]);
     }
 
     #[tokio::test]

rust/lancedb/src/expr.rs

@@ -157,7 +157,7 @@ mod tests {
         use datafusion_common::ScalarValue;
         let expr = col("data").eq(lit(ScalarValue::Binary(Some(vec![0xca, 0xfe]))));
         let sql = expr_to_sql_string(&expr).unwrap();
-        assert_eq!(sql, "(data = X'CAFE')");
+        assert_eq!(sql, "(`data` = X'CAFE')");
     }
 
     #[test]
@@ -167,7 +167,7 @@ mod tests {
         let int_expr = col("id").gt(lit(5i64));
         let combined = bin_expr.and(int_expr);
         let sql = expr_to_sql_string(&combined).unwrap();
-        assert_eq!(sql, "((data = X'01') AND (id > 5))");
+        assert_eq!(sql, "((`data` = X'01') AND (id > 5))");
     }
 
     #[test]
@@ -185,7 +185,7 @@ mod tests {
         // serialized correctly (regression test for placeholder rewrite path).
         let expr = contains(col("data"), lit(ScalarValue::Binary(Some(vec![0xff]))));
         let sql = expr_to_sql_string(&expr).unwrap();
-        assert_eq!(sql, "contains(data, X'FF')");
+        assert_eq!(sql, "contains(`data`, X'FF')");
     }
 
     #[test]
@@ -196,7 +196,7 @@ mod tests {
             .eq(lit(ScalarValue::Binary(Some(vec![0xab, 0xcd]))))
             .not();
         let sql = expr_to_sql_string(&expr).unwrap();
-        assert_eq!(sql, "NOT (data = X'ABCD')");
+        assert_eq!(sql, "NOT (`data` = X'ABCD')");
     }
 
     #[test]
@@ -206,6 +206,122 @@ mod tests {
         assert!(sql.contains("IN"), "expected IN in: {}", sql);
     }
 
+    #[test]
+    fn test_empty_is_in() {
+        let expr = is_in(col("id"), vec![]);
+        assert_eq!(expr_to_sql_string(&expr).unwrap(), "false");
+    }
+
+    #[test]
+    fn test_empty_is_in_discards_binary_children() {
+        use datafusion_common::ScalarValue;
+
+        let expr = is_in(
+            col("payload").eq(lit(ScalarValue::Binary(Some(vec![0x01])))),
+            vec![],
+        );
+        assert_eq!(expr_to_sql_string(&expr).unwrap(), "false");
+    }
+
+    #[test]
+    fn test_keyword_identifier() {
+        let expr = col("null").eq(lit(1i64));
+        assert_eq!(expr_to_sql_string(&expr).unwrap(), "(`null` = 1)");
+    }
+
+    #[test]
+    fn test_decimal_literal_preserves_type() {
+        use datafusion_common::ScalarValue;
+
+        let expr = col("val").lt(lit(ScalarValue::Decimal128(
+            Some(1_234_567_890_123_456_790),
+            19,
+            18,
+        )));
+        let sql = expr_to_sql_string(&expr).unwrap();
+        assert_eq!(
+            sql,
+            "(val < arrow_cast('1.234567890123456790', 'Decimal128(19, 18)'))"
+        );
+    }
+
+    #[test]
+    fn test_non_finite_float_literal_preserves_type() {
+        let expr = col("x").lt(lit(f64::INFINITY));
+        assert_eq!(
+            expr_to_sql_string(&expr).unwrap(),
+            "(x < arrow_cast('inf', 'Float64'))"
+        );
+    }
+
+    #[test]
+    fn test_cast_uses_arrow_type_name() {
+        let string = expr_cast(col("x"), DataType::Utf8);
+        assert_eq!(
+            expr_to_sql_string(&string).unwrap(),
+            "arrow_cast(x, 'Utf8')"
+        );
+
+        let int32 = expr_cast(col("x"), DataType::Int32);
+        assert_eq!(
+            expr_to_sql_string(&int32).unwrap(),
+            "arrow_cast(x, 'Int32')"
+        );
+
+        let expr = expr_cast(col("x"), DataType::Float16).lt(lit(2.0));
+        assert_eq!(
+            expr_to_sql_string(&expr).unwrap(),
+            "(arrow_cast(x, 'Float16') < 2.0)"
+        );
+
+        let decimal = expr_cast(lit("2.00"), DataType::Decimal256(40, 2));
+        assert_eq!(
+            expr_to_sql_string(&decimal).unwrap(),
+            "arrow_cast('2.00', 'Decimal256(40, 2)')"
+        );
+    }
+
+    #[test]
+    fn test_binary_placeholder_does_not_rewrite_user_string() {
+        use datafusion_common::ScalarValue;
+
+        let marker = "__lancedb_binary_placeholder_0__";
+        let expr = col("payload")
+            .eq(lit(ScalarValue::Binary(Some(vec![0x01]))))
+            .or(col("text").eq(lit(marker)));
+        assert_eq!(
+            expr_to_sql_string(&expr).unwrap(),
+            "((payload = X'01') OR (`text` = '__lancedb_binary_placeholder_0__'))"
+        );
+    }
+
+    #[test]
+    fn test_binary_binding_skips_quoted_identifiers() {
+        use datafusion_common::ScalarValue;
+
+        let expr = col("payload")
+            .eq(lit(ScalarValue::Binary(Some(vec![0x01]))))
+            .and(col("odd'name").eq(lit(1i64)))
+            .and(col("odd`'name").eq(lit(2i64)));
+        assert_eq!(
+            expr_to_sql_string(&expr).unwrap(),
+            "(((payload = X'01') AND (`odd'name` = 1)) AND (`odd``'name` = 2))"
+        );
+    }
+
+    #[test]
+    fn test_binary_placeholder_collision_search_is_linear() {
+        use datafusion_common::ScalarValue;
+
+        let collision_shaped = format!("__lancedb_binary_placeholder_0__{}", "_".repeat(64_000));
+        let expr = col("payload")
+            .eq(lit(ScalarValue::Binary(Some(vec![0x01]))))
+            .and(col("text").eq(lit(collision_shaped.clone())));
+        let sql = expr_to_sql_string(&expr).unwrap();
+        assert!(sql.contains("X'01'"));
+        assert!(sql.contains(&format!("'{collision_shaped}'")));
+    }
+
     #[test]
     fn test_multiple_binary_literals() {
         use datafusion_common::ScalarValue;

rust/lancedb/src/expr/sql.rs

@@ -1,13 +1,24 @@
 // SPDX-License-Identifier: Apache-2.0
 // SPDX-FileCopyrightText: Copyright The LanceDB Authors
 
-use std::any::TypeId;
+use std::{
+    any::TypeId,
+    collections::{HashMap, HashSet},
+};
 
+use arrow_array::types::{
+    Decimal32Type, Decimal64Type, Decimal128Type, Decimal256Type, DecimalType,
+};
+use arrow_schema::DataType;
 use datafusion_common::ScalarValue;
 use datafusion_common::tree_node::{Transformed, TreeNode, TreeNodeRecursion};
 use datafusion_expr::Expr;
+use datafusion_functions::core::expr_fn::{
+    arrow_cast as datafusion_arrow_cast, arrow_try_cast as datafusion_arrow_try_cast,
+};
 use datafusion_sql::sqlparser::{
     dialect::{Dialect as SqlParserDialect, GenericDialect},
+    keywords::ALL_KEYWORDS,
     tokenizer::{Token, Tokenizer},
 };
 use datafusion_sql::unparser::{self, dialect::Dialect as UnparserDialect};
@@ -27,11 +38,13 @@ struct LanceSqlDialect;
 
 impl UnparserDialect for LanceSqlDialect {
     fn identifier_quote_style(&self, identifier: &str) -> Option<char> {
-        let needs_quote = identifier.chars().any(|c| c.is_ascii_uppercase())
-            || !identifier
-                .chars()
-                .enumerate()
-                .all(|(i, c)| c == '_' || c.is_ascii_alphabetic() || (i > 0 && c.is_ascii_digit()));
+        let identifier_upper = identifier.to_ascii_uppercase();
+        let needs_quote =
+            (identifier_upper != "ID" && ALL_KEYWORDS.contains(&identifier_upper.as_str()))
+                || identifier.chars().any(|c| c.is_ascii_uppercase())
+                || !identifier.chars().enumerate().all(|(i, c)| {
+                    c == '_' || c.is_ascii_alphabetic() || (i > 0 && c.is_ascii_digit())
+                });
         if needs_quote { Some('`') } else { None }
     }
 }
@@ -100,24 +113,128 @@ fn bytes_to_hex_sql(bytes: &[u8]) -> String {
     format!("X'{hex}'")
 }
 
-/// Returns true if *expr* contains a `Binary` or `LargeBinary` scalar literal
-/// anywhere in its subtree.  DataFusion's SQL unparser cannot serialize those
-/// variants, so we route such expressions through a placeholder-substitution
-/// path that emits SQL `X'...'` byte-string literals.
-fn has_binary_literal(expr: &Expr) -> bool {
-    let mut found = false;
+fn string_literals(expr: &Expr) -> HashSet<String> {
+    let mut literals = HashSet::new();
     let _ = expr.apply(&mut |e: &Expr| {
-        if matches!(
-            e,
-            Expr::Literal(ScalarValue::Binary(_) | ScalarValue::LargeBinary(_), _)
-        ) {
-            found = true;
-            Ok(TreeNodeRecursion::Stop)
-        } else {
-            Ok(TreeNodeRecursion::Continue)
+        if let Expr::Literal(
+            ScalarValue::Utf8(Some(value))
+            | ScalarValue::LargeUtf8(Some(value))
+            | ScalarValue::Utf8View(Some(value)),
+            _,
+        ) = e
+        {
+            literals.insert(value.clone());
         }
+        Ok(TreeNodeRecursion::Continue)
     });
-    found
+    literals
+}
+
+fn typed_string_literal(value: String, data_type: DataType) -> Expr {
+    datafusion_arrow_cast(
+        Expr::Literal(ScalarValue::Utf8(Some(value)), None),
+        Expr::Literal(ScalarValue::Utf8(Some(data_type.to_string())), None),
+    )
+}
+
+fn next_binary_placeholder(user_strings: &HashSet<String>, next_id: &mut usize) -> String {
+    loop {
+        let placeholder = format!("{BINARY_PLACEHOLDER_PREFIX}{}__", *next_id);
+        *next_id += 1;
+        if !user_strings.contains(&placeholder) {
+            return placeholder;
+        }
+    }
+}
+
+fn bind_binary_literals(
+    sql: &str,
+    mut bindings: HashMap<String, Vec<u8>>,
+) -> crate::Result<String> {
+    let bytes = sql.as_bytes();
+    let mut output = Vec::with_capacity(bytes.len());
+    let mut index = 0;
+
+    // Walk SQL string tokens once. Placeholders are plain, unescaped string
+    // literals, so this remains linear even when user strings are large or
+    // deliberately resemble the placeholder prefix.
+    while index < bytes.len() {
+        if bytes[index] == b'`' {
+            let identifier_start = index;
+            index += 1;
+            let mut identifier_end = None;
+            while index < bytes.len() {
+                if bytes[index] == b'`' {
+                    if index + 1 < bytes.len() && bytes[index + 1] == b'`' {
+                        index += 2;
+                    } else {
+                        index += 1;
+                        identifier_end = Some(index);
+                        break;
+                    }
+                } else {
+                    index += 1;
+                }
+            }
+
+            let Some(identifier_end) = identifier_end else {
+                return Err(crate::Error::InvalidInput {
+                    message: "unterminated identifier while binding binary literal".to_string(),
+                });
+            };
+            output.extend_from_slice(&bytes[identifier_start..identifier_end]);
+            continue;
+        }
+
+        if bytes[index] != b'\'' {
+            output.push(bytes[index]);
+            index += 1;
+            continue;
+        }
+
+        let literal_start = index;
+        index += 1;
+        let content_start = index;
+        let mut escaped = false;
+        let mut content_end = None;
+        while index < bytes.len() {
+            if bytes[index] == b'\'' {
+                if index + 1 < bytes.len() && bytes[index + 1] == b'\'' {
+                    escaped = true;
+                    index += 2;
+                } else {
+                    content_end = Some(index);
+                    index += 1;
+                    break;
+                }
+            } else {
+                index += 1;
+            }
+        }
+
+        let Some(content_end) = content_end else {
+            return Err(crate::Error::InvalidInput {
+                message: "unterminated string while binding binary literal".to_string(),
+            });
+        };
+
+        let placeholder = &sql[content_start..content_end];
+        if !escaped && let Some(value) = bindings.remove(placeholder) {
+            output.extend_from_slice(bytes_to_hex_sql(&value).as_bytes());
+        } else {
+            output.extend_from_slice(&bytes[literal_start..index]);
+        }
+    }
+
+    if !bindings.is_empty() {
+        return Err(crate::Error::InvalidInput {
+            message: "failed to bind binary literal while serializing expression".to_string(),
+        });
+    }
+
+    String::from_utf8(output).map_err(|e| crate::Error::InvalidInput {
+        message: format!("failed to bind binary literal: {e}"),
+    })
 }
 
 fn run_unparser(expr: &Expr) -> crate::Result<String> {
@@ -130,25 +247,37 @@ fn run_unparser(expr: &Expr) -> crate::Result<String> {
 }
 
 pub fn expr_to_sql_string(expr: &Expr) -> crate::Result<String> {
-    // Fast path: no binary literals — DataFusion's unparser handles everything.
-    if !has_binary_literal(expr) {
-        return run_unparser(expr);
-    }
-
-    // Slow path: DataFusion's unparser cannot serialize `Binary`/`LargeBinary`
-    // scalars, so we rewrite each one to a unique string-literal placeholder,
-    // let the unparser do the rest of the work, then substitute the SQL
-    // `X'...'` byte-string literal back in.  This keeps the operator/function
-    // serialization logic centralized in DataFusion and works for every
-    // expression node type the unparser supports.
-    let mut bindings: Vec<Vec<u8>> = Vec::new();
+    // DataFusion's unparser needs a few adaptations before its SQL can be
+    // reparsed by Lance without changing the typed expression's semantics:
+    //
+    // * decimal literals need an explicit cast to preserve precision and scale;
+    // * casts need exact Arrow type names rather than SQL type aliases;
+    // * an empty IN list is valid in DataFusion but invalid SQL;
+    // * binary literals are unsupported by the unparser and need placeholders.
+    // Eliminate empty membership expressions before visiting their children.
+    // Otherwise a discarded binary child could leave behind a stale binding.
     let rewritten = expr
         .clone()
+        .transform(|e: Expr| match e {
+            Expr::InList(in_list) if in_list.list.is_empty() => Ok(Transformed::yes(
+                Expr::Literal(ScalarValue::Boolean(Some(in_list.negated)), None),
+            )),
+            other => Ok(Transformed::no(other)),
+        })
+        .map_err(|e| crate::Error::InvalidInput {
+            message: format!("failed to rewrite expression: {e}"),
+        })?
+        .data;
+
+    let user_strings = string_literals(&rewritten);
+    let mut next_placeholder_id = 0;
+    let mut binary_bindings = HashMap::new();
+    let rewritten = rewritten
         .transform(|e: Expr| match e {
             Expr::Literal(ScalarValue::Binary(Some(bytes)), m)
             | Expr::Literal(ScalarValue::LargeBinary(Some(bytes)), m) => {
-                let placeholder = format!("{}{}__", BINARY_PLACEHOLDER_PREFIX, bindings.len());
-                bindings.push(bytes);
+                let placeholder = next_binary_placeholder(&user_strings, &mut next_placeholder_id);
+                binary_bindings.insert(placeholder.clone(), bytes);
                 Ok(Transformed::yes(Expr::Literal(
                     ScalarValue::Utf8(Some(placeholder)),
                     m,
@@ -158,6 +287,57 @@ pub fn expr_to_sql_string(expr: &Expr) -> crate::Result<String> {
             | Expr::Literal(ScalarValue::LargeBinary(None), m) => {
                 Ok(Transformed::yes(Expr::Literal(ScalarValue::Null, m)))
             }
+            Expr::Literal(ScalarValue::Decimal32(Some(value), precision, scale), _m) => {
+                let value = Decimal32Type::format_decimal(value, precision, scale);
+                Ok(Transformed::yes(typed_string_literal(
+                    value,
+                    DataType::Decimal32(precision, scale),
+                )))
+            }
+            Expr::Literal(ScalarValue::Decimal64(Some(value), precision, scale), _m) => {
+                let value = Decimal64Type::format_decimal(value, precision, scale);
+                Ok(Transformed::yes(typed_string_literal(
+                    value,
+                    DataType::Decimal64(precision, scale),
+                )))
+            }
+            Expr::Literal(ScalarValue::Decimal128(Some(value), precision, scale), _m) => {
+                let value = Decimal128Type::format_decimal(value, precision, scale);
+                Ok(Transformed::yes(typed_string_literal(
+                    value,
+                    DataType::Decimal128(precision, scale),
+                )))
+            }
+            Expr::Literal(ScalarValue::Decimal256(Some(value), precision, scale), _m) => {
+                let value = Decimal256Type::format_decimal(value, precision, scale);
+                Ok(Transformed::yes(typed_string_literal(
+                    value,
+                    DataType::Decimal256(precision, scale),
+                )))
+            }
+            Expr::Literal(ScalarValue::Float16(Some(value)), _m) if !value.is_finite() => Ok(
+                Transformed::yes(typed_string_literal(value.to_string(), DataType::Float16)),
+            ),
+            Expr::Literal(ScalarValue::Float32(Some(value)), _m) if !value.is_finite() => Ok(
+                Transformed::yes(typed_string_literal(value.to_string(), DataType::Float32)),
+            ),
+            Expr::Literal(ScalarValue::Float64(Some(value)), _m) if !value.is_finite() => Ok(
+                Transformed::yes(typed_string_literal(value.to_string(), DataType::Float64)),
+            ),
+            Expr::Cast(cast) => Ok(Transformed::yes(datafusion_arrow_cast(
+                *cast.expr,
+                Expr::Literal(
+                    ScalarValue::Utf8(Some(cast.field.data_type().to_string())),
+                    None,
+                ),
+            ))),
+            Expr::TryCast(cast) => Ok(Transformed::yes(datafusion_arrow_try_cast(
+                *cast.expr,
+                Expr::Literal(
+                    ScalarValue::Utf8(Some(cast.field.data_type().to_string())),
+                    None,
+                ),
+            ))),
             other => Ok(Transformed::no(other)),
         })
         .map_err(|e| crate::Error::InvalidInput {
@@ -165,14 +345,12 @@ pub fn expr_to_sql_string(expr: &Expr) -> crate::Result<String> {
         })?
         .data;
 
-    let mut sql = run_unparser(&rewritten)?;
-    for (i, bytes) in bindings.iter().enumerate() {
-        // The unparser quotes string literals with single quotes, so the
-        // placeholder appears as `'__lancedb_binary_placeholder_<i>__'`.
-        let quoted = format!("'{}{}__'", BINARY_PLACEHOLDER_PREFIX, i);
-        sql = sql.replace(&quoted, &bytes_to_hex_sql(bytes));
+    let sql = run_unparser(&rewritten)?;
+    if binary_bindings.is_empty() {
+        Ok(sql)
+    } else {
+        bind_binary_literals(&sql, binary_bindings)
     }
-    Ok(sql)
 }
 
 #[cfg(test)]

rust/lancedb/src/query.rs

@@ -1,37 +1,21 @@
 // SPDX-License-Identifier: Apache-2.0
 // SPDX-FileCopyrightText: Copyright The LanceDB Authors
 
-use std::collections::{HashMap, HashSet};
-use std::pin::Pin;
 use std::sync::Arc;
 use std::{future::Future, time::Duration};
 
 use arrow::compute::concat_batches;
-use arrow_array::{
-    Array, Float16Array, Float32Array, Float64Array, RecordBatch, UInt64Array,
-    cast::AsArray,
-    make_array,
-    types::{Int64Type, UInt64Type},
-};
+use arrow_array::{Array, Float16Array, Float32Array, Float64Array, RecordBatch, make_array};
 use arrow_schema::{DataType, SchemaRef};
-use datafusion_common::{DataFusionError, Result as DataFusionResult};
-use datafusion_execution::TaskContext;
 use datafusion_expr::{Expr, col, lit};
-use datafusion_physical_expr::{EquivalenceProperties, Partitioning};
-use datafusion_physical_plan::{
-    DisplayAs, DisplayFormatType, ExecutionPlan, ExecutionPlanProperties, PlanProperties,
-    coalesce_partitions::CoalescePartitionsExec,
-    execution_plan::{Boundedness, EmissionType},
-    limit::GlobalLimitExec,
-    stream::RecordBatchStreamAdapter,
-};
-use futures::{FutureExt, StreamExt, TryFutureExt, TryStreamExt, stream, try_join};
+use datafusion_physical_plan::ExecutionPlan;
+use futures::{FutureExt, TryFutureExt, TryStreamExt, stream, try_join};
 use half::f16;
 /// Re-export Lance ColumnOrdering type for use in query ordering
 pub use lance::dataset::scanner::ColumnOrdering;
 use lance::dataset::{ROW_ID, scanner::DatasetRecordBatchStream};
 use lance_arrow::RecordBatchExt;
-use lance_datafusion::exec::{execute_plan, format_plan as format_analyzed_plan};
+use lance_datafusion::exec::execute_plan;
 use lance_index::scalar::FullTextSearchQuery;
 use lance_index::scalar::inverted::SCORE_COL;
 use lance_index::vector::DIST_COL;
@@ -841,14 +825,6 @@ pub struct QueryRequest {
     /// Offset of the query.
     pub offset: Option<usize>,
 
-    /// Dataset offsets whose occurrence multiplicity must be restored after
-    /// executing the physical lookup represented by this request.
-    ///
-    /// This is client-side execution metadata used when a [`TakeQuery`] is
-    /// converted into a request. It is not sent to remote services.
-    #[doc(hidden)]
-    pub take_offsets: Option<Vec<u64>>,
-
     /// Apply filter to the returned rows.
     pub filter: Option<QueryFilter>,
 
@@ -917,7 +893,6 @@ impl Default for QueryRequest {
         Self {
             limit: None,
             offset: None,
-            take_offsets: None,
             filter: None,
             filter_error: None,
             full_text_search: None,
@@ -1554,302 +1529,6 @@ impl HasQuery for VectorQuery {
     }
 }
 
-fn take_occurrences(offsets: &[u64]) -> HashMap<u64, usize> {
-    let mut occurrences = HashMap::with_capacity(offsets.len());
-    for offset in offsets {
-        *occurrences.entry(*offset).or_insert(0) += 1;
-    }
-    occurrences
-}
-
-fn restore_take_batch_with_occurrences(
-    batch: RecordBatch,
-    offsets: &[u64],
-    occurrences: &HashMap<u64, usize>,
-    ordering_column: &str,
-    drop_ordering_column: bool,
-    preserve_order: bool,
-) -> Result<RecordBatch> {
-    let actual_offsets = batch
-        .column_by_name(ordering_column)
-        .ok_or_else(|| Error::Schema {
-            message: format!(
-                "take query result did not include ordering column '{ordering_column}'"
-            ),
-        })?;
-    let actual_offsets = match actual_offsets.data_type() {
-        DataType::UInt64 => actual_offsets
-            .as_primitive::<UInt64Type>()
-            .values()
-            .to_vec(),
-        DataType::Int64 => actual_offsets
-            .as_primitive::<Int64Type>()
-            .values()
-            .iter()
-            .map(|offset| {
-                u64::try_from(*offset).map_err(|_| Error::Schema {
-                    message: format!(
-                        "take query ordering column '{ordering_column}' contained a negative offset"
-                    ),
-                })
-            })
-            .collect::<Result<Vec<_>>>()?,
-        data_type => {
-            return Err(Error::Schema {
-                message: format!(
-                    "take query ordering column '{ordering_column}' had unsupported type {data_type}"
-                ),
-            });
-        }
-    };
-
-    let mut desired_order = Vec::with_capacity(offsets.len());
-    if preserve_order {
-        let ordering = actual_offsets
-            .iter()
-            .copied()
-            .enumerate()
-            .map(|(index, offset)| (offset, index as u64))
-            .collect::<HashMap<_, _>>();
-        // Missing offsets retain the filter-based behavior of returning no row.
-        desired_order.extend(
-            offsets
-                .iter()
-                .filter_map(|offset| ordering.get(offset).copied()),
-        );
-    } else {
-        // Public take queries do not guarantee output order. Preserve the lookup's
-        // existing order and only restore the multiplicity of each matching row.
-        for (index, offset) in actual_offsets.iter().enumerate() {
-            if let Some(count) = occurrences.get(offset) {
-                desired_order.extend(std::iter::repeat_n(index as u64, *count));
-            }
-        }
-    }
-
-    let mut ordered_batch = if desired_order.len() == batch.num_rows()
-        && desired_order
-            .iter()
-            .enumerate()
-            .all(|(index, desired)| *desired == index as u64)
-    {
-        batch
-    } else {
-        arrow_select::take::take_record_batch(&batch, &UInt64Array::from(desired_order))?
-    };
-
-    if drop_ordering_column {
-        ordered_batch = ordered_batch.drop_column(ordering_column)?;
-    }
-
-    Ok(ordered_batch)
-}
-
-#[cfg(test)]
-fn restore_take_batch(
-    batch: RecordBatch,
-    offsets: &[u64],
-    ordering_column: &str,
-    drop_ordering_column: bool,
-    preserve_order: bool,
-) -> Result<RecordBatch> {
-    restore_take_batch_with_occurrences(
-        batch,
-        offsets,
-        &take_occurrences(offsets),
-        ordering_column,
-        drop_ordering_column,
-        preserve_order,
-    )
-}
-
-/// Restores the logical offset occurrence sequence above the physical lookup plan.
-///
-/// The lookup plan returns each matching row at most once. For ordinary unordered
-/// takes this operator expands each input batch incrementally and preserves the
-/// lookup's partitioning. The explicitly ordered reader path collects one coalesced
-/// input before restoring requested order. Pagination must remain above this operator
-/// so it applies to occurrences.
-#[derive(Debug)]
-struct TakeRestoreExec {
-    input: Arc<dyn ExecutionPlan>,
-    offsets: Vec<u64>,
-    occurrences: Arc<HashMap<u64, usize>>,
-    ordering_column: String,
-    drop_ordering_column: bool,
-    preserve_order: bool,
-    schema: SchemaRef,
-    properties: Arc<PlanProperties>,
-}
-
-impl TakeRestoreExec {
-    fn try_new(
-        input: Arc<dyn ExecutionPlan>,
-        offsets: Vec<u64>,
-        ordering_column: String,
-        drop_ordering_column: bool,
-        preserve_order: bool,
-    ) -> Result<Self> {
-        let schema = if drop_ordering_column {
-            RecordBatch::new_empty(input.schema())
-                .drop_column(&ordering_column)?
-                .schema()
-        } else {
-            input.schema()
-        };
-        let partition_count = if preserve_order {
-            1
-        } else {
-            input.output_partitioning().partition_count()
-        };
-        let emission_type = if preserve_order {
-            EmissionType::Final
-        } else {
-            EmissionType::Incremental
-        };
-        let properties = Arc::new(PlanProperties::new(
-            EquivalenceProperties::new(schema.clone()),
-            Partitioning::UnknownPartitioning(partition_count),
-            emission_type,
-            Boundedness::Bounded,
-        ));
-
-        Ok(Self {
-            input,
-            occurrences: Arc::new(take_occurrences(&offsets)),
-            offsets,
-            ordering_column,
-            drop_ordering_column,
-            preserve_order,
-            schema,
-            properties,
-        })
-    }
-}
-
-impl DisplayAs for TakeRestoreExec {
-    fn fmt_as(
-        &self,
-        _display_type: DisplayFormatType,
-        formatter: &mut std::fmt::Formatter<'_>,
-    ) -> std::fmt::Result {
-        write!(
-            formatter,
-            "TakeRestoreExec: occurrences={}",
-            self.offsets.len()
-        )
-    }
-}
-
-impl ExecutionPlan for TakeRestoreExec {
-    fn name(&self) -> &str {
-        "TakeRestoreExec"
-    }
-
-    fn properties(&self) -> &Arc<PlanProperties> {
-        &self.properties
-    }
-
-    fn children(&self) -> Vec<&Arc<dyn ExecutionPlan>> {
-        vec![&self.input]
-    }
-
-    fn maintains_input_order(&self) -> Vec<bool> {
-        vec![!self.preserve_order]
-    }
-
-    fn benefits_from_input_partitioning(&self) -> Vec<bool> {
-        vec![false]
-    }
-
-    fn with_new_children(
-        self: Arc<Self>,
-        children: Vec<Arc<dyn ExecutionPlan>>,
-    ) -> DataFusionResult<Arc<dyn ExecutionPlan>> {
-        if children.len() != 1 {
-            return Err(DataFusionError::Internal(format!(
-                "TakeRestoreExec expected one child, got {}",
-                children.len()
-            )));
-        }
-        let child = children.into_iter().next().unwrap();
-        let plan = Self::try_new(
-            child,
-            self.offsets.clone(),
-            self.ordering_column.clone(),
-            self.drop_ordering_column,
-            self.preserve_order,
-        )
-        .map_err(|error| DataFusionError::External(Box::new(error)))?;
-        Ok(Arc::new(plan))
-    }
-
-    fn execute(
-        &self,
-        partition: usize,
-        context: Arc<TaskContext>,
-    ) -> DataFusionResult<datafusion_physical_plan::SendableRecordBatchStream> {
-        let partition_count = self.input.output_partitioning().partition_count();
-        if partition >= partition_count || (self.preserve_order && partition != 0) {
-            return Err(DataFusionError::Internal(format!(
-                "TakeRestoreExec cannot execute partition {partition}; input has {partition_count} partitions"
-            )));
-        }
-
-        let input = self.input.execute(partition, context)?;
-        let output_schema = self.schema.clone();
-        let offsets = self.offsets.clone();
-        let occurrences = self.occurrences.clone();
-        let ordering_column = self.ordering_column.clone();
-        let drop_ordering_column = self.drop_ordering_column;
-        let preserve_order = self.preserve_order;
-        let stream: Pin<Box<dyn futures::Stream<Item = DataFusionResult<RecordBatch>> + Send>> =
-            if preserve_order {
-                let input_schema = input.schema();
-                Box::pin(stream::once(async move {
-                    let batches = input.try_collect::<Vec<_>>().await?;
-                    let batch = if batches.is_empty() {
-                        RecordBatch::new_empty(input_schema.clone())
-                    } else {
-                        concat_batches(&input_schema, &batches)?
-                    };
-                    restore_take_batch_with_occurrences(
-                        batch,
-                        &offsets,
-                        &occurrences,
-                        &ordering_column,
-                        drop_ordering_column,
-                        true,
-                    )
-                    .map_err(|error| DataFusionError::External(Box::new(error)))
-                }))
-            } else {
-                Box::pin(input.map(move |batch| {
-                    batch.and_then(|batch| {
-                        restore_take_batch_with_occurrences(
-                            batch,
-                            &offsets,
-                            &occurrences,
-                            &ordering_column,
-                            drop_ordering_column,
-                            false,
-                        )
-                        .map_err(|error| DataFusionError::External(Box::new(error)))
-                    })
-                }))
-            };
-
-        Ok(Box::pin(RecordBatchStreamAdapter::new(
-            output_schema,
-            stream,
-        )))
-    }
-
-    fn supports_limit_pushdown(&self) -> bool {
-        false
-    }
-}
-
 /// A builder for LanceDB take queries.
 ///
 /// See [`crate::Table::query`] for more details on queries
@@ -1866,8 +1545,6 @@ impl ExecutionPlan for TakeRestoreExec {
 pub struct TakeQuery {
     parent: Arc<dyn BaseTable>,
     request: QueryRequest,
-    offsets: Option<Vec<u64>>,
-    preserve_order: bool,
 }
 
 impl TakeQuery {
@@ -1875,24 +1552,15 @@ impl TakeQuery {
     ///
     /// See [`crate::Table::take_offsets`] for more details.
     pub fn from_offsets(parent: Arc<dyn BaseTable>, offsets: Vec<u64>) -> Self {
-        let mut seen = HashSet::with_capacity(offsets.len());
-        let in_list: Vec<Expr> = offsets
-            .iter()
-            .copied()
-            .filter(|offset| seen.insert(*offset))
-            .map(lit)
-            .collect();
+        let in_list: Vec<Expr> = offsets.iter().map(|o| lit(*o)).collect();
         Self {
             parent,
             request: QueryRequest {
                 filter: Some(QueryFilter::Datafusion(
                     col("_rowoffset").in_list(in_list, false),
                 )),
-                take_offsets: Some(offsets.clone()),
                 ..Default::default()
             },
-            offsets: Some(offsets),
-            preserve_order: false,
         }
     }
 
@@ -1907,181 +1575,9 @@ impl TakeQuery {
                 filter: Some(QueryFilter::Datafusion(col(ROW_ID).in_list(in_list, false))),
                 ..Default::default()
             },
-            offsets: None,
-            preserve_order: false,
         }
     }
 
-    /// Preserve the requested offset order when restoring duplicate occurrences.
-    ///
-    /// This is reserved for readers whose API explicitly guarantees ordering.
-    pub(crate) fn preserve_order(mut self) -> Self {
-        debug_assert!(self.offsets.is_some());
-        self.preserve_order = true;
-        self
-    }
-
-    async fn request_with_row_offset(
-        parent: &dyn BaseTable,
-        request: &QueryRequest,
-    ) -> Result<(QueryRequest, String, bool)> {
-        const ROW_OFFSET: &str = "_rowoffset";
-        const INTERNAL_ROW_OFFSET: &str = "__lancedb_take_row_offset";
-
-        let mut request = request.clone();
-        // The physical lookup must not recursively restore occurrences. The
-        // wrapper above this request owns that logical operation.
-        request.take_offsets = None;
-        let (ordering_column, drop_ordering_column) = match &mut request.select {
-            Select::All => {
-                let mut columns = parent
-                    .schema()
-                    .await?
-                    .fields()
-                    .iter()
-                    .map(|field| field.name().clone())
-                    .collect::<Vec<_>>();
-                columns.push(ROW_OFFSET.to_string());
-                request.select = Select::Columns(columns);
-                (ROW_OFFSET.to_string(), true)
-            }
-            Select::Columns(columns) => {
-                if columns.iter().any(|column| column == ROW_OFFSET) {
-                    (ROW_OFFSET.to_string(), false)
-                } else {
-                    columns.push(ROW_OFFSET.to_string());
-                    (ROW_OFFSET.to_string(), true)
-                }
-            }
-            Select::Dynamic(columns) => {
-                let mut ordering_column = INTERNAL_ROW_OFFSET.to_string();
-                while columns.iter().any(|(name, _)| name == &ordering_column) {
-                    ordering_column.push('_');
-                }
-                columns.push((ordering_column.clone(), ROW_OFFSET.to_string()));
-                (ordering_column, true)
-            }
-            Select::Expr(columns) => {
-                let mut ordering_column = INTERNAL_ROW_OFFSET.to_string();
-                while columns.iter().any(|(name, _)| name == &ordering_column) {
-                    ordering_column.push('_');
-                }
-                columns.push((ordering_column.clone(), col(ROW_OFFSET)));
-                (ordering_column, true)
-            }
-        };
-
-        Ok((request, ordering_column, drop_ordering_column))
-    }
-
-    async fn prepare_offsets_lookup(
-        parent: &dyn BaseTable,
-        request: &QueryRequest,
-    ) -> Result<(QueryRequest, String, bool, usize, Option<usize>)> {
-        let (mut request, ordering_column, drop_ordering_column) =
-            Self::request_with_row_offset(parent, request).await?;
-        // The lookup operates on distinct physical rows. Pagination is a logical
-        // operation over occurrences and must be applied only after restoration.
-        let output_offset = request.offset.take().unwrap_or_default();
-        let output_limit = request.limit.take();
-
-        Ok((
-            request,
-            ordering_column,
-            drop_ordering_column,
-            output_offset,
-            output_limit,
-        ))
-    }
-
-    fn wrap_offsets_plan(
-        lookup: Arc<dyn ExecutionPlan>,
-        offsets: &[u64],
-        ordering_column: String,
-        drop_ordering_column: bool,
-        output_offset: usize,
-        output_limit: Option<usize>,
-        preserve_order: bool,
-    ) -> Result<Arc<dyn ExecutionPlan>> {
-        let lookup = if preserve_order {
-            Arc::new(CoalescePartitionsExec::new(lookup)) as Arc<dyn ExecutionPlan>
-        } else {
-            lookup
-        };
-        let restored: Arc<dyn ExecutionPlan> = Arc::new(TakeRestoreExec::try_new(
-            lookup,
-            offsets.to_vec(),
-            ordering_column,
-            drop_ordering_column,
-            preserve_order,
-        )?);
-
-        if output_offset > 0 || output_limit.is_some() {
-            Ok(Arc::new(GlobalLimitExec::new(
-                restored,
-                output_offset,
-                output_limit,
-            )))
-        } else {
-            Ok(restored)
-        }
-    }
-
-    fn wrap_offsets_explanation(
-        lookup: &str,
-        occurrence_count: usize,
-        output_offset: usize,
-        output_limit: Option<usize>,
-        preserve_order: bool,
-    ) -> String {
-        fn indent(plan: &str, spaces: usize) -> String {
-            let indentation = " ".repeat(spaces);
-            plan.lines()
-                .map(|line| format!("{indentation}{line}"))
-                .collect::<Vec<_>>()
-                .join("\n")
-        }
-
-        let restored = if preserve_order {
-            format!(
-                "TakeRestoreExec: occurrences={occurrence_count}\n  CoalescePartitionsExec\n{}",
-                indent(lookup, 4)
-            )
-        } else {
-            format!(
-                "TakeRestoreExec: occurrences={occurrence_count}\n{}",
-                indent(lookup, 2)
-            )
-        };
-
-        if output_offset > 0 || output_limit.is_some() {
-            let fetch = output_limit
-                .map(|limit| limit.to_string())
-                .unwrap_or_else(|| "None".to_string());
-            format!(
-                "GlobalLimitExec: skip={output_offset}, fetch={fetch}\n{}",
-                indent(&restored, 2)
-            )
-        } else {
-            restored
-        }
-    }
-
-    async fn create_offsets_plan(
-        &self,
-        offsets: &[u64],
-        options: QueryExecutionOptions,
-    ) -> Result<Arc<dyn ExecutionPlan>> {
-        create_take_offsets_plan(
-            self.parent.as_ref(),
-            &self.request,
-            offsets,
-            options,
-            self.preserve_order,
-        )
-        .await
-    }
-
     /// Convert the `TakeQuery` into a `QueryRequest`.
     pub fn into_request(self) -> QueryRequest {
         self.request
@@ -2126,63 +1622,6 @@ impl TakeQuery {
     }
 }
 
-pub(crate) async fn create_take_offsets_plan(
-    parent: &dyn BaseTable,
-    request: &QueryRequest,
-    offsets: &[u64],
-    options: QueryExecutionOptions,
-    preserve_order: bool,
-) -> Result<Arc<dyn ExecutionPlan>> {
-    let (request, ordering_column, drop_ordering_column, output_offset, output_limit) =
-        TakeQuery::prepare_offsets_lookup(parent, request).await?;
-    let lookup_options = if preserve_order {
-        options.without_output_batch_length_limit()
-    } else {
-        options
-    };
-    let lookup = parent
-        .create_plan(&AnyQuery::Query(request), lookup_options)
-        .await?;
-
-    TakeQuery::wrap_offsets_plan(
-        lookup,
-        offsets,
-        ordering_column,
-        drop_ordering_column,
-        output_offset,
-        output_limit,
-        preserve_order,
-    )
-}
-
-pub(crate) async fn explain_take_offsets_plan(
-    parent: &dyn BaseTable,
-    request: &QueryRequest,
-    offsets: &[u64],
-    verbose: bool,
-) -> Result<String> {
-    let (request, _, _, output_offset, output_limit) =
-        TakeQuery::prepare_offsets_lookup(parent, request).await?;
-    let lookup = parent
-        .explain_plan(&AnyQuery::Query(request), verbose)
-        .await?;
-    Ok(TakeQuery::wrap_offsets_explanation(
-        &lookup,
-        offsets.len(),
-        output_offset,
-        output_limit,
-        false,
-    ))
-}
-
-pub(crate) async fn prepare_take_offsets_request(
-    parent: &dyn BaseTable,
-    request: &QueryRequest,
-) -> Result<QueryRequest> {
-    let (request, _, _, _, _) = TakeQuery::prepare_offsets_lookup(parent, request).await?;
-    Ok(request)
-}
-
 impl HasQuery for TakeQuery {
     fn mut_query(&mut self) -> &mut QueryRequest {
         &mut self.request
@@ -2191,10 +1630,6 @@ impl HasQuery for TakeQuery {
 
 impl ExecutableQuery for TakeQuery {
     async fn create_plan(&self, options: QueryExecutionOptions) -> Result<Arc<dyn ExecutionPlan>> {
-        if let Some(offsets) = &self.offsets {
-            return self.create_offsets_plan(offsets, options).await;
-        }
-
         let req = AnyQuery::Query(self.request.clone());
         self.parent.clone().create_plan(&req, options).await
     }
@@ -2203,18 +1638,6 @@ impl ExecutableQuery for TakeQuery {
         &self,
         options: QueryExecutionOptions,
     ) -> Result<SendableRecordBatchStream> {
-        if self.offsets.is_some() {
-            let plan = self.create_plan(options.clone()).await?;
-            let inner = execute_plan(plan, Default::default())?;
-            let inner = MaxBatchLengthStream::new_boxed(inner, options.max_batch_length as usize);
-            let inner = if let Some(timeout) = options.timeout {
-                TimeoutStream::new_boxed(inner, timeout)
-            } else {
-                inner
-            };
-            return Ok(DatasetRecordBatchStream::new(inner).into());
-        }
-
         let query = AnyQuery::Query(self.request.clone());
         Ok(SendableRecordBatchStream::from(
             self.parent.clone().query(&query, options).await?,
@@ -2222,51 +1645,11 @@ impl ExecutableQuery for TakeQuery {
     }
 
     async fn explain_plan(&self, verbose: bool) -> Result<String> {
-        if let Some(offsets) = &self.offsets {
-            let (request, _, _, output_offset, output_limit) =
-                Self::prepare_offsets_lookup(self.parent.as_ref(), &self.request).await?;
-            // Ask the backend to explain only the distinct-row lookup. This keeps
-            // remote explanation non-executing while still showing the client-side
-            // operators that create_plan and execution place above that lookup.
-            let lookup = self
-                .parent
-                .explain_plan(&AnyQuery::Query(request), verbose)
-                .await?;
-            return Ok(Self::wrap_offsets_explanation(
-                &lookup,
-                offsets.len(),
-                output_offset,
-                output_limit,
-                self.preserve_order,
-            ));
-        }
-
         let query = AnyQuery::Query(self.request.clone());
         self.parent.explain_plan(&query, verbose).await
     }
 
     async fn analyze_plan_with_options(&self, options: QueryExecutionOptions) -> Result<String> {
-        if self.offsets.is_some() {
-            if self.parent.analyze_plan_is_remote() {
-                let (request, _, _, _, _) =
-                    Self::prepare_offsets_lookup(self.parent.as_ref(), &self.request).await?;
-                // Remote analysis is owned by the service. The current wire
-                // request represents only the distinct-row lookup, so return
-                // the service report unchanged instead of fabricating metrics
-                // for client-side restoration operators.
-                return self
-                    .parent
-                    .analyze_plan(&AnyQuery::Query(request), options)
-                    .await;
-            }
-
-            let plan = self.create_plan(options).await?;
-            execute_plan(plan.clone(), Default::default())?
-                .try_collect::<Vec<_>>()
-                .await?;
-            return Ok(format_analyzed_plan(plan));
-        }
-
         let query = AnyQuery::Query(self.request.clone());
         self.parent.analyze_plan(&query, options).await
     }
@@ -2287,7 +1670,6 @@ mod tests {
         StringArray, cast::AsArray, types::Float32Type,
     };
     use arrow_schema::{DataType, Field as ArrowField, Schema as ArrowSchema};
-    use datafusion_physical_plan::display::DisplayableExecutionPlan;
     use futures::{StreamExt, TryStreamExt};
     use lance_testing::datagen::{BatchGenerator, IncrementingInt32, RandomVector};
     use rand::seq::IndexedRandom;
@@ -3542,218 +2924,6 @@ mod tests {
         assert_eq!(results[0].num_columns(), 1);
     }
 
-    #[tokio::test]
-    async fn test_take_offsets_preserves_duplicate_multiplicity() {
-        let tmp_dir = tempdir().unwrap();
-        let table = make_test_table(&tmp_dir).await;
-
-        let results = table
-            .take_offsets(vec![5, 1, 5, 17])
-            .select(Select::Columns(vec!["id".to_string()]))
-            .execute_with_options(QueryExecutionOptions {
-                max_batch_length: 2,
-                ..Default::default()
-            })
-            .await
-            .unwrap()
-            .try_collect::<Vec<_>>()
-            .await
-            .unwrap();
-
-        assert_eq!(results.len(), 2);
-        assert!(results.iter().all(|batch| batch.num_columns() == 1));
-        let mut ids = results
-            .iter()
-            .flat_map(|batch| {
-                batch
-                    .column_by_name("id")
-                    .unwrap()
-                    .as_primitive::<Int32Type>()
-                    .values()
-                    .to_vec()
-            })
-            .collect::<Vec<_>>();
-        ids.sort_unstable();
-        assert_eq!(ids, vec![1, 5, 5, 17]);
-    }
-
-    #[tokio::test]
-    async fn test_take_offsets_plan_is_incremental() {
-        let tmp_dir = tempdir().unwrap();
-        let table = make_test_table(&tmp_dir).await;
-
-        let plan = table
-            .take_offsets(vec![5, 1, 17])
-            .create_plan(QueryExecutionOptions {
-                max_batch_length: 1,
-                ..Default::default()
-            })
-            .await
-            .unwrap();
-
-        assert_eq!(plan.properties().emission_type, EmissionType::Incremental);
-        let displayed = DisplayableExecutionPlan::new(plan.as_ref())
-            .indent(false)
-            .to_string();
-        assert!(displayed.contains("TakeRestoreExec"));
-        assert!(!displayed.contains("CoalescePartitionsExec"));
-    }
-
-    #[tokio::test]
-    async fn test_take_into_request_preserves_duplicate_multiplicity() {
-        let tmp_dir = tempdir().unwrap();
-        let table = make_test_table(&tmp_dir).await;
-        let request = table.take_offsets(vec![5, 5]).into_request();
-        assert_eq!(request.take_offsets, Some(vec![5, 5]));
-
-        let batches = table
-            .base_table()
-            .query(&AnyQuery::Query(request), QueryExecutionOptions::default())
-            .await
-            .unwrap()
-            .try_collect::<Vec<_>>()
-            .await
-            .unwrap();
-
-        assert_eq!(batches.iter().map(RecordBatch::num_rows).sum::<usize>(), 2);
-    }
-
-    #[test]
-    fn test_restore_take_batch_only_reorders_when_requested() {
-        let batch = RecordBatch::try_from_iter([
-            (
-                "id",
-                Arc::new(Int32Array::from(vec![17, 5, 1])) as Arc<dyn Array>,
-            ),
-            (
-                "_rowoffset",
-                Arc::new(UInt64Array::from(vec![17, 5, 1])) as Arc<dyn Array>,
-            ),
-        ])
-        .unwrap();
-
-        let restored =
-            restore_take_batch(batch.clone(), &[5, 1, 5, 17], "_rowoffset", true, false).unwrap();
-        assert_eq!(
-            restored
-                .column_by_name("id")
-                .unwrap()
-                .as_primitive::<Int32Type>()
-                .values(),
-            &[17, 5, 5, 1]
-        );
-
-        let ordered = restore_take_batch(batch, &[5, 1, 5, 17], "_rowoffset", true, true).unwrap();
-        assert_eq!(
-            ordered
-                .column_by_name("id")
-                .unwrap()
-                .as_primitive::<Int32Type>()
-                .values(),
-            &[5, 1, 5, 17]
-        );
-    }
-
-    #[tokio::test]
-    async fn test_take_offsets_applies_pagination_after_restoration() {
-        let tmp_dir = tempdir().unwrap();
-        let table = make_test_table(&tmp_dir).await;
-
-        let limited = table
-            .take_offsets(vec![0, 1, 0, 2])
-            .select(Select::Columns(vec!["id".to_string()]))
-            .limit(3)
-            .execute()
-            .await
-            .unwrap()
-            .try_collect::<Vec<_>>()
-            .await
-            .unwrap();
-        let limited = concat_batches(&limited[0].schema(), &limited).unwrap();
-        assert_eq!(limited.num_rows(), 3);
-        assert!(
-            limited
-                .column_by_name("id")
-                .unwrap()
-                .as_primitive::<Int32Type>()
-                .values()
-                .iter()
-                .all(|id| [0, 1, 2].contains(id))
-        );
-
-        let offset = table
-            .take_offsets(vec![5, 1, 5, 17])
-            .select(Select::Columns(vec!["id".to_string()]))
-            .offset(1)
-            .execute()
-            .await
-            .unwrap()
-            .try_collect::<Vec<_>>()
-            .await
-            .unwrap();
-        let offset = concat_batches(&offset[0].schema(), &offset).unwrap();
-        assert_eq!(offset.num_rows(), 3);
-        assert!(
-            offset
-                .column_by_name("id")
-                .unwrap()
-                .as_primitive::<Int32Type>()
-                .values()
-                .iter()
-                .all(|id| [1, 5, 17].contains(id))
-        );
-    }
-
-    #[tokio::test]
-    async fn test_take_offsets_create_plan_restores_occurrences() {
-        let tmp_dir = tempdir().unwrap();
-        let table = make_test_table(&tmp_dir).await;
-        let take = table
-            .take_offsets(vec![5, 1, 5, 17])
-            .select(Select::Columns(vec!["id".to_string()]));
-
-        let plan = take
-            .create_plan(QueryExecutionOptions::default())
-            .await
-            .unwrap();
-        assert_eq!(plan.schema().fields().len(), 1);
-        assert_eq!(plan.schema().field(0).name(), "id");
-        let planned = execute_plan(plan, Default::default())
-            .unwrap()
-            .try_collect::<Vec<_>>()
-            .await
-            .unwrap();
-        let planned = concat_batches(&planned[0].schema(), &planned).unwrap();
-        let mut ids = planned
-            .column_by_name("id")
-            .unwrap()
-            .as_primitive::<Int32Type>()
-            .values()
-            .to_vec();
-        ids.sort_unstable();
-        assert_eq!(ids, vec![1, 5, 5, 17]);
-    }
-
-    #[tokio::test]
-    async fn test_take_offsets_plan_introspection_shows_restoration() {
-        let tmp_dir = tempdir().unwrap();
-        let table = make_test_table(&tmp_dir).await;
-        let take = table
-            .take_offsets(vec![0, 1, 0, 2])
-            .select(Select::Columns(vec!["id".to_string()]))
-            .limit(3);
-
-        let explained = take.explain_plan(false).await.unwrap();
-        assert!(explained.contains("GlobalLimitExec"));
-        assert!(explained.contains("TakeRestoreExec"));
-        assert!(!explained.contains("CoalescePartitionsExec"));
-
-        let analyzed = take.analyze_plan().await.unwrap();
-        assert!(analyzed.contains("GlobalLimitExec"));
-        assert!(analyzed.contains("TakeRestoreExec"));
-        assert!(!analyzed.contains("CoalescePartitionsExec"));
-    }
-
     #[tokio::test]
     async fn test_take_row_ids() {
         let tmp_dir = tempdir().unwrap();

rust/lancedb/src/remote/table.rs

@@ -40,8 +40,8 @@ use crate::table::{
 use crate::table::{AnyQuery, Filter, Predicate, PreprocessingOutput, TableStatistics};
 use crate::utils::background_cache::BackgroundCache;
 use crate::utils::{
-    MaxBatchLengthStream, TimeoutStream, resolve_arrow_field_path, resolve_arrow_fts_field_path,
-    supported_btree_data_type, supported_vector_data_type,
+    resolve_arrow_field_path, resolve_arrow_fts_field_path, supported_btree_data_type,
+    supported_vector_data_type,
 };
 use crate::{DistanceType, Error};
 use crate::{
@@ -2022,9 +2022,6 @@ impl<S: HttpSend> BaseTable for RemoteTable<S> {
     fn as_any(&self) -> &dyn std::any::Any {
         self
     }
-    fn analyze_plan_is_remote(&self) -> bool {
-        true
-    }
     fn name(&self) -> &str {
         &self.name
     }
@@ -2597,13 +2594,6 @@ impl<S: HttpSend> BaseTable for RemoteTable<S> {
         query: &AnyQuery,
         options: QueryExecutionOptions,
     ) -> Result<Arc<dyn ExecutionPlan>> {
-        if let AnyQuery::Query(request) = query
-            && let Some(offsets) = &request.take_offsets
-        {
-            return crate::query::create_take_offsets_plan(self, request, offsets, options, false)
-                .await;
-        }
-
         let streams = self.execute_query(query, &options).await?;
         if streams.len() == 1 {
             let stream = streams.into_iter().next().unwrap();
@@ -2622,27 +2612,6 @@ impl<S: HttpSend> BaseTable for RemoteTable<S> {
         query: &AnyQuery,
         options: QueryExecutionOptions,
     ) -> Result<DatasetRecordBatchStream> {
-        if let AnyQuery::Query(request) = query
-            && let Some(offsets) = &request.take_offsets
-        {
-            let plan = crate::query::create_take_offsets_plan(
-                self,
-                request,
-                offsets,
-                options.clone(),
-                false,
-            )
-            .await?;
-            let inner = execute_plan(plan, Default::default())?;
-            let inner = MaxBatchLengthStream::new_boxed(inner, options.max_batch_length as usize);
-            let inner = if let Some(timeout) = options.timeout {
-                TimeoutStream::new_boxed(inner, timeout)
-            } else {
-                inner
-            };
-            return Ok(DatasetRecordBatchStream::new(inner));
-        }
-
         let streams = self.execute_query(query, &options).await?;
 
         if streams.len() == 1 {
@@ -2680,12 +2649,6 @@ impl<S: HttpSend> BaseTable for RemoteTable<S> {
     }
 
     async fn explain_plan(&self, query: &AnyQuery, verbose: bool) -> Result<String> {
-        if let AnyQuery::Query(request) = query
-            && let Some(offsets) = &request.take_offsets
-        {
-            return crate::query::explain_take_offsets_plan(self, request, offsets, verbose).await;
-        }
-
         let base_request = self
             .client
             .post(&format!("/v1/table/{}/explain_plan/", self.identifier));
@@ -2738,17 +2701,6 @@ impl<S: HttpSend> BaseTable for RemoteTable<S> {
         query: &AnyQuery,
         options: QueryExecutionOptions,
     ) -> Result<String> {
-        let prepared_query = if let AnyQuery::Query(request) = query
-            && request.take_offsets.is_some()
-        {
-            Some(AnyQuery::Query(
-                crate::query::prepare_take_offsets_request(self, request).await?,
-            ))
-        } else {
-            None
-        };
-        let query = prepared_query.as_ref().unwrap_or(query);
-
         let mut request = self
             .client
             .post(&format!("/v1/table/{}/analyze_plan/", self.identifier));
@@ -3738,7 +3690,7 @@ mod tests {
     };
     use arrow_schema::{DataType, Field, Schema};
     use chrono::{DateTime, Utc};
-    use futures::{StreamExt, TryFutureExt, TryStreamExt, future::BoxFuture};
+    use futures::{StreamExt, TryFutureExt, future::BoxFuture};
     use lance_index::scalar::inverted::{DocumentGranularity, query::MatchQuery};
     use lance_index::scalar::{FullTextSearchQuery, InvertedIndexParams};
     use reqwest::Body;
@@ -5659,114 +5611,6 @@ mod tests {
         assert_eq!(result, "analyzed plan");
     }
 
-    #[tokio::test]
-    async fn test_take_offsets_explain_plan_does_not_execute_query() {
-        let table = Table::new_with_handler("my_table", |request| {
-            assert_eq!(request.method(), "POST");
-            assert_eq!(request.url().path(), "/v1/table/my_table/explain_plan/");
-
-            http::Response::builder()
-                .status(200)
-                .body(r#""RemoteLookupExec""#)
-                .unwrap()
-        });
-
-        let explained = table
-            .take_offsets(vec![0, 1, 0, 2])
-            .select(crate::query::Select::columns(&["id"]))
-            .limit(3)
-            .explain_plan(false)
-            .await
-            .unwrap();
-
-        assert!(explained.contains("GlobalLimitExec"));
-        assert!(explained.contains("TakeRestoreExec"));
-        assert!(!explained.contains("CoalescePartitionsExec"));
-        assert!(explained.contains("RemoteLookupExec"));
-    }
-
-    #[tokio::test]
-    async fn test_converted_take_request_restores_remote_occurrences() {
-        let table = Table::new_with_handler("my_table", |request| {
-            assert_eq!(request.method(), "POST");
-            assert_eq!(request.url().path(), "/v1/table/my_table/query/");
-
-            let body: serde_json::Value =
-                serde_json::from_slice(request.body().unwrap().as_bytes().unwrap()).unwrap();
-            assert_eq!(body["columns"], json!(["id", "_rowoffset"]));
-
-            let data = RecordBatch::try_new(
-                Arc::new(Schema::new(vec![
-                    Field::new("id", DataType::Int32, false),
-                    Field::new("_rowoffset", DataType::UInt64, false),
-                ])),
-                vec![
-                    Arc::new(Int32Array::from(vec![5])),
-                    Arc::new(arrow_array::UInt64Array::from(vec![5])),
-                ],
-            )
-            .unwrap();
-            http::Response::builder()
-                .status(200)
-                .header(CONTENT_TYPE, ARROW_FILE_CONTENT_TYPE)
-                .body(write_ipc_file(&data))
-                .unwrap()
-        });
-
-        let request = table
-            .take_offsets(vec![5, 5])
-            .select(crate::query::Select::columns(&["id"]))
-            .into_request();
-        let batches = table
-            .base_table()
-            .query(&AnyQuery::Query(request), QueryExecutionOptions::default())
-            .await
-            .unwrap()
-            .try_collect::<Vec<_>>()
-            .await
-            .unwrap();
-
-        assert_eq!(batches.iter().map(RecordBatch::num_rows).sum::<usize>(), 2);
-        assert!(
-            batches
-                .iter()
-                .all(|batch| batch.schema().fields().len() == 1)
-        );
-    }
-
-    #[tokio::test]
-    async fn test_take_offsets_analyze_plan_delegates_to_remote() {
-        let table = Table::new_with_handler("my_table", |request| {
-            assert_eq!(request.method(), "POST");
-            assert_eq!(request.url().path(), "/v1/table/my_table/analyze_plan/");
-            assert_eq!(
-                request
-                    .url()
-                    .query_pairs()
-                    .find(|(key, _)| key == "distributed_metrics"),
-                Some(("distributed_metrics".into(), "per_worker".into()))
-            );
-
-            http::Response::builder()
-                .status(200)
-                .body(r#""Remote analyzed plan: worker metrics""#)
-                .unwrap()
-        });
-
-        let analyzed = table
-            .take_offsets(vec![0, 1, 0, 2])
-            .select(crate::query::Select::columns(&["id"]))
-            .limit(3)
-            .analyze_plan_with_options(QueryExecutionOptions {
-                analyze_plan_distributed_metrics: AnalyzePlanDistributedMetrics::PerWorker,
-                ..Default::default()
-            })
-            .await
-            .unwrap();
-
-        assert_eq!(analyzed, "Remote analyzed plan: worker metrics");
-    }
-
     #[tokio::test]
     async fn test_query_structured_fts() {
         let table =

rust/lancedb/src/table.rs

@@ -595,14 +595,6 @@ pub trait BaseTable: std::fmt::Display + std::fmt::Debug + Send + Sync {
         query: &AnyQuery,
         options: QueryExecutionOptions,
     ) -> Result<String>;
-    /// Whether [`BaseTable::analyze_plan`] is provided by a remote service.
-    ///
-    /// Client-side query wrappers use this to preserve backend metrics and
-    /// distributed-analysis options instead of replacing them with a local plan.
-    #[doc(hidden)]
-    fn analyze_plan_is_remote(&self) -> bool {
-        false
-    }
 
     /// Add new records to the table.
     async fn add(&self, add: AddDataBuilder) -> Result<AddResult>;
@@ -1660,9 +1652,9 @@ impl Table {
     /// Offsets are useful for sampling as the set of all valid offsets is easily
     /// known in advance to be [0, len(table)).
     ///
-    /// No guarantees are made regarding the order in which results are returned.
-    /// Repeated offsets produce repeated rows, which makes this method suitable for
-    /// sampling with replacement.
+    /// No guarantees are made regarding the order in which results are returned.  If you
+    /// desire an output order that matches the order of the given offsets, you will need
+    /// to add the row offset column to the output and align it yourself.
     ///
     /// Parameters
     /// ----------

rust/lancedb/src/table/query.rs

@@ -17,6 +17,7 @@ use arrow::array::{AsArray, FixedSizeListBuilder, Float32Builder};
 use arrow::datatypes::{Float32Type, UInt8Type};
 use arrow_array::Array;
 use arrow_schema::{DataType, Schema};
+use datafusion_common::{Column, DataFusionError, SchemaError};
 use datafusion_physical_plan::ExecutionPlan;
 use datafusion_physical_plan::projection::ProjectionExec;
 use datafusion_physical_plan::repartition::RepartitionExec;
@@ -109,7 +110,7 @@ fn requires_local_namespace_execution(query: &AnyQuery) -> bool {
     // pushing these down would silently ignore the user's setting. For use_lsm that
     // is worse than a tuning miss: MemWAL read routing lives only in `create_plan`,
     // so a pushed-down query would return stale base-only data with no error.
-    if query.base().use_lsm.is_some() || query.base().take_offsets.is_some() {
+    if query.base().use_lsm.is_some() {
         return true;
     }
     matches!(
@@ -153,13 +154,6 @@ pub async fn create_plan(
     options: QueryExecutionOptions,
 ) -> Result<Arc<dyn ExecutionPlan>> {
     let query = query.canonicalized()?;
-    if let AnyQuery::Query(request) = &query
-        && let Some(offsets) = &request.take_offsets
-    {
-        return crate::query::create_take_offsets_plan(table, request, offsets, options, false)
-            .await;
-    }
-
     let query = match query {
         AnyQuery::VectorQuery(query) => query,
         AnyQuery::Query(query) => VectorQueryRequest::from_plain_query(query),
@@ -198,7 +192,7 @@ pub async fn create_plan(
     if query.query_vector.len() > 1 {
         if column.is_none() {
             // Infer a vector column with the same dimension of the query vector.
-            let arrow_schema = Schema::from(ds_ref.schema());
+            let arrow_schema = Schema::from(schema);
             column = Some(default_vector_column(
                 &arrow_schema,
                 Some(query.query_vector[0].len() as i32),
@@ -275,7 +269,7 @@ pub async fn create_plan(
         let column = if let Some(col) = column {
             col
         } else {
-            let arrow_schema = Schema::from(ds_ref.schema());
+            let arrow_schema = Schema::from(schema);
             default_vector_column(&arrow_schema, Some(query_vector.len() as i32))?
         };
 
@@ -381,7 +375,97 @@ pub async fn create_plan(
         scanner.order_by(Some(order_by.clone()))?;
     }
 
-    Ok(scanner.create_plan().await?)
+    scanner
+        .create_plan()
+        .await
+        .map_err(|error| enrich_lance_field_not_found(error, schema))
+}
+
+/// Replace DataFusion's top-level field candidates with qualified leaf paths.
+///
+/// DataFusion resolves nested fields but its `FieldNotFound` error only lists the
+/// top-level Arrow fields. This makes a missing leaf look unavailable even when it
+/// exists below a struct. Keep every other Lance/DataFusion error unchanged and
+/// enrich only this one schema error at the LanceDB query boundary.
+fn enrich_lance_field_not_found(
+    error: lance::Error,
+    schema: &lance_core::datatypes::Schema,
+) -> Error {
+    let Some(field) = find_missing_field(&error) else {
+        return error.into();
+    };
+    field_not_found_error(field, &Schema::from(schema))
+}
+
+fn field_not_found_diagnostic(
+    error: &(dyn std::error::Error + 'static),
+    schema: &Schema,
+) -> Option<Error> {
+    let field = find_missing_field(error)?;
+    Some(field_not_found_error(field, schema))
+}
+
+fn field_not_found_error(field: &Column, schema: &Schema) -> Error {
+    let valid_fields = leaf_field_paths(schema);
+    let mut message = format!("Schema error: No field named {}", field.quoted_flat_name());
+    if !valid_fields.is_empty() {
+        message.push_str(". Valid fields are ");
+        message.push_str(&valid_fields.join(", "));
+    }
+    message.push('.');
+
+    Error::InvalidInput { message }
+}
+
+fn find_missing_field<'a>(error: &'a (dyn std::error::Error + 'static)) -> Option<&'a Column> {
+    if let Some(DataFusionError::SchemaError(schema_error, _)) =
+        error.downcast_ref::<DataFusionError>()
+        && let SchemaError::FieldNotFound { field, .. } = schema_error.as_ref()
+    {
+        return Some(field);
+    }
+
+    error.source().and_then(find_missing_field)
+}
+
+fn leaf_field_paths(schema: &Schema) -> Vec<String> {
+    fn format_segment(segment: &str) -> String {
+        // Quote every segment instead of maintaining a SQL keyword list. Bare
+        // lowercase names such as `true` can be parsed as expressions rather
+        // than identifiers, while backticks preserve all field names in both
+        // local SQL parsers.
+        format!("`{}`", segment.replace('`', "``"))
+    }
+
+    fn visit(fields: &arrow_schema::Fields, path: &mut Vec<String>, paths: &mut Vec<String>) {
+        for field in fields {
+            // Neither local planner can address an empty field-path segment,
+            // even when it is backtick-quoted. Do not advertise leaves beneath
+            // such a segment as valid filter fields.
+            if field.name().is_empty() {
+                continue;
+            }
+            path.push(field.name().clone());
+            match field.data_type() {
+                DataType::Struct(children) if !children.is_empty() => {
+                    visit(children, path, paths);
+                }
+                _ => {
+                    paths.push(
+                        path.iter()
+                            .map(|segment| format_segment(segment))
+                            .collect::<Vec<_>>()
+                            .join("."),
+                    );
+                }
+            }
+            path.pop();
+        }
+    }
+
+    let mut paths = Vec::new();
+    visit(schema.fields(), &mut Vec::new(), &mut paths);
+    paths
 }
 
 //Helper functions below
@@ -741,7 +825,10 @@ async fn parse_arrow_ipc_response(bytes: bytes::Bytes) -> Result<DatasetRecordBa
 #[cfg(test)]
 #[allow(deprecated)]
 mod tests {
-    use arrow_array::{ArrayRef, FixedSizeListArray, Float32Array};
+    use arrow_array::{
+        ArrayRef, FixedSizeListArray, Float32Array, Int32Array, RecordBatch, StringArray,
+        StructArray,
+    };
     use futures::TryStreamExt;
     use lance_arrow::FixedSizeListArrayExt;
     use std::sync::{
@@ -750,7 +837,7 @@ mod tests {
     };
 
     use super::*;
-    use crate::query::{QueryExecutionOptions, QueryRequest};
+    use crate::query::{ExecutableQuery, QueryBase, QueryExecutionOptions, QueryRequest};
     use crate::table::BaseTable;
 
     fn fixed_size_list_array(values: Vec<f32>, dimension: i32) -> FixedSizeListArray {
@@ -891,7 +978,6 @@ mod tests {
     async fn test_execute_query_local_routing() {
         use crate::connect;
         use crate::table::query::execute_query;
-        use arrow_array::{Int32Array, RecordBatch};
         use arrow_schema::{DataType, Field, Schema};
 
         let conn = connect("memory://").execute().await.unwrap();
@@ -931,6 +1017,164 @@ mod tests {
         assert_eq!(count, 2); // 4 and 5
     }
 
+    #[tokio::test]
+    async fn test_missing_filter_field_lists_nested_fields_in_local_planners() {
+        use crate::connect;
+        use arrow_schema::{DataType, Field, Schema};
+
+        let conn = connect("memory://").execute().await.unwrap();
+        let metadata = Arc::new(StructArray::from(vec![
+            (
+                Arc::new(Field::new("year", DataType::Int32, false)),
+                Arc::new(Int32Array::from(vec![2024])) as ArrayRef,
+            ),
+            (
+                Arc::new(Field::new("genre", DataType::Utf8, false)),
+                Arc::new(StringArray::from(vec!["fiction"])) as ArrayRef,
+            ),
+            (
+                Arc::new(Field::new("Title", DataType::Int32, false)),
+                Arc::new(Int32Array::from(vec![7])) as ArrayRef,
+            ),
+            (
+                Arc::new(Field::new("true", DataType::Int32, false)),
+                Arc::new(Int32Array::from(vec![8])) as ArrayRef,
+            ),
+            (
+                Arc::new(Field::new("", DataType::Int32, false)),
+                Arc::new(Int32Array::from(vec![10])) as ArrayRef,
+            ),
+        ]));
+        let vector = Arc::new(fixed_size_list_array(vec![0.0, 1.0], 2));
+        let schema = Arc::new(Schema::new(vec![
+            Field::new("id", DataType::Int32, false),
+            Field::new("vector", vector.data_type().clone(), false),
+            Field::new("content", DataType::Utf8, false),
+            Field::new("metadata", metadata.data_type().clone(), false),
+        ]));
+        let batch = RecordBatch::try_new(
+            schema,
+            vec![
+                Arc::new(Int32Array::from(vec![1])),
+                vector,
+                Arc::new(StringArray::from(vec!["example"])),
+                metadata,
+            ],
+        )
+        .unwrap();
+        let table = conn
+            .create_table("nested_error", batch)
+            .execute()
+            .await
+            .unwrap();
+
+        let error = table
+            .query()
+            .only_if("year = 2024")
+            .execute()
+            .await
+            .err()
+            .expect("query should reject the unqualified nested field");
+        let case_sensitive_path = "`metadata`.`Title`";
+        let keyword_path = "`metadata`.`true`";
+        let expected = format!(
+            "No field named year. Valid fields are `id`, `vector`, `content`, `metadata`.`year`, `metadata`.`genre`, {case_sensitive_path}, {keyword_path}."
+        );
+
+        assert!(
+            error.to_string().contains(&expected),
+            "unexpected error: {error}"
+        );
+        for (path, value) in [(case_sensitive_path, 7), (keyword_path, 8)] {
+            table
+                .query()
+                .only_if(format!("{path} = {value}"))
+                .execute()
+                .await
+                .expect("the path advertised by the diagnostic should be reusable");
+        }
+
+        table.set_unenforced_primary_key(["id"]).await.unwrap();
+        table
+            .set_lsm_write_spec(crate::table::LsmWriteSpec::unsharded())
+            .await
+            .unwrap();
+        let lsm_error = table
+            .query()
+            .only_if("year = 2024")
+            .execute()
+            .await
+            .err()
+            .expect("LSM query should reject the unqualified nested field");
+
+        assert!(
+            lsm_error.to_string().contains(&expected),
+            "unexpected LSM error: {lsm_error}"
+        );
+        for (path, value) in [(case_sensitive_path, 7), (keyword_path, 8)] {
+            table
+                .query()
+                .only_if(format!("{path} = {value}"))
+                .execute()
+                .await
+                .expect("the path advertised by the diagnostic should be reusable in LSM queries");
+        }
+    }
+
+    #[test]
+    fn test_leaf_field_paths_preserve_arbitrary_depth() {
+        use arrow_schema::{DataType, Field, Schema};
+
+        fn nested_field(path: &[&str]) -> Field {
+            let mut segments = path.iter().rev();
+            let mut field = Field::new(
+                *segments.next().expect("path must have a leaf"),
+                DataType::Int32,
+                false,
+            );
+            for segment in segments {
+                field = Field::new(*segment, DataType::Struct(vec![field].into()), false);
+            }
+            field
+        }
+
+        let schema = Schema::new(vec![
+            nested_field(&["a", "b", "c", "d", "e"]),
+            nested_field(&["metadata", "child.with.dot"]),
+            nested_field(&["metadata", "Title"]),
+            nested_field(&["metadata", "123child"]),
+            nested_field(&["metadata", "child`tick"]),
+            nested_field(&["metadata", ""]),
+            nested_field(&["", "child"]),
+        ]);
+
+        assert_eq!(
+            leaf_field_paths(&schema),
+            vec![
+                "`a`.`b`.`c`.`d`.`e`",
+                "`metadata`.`child.with.dot`",
+                "`metadata`.`Title`",
+                "`metadata`.`123child`",
+                "`metadata`.`child``tick`",
+            ]
+        );
+
+        let source = DataFusionError::SchemaError(
+            Box::new(SchemaError::FieldNotFound {
+                field: Box::new(Column::from_name("missing")),
+                valid_fields: Vec::new(),
+            }),
+            Box::new(None),
+        );
+        let error = field_not_found_diagnostic(&source, &schema).unwrap();
+        assert!(
+            error.to_string().contains(
+                "Valid fields are `a`.`b`.`c`.`d`.`e`, `metadata`.`child.with.dot`, `metadata`.`Title`, `metadata`.`123child`, `metadata`.`child``tick`"
+            ),
+            "unexpected error: {error}"
+        );
+    }
+
     #[derive(Debug, Default)]
     struct CountingNamespaceClient {
         query_table_calls: AtomicUsize,

rust/lancedb/src/table/query/lsm.rs

@@ -27,6 +27,8 @@ use std::sync::Arc;
 
 use arrow_array::Array;
 use arrow_schema::{DataType, Schema as ArrowSchema};
+use datafusion::common::{DataFusionError, ToDFSchema};
+use datafusion::prelude::SessionContext;
 use datafusion_physical_plan::expressions::Column;
 use datafusion_physical_plan::projection::ProjectionExec;
 use datafusion_physical_plan::{ExecutionPlan, PhysicalExpr};
@@ -391,7 +393,21 @@ fn base_scanner(
     }
     if let Some(filter) = &query.base.filter {
         scanner = match filter {
-            QueryFilter::Sql(sql) => scanner.filter(sql)?,
+            QueryFilter::Sql(sql) => {
+                // Parse here instead of inside `LsmScanner::filter` so the typed
+                // DataFusion `FieldNotFound` error is still available for the
+                // same nested-field enrichment used by the ordinary scanner.
+                let schema = ArrowSchema::from(dataset.schema());
+                let df_schema = schema.clone().to_dfschema().map_err(|error| {
+                    enrich_filter_error(error, &schema, "Failed to create DFSchema")
+                })?;
+                let expr = SessionContext::new()
+                    .parse_sql_expr(sql, &df_schema)
+                    .map_err(|error| {
+                        enrich_filter_error(error, &schema, "Failed to parse filter expression")
+                    })?;
+                scanner.filter_expr(expr)
+            }
             QueryFilter::Datafusion(expr) => scanner.filter_expr(expr.clone()),
             QueryFilter::Substrait(_) => {
                 return Err(Error::NotSupported {
@@ -403,6 +419,12 @@ fn base_scanner(
     Ok(scanner)
 }
 
+fn enrich_filter_error(error: DataFusionError, schema: &ArrowSchema, context: &str) -> Error {
+    super::field_not_found_diagnostic(&error, schema).unwrap_or_else(|| Error::InvalidInput {
+        message: format!("{context}: {error}"),
+    })
+}
+
 /// Plain scan: filter / projection / limit over base ∪ SSTables ∪ in-memory.
 /// The plain scan applies limit and offset inside the planner.
 async fn plain_plan(