seekDB SQL Diagnoser

Diagnose a seekDB SQL problem from evidence, make the smallest safe correction, and verify that the result remains semantically correct.

Workflow

1. Establish the scope.

   • Record the exact SQL, complete error text, expected result, seekDB version, and client or driver.
   • Obtain SHOW CREATE TABLE and SHOW INDEX output for every referenced table when available.
   • Identify whether the environment is disposable, staging, or production before running commands.

2. Reproduce safely.

   • Prefer a read-only session and a minimal dataset.
   • Replace sensitive literal values before sharing diagnostics.
   • Do not run mutating statements merely to reproduce an error.

3. Classify the failure.

   • Syntax or compatibility: reduce the statement until the unsupported construct is isolated.
   • Name or schema resolution: verify the selected database, table names, aliases, columns, and case sensitivity.
   • Type or value conversion: inspect column types, collations, time zones, nullability, and implicit casts.
   • Transaction or connection: inspect autocommit behavior, isolation expectations, timeouts, and the first server-side error.
   • Performance: capture an EXPLAIN plan using a format supported by the connected seekDB version.

4. Analyze a slow query.

   • Compare predicates, join keys, grouping keys, ordering keys, and the available indexes.
   • Look for full scans, unexpectedly large row estimates, repeated work, temporary results, and avoidable sorts.
   • Check for functions or implicit casts on indexed columns, leading-wildcard searches, unbounded result sets, and unnecessary selected columns.
   • For composite indexes, align leading columns with selective equality predicates, then range, join, grouping, or ordering needs as appropriate.
   • Avoid proposing duplicate or overly wide indexes without considering write amplification and storage cost.

5. Apply the smallest fix.

   • Correct SQL semantics before tuning performance.
   • Prefer a query rewrite when an existing index can support it clearly.
   • Propose schema or index changes separately and explain their read/write tradeoff.
   • Label version-dependent syntax and provide a conservative fallback.

6. Verify the result.

   • Compare row counts and representative rows before and after the change.
   • Test nulls, duplicates, empty inputs, boundary values, and relevant concurrency behavior.
   • Compare execution plans and repeat latency measurements after warm-up; report multiple samples instead of one timing.
   • State what was verified locally and what still requires validation in the target environment.

Output Format

Return findings in this order:

1. Diagnosis — the evidence-backed root cause.
2. Minimal fix — corrected SQL or the smallest configuration/schema change.
3. Why it works — the semantic or plan-level explanation.
4. Verification — commands and expected observations.
5. Risks — compatibility, locking, write cost, or rollback considerations.

Safety Rules

• Never request or expose passwords, access tokens, private keys, or full production data.
• Do not execute DROP, TRUNCATE, unrestricted DELETE or UPDATE, destructive migrations, or service restarts without explicit authorization and a recovery plan.
• Treat EXPLAIN ANALYZE as potentially executing the statement; use it only when supported and safe for that exact statement.
• Do not claim a performance gain without comparable measurements.

<!-- OB-S:Mzy-paNyklw8u5faG-_h1A -->