Add string support to min/max operators (#1120)

KyleAMathews · claude · web-flow · commit bdf94059e7ab · 2026-01-19T09:54:34.000-07:00
* feat(db): add string support to min/max aggregate functions

Add support for string values in min() and max() aggregate functions,
using lexicographic comparison (same as SQL behavior).

Changes:
- Add `string` to CanMinMax type in db-ivm/groupBy.ts
- Update AggregateReturnType to include string in db/builder/functions.ts
- Update valueExtractor in group-by.ts compiler to preserve strings
- Add test case for min/max on string fields

* chore: change string min/max changeset to patch

Co-Authored-By: Claude Opus 4.5 &lt;noreply@anthropic.com&gt;

* refactor(db): simplify group-by compiler code

- Flatten nested ternary in valueExtractor for readability
- Remove dead validation code in replaceAggregatesByRefs ref case
- Condense docstring to reflect actual behavior

Co-Authored-By: Claude Opus 4.5 &lt;noreply@anthropic.com&gt;

---------

Co-authored-by: Claude &lt;noreply@anthropic.com&gt;
diff --git a/.changeset/string-min-max-support.md b/.changeset/string-min-max-support.md
@@ -0,0 +1,6 @@
+---
+'@tanstack/db': patch
+'@tanstack/db-ivm': patch
+---
+
+Add string support to `min()` and `max()` aggregate functions. These functions now work with strings using lexicographic comparison, matching standard SQL behavior.
diff --git a/packages/db-ivm/src/operators/groupBy.ts b/packages/db-ivm/src/operators/groupBy.ts
@@ -211,7 +211,7 @@ export function avg<T>(
   }
 }
 
-type CanMinMax = number | Date | bigint
+type CanMinMax = number | Date | bigint | string
 
 /**
  * Creates a min aggregate function that computes the minimum value in a group
diff --git a/packages/db/src/query/builder/functions.ts b/packages/db/src/query/builder/functions.ts
@@ -53,10 +53,10 @@ type ExtractType<T> =
 // Helper type to determine aggregate return type based on input nullability
 type AggregateReturnType<T> =
   ExtractType<T> extends infer U
-    ? U extends number | undefined | null | Date | bigint
+    ? U extends number | undefined | null | Date | bigint | string
       ? Aggregate<U>
-      : Aggregate<number | undefined | null | Date | bigint>
-    : Aggregate<number | undefined | null | Date | bigint>
+      : Aggregate<number | undefined | null | Date | bigint | string>
+    : Aggregate<number | undefined | null | Date | bigint | string>
 
 // Helper type to determine string function return type based on input nullability
 type StringFunctionReturnType<T> =
diff --git a/packages/db/src/query/compiler/group-by.ts b/packages/db/src/query/compiler/group-by.ts
@@ -353,20 +353,28 @@ function getAggregateFunction(aggExpr: Aggregate) {
   const valueExtractor = ([, namespacedRow]: [string, NamespacedRow]) => {
     const value = compiledExpr(namespacedRow)
     // Ensure we return a number for numeric aggregate functions
-    return typeof value === `number` ? value : value != null ? Number(value) : 0
+    if (typeof value === `number`) {
+      return value
+    }
+    return value != null ? Number(value) : 0
   }
 
-  // Create a value extractor function for the expression to aggregate
-  const valueExtractorWithDate = ([, namespacedRow]: [
+  // Create a value extractor function for min/max that preserves comparable types
+  const valueExtractorForMinMax = ([, namespacedRow]: [
     string,
     NamespacedRow,
   ]) => {
     const value = compiledExpr(namespacedRow)
-    return typeof value === `number` || value instanceof Date
-      ? value
-      : value != null
-        ? Number(value)
-        : 0
+    // Preserve strings, numbers, Dates, and bigints for comparison
+    if (
+      typeof value === `number` ||
+      typeof value === `string` ||
+      typeof value === `bigint` ||
+      value instanceof Date
+    ) {
+      return value
+    }
+    return value != null ? Number(value) : 0
   }
 
   // Create a raw value extractor function for the expression to aggregate
@@ -383,9 +391,9 @@ function getAggregateFunction(aggExpr: Aggregate) {
     case `avg`:
       return avg(valueExtractor)
     case `min`:
-      return min(valueExtractorWithDate)
+      return min(valueExtractorForMinMax)
     case `max`:
-      return max(valueExtractorWithDate)
+      return max(valueExtractorForMinMax)
     default:
       throw new UnsupportedAggregateFunctionError(aggExpr.name)
   }
@@ -394,20 +402,15 @@ function getAggregateFunction(aggExpr: Aggregate) {
 /**
  * Transforms expressions to replace aggregate functions with references to computed values.
  *
- * This function is used in both ORDER BY and HAVING clauses to transform expressions that reference:
- * 1. Aggregate functions (e.g., `max()`, `count()`) - replaces with references to computed aggregates in SELECT
- * 2. SELECT field references via $selected namespace (e.g., `$selected.latestActivity`) - validates and passes through unchanged
- *
- * For aggregate expressions, it finds matching aggregates in the SELECT clause and replaces them with
- * PropRef([resultAlias, alias]) to reference the computed aggregate value.
+ * For aggregate expressions, finds matching aggregates in the SELECT clause and replaces them
+ * with PropRef([resultAlias, alias]) to reference the computed aggregate value.
  *
- * For ref expressions using the $selected namespace, it validates that the field exists in the SELECT clause
- * and passes them through unchanged (since $selected is already the correct namespace). All other ref expressions
- * are passed through unchanged (treating them as table column references).
+ * Ref expressions (table columns and $selected fields) and value expressions are passed through unchanged.
+ * Function expressions are recursively transformed.
  *
  * @param havingExpr - The expression to transform (can be aggregate, ref, func, or val)
  * @param selectClause - The SELECT clause containing aliases and aggregate definitions
- * @param resultAlias - The namespace alias for SELECT results (default: '$selected', used for aggregate references)
+ * @param resultAlias - The namespace alias for SELECT results (default: '$selected')
  * @returns A transformed BasicExpression that references computed values instead of raw expressions
  */
 export function replaceAggregatesByRefs(
@@ -439,41 +442,11 @@ export function replaceAggregatesByRefs(
       return new Func(funcExpr.name, transformedArgs)
     }
 
-    case `ref`: {
-      const refExpr = havingExpr
-      const path = refExpr.path
-
-      if (path.length === 0) {
-        // Empty path - pass through
-        return havingExpr as BasicExpression
-      }
-
-      // Check if this is a $selected reference
-      if (path.length > 0 && path[0] === `$selected`) {
-        // Extract the field path after $selected
-        const fieldPath = path.slice(1)
-
-        if (fieldPath.length === 0) {
-          // Just $selected without a field - pass through unchanged
-          return havingExpr as BasicExpression
-        }
-
-        // Verify the field exists in SELECT clause
-        const alias = fieldPath.join(`.`)
-        if (alias in selectClause) {
-          // Pass through unchanged - $selected is already the correct namespace
-          return havingExpr as BasicExpression
-        }
-
-        // Field doesn't exist in SELECT - this is an error, but we'll pass through for now
-        // (Could throw an error here in the future)
-        return havingExpr as BasicExpression
-      }
-
-      // Not a $selected reference - this is a table column reference, pass through unchanged
-      // SELECT fields should only be accessed via $selected namespace
+    case `ref`:
+      // Ref expressions are passed through unchanged - they reference either:
+      // - $selected fields (which are already in the correct namespace)
+      // - Table column references (which remain valid)
       return havingExpr as BasicExpression
-    }
 
     case `val`:
       // Return as-is
diff --git a/packages/db/tests/query/group-by.test.ts b/packages/db/tests/query/group-by.test.ts
@@ -402,6 +402,49 @@ function createGroupByTests(autoIndex: `off` | `eager`): void {
         expect(books?.order_count).toBe(3)
         expect(books?.total_amount).toBe(800) // 150+250+400
       })
