Skip to content

sql-839: Report SQL string 'NULL' for table column defaults in mz_columns again - #40316

Merged
SangJunBak merged 4 commits into
mainfrom
jun/sql-839-terraform-apply-drops-and-recreates-tables-after-the
Oct 29, 2026
Merged

SangJunBak merged 4 commits into
mainfrom
jun/sql-839-terraform-apply-drops-and-recreates-tables-after-the

Conversation

@SangJunBak

@SangJunBak SangJunBak commented Oct 28, 2026 •

Copy link
Copy Markdown
Contributor

Before, tables whose columns' defaults were undefined we'd store their mz_columns default value as the SQL string 'NULL' but would store it as NULL for other relation objects. In #40001 , we decided to unify the behavior and store it as NULL for tables too. However, this created a regression in our terraform provider where it'd drop and create tables with no defaults on their columns because of a schema mismatch. Thus we change it back to the SQL string NULL.

Motivation

Closes: SQL-839

Verification

We still get pg compatibility by just changing the definition of the information_schema / pg views. This is why we don't need to update any of the SLT tests related to those views.

SangJunBak and others added 3 commits October 28, 2026 16:52
mz_columns.default reads as the string NULL for a table column without a
default, as it did in every release through v26.48. #40001 made it SQL
NULL, which the Terraform provider (every release through v0.11.12)
compares against its declared default of "NULL": it then replaces the
table on every apply and drops its rows (SQL-839).

Builtin tables read the string too, so the contract holds for every table
and not only for user tables.

The PostgreSQL-compatible views keep the behavior #40001 gave them by
mapping the string to no default: pg_attribute.atthasdef is false,
pg_attrdef has no row and information_schema.columns.column_default is
NULL for such a column.

Closes: SQL-839

Co-Authored-By: Claude Fable 5.1 <[email protected]>
A table column without a default reads as the string NULL in mz_columns
and as no default in pg_attribute, pg_attrdef and information_schema
(SQL-839).

Co-Authored-By: Claude Fable 5.1 <[email protected]>
@SangJunBak
SangJunBak requested review from a team as code owners October 28, 2026 20:56
@linear-code

linear-code Bot commented Oct 28, 2026

Copy link
Copy Markdown

SQL-839

@ggevay ggevay left a comment •

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thank you for the quick fix!

Looks good. Two nits inline.

item_columns_initial_mv 2 b true text <null> text -1
item_columns_initial_mv 3 c1 true numeric <null> numeric -1
item_columns_initial_table 1 a false integer <null> int4 -1
item_columns_initial_table 1 a false integer NULL int4 -1

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

The two version branches now expect the same rows, and the comment above them ("From 26.49 on ... no default reads as SQL NULL") no longer holds. The guard and comment can go, leaving one query.

Comment thread src/catalog-decode/src/create_sql.rs Outdated
.map_or(serde_json::Value::Null, |expr| {
json!(expr.to_ast_string_stable())
})
// A column without a default reads as the string NULL, see the

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Worth saying why here, so the next cleanup doesn't undo it: clients compare against this string, and the Terraform provider replaces a table whose column default reads as SQL NULL (SQL-839). Maybe also TODO(DEX-197): switch to SQL NULL once released providers read it as no default.

- Say at the decision point why a missing default reads as the string NULL
- Drop the 26.49 version guard in
the item_columns platform check, since both sides now expect the same rows.

