Workflow·Databases

Reduce Unoptimized Query Oracle Test Failure

Name: Reduce Unoptimized Query Oracle Test Failure
Author: cockroachdb

Reduce an unoptimized-query-oracle test failure log to the simplest possible reproduction case.

You say

Install this skill Read the source first Free Written by cockroachdb · unverified publisher

Context cost: 4.3k tokensestimated from the bundle, loaded when it triggers
Bundle: 1 file · 17.0 kBtext throughout, nothing executable
Licence: Source-availablefree to use
Last change: —no release on file
Servers it uses: Noneruns standalone

What it does

Reduce an unoptimized-query-oracle test failure log to the simplest possible reproduction case. Use when you have unoptimized-query-oracle*.log files from a failed roachtest and need to find the minimal SQL to reproduce the bug.

Installed, it changes the agent in these ways.

What this skill changes about the agent is not written down here yet. The listing was collected from its source, and the description is in its own SKILL.md.

Workflow

Runs a procedure end to end.

database

Filed under

Databases

The skill itself

This is the whole product. A skill is instructions the model reads, so there is nothing behind the listing you cannot see first — the front matter loads with every session, and the body below it loads when the skill triggers.

SKILL.md17.0 kB · 492 lines

--- name: reduce-unoptimized-query-oracle description: Reduce an unoptimized-query-oracle test failure log to the simplest possible reproduction case. Use when you have unoptimized-query-oracle*.log files from a failed roachtest and need to find the minimal SQL to reproduce the bug. disable-model-invocation: true ---

7# Reduce Unoptimized Query Oracle Test Failure

9Reduce an unoptimized-query-oracle test failure log to the simplest possible

10reproduction case.

12The unoptimized-query-oracle roachtest runs a series of random SQL statements to

13create a random dataset, and then executes a random "Query of Interest" twice,

14with different optimization settings. If the two executions return different

15results, it indicates a bug in CockroachDB.

17## When to Use

19Use this skill when:

20- You have a test failure from the unoptimized-query-oracle roachtest.

21- You need to find the minimal SQL to reproduce the test failure.

23## Step 1: Locate artifacts

25**Ask the user where the artifacts directory is.**

27Find the relevant files in the artifacts directory:

28- **Test parameters**: params.log (the parameters from the roachtest)

29- **Test log**: test.log (the log from the roachtest)

30- **Failure log**: failure*.log (the failure log from the roachtest)

31- **Full SQL log**: unoptimized-query-oracle*.log (the SQL statements that led to failure)

32- **Query of interest log**: unoptimized-query-oracle*.failure.log (containing

33 the query of interest and possibly more information about the failure)

34- **Cockroach log**: logs/1.unredacted/cockroach.log or

35 logs/unredacted/cockroach.log (contains the git commit)

37## Step 2: Determine test configuration

39Determine the git commit from cockroach.log:

40```bash

41grep "binary: CockroachDB" cockroach.log

42```

43Look for the commit hash in the version string (e.g., cb94db961b8f55e3473f279d98ae90f0eeb0adcb).

45Determine if runtime assertions are enabled by checking for:

46- "runtimeAssertionsBuild": "true" in params.log

47- or Runtime assertions enabled in test.log

49Determine if metamorphic settings apply by looking for:

50- lines like these in params.log:

51 ```

52 "metamorphicBufferedSender": "true",

53 "metamorphicWriteBuffering": "true",

54 ```

55- or lines like these in test.log:

56 ```

57 metamorphically setting "kv.rangefeed.buffered_sender.enabled" to 'true'

58 metamorphically setting "kv.transaction.write_buffering.enabled" to 'true'

59 ```

61Determine environment variables from the beginning of cockroach.log:

62```bash

63grep -A10 "using local environment variables:" cockroach.log

64```

66Important environment variables include:

67- COCKROACH_INTERNAL_CHECK_CONSISTENCY_FATAL

68- COCKROACH_INTERNAL_DISABLE_METAMORPHIC_TESTING

69- COCKROACH_RANDOM_SEED

70- COCKROACH_TESTING_FORCE_RELEASE_BRANCH

71But there might be more important environment variables, so best to get all of

72them.

74Determine if this is a multi-region test or single-region test by checking:

75- the test name (e.g., seed-multi-region in test.log indicates multi-region)

76- or the presence of \connect lines in the full SQL log

77If both of these are missing, it's a single-region test.

79## Step 3: Check Out and Build

81For a normal build use:

82```bash

83git checkout <commit-hash>

84./dev build short

85```

87If runtime assertions were enabled, use a test build instead:

88```bash

89git checkout <commit-hash>

90./dev build short -- --crdb_test

91```

