Brackets are dropped from saved expressions: generated columns, defaults and CHECK constraints compute wrong results
- Dominant language
- Go
- Stars
- 2.1k
- Forks
- 73
- Avg merge
- 1d 10h
- Merged PRs (30d)
- 129
Description
On DoltgreSQL 1.3.1, a generated column, a column default or a `CHECK` constraint whose expression needs brackets silently computes the wrong result. No error is raised: the expression is saved without its brackets.
```sql
CREATE TABLE t (
a int,
b int GENERATED ALWAYS AS ((a + 1) * 2) STORED,
c int DEFAULT ((1 + 2) * 3)
);
INSERT INTO t (a) VALUES (1);
SELECT a, b, (a + 1) * 2 AS expected_b, c, (1 + 2) * 3 AS expected_c FROM t;
SELECT column_default FROM information_schema.columns WHERE table_name = 't' AND column_name = 'c';
```
| | PostgreSQL 18.6 | DoltgreSQL 1.3.1 |
|---|---|---|
| `b`, from `GENERATED ALWAYS AS ((a + 1) * 2)`, for `a = 1` | 4 | **3** |
| `c`, from `DEFAULT ((1 + 2) * 3)` | 9 | **7** |
| the same two expressions computed in the `SELECT` | 4 and 9 | 4 and 9 |
| the default as saved, in `information_schema.columns` | `((1 + 2) * 3)` | `1 + 2 * 3` |
A runnable reproduction is at https://github.com/Reliable-Collaboration/repro-doltgresql-bug-brackets. It runs the SQL on both engines in throwaway containers and prints the two outputs side by side:
```sh
git clone https://github.com/Reliable-Collaboration/repro-doltgresql-bug-brackets.git
cd repro-doltgresql-bug-brackets
./repro.sh # exits 1 while DoltgreSQL's output differs from PostgreSQL's
```
## Other observations
All on DoltgreSQL 1.3.1, compared with PostgreSQL 18.6:
- Brackets on the right are lost too. For `a = 1`, `2 * (a + 1)` stores 3, `a - (1 - 2)` stores -2 and `-(a + 1)` stores 0, where PostgreSQL stores 4, 2 and -2.
- `CHECK ((a + 1) * 2 > 3)` refuses the valid row `a = 1`, and `information_schema.check_constraints` shows the check as `"a" + 1 * 2 > 3`. PostgreSQL accepts the row and shows `(((a + 1) * 2) > 3)`.
- The wrong default persists after `ALTER TABLE ... ADD PRIMARY KEY`.
- Not affected in these tests: `NOT (a = 1 AND a = 2)` as a generated column, a lookup through an expression index on `((a + 1) * 2)`, and a view computing `(1 + 2) * 3`.
## Why it matters
The results are wrong without any error, so they go unnoticed: generated columns hold wrong values, defaults insert wrong values, and CHECK constraints enforce a different condition from the one written.
## Possible cause
Read in the source, not verified with a build. Dolt saves a column's default and generated expression by printing the resolved expression back to text (https://github.com/dolthub/dolt/blob/3ca2680964382c04db862b948bd78094b2d78434/go/libraries/doltcore/sqle/sqlutil/convert.go#L153-L155). go-mysql-server's planbuilder drops bracket nodes when it builds expressions (https://github.com/dolthub/go-mysql-server/blob/da4d8ec733de125e923e676416c2573b63c8204c/sql/planbuilder/scalar.go#L361-L362), so the saved text keeps the grouping only if every node prints brackets around itself. go-mysql-server's MySQL arithmetic does (https://github.com/dolthub/go-mysql-server/blob/da4d8ec733de125e923e676416c2573b63c8204c/sql/expression/arithmetic.go#L114-L116). DoltgreSQL's `BinaryOperator.String()` prints `%s %s %s` without them (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/expression/binary_operator.go#L92-L107), and `UnaryOperator.String()` prints the operator and its argument without them too. A TODO in `ColumnDefaultValue.String()` describes the same class of problem (https://github.com/dolthub/go-mysql-server/blob/da4d8ec733de125e923e676416c2573b63c8204c/sql/columndefault.go#L146). `binary_operator.go` is unchanged on `main`.
## Possibly related
- #3323: a saved generated expression is written back with an alias that DoltgreSQL cannot parse. It goes through the same saving path, with a different printing defect.
## Environment
- `dolthub/doltgresql:1.3.1`, digest `sha256:6c85cb1f35beabf47f094336a420255130b841b1645f36d79ef046276af36851`, the latest release when this was written
- `postgres:18.6-bookworm`, digest `sha256:1c59e2c3c818eaa0f0628f695b36e7c9e362d6b219b36a54a32df645cbd7e1af`
- Docker 29.7.2 on Linux x86_64
Contributor guide
Assessment
This issue has not been assessed yet.