Co-Authored-By: Claude Opus 5.5 <[email protected]>
@SangJunBak
SangJunBak force-pushed the jun/sql-839-terraform-apply-drops-and-recreates-tables-after-the branch from 8d8f551 to 4e7e4aa Compare October 29, 2026 13:52
@SangJunBak
SangJunBak merged commit 077d0f3 into main Oct 29, 2026
87 checks passed
@SangJunBak
SangJunBak deleted the jun/sql-839-terraform-apply-drops-and-recreates-tables-after-the branch October 29, 2026 14:35
def- added a commit that referenced this pull request Nov 6, 2026
Undo the string "NULL" default from #40316 now that
terraform-provider-materialize v0.11.13 reads a SQL NULL column default
as no default
([DEX-197](https://linear.app/materializeinc/issue/DEX-197)). Table
columns again match every other relation in `mz_columns.default`, and
the PostgreSQL-compatible views go back to plain `IS NOT NULL` checks.
`information_schema.columns`, `pg_attribute`, and `pg_attrdef` report
the same results as before.

`materialize_table` is the only provider resource that reads
`mz_columns.default`.

### Release notes

This release will report SQL `NULL` instead of the string `'NULL'` in
`mz_catalog.mz_columns.default` for table columns without a default,
matching sources, views, and materialized views. If you manage tables
with the Materialize Terraform provider, upgrade the provider to
v0.11.13 or later before you upgrade Materialize.

Co-authored-by: Claude Opus 5.5 <[email protected]>
leedqin added a commit to leedqin/materialize that referenced this pull request Nov 6, 2026
Generated with the fixed `yarn gen:types`. Adds mz_builtin_columns and
mz_builtin_index_columns. Builtin columns lose their Generated<>
wrappers, since MaterializeInc#40316 reports no default for them in pg_attribute.
Keeps the two hand-added TODO markers for mz >= 26.34.
leedqin added a commit that referenced this pull request Nov 6, 2026
### Motivation

`yarn gen:types` fails with `function "pg_get_serial_sequence" does not
exist`. kysely-codegen runs kysely's Postgres introspector for its table
query. Since the bump to kysely 0.28 (#35669), that query calls
`pg_get_serial_sequence`, which Materialize lacks. kysely 0.29 still
does. The query also lists only relkinds `r`, `v` and `p`, so the
catalog's builtin materialized views (`m`) never reached the types under
kysely-codegen 0.17. #40044's types were generated with both patched by
hand in `node_modules`.

This also bumps kysely to 0.29 and kysely-codegen to 0.20, superseding
#39686.

### Description

- `bin/kysely-codegen.ts` runs kysely-codegen programmatically with an
introspector that overrides only the table query. kysely-codegen's
materialized view query, enum handling and serializer still apply.
- The script writes the copyright header to `types/materialize.d.ts`.
The codegen output lacks it, and the copyright lint fails without it.
- `bin/generate-types` exits on errors, pulls the requested image (a
cached `latest` went stale), and stops the container on exit. Before,
each run left a container bound to port 6875, and the next run generated
types from that old container.
- kysely 0.28.17 → 0.29.8 and kysely-codegen 0.17 → 0.20.0:
- 0.29 rejects `limit(sql.raw(...))`. `rawLimit` and the lag history
limit now call `query.limit(sql.lit(n))`, which still inlines the limit
as a literal, as Materialize requires.
- `rawLimit` used to append its limit with `modifyEnd`. In 0.29,
compiling end modifiers calls `Array.prototype.toSorted`, which Chrome
109 lacks. No console query uses end modifiers now. Neither `rawLimit`
caller has an OFFSET or another end modifier, so the compiled SQL is
unchanged.
- kysely 0.29 is ESM-only and kysely-codegen 0.20 `require`s it, so
`yarn gen:types` needs Node 22.12 or later. CI and the Volta pin run
Node 26, and #39683 updates `engines.node`.
- `types/materialize.d.ts` is regenerated against v26.49.1.
kysely-codegen 0.17 and 0.20 produce identical output.
  - Adds `mz_builtin_columns` and `mz_builtin_index_columns`.
- Builtin columns lose their `Generated<>` wrappers, because #40316
changed `pg_attribute.atthasdef` to report no default for them. The
console only selects from these relations, where `Generated<T>` and `T`
read the same.
- Drops the two hand-added `TODO` markers from #39992. The generator now
emits `mz_cluster_auto_scaling_strategies` and
`mz_cluster_reconfigurations`, and `AUTO_SCALING_MIN_VERSION` still
gates them at runtime. The file is now exactly the generator output.

Worth checking on the preview, since these run the changed limits:
- the Query History list;
- a sink's Errors tab;
- the freshness lag charts on the environment overview and the cluster
pages.

🤖 Generated with [Claude Code](https://claude.com/claude-code)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

3 participants