93**Note:** Only build libgeos if the reproduction uses geospatial functions (BOX2D,

94geometry, geography, etc.):

95```bash

96./dev build libgeos

97```

99## Step 4: Prepare the Full SQL Log File

100

101First, check that the following statements are at the top of the full SQL log

102file. If they are not, add them:

103```sql

104SET statement_timeout='1m0s';

105SET sql_safe_updates = false;

106```

107

108If metamorphic settings were used, also add them to the top of the full SQL log

109file:

110```sql

111SET CLUSTER SETTING kv.rangefeed.buffered_sender.enabled = true;

112SET CLUSTER SETTING kv.transaction.write_buffering.enabled = true;

113```

114

115Create an appropriate directory either in the artifacts directory or in the

116repository root for holding temp files.

117

118## Step 5: Initial Reproduction

119

120Determine the correct demo command based on test type:

121- **Multi-region test**: Use --nodes=9

122- **Single-region test**: Omit --nodes option

123

124Use a command like this to try reproducing the test failure from the full SQL

125log file. This command could take up to 20 minutes to finish.

126

127```bash

128<env vars> ./cockroach demo --multitenant=false --nodes=9 --insecure --set=errexit=false --no-example-database --format=tsv -f <full-sql-log-file>

129```

130

131**Check that the output reproduces the test failure described in the failure

132log.** There are many possible failure modes. Look for one of the following,

133which should match the failure log:

134

1351. **Different results** between the two executions of the "Query of Interest"

136 (which is the randomly generated SELECT statement repeated twice near the end

137 of the log, wrapped in various SET and RESET staements). These different

138 results could take the form of different result sets, or could also be an

139 error in one case and no error in the other case. This is an **"oracle"

140 failure**.

1412. Or, internal error or **assertion failure**. Note the error message for the

142 reduce step.

1433. Or, a **panic**. Note the error message for the reduce step.

1444. Or, a **timeout**. Note the statement that timed out.

145

146### Troubleshooting

147

148**IMPORTANT:** Many failures are nondeterministic, especially for multi-region

149tests. If no failure happens on the first run, try up to 10 times before

150concluding it doesn't reproduce.

151

152It can be helpful at this point to compare the output with the failure*.log

153which should show the failure from the original test run.

154

155**If the initial run fails to reproduce after 10 times, pause here and report to

156the user that the failure cannot be reproduced, and show the command that was

157tried.** The user might have additional instructions.

158

159If it looks like it reproduces, it's time to move on to the next step.

160

161## Step 6: Use the Reduce Tool

162

163Build the reduce tool:

164```bash

165./dev build reduce

166```

167

168### Prepare the Full SQL Log File again

169

170For multi-region tests, remove \connect lines (they cause syntax errors in the

171reduce tool):

172```bash

173grep -v '^\\connect' <full-sql-log-file> > <cleaned-log>

174```

175

176### Run Reduce

177

178**IMPORTANT:** The reduce tool must be run from the cockroach repository root

179directory, because it looks for ./cockroach in the current directory.

180

181Use the -multi-region option for multi-region tests, or omit it for

182single-region tests.

183

184**For "oracle" failures (different results):**

185```bash

186./bin/reduce -unoptimized-query-oracle -multi-region -chunk 25 -v -file <cleaned-log> 2>&1 | tee reduce-output.log

187```

188The -unoptimized-query-oracle option checks whether the two executions of the

189"Query of Interest" produce the same results.

190

191**For internal errors/assertion failures/panics:**

192```bash

193./bin/reduce -contains "<error-regex>" -multi-region -chunk 25 -v -file <cleaned-log> 2>&1 | tee reduce-output.log

194```

195Use a distinctive part of the error message as the -contains regex (e.g.,

196"nil LeafTxnInputState").

197

198The reduce tool might take up to an hour to run.

199

200### Extract the Reduced SQL

201

202The reduce tool outputs progress lines followed by the final SQL. Extract just the SQL:

203```bash

204grep -A1000 "^reduction: " reduce-output.log | tail -n +2 > reduced.sql

205```

206

207**IMPORTANT:** Immediately save a backup of the reduce output before manual simplification:

208```bash

209cp reduced.sql reduced_original.sql

210```

211This provides a recovery point if the working file gets corrupted during simplification.

212

213**If the reduce tool fails to reproduce, pause here and report this to the

214user. They might have additional instructions.** Occasionally we have to modify

215the reduce tool itself, if the test failure is not reproducing.

216

217## Step 7: Create Test Script and Determine Reproduction Rate

218

