Write and test a regex or SQL query

Write and explain this, then try to break it.

What I need it to match or return:
[DESCRIBE IT IN PLAIN LANGUAGE]

Regex or SQL: [WHICH ONE]
Flavour and version: [E.G. PCRE, JAVASCRIPT, POSTGRES 16, MYSQL 8]
Real examples that must match / must be returned: [PASTE SEVERAL]
Real examples that must NOT: [PASTE SEVERAL]
Where it runs: [E.G. VALIDATING USER INPUT, A REPORTING QUERY OVER 20M ROWS]

Produce four sections.

1. THE ANSWER — the regex or query, formatted to be read.

2. PIECE BY PIECE — break it into its parts and explain each in one line. For regex, name every group, quantifier, and anchor. For SQL, explain each join and each condition, and say which conditions filter before the join and which after.

3. TEST CASES — a table of inputs and expected results, covering: my examples, empty and missing values, the boundaries of every condition, duplicates, unicode and case where relevant, and for SQL specifically what happens when a joined row is absent and when a value is NULL. Mark any case where my requirements were ambiguous and you had to choose.

4. WHERE IT SILENTLY GIVES THE WRONG ANSWER — inputs that produce no error and the wrong result. For regex include catastrophic backtracking if it applies. For SQL include rows dropped by an inner join that should have been outer, aggregates skewed by duplicated rows from a join, and NULL comparisons that quietly exclude rows.

If my description and my examples disagree, say so and follow the examples.

Both of these fail in the same way, and it is the worst way: they run, they return something, and the something is wrong. A regex that misses one form of input passes every test you thought to write. An inner join that should have been a left join returns a smaller result set that looks entirely reasonable, and the report is wrong by an amount nobody notices for a quarter.

Asking for the regex or the query alone gets you something that handles the examples you gave, which is exactly the set of cases you already knew about. What is worth having is section four: the inputs that produce no error and the wrong answer. NULL semantics and join multiplication are responsible for a large share of quietly wrong numbers in production, and neither announces itself.

Section two is not filler either. A pattern you cannot read is one you cannot safely change in six months, and the usual outcome is a second pattern bolted onto the first.

1. Give real examples, including the ugly ones

   The addresses with plus signs, the names with apostrophes, the rows where the optional field is empty. Clean examples produce a clean pattern that meets real data and fails.

2. Name the exact flavour

   Lookbehind, named groups, and unicode classes differ across regex engines; NULL handling, string comparison, and date functions differ across databases. A pattern for the wrong engine is a runtime error at best and different semantics at worst.

3. Say where it runs

   Validating one input and scanning twenty million rows have different answers. Backtracking that is irrelevant on short strings is a denial-of-service on user input, and a query shape that is fine on a small table is not on a large one.

4. Run section three before you ship it

   The table is written to be executed, not read. The cases marked ambiguous are where it had to guess at your requirements — those are the ones to check first.

• Regex for anything with real grammarHTML, nested structures, and full email validation to the RFC are the classic cases where a regex can be made to look right and cannot be made right. Use a parser; if the answer is a very long pattern, that is the signal.
• Query performance advice without your actual planIt can spot obviously expensive shapes, but it cannot see your indexes, statistics, or planner. Run EXPLAIN yourself before accepting anything about speed.
• When the wrong answer is expensiveFinancial reconciliation, compliance reporting, anything used for a decision that is hard to reverse. Section four narrows what to verify; it does not replace verifying against a known-correct result.

• CodingRagnarökDebug a stack traceTurns an error and its stack trace into an ordered list of what could cause it, each with the specific check that would rule it in or out.
• CodingApolloWrite a spreadsheet formula and understand itBuilds the Excel or Sheets formula you described, explains each part, and supplies the data that makes it return the wrong answer without erroring.
• CodingRagnarökReview a diff for production risksReviews a change for the failures that only appear under real traffic and real data, and separately lists which of the new behaviour has no test.
• WritingApolloRemove AI tells from a draftRewrites text that reads as machine-written, then lists the specific phrases and structures that gave it away so you stop producing them.