Fix various comparison key bugs

stack-auth · N2D4 · Mar 24, 2026 · Mar 24, 2026 · Mar 24, 2026 · Mar 24, 2026
commit 2d49d2308fc877b18c5a90f2381ecb2a420cd7d2
diff --git a/apps/backend/src/lib/bulldozer/db/bulldozer-sort-helpers-sql.ts b/apps/backend/src/lib/bulldozer/db/bulldozer-sort-helpers-sql.ts
@@ -570,13 +570,17 @@ export const BULLDOZER_SORT_HELPERS_SQL = deindent`
   END;
   $$;
 
-  CREATE OR REPLACE FUNCTION pg_temp.bulldozer_sort_bulk_init_from_table(groups_path jsonb[], source_table_name text)
+  CREATE OR REPLACE FUNCTION pg_temp.bulldozer_sort_bulk_init_from_table(groups_path jsonb[], source_table_name text, compare_sort_keys_sql text)
   RETURNS text LANGUAGE plpgsql AS $$
   DECLARE
     current_group_key jsonb;
     ordered_rows jsonb[];
     root_row_identifier text;
     row_count integer;
+    is_order_compatible boolean;
+    current_index integer;
+    cmp integer;
+    current_row jsonb;
   BEGIN
     FOR current_group_key IN EXECUTE format('SELECT DISTINCT "groupKey" FROM %I', source_table_name)
     LOOP
@@ -593,22 +597,52 @@ export const BULLDOZER_SORT_HELPERS_SQL = deindent`
         CONTINUE;
       END IF;
 
-      root_row_identifier := pg_temp.bulldozer_sort_build_balanced_group(
-        groups_path,
-        current_group_key,
-        ordered_rows,
-        1,
-        row_count,
-        1
-      );
-      PERFORM pg_temp.bulldozer_sort_put_group_metadata(
-        groups_path,
-        current_group_key,
-        root_row_identifier,
-        ordered_rows[1]->>'rowIdentifier',
-        ordered_rows[row_count]->>'rowIdentifier',
-        row_count
-      );
+      is_order_compatible := TRUE;
+      FOR current_index IN 2..row_count
+      LOOP
+        cmp := pg_temp.bulldozer_sort_compare_row_keys(
+          compare_sort_keys_sql,
+          ordered_rows[current_index - 1]->'rowSortKey',
+          ordered_rows[current_index - 1]->>'rowIdentifier',
+          ordered_rows[current_index]->'rowSortKey',
+          ordered_rows[current_index]->>'rowIdentifier'
+        );
+        IF cmp > 0 THEN
+          is_order_compatible := FALSE;
+          EXIT;
+        END IF;
+      END LOOP;
+
+      IF is_order_compatible THEN
+        root_row_identifier := pg_temp.bulldozer_sort_build_balanced_group(
+          groups_path,
+          current_group_key,
+          ordered_rows,
+          1,
+          row_count,
+          1
+        );
+        PERFORM pg_temp.bulldozer_sort_put_group_metadata(
+          groups_path,
+          current_group_key,
+          root_row_identifier,
+          ordered_rows[1]->>'rowIdentifier',
+          ordered_rows[row_count]->>'rowIdentifier',
+          row_count
+        );
+      ELSE
+        FOREACH current_row IN ARRAY ordered_rows
+        LOOP
+          PERFORM pg_temp.bulldozer_sort_insert(
+            groups_path,
+            current_group_key,
+            compare_sort_keys_sql,
+            current_row->>'rowIdentifier',
+            current_row->'rowSortKey',
+            current_row->'rowData'
+          );
+        END LOOP;
+      END IF;
     END LOOP;
 
     RETURN source_table_name;