219**IMPORTANT:** Many bugs are nondeterministic. Before manual simplification,

220create a reusable test script and determine the reproduction rate.

221

222Create a small test script (adjust as needed):

223```bash

224cat > test_repro.sh << 'EOF'

225#!/bin/bash

226# Test if reduced_v2.sql reproduces the error (exits on first success, up to 10 attempts)

227for i in {1..10}; do

228 if ./cockroach demo --multitenant=false --nodes=9 --insecure \

229 --set=errexit=false --no-example-database --format=tsv \

230 -f reduced_v2.sql 2>&1 | grep -q "<error-pattern>"; then

231 echo "Run $i: REPRODUCED"

232 exit 0

233 else

234 echo "Run $i: no error"

235 fi

236done

237echo "FAILED"

238EOF

239chmod +x test_repro.sh

240```

241For "oracle" failures, instead of checking for an error pattern, the test script

242probably needs to isolate and diff the results of the two executions of the

243"Query of Interest".

244

245Run the test script to determine the reproduction rate. It's not always 100%.

246

247This rate determines how many attempts you need when testing simplifications:

248- 100% rate: Single attempt sufficient

249- 50% rate: 2-3 attempts usually sufficient

250- 10% rate: Need ~10 attempts to be confident

251- <5% rate: May need 20+ attempts

252

253Note that in some cases, the following settings might need to be added back to

254the reduced file to get a repro:

255```sql

256SET statement_timeout='1m0s';

257SET sql_safe_updates = false;

258```

259

260**If the reduced SQL fails to reproduce after 10 attempts, pause here and report

261this to the user. They might have additional instructions.**

262

263## Step 8: Manual Simplification

264

265Now iteratively simplify the SQL while maintaining reproduction.

266

267**CRITICAL:** For nondeterministic failures, you MUST test each simplification

268with enough attempts based on the repro rate. A single failed attempt does NOT

269mean the simplification broke the repro - it may just be nondeterminism.

270

271### Workflow for Each Simplification

272

2731. Copy reduced.sql to reduced_v2.sql

2742. Make ONE small change to reduced_v2.sql

2753. Run ./test_repro.sh (which tests reduced_v2.sql)

2764. If it reproduces: Copy reduced_v2.sql to reduced.sql, continue simplifying

2775. If it doesn't reproduce after enough attempts: Discard reduced_v2.sql, try

278 a different change (i.e. backtrack).

279

280This workflow avoids needing to restore files - you always keep the last working

281version in reduced.sql.

282

283**IMPORTANT:** Run copy, edit, and test as separate bash commands (not chained with &&).

284This reduces the number of permission checks.

285

286### What to Try Removing (in rough order)

287

2881. **Query projections and aggregations** - Simplify SELECT list to just essential columns

2892. **Query predicates** - Simplify WHERE clause

2903. **Indexes** - Try removing secondary indexes

2914. **Query joins** - Simplify WHERE clause

2925. **Columns from CREATE TABLE** - Remove columns not referenced in the failing query

2936. **Weird characters** - Remove or replace non-ASCII characters from names and data

2947. other SQL simplifications

295

296**For "oracle" failures, when editing the Query of Interest, be sure to edit

297_BOTH_ copies of the Query of Interest so that they are identical.** Otherwise

298it won't be an apples-to-apples comparison when diffing the result sets.

299

300### Common Required Elements

301

302These often cannot be removed:

303- **Optimizer random seed**: SET testing_optimizer_random_seed = <value> - this

304 specific value often cannot be changed, as it determines which optimizer rules

305 are disabled

306- **Optimizer rule probability**: SET testing_optimizer_disable_rule_probability

307 - affects query plan selection

308- Specific RESET/SET sequences for optimizer settings, such as distsql and vectorize

309- Certain indexes (affect query plans)

310- Multi-node setup (--nodes=9) for distributed query bugs (though try

311 single-node first - it may work and is simpler)

312- CREATE STATISTICS statements (affect query planning)

313

314### Backtracking

315

316If a change breaks reproduction:

