DELETE ... LIMIT 0 deleted nothing: why your purge job did no work

VERICTO-002 High In CI (offline CLI): BLOCKED

On MySQL, DELETE FROM sessions WHERE user_id = 7 LIMIT 0; succeeds with 0 rows affected: LIMIT 0 means "delete at most zero rows". Nothing was lost, but the job that ran it did none of its work, and a loop that waits for "0 rows affected" to stop exits on the first pass. Find where the limit value comes from and fix it there.

Why it happens

The limit is almost never written as a literal 0. It comes from a variable: a batch size read from configuration that is missing and defaults to 0, an integer division that rounds down, a template that renders an empty value as 0, or code generated by a tool that picked a placeholder. The statement is valid, so nothing fails loudly.

How to fix it

Validate the batch size before building the statement (a positive integer, with a sane default) and make the job fail when it is not. For batched deletes on MySQL, add an ORDER BY on the primary key so each batch is deterministic (MySQL warns that DELETE ... LIMIT without ORDER BY is unsafe for statement-based replication):

DELETE FROM sessions WHERE user_id = 7 ORDER BY id LIMIT 1000;

Repeat until the statement affects 0 rows.

How to stop it from happening again

VERICTO-002 · DELETE with LIMIT 0 (MySQL, SQLite) · Severity: High · AST node: DeleteStmt > LimitCount = 0

The statement the rule reports

-- purge.sql
DELETE FROM sessions WHERE user_id = 7 LIMIT 0;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline --dialect mysql purge.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
✖ purge.sql  BLOCKED  VERICTO-002 high
    DeleteStmt > LimitCount = 0
    fix: DELETE FROM sessions WHERE id = $1
1 blocked · 0 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

✖ Vericto blocked 1 statement  (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
  ✖ purge.sql  BLOCKED  VERICTO-002 high
      DeleteStmt > LimitCount = 0
      fix: DELETE FROM sessions WHERE id = $1

This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
    -- vericto:ignore[VERICTO-002] <reason>   (a reason is required)

The CLI blocks it and exits with code 1, so the CI job fails before the merge.

The corrected version

-- purge.sql
DELETE FROM sessions WHERE user_id = 7 LIMIT 1000;
$ npx @vericto/vericto-cli@2.3.0 check --offline --dialect mysql purge.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
0 blocked · 1 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

Try it free, no account

The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).

npx @vericto/vericto-cli@2.3.0 check --offline --dialect mysql migrations/

On every pull request

The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:

on: pull_request
jobs:
  vericto:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
        with: { fetch-depth: 0 }
      - uses: vericto/check-sql@v1
        with: { offline: true, upload-sarif: false, dialect: mysql }

At runtime, for AI agents

The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.

The AST condition in the engine

The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:

"VERICTO-002" => find(stmts, |s| {
    s.kind == StatementKind::Delete && s.delete_limit == Some(0)
})
.map(|s| {
    let mut v = violation(rule, s, suggest_delete(s));
    v.ast_node_path = "DeleteStmt > LimitCount = 0".to_string();
    v
}),

Frequently asked questions