[BUG] Multi-query and transactional script execution surfaces stale results or strips column headers due to backwards search for non-empty row sets (lastResultWithRows).

Read src/app/api/db/multi-query/route.ts and src/app/api/db/transaction/route.ts, where the main result is selected, then trace how src/hooks/use-query-execution.ts passes it to src/components/ResultsGrid.tsx. Check existing tests for multi-query and transaction responses; add coverage for a final successful statement with zero rows, preserved fields and row counts, and pagination. Done means the final statement's result is shown without stale rows.

Description

When running multiple statements in the Query Editor ("Run Selection" or "Run Script"), requests are dispatched to POST /api/db/multi-query or POST /api/db/transaction.

To determine the primary result object returned to the frontend's ResultsGrid, both handlers use a reverse array search looking for the last statement whose result set contains rows.length > 0:

In src/app/api/db/multi-query/route.ts#L362-L375:

// Return the last successful result with rows as the main result (for ResultsGrid)
const lastResultWithRows = [...results]
  .reverse()
  .find((r) => r.status === "success" && r.rows && r.rows.length > 0);
const hasError = results.some((r) => r.status === "error");

return NextResponse.json({
  // Main result (for backward compatibility with ResultsGrid)
  rows: lastResultWithRows?.rows || [],
  fields: lastResultWithRows?.fields || [],
  rowCount: lastResultWithRows?.rowCount || 0,
  executionTime: totalExecutionTime,
  ...carriedChannels(lastResultWithRows),
  multiStatement: true,
  ...

And identically in src/app/api/db/transaction/route.ts#L124:

const shown = [...statements].reverse().find((r) => r.status === "success" && r.rows && r.rows.length > 0);

Because find() requires rows.length > 0, any final statement that legitimately returns 0 rows (an empty table or query with no matching rows) is skipped. The handler walks backwards and surfaces the output of a prior statement instead.

This causes three severe correctness and UX bugs:

1. Misleading Data & Stale Ghost Rows: If a developer runs a script to clean up or verify records:

   SELECT count(*) FROM expired_tokens; -- Statement 1: returns 100 rows
   DELETE FROM expired_tokens;          -- Statement 2: deletes records
   SELECT count(*) FROM expired_tokens; -- Statement 3: returns 0 rows (empty table)
   

   Statement 3 succeeds and returns rows: [], fields: ['count']. Because rows.length === 0, lastResultWithRows discards Statement 3 and renders the rows from Statement 1! The user sees the old 100 rows in ResultsGrid, leading them to falsely conclude that the DELETE failed.
2. Missing Schema Headers on Empty Result Sets: If a query returns 0 rows (e.g. SELECT id, name, created_at FROM users WHERE id = -1;), single-query mode (POST /api/db/query) returns fields: ["id", "name", "created_at"] and rows: [], allowing ResultsGrid to display the column schema. In multi-query, because lastResultWithRows is undefined, fields falls back to [], completely stripping the table column headers from the UI.
3. Loss of DML Row Counts & Pagination: For scripts ending in UPDATE or INSERT, any rowCount returned by the final statement is overwritten by an earlier statement's rows or set to 0. Additionally, POST /api/db/multi-query omits the pagination object entirely from the response, permanently disabling "Load More" pagination even when statements are truncated by Studio's default bounds.

Steps to Reproduce

1. Open LibreDB Studio Query Editor.
2. Connect to any database (PostgreSQL, MySQL, SQLite, DuckDB, etc.).
3. Paste and run the following multi-statement script:

   SELECT 'Initial Check' AS stage, 10 AS value;
   SELECT 'Post Check' AS stage, 0 AS value WHERE 1 = 0;
   

4. Observe the output in ResultsGrid.
5. Statement 2 executed cleanly, but the ResultsGrid displays stage: 'Initial Check', value: 10.
6. Now run a script with a single query that returns 0 rows:

   SELECT id, username, email FROM (SELECT 1 AS id, 'a' AS username, 'b' AS email) t WHERE 1 = 0;
   -- comment to trigger multi-query splitter
   SELECT 1 WHERE 1 = 0;
   

7. Notice that column headers (id, username, email) completely disappear because fields is returned as [].

Expected Behavior

• The main result should reflect the last executed statement in the script (or the last query statement), retaining its fields metadata even if rows.length === 0.
• If Statement 2 returns an empty result set, ResultsGrid must display Statement 2's column schema with a "0 rows returned" badge, rather than silently resurrecting Statement 1's stale data.
• The UI should ideally expose a tab or dropdown to inspect each individual statement's result from the statements array.

Actual Behavior

• lastResultWithRows skips valid statements that return 0 rows.
• Stale data from prior statements is presented as the final query outcome.
• Column headers disappear (fields: []) when no statement produces rows.

Environment

• LibreDB Studio Version: 0.5.4+ (all versions with multi-statement support)
• Browser: All
• OS: macOS / Linux / Windows
• Database Type: PostgreSQL, MySQL, SQLite, DuckDB, MSSQL, ClickHouse, etc.

Additional Context

• Route handler: src/app/api/db/multi-query/route.ts#L362-L375
• Transaction route handler: src/app/api/db/transaction/route.ts#L124
• Frontend consumer: src/hooks/use-query-execution.ts#L858-L885 and src/components/ResultsGrid.tsx

Possible Solution

1. In src/app/api/db/multi-query/route.ts and src/app/api/db/transaction/route.ts, pick the last successful query statement rather than requiring rows.length > 0:

   // Pick the last successful statement that produced a query result structure (even with 0 rows)
   const lastResultWithFields = [...results]
     .reverse()
     .find((r) => r.status === "success" && Array.isArray(r.fields) && r.fields.length > 0);
   
   const lastSuccessful = [...results].reverse().find((r) => r.status === "success");
   const activeResult = lastResultWithFields ?? lastSuccessful ?? results[results.length - 1];
   
   return NextResponse.json({
     rows: activeResult?.rows || [],
     fields: activeResult?.fields || [],
     rowCount: activeResult?.rowCount ?? activeResult?.rows?.length ?? 0,
     executionTime: totalExecutionTime,
     ...carriedChannels(activeResult),
     multiStatement: true,
     statementCount: statements.length,
     executedCount: results.length,
     hasError,
     statements: results,
   });
   

2. Enhance ResultsGrid / QueryEditor to include a statement switcher bar when result.multiStatement is true, enabling users to click between Statement 1, Statement 2, etc., and inspect each statement's output individually.