3171. Discard reduced_v2.sql (don't copy it to reduced.sql)

3182. Verify reduced.sql still reproduces. If it doesn't, this means the repro is

319 nondeterministic. (It might have started out nondeterministic, or might have

320 become nondeterministic over the course of simplification.) Try reproducing

321 it 10 times and note the new repro rate. Use the new repro rate to adjust the

322 number of repro attempts during each simplification step going forward.

3233. Try a DIFFERENT simplification

324

325Never continue simplifying from a broken state.

326

327**If you get stuck (i.e. cannot reproduce again after backtracking), stop and

328report to the user with the exact command you were trying.**

329

330## Step 9: Final Verification and Output

331

332**After about 20 minutes of simplification, or if there are no more

333simplifications after backtracking a few times, it's time to stop.**

334

3351. Run reproduction 10+ times to confirm stability and determine final repro rate

3362. Document the minimal reproduction steps

3373. Note which elements were required vs optional

338

339### Output

340

341The final output should include two files that can be shown to the user:

342

3431. **reduced.sql** - The minimal SQL script that reproduces the bug

3442. **bisect_run.sh** - A script for use with git bisect run

345

346Write the output in such a way that it could be copied and pasted into a

347terminal.

348

349### Example Output Format

350

351(The commands in this output should be edited to match what was necessary to

352reproduce.)

353

354```bash

355# Minimal Reproduction

356

357# reduced.sql

358cat > reduced.sql << 'EOF'

359CREATE TABLE t ();

360

361SET testing_optimizer_random_seed = 1234567890;

362SET testing_optimizer_disable_rule_probability = 0.5;

363

364SELECT ...;

365EOF

366

367# bisect_run.sh

368cat > bisect_run.sh << 'EOF'

369#!/bin/bash

370# Git bisect run script

371# Exit codes: 0=good (bug not present), 1=bad (bug present), 125=skip (build failed)

372

373REPO_DIR="/path/to/cockroach"

374REPRO_SQL="/path/to/reduced.sql"

375

376cd "$REPO_DIR" || exit 125

377

378echo "=== Testing commit $(git rev-parse --short HEAD) ==="

379

380# Build (use --crdb_test if runtime assertions were enabled in the original test)

381if ! ./dev build short -- --crdb_test 2>&1 | grep -q "Successfully built"; then

382 echo "BUILD FAILED - skipping"

383 exit 125

384fi

385

386# Test for bug (try 3 times for flaky bugs)

387for i in {1..3}; do

388 if ./cockroach demo --multitenant=false --insecure \

389 --set=errexit=false --no-example-database --format=tsv \

390 -f "$REPRO_SQL" 2>&1 | grep -q "<error-pattern>"; then

391 echo "BUG PRESENT - marking as BAD"

392 exit 1

393 fi

394done

395

396echo "Bug not present - marking as GOOD"

397exit 0

398EOF

399chmod +x bisect_run.sh

400

401# Command to reproduce

402git checkout <commit-hash>

403./bisect_run.sh

404

405# Command to bisect

406git bisect start ...

407git bisect run bisect_run.sh

408

409# Failure

410# <paste stacktrace or relevant failure details here>

411

412# Repro rate: ~X% (may need multiple attempts)

413```

414

415**After showing this output, ask the user if they want to try reproducing the

416bug on master branch.**

417

418## Optional Step 10: Check if Bug is Fixed on Master

419

420Before bisecting, check whether the bug has already been fixed on master.

421

422```bash

423git stash # if needed

424git checkout master

425./dev build short -- --crdb_test

426./cockroach demo --multitenant=false --insecure --set=errexit=false --no-example-database --format=tsv -f reduced.sql

427```

428

429Run this a few times to account for flakiness. Note whether the bug reproduces

430on master or not.

431

432## Optional Step 11: Bisect

433

434If the user wants to find the commit that introduced or fixed the bug, use

435git bisect.

436

437### If the Bug is Already Fixed on Master

438

439Bisect to find the **fix commit** (the first commit where the bug no longer

440reproduces). Use custom terms since the "good" commit (master) is newer than the

441"bad" commit:

442

443```bash

444git bisect start --first-parent --term-old=broken --term-new=fixed

445git bisect broken <commit-where-bug-exists> # e.g., the original failing commit

446git bisect fixed master # master is fixed

447

448git bisect run ./bisect_run.sh

449

450# When done

451git bisect reset

452```

453

454**Note:** The --first-parent option follows only merge commits on the main

455branch, avoiding detours into feature branches. The bisect script must return 0

456when the bug is NOT present (fixed) and 1 when the bug IS present (broken).

457

458### If the Bug Still Exists on Master

459

460Bisect to find the **regression commit** (the first commit where the bug was

461introduced):

462

463```bash

464git bisect start --first-parent

465git bisect good <known-good-commit> # e.g., a previous release tag

466git bisect bad master # master has the bug

467

468git bisect run ./bisect_run.sh

469

470# When done

471git bisect reset

472```

473

474The bisect will identify the commit that introduced or fixed the bug.

475

476### Finding a Good Commit

477

478If you don't know a good commit (where the bug doesn't exist), you can jump back

479in time to find one.

480