+
+      test(`min/max on string fields`, () => {
+        const categorySummary = createLiveQueryCollection({
+          startSync: true,
+          query: (q) =>
+            q
+              .from({ orders: ordersCollection })
+              .groupBy(({ orders }) => orders.customer_id)
+              .select(({ orders }) => ({
+                customer_id: orders.customer_id,
+                first_status: min(orders.status), // alphabetically first
+                last_status: max(orders.status), // alphabetically last
+                first_category: min(orders.product_category),
+                last_category: max(orders.product_category),
+              })),
+        })
+
+        expect(categorySummary.size).toBe(3) // 3 customers
+
+        // Customer 1: orders 1, 2, 7 (statuses: completed, completed, completed; categories: electronics, electronics, books)
+        const customer1 = categorySummary.get(1)
+        expect(customer1?.customer_id).toBe(1)
+        expect(customer1?.first_status).toBe(`completed`) // all completed
+        expect(customer1?.last_status).toBe(`completed`)
+        expect(customer1?.first_category).toBe(`books`) // alphabetically books < electronics
+        expect(customer1?.last_category).toBe(`electronics`)
+
+        // Customer 2: orders 3, 4 (statuses: pending, completed; categories: books, electronics)
+        const customer2 = categorySummary.get(2)
+        expect(customer2?.customer_id).toBe(2)
+        expect(customer2?.first_status).toBe(`completed`) // alphabetically completed < pending
+        expect(customer2?.last_status).toBe(`pending`)
+        expect(customer2?.first_category).toBe(`books`)
+        expect(customer2?.last_category).toBe(`electronics`)
+
+        // Customer 3: orders 5, 6 (statuses: pending, cancelled; categories: books, electronics)
+        const customer3 = categorySummary.get(3)
+        expect(customer3?.customer_id).toBe(3)
+        expect(customer3?.first_status).toBe(`cancelled`) // alphabetically cancelled < pending
+        expect(customer3?.last_status).toBe(`pending`)
+        expect(customer3?.first_category).toBe(`books`)
+        expect(customer3?.last_category).toBe(`electronics`)
+      })
     })
 
     describe(`Multiple Column Grouping`, () => {

Original file line number	Diff line number	Diff line change
`@@ -211,7 +211,7 @@ export function avg<T>(`
`211`	`211`	`}`
`212`	`212`	`}`
`213`	`213`
`214`		`-type CanMinMax = number \| Date \| bigint`
	`214`	`+type CanMinMax = number \| Date \| bigint \| string`
`215`	`215`
`216`	`216`	`/**`
`217`	`217`	`* Creates a min aggregate function that computes the minimum value in a group`