Compare two query results row by row #3625

Problem

Two query results can be kept, but not compared. Pinning keeps a result from being replaced, and features/query-results.mdx says that "is what makes them useful for comparing two runs", but the comparing is done by eye, flipping between Result 1 of 2 and Result 2 of 2 in the chooser.

Compare & Sync has a row diff (features/compare-sync.mdx, Comparing rows), built on a key-ordered walk in apple/TablePro/Core/Compare/DataDiffEngine.swift. It compares whole tables between two databases, not the output of a query. So checking that a rewritten query returns the same rows as the old one, or that staging and production agree on a report, has no tool.

Proposed solution

• Compare With… in the result chooser: pick another result in the same tab, or a pinned result in another tab, including a tab on another connection.
• Rows match on key columns the user picks. When a result comes from one table with a primary key, that key is the default; otherwise whole rows are matched, counting duplicates.
• Columns match by name. A column on one side only is listed, not compared.
• The review grid follows the Compare & Sync Rows tab: only left, only right, and changed rows with each differing value marked, with counts and a Show filter for All, Difference and Same.
• Read-only: it compares what the two results hold, writes no SQL, and works on query results that cannot be edited.
• A result cut off at the row cap is compared on the rows it holds, and the comparison says so.

Done means: run a query, pin it, change the query, run it again, choose Compare With… on the pinned result, and see exactly the rows that changed.

Alternatives considered

Export both results to CSV and diff the files, or flip between pinned results.

Platform

macOS

Related database type

N/A / General