481```bash

482# Find a commit from ~6 months ago on the main branch

483git rev-list --first-parent -1 --before="6 months ago" HEAD

484```

485

486Test whether the bug exists at that commit. If not, use it as the good commit

487for bisect. If the bug still exists, try going back further in time, but don't

488go back further than 1 year.

489

490**If a known good commit can't be found within 1 year, stop and report this to

491the user.**

492

In the file

SKILL.md2,512 words

Files1

LicenceSource-available

Why you can read it

Nothing in a skill executes. The client loads the text and the model follows it, so a skill can be audited the way a runbook is — by reading it.

What it costs in context

Skills are not billed by the call. They are paid for in context: every token the instructions occupy is a token your code, your diff and your conversation cannot use. Here is what this one takes and when it takes it.

≈80

always loaded

The name and description, so the model knows the skill exists and when to reach for it.

4,170

on trigger

The instruction body, read only when the skill fires.

2.1%

of a 200k window

Ten skills this size would take about 21% of the window before you open a file.

050k100k150k200k context window

4.3k tokens, estimated from the bundle at four bytes to the token, held for the rest of the session once it triggers. Heavy. Teams tend to install this one per project rather than globally, and load it only when the job comes up.

Servers bill, skills cost

A server charges by the month. A skill charges once per session, in context, and then keeps charging it for as long as the session lives.

Before and after

The same question, put to the same model twice: once as it comes, and once with these instructions loaded.

No worked example has been published for this skill yet.

Adoption

Installsnone yet

Ratingno reviews yet

The procedure it runs

The procedure has not been published here. It is in the skill’s own SKILL.md, which its author has not sent to the marketplace yet.

Prose, not code

These steps are written for a model to follow, not executed by a runtime. It can still be told to skip one, and it will say so when it does.

Servers it uses

None. This skill calls no MCP servers at all.

Everything it needs is in the instructions, so it works in a project with nothing connected — the model reads the file and changes how it works with what it can already reach.

It writes no files and reaches no network. All it changes is how the model reasons and writes.

What it asks for

Writes filesno

Network accessno

Read from the allowed-tools line of this skill’s own SKILL.md. A skill grants no permissions of its own — it can only ask for tools your client already has.

What it will not do

Every skill is narrow, and the useful ones say where they stop. These are the jobs this one is the wrong tool for.

What this skill is not for has not been published here. Nothing is implied by that: it is a section the author has not filled in.

If this is not it

20 other skills in Databases

What is in the bundle

1 file, 17.0 kB on disk. A bundle is text throughout: the instructions the model reads, plus the templates it fills in.

SKILL.md17.0 kB

What is not in it

No dependencies and nothing executable: a skill is text the agent reads, so the bundle is 1 file you can review in full before installing. The Source-available licence covers the templates and examples as well as the instructions.

Install

Installing copies the bundle into your project. Nothing runs at install time — the files sit on disk until the model reads them.

# Reduce Unoptimized Query Oracle Test Failure · 4.3k tokens when loaded
npx mcprush@latest skill add cockroachdb/reduce-unoptimized-query-oracle-test-failure

Writes to .claude/skills/reduce-unoptimized-query-oracle-test-failure/ in the current project. Add --global to put it in your home directory instead, for every project.

Which clients pick it up on their own

A skill is a folder of text. A client with a skills folder reads it without being told; everywhere else the same text works, it is just handed to the model rather than found.

Claude Code.claude/skills/

Claude Desktop

ChatGPT

Cursor.cursor/skills/

VS Code.github/skills/

Codex CLI.agents/skills/

Gemini CLI.gemini/skills/

Grok.grok/skills/

Zed.agents/skills/

Windsurf.windsurf/skills/

Agent SDK.claude/skills/

HTTP / API

This release

Versionnot versioned

Publishedno release date on file

PriceFree

Referencecockroachdb/reduce-unoptimized-query-oracle-test-failure

Versions

Its author publishes no version number, so there is nothing here to pin to: what you install is the folder as it stands today. Instructions change more often than APIs do — a skill can be rewritten entirely without anything it depends on moving.

No earlier releases have been published to the marketplace.

Pinning

Nothing to pin to: this skill carries no version number of its own. What you install is what the folder holds on the day you install it.

Reviews

—no reviews yet · no installs yet

Nobody has reviewed this skill yet. The rating is the mean of the reviews written here, so there is none until somebody writes the first.

Who can post

Only accounts that have had the skill installed for fourteen days, so a review is written after living with it rather than after reading it. Publishers may reply once.

Who wrote it

cockroachdb

Publishes on mcprush.

0 servers listed1 skill listednot claimed

Profile

Publisher

Servers0

Claim this skill