diff --git a/apps/backend/src/lib/bulldozer/db/index.perf.test.ts b/apps/backend/src/lib/bulldozer/db/index.perf.test.ts
@@ -847,18 +847,21 @@ describe.sequential("bulldozer db performance (real postgres)", () => {
     expect(filteredDeltaRows.map((row) => ({ rowIdentifier: row.rowidentifier, rowData: row.rowdata }))).toEqual([
       { rowIdentifier: "seed-100000:1", rowData: { team: "delta", value: 999 } },
     ]);
+    const groupedSubsetSql = `
+      SELECT *
+      FROM (${toQueryableSqlQuery(groupedByTeam.listRowsInGroup({
+        groupKey: expr(`to_jsonb('beta'::text)`),
+        start: "start",
+        end: "end",
+        startInclusive: true,
+        endInclusive: true,
+      }))}) AS "rows"
+      LIMIT ${LOAD_SUBSET_ITERATION_ROW_COUNT}
+    `;
+    // Warm once so we measure steady-state subset iteration instead of first-touch planner/cache cost.
+    await sql.unsafe(groupedSubsetSql);
     const groupedSubsetFromStart = await measureMs(`load iterate groupedByTeam subset from start (${LOAD_SUBSET_ITERATION_ROW_COUNT} rows)`, async () => {
-      return await sql.unsafe(`
-        SELECT *
-        FROM (${toQueryableSqlQuery(groupedByTeam.listRowsInGroup({
-          groupKey: expr(`to_jsonb('beta'::text)`),
-          start: "start",
-          end: "end",
-          startInclusive: true,
-          endInclusive: true,
-        }))}) AS "rows"
-        LIMIT ${LOAD_SUBSET_ITERATION_ROW_COUNT}
-      `);
+      return await sql.unsafe(groupedSubsetSql);
     });
     expect(groupedSubsetFromStart.elapsedMs).toBeLessThanOrEqual(LOAD_SUBSET_ITERATION_MAX_MS);
     expect(groupedSubsetFromStart.result).toHaveLength(LOAD_SUBSET_ITERATION_ROW_COUNT);

diff --git a/apps/backend/src/lib/bulldozer/db/index.test.ts b/apps/backend/src/lib/bulldozer/db/index.test.ts
@@ -2882,7 +2882,7 @@ describe.sequential("declareStoredTable (real postgres)", () => {
     ]);
   });
 
-  it.fails("known issue: sortTable bulk init should respect descending comparator", async () => {
+  test("sortTable bulk init respects descending comparator", async () => {
     const { fromTable, groupedTable, sortedTable } = createDescendingSortedTable();
     await runStatements(fromTable.init());
     await runStatements(fromTable.setRow("a1", expr(`'{"team":"alpha","value":1}'::jsonb`)));
@@ -2901,7 +2901,7 @@ describe.sequential("declareStoredTable (real postgres)", () => {
     expect(alphaRows.map((row) => row.rowidentifier)).toEqual(["a3", "a2", "a1"]);
   });
 
-  it.fails("known issue: limitTable should honor source comparator for top-N", async () => {
+  test("limitTable honors source comparator for top-N", async () => {
     const { fromTable, groupedTable, sortedTable, limitedTable } = createDescendingLimitedTable();
     await runStatements(fromTable.init());
     await runStatements(fromTable.setRow("a1", expr(`'{"team":"alpha","value":1}'::jsonb`)));
@@ -2921,7 +2921,7 @@ describe.sequential("declareStoredTable (real postgres)", () => {
     expect(alphaRows.map((row) => row.rowidentifier)).toEqual(["a3", "a2"]);
   });
 
-  it.fails("known issue: lFoldTable read order should match source comparator", async () => {
+  test("lFoldTable read order matches source comparator", async () => {
     const { fromTable, groupedTable, sortedTable, lFoldTable } = createDescendingLFoldTable();
     await runStatements(fromTable.init());
     await runStatements(fromTable.setRow("a1", expr(`'{"team":"alpha","value":1}'::jsonb`)));

diff --git a/apps/backend/src/lib/bulldozer/db/index.ts b/apps/backend/src/lib/bulldozer/db/index.ts
@@ -53,8 +53,8 @@ export function toQueryableSqlQuery(query: SqlQuery): string {
 }
 export function toExecutableSqlStatements(statements: SqlStatement[]): string {
   const requiresSortHelpers = statements.some((statement) => statement.sql.includes("pg_temp.bulldozer_sort_"));
-  const requiresSortSequentialExecutor = requiresSortHelpers;
-  if (!requiresSortSequentialExecutor) {
+  const requiresSequentialExecutor = requiresSortHelpers || statements.some((statement) => statement.requiresSequentialExecution === true);
+  if (!requiresSequentialExecutor) {
     return deindent`
       WITH __dummy_statement_1__ AS (SELECT 1),
       ${statements.map(statement => deindent`
@@ -80,7 +80,7 @@ export function toExecutableSqlStatements(statements: SqlStatement[]): string {
     `;
   }).join("\n\n");
   return deindent`
-    ${BULLDOZER_SORT_HELPERS_SQL}
+    ${requiresSortHelpers ? BULLDOZER_SORT_HELPERS_SQL : ""}
 
     ${executableStatements}
   `;