Generated column with COALESCE: every INSERT fails after ALTER TABLE ADD PRIMARY KEY
- Dominant language
- Go
- Stars
- 2.1k
- Forks
- 73
- Avg merge
- 1d 10h
- Merged PRs (30d)
- 129
Description
On DoltgreSQL 1.3.1, a table with a `STORED` generated column whose expression is `COALESCE` over an expression, such as `COALESCE(a + 1, 0)`, accepts inserts until the table is altered. After `ALTER TABLE t ADD PRIMARY KEY (a)`, every `INSERT` fails:
```
ERROR: Invalid default value for '(coalesce("a" + 1 as a + 1,0))': at or near "as": syntax error
```
PostgreSQL 18.6 runs the same statements without an error.
## Reproduction
```sql
CREATE TABLE t (
a int NOT NULL,
b int GENERATED ALWAYS AS (COALESCE(a + 1, 0)) STORED
);
INSERT INTO t (a) VALUES (1); -- INSERT 0 1
ALTER TABLE t ADD PRIMARY KEY (a); -- ALTER TABLE
INSERT INTO t (a) VALUES (2); -- ERROR: Invalid default value for '(coalesce("a" + 1 as a + 1,0))': at or near "as": syntax error
SELECT * FROM t ORDER BY a;
```
**Expected** (PostgreSQL 18.6): both inserts succeed, and the `SELECT` returns two rows, `(1, 2)` and `(2, 3)`.
**Actual** (DoltgreSQL 1.3.1): the `ALTER TABLE` succeeds, the second insert fails with the error above, and the `SELECT` returns only `(1, 2)`.
A runnable reproduction is at https://github.com/Reliable-Collaboration/repro-doltgresql-bug-1. Its script runs the SQL in a throwaway `dolthub/doltgresql:1.3.1` container, or in `postgres:18.6` for comparison, and prints the full output:
```sh
git clone https://github.com/Reliable-Collaboration/repro-doltgresql-bug-1.git
cd repro-doltgresql-bug-1
./repro.sh # DoltgreSQL 1.3.1: fails, exits 1
./repro.sh postgres # PostgreSQL 18.6: succeeds, exits 0
```
## Other observations
All on DoltgreSQL 1.3.1:
- After the alteration, `UPDATE` and `CREATE INDEX` on the table fail with the same error. `SELECT` still works.
- `ADD UNIQUE`, `DROP COLUMN` and `ALTER COLUMN ... TYPE` have the same effect as `ADD PRIMARY KEY`. `ADD COLUMN` or `CREATE INDEX`, as the only alteration, does not.
- `COALESCE(abs(a), 0)` fails the same way, stored as `coalesce(abs("a") as abs,0)`. `COALESCE(a, 0)` does not, and neither does `a + 1` without `COALESCE`.
- Declaring the primary key inside `CREATE TABLE`, instead of adding it afterwards, avoids the error.
## Why it matters
`pg_dump` writes primary keys as `ALTER TABLE ONLY ... ADD CONSTRAINT ... PRIMARY KEY` after `CREATE TABLE`, so restoring a dump that has such a table leaves the table unable to accept inserts.
## Possible cause
Not verified with a build; offered in case it saves time. The stored text in the error, `coalesce("a" + 1 as a + 1,0)`, looks like `PgCoalesce.String()` output with `*expression.Alias` arguments. `CREATE TABLE` clears these aliases from the generated expression (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/ast/column_table_def.go#L121-L123), but the alterations above appear to write the expression back from its resolved form. `CompiledFunction.String()` unwraps alias arguments, noting that "Aliases will output the string "x as x", which is an artifact of how we build the AST" (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/functions/framework/compiled_function.go#L291-L306). `PgCoalesce.String()` prints its arguments as they are (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/expression/coalesce.go#L162-L168), and it is unchanged on `main`.
## Possibly related
- #810: a generated column's expression is stored in a form the parser rejects (backticks), so inserts into the table fail.
- #3094, fixed by #3142: an expression index on `coalesce()` was accepted, and then every write to the table failed. It had a different error, but the same shape of delayed failure.
## Environment
- `dolthub/doltgresql:1.3.1`, digest `sha256:6c85cb1f35beabf47f094336a420255130b841b1645f36d79ef046276af36851`, the latest release when this was written
- Docker 29.7.2 on Linux x86_64
Contributor guide
Assessment
This issue has not been assessed yet.