# Bug 246641: Zero SQLite replica reorders compound primary key columns in ORDER BY

- Status: closed
- Project: Zero
- Creator: @arv
- Assignee: @arv
- Created: 2026-03-26T14:04:30Z
- Modified: 2026-04-14T09:41:06Z
- URL: https://bugs.rocicorp.dev/p/zero/issue/246641

## Description

https://discord.com/channels/830183651022471199/1486429069850443827

**Summary**

When using a compound primary key, the Zero SQLite replica appears to reorder the key columns alphabetically in generated queries, instead of respecting the order defined in the schema. This leads SQLite to require an extra temp B-tree for the ORDER BY, even though the schema’s primary key order should allow an index-only plan.

**Details**

Schema (simplified):

- Table: `connected_calls`
- Primary key (as defined in Zero schema):

  primaryKey: ["callId", "userId", "connectionId"]

Expected behavior:

- The zero syncer / SQLite replica should generate queries whose `ORDER BY` matches the defined primary key column order: `call_id`, `user_id`, `connection_id`, so that SQLite can fully leverage the primary key index without additional sorting.

Observed behavior:

- The generated query and plan show the columns ordered alphabetically in the ORDER BY:

  connected_calls vended:
    'SELECT ... FROM "connected_calls" WHERE "call_id" = ? ORDER BY "call_id" asc, "connection_id" asc, "user_id" asc': 309

  Query Plan:
  query SELECT "call_id","user_id","connection_id","_0_version" FROM "connected_calls" WHERE "call_id" = ? ORDER BY "call_id" asc, "connection_id" asc, "user_id" asc
  SEARCH connected_calls USING INDEX connected_calls_call_id_user_id_connection_id_pk (call_id=?)
  USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY

- Note that the `ORDER BY` is: `call_id`, `connection_id`, `user_id` (alphabetical), **not** `call_id`, `user_id`, `connection_id` as defined in the schema.

Impact:

- SQLite cannot fully satisfy the ORDER BY using the primary key index alone and instead builds a temp B-tree for the last two terms of the ORDER BY.
- This is likely suboptimal for performance and surprising given the schema’s primary key definition.
- As a workaround, I can reorder my primary key fields alphabetically in the Zero schema, but this is undesirable and couples schema design to this behavior.

**Why this might be a bug**

- The schema clearly defines the primary key ordering as [`callId`, `userId`, `connectionId`], but the generated ORDER BY appears to be sorted alphabetically by column name.
- This suggests that somewhere in the Zero syncer / query generation, the ORDER BY terms for compound keys may be being sorted instead of preserving the original key order.

**Workaround**

- Define the primary key fields in the schema in alphabetical order:

  primaryKey: ["callId", "connectionId", "userId"]

- This makes the generated ORDER BY match the index order and avoids the extra temp B-tree, but forces an arbitrary naming-driven ordering on the schema.

**Requested**

- Confirm whether this is an unintended behavior in the Zero syncer / SQLite replica.
- If so, ensure that the ORDER BY for compound primary keys preserves the order defined in the Zero schema, so that SQLite can use the primary key index efficiently without building a temp B-tree.

## Comments (1)

### @arv — 2026-04-14T09:07:12Z

https://github.com/rocicorp/mono/pull/5807