dolthub / dolthub/dolt

information_schema.triggers fails with "table not found: new" after RENAME COLUMN on a column the trigger body references

Open
#11,587 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Renaming a column that a trigger body references leaves the trigger stored but makes `information_schema.triggers` unqueryable. Any query touching that view fails for the whole database, not just for the affected trigger.

Tested on **dolt 2.2.2** (linux/darwin arm64). Not yet retested on 2.3.1 — apologies if it is already fixed there.

## Repro

```bash
dolt init
dolt sql -c -r csv <<'SQL'
CREATE TABLE t(k INT PRIMARY KEY, a VARCHAR(9), n INT);
CREATE TRIGGER tg0 BEFORE INSERT ON t FOR EACH ROW SET NEW.a = NEW.a;
CALL dolt_commit('-Am','base');
ALTER TABLE t RENAME COLUMN a TO a2;
CALL dolt_commit('-Am','ren');
SQL

dolt sql -q "SELECT * FROM information_schema.triggers;"
```

```
error on line 1 for query SELECT * FROM information_schema.triggers: table not found: new
```

## The trigger itself is fine

Both other paths still return it, so this is specific to that view's evaluation rather than lost data:

```
dolt sql -q "SELECT type, name FROM dolt_schemas;"
type,name
trigger,tg0

dolt sql -q "SHOW TRIGGERS;"
Trigger,Event,Table,Statement,Timing,...
tg0,INSERT,t,SET NEW.a = NEW.a,BEFORE,...
```

So the body still says `SET NEW.a = NEW.a` while the column is now `a2`. `SHOW TRIGGERS` returns the text without resolving it; `information_schema.triggers` appears to resolve/bind the body and fails on the missing `NEW.a`, and the error surfaces as `table not found: new`.

## Why it matters

- The failure is **database-wide for that view**: one stale trigger makes `SELECT * FROM information_schema.triggers` fail, so tooling that enumerates triggers (introspection, ORMs, schema diffing, migration tools) breaks on an unrelated query.
- The error text points at a table named `new`, which gives no indication that a trigger body or a renamed column is involved. It took reading `dolt_schemas` to work out what had happened.
- `RENAME COLUMN` silently leaves the trigger inconsistent. MySQL rejects a rename that a trigger depends on in some configurations; either updating the body or refusing the rename would avoid the stale state.

## How we ran into it

We build DoltLite (a SQLite fork with Dolt-style version control) and oracle its merge behaviour against Dolt, comparing the merged schema, indexes, row data, and — as of this week — the view/trigger inventory. Reading dependents from `information_schema.triggers` made five rename cases look like divergences, because the query failed and our harness read the failure as "Dolt has no triggers". It is not a divergence; the trigger is present in Dolt, and merges do carry it. We have switched to a source that does not have this problem, but the underlying view failure looks worth fixing.

Happy to test a fix or provide more detail.

Contributor guide

No contributing guide indexed for this repository

Research direction

Run the supplied Dolt SQL reproduction and compare SELECT * FROM information_schema.triggers with SHOW TRIGGERS and dolt_schemas after RENAME COLUMN. Start at the implementation or tests for information_schema.triggers and trigger handling during column renames. Done means the view no longer fails database-wide and the behavior of the stale trigger is covered by a regression test.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.