[MySQL] detect or exclude GENERATED ALWAYS VIRTUAL columns from mutable columns

[mutefiRe]

trafficstars

Related Issues

https://github.com/go-jet/jet/issues/209 https://github.com/go-jet/jet/issues/300

This seems to be solved for PostgreSQL already, so opening another issue for MySQL.

Is your feature request related to a problem? Please describe.

If having a MySQL table for e.g. cars that supports soft deletion via a generated column. The schema could look like this:

CREATE TABLE car (
  car_id int NOT NULL AUTO_INCREMENT,
  license_plate VARCHAR(10) NOT NULL,
  deleted_at TIMESTAMP NULL,
  not_deleted INT(1) GENERATED ALWAYS AS (IF(`deleted_at` IS NULL, 1, NULL)) VIRTUAL,
  CONSTRAINT `car_unique` UNIQUE (license_plate, not_deleted),
  PRIMARY KEY (car_id)
);

With this setup, an entry is “soft-deleted” by setting deleted_at. Additionally when using not_deleted as an GENERATED ALWAYS column, it should probably not be part of insert or update statements. However insert with MutableColumns includes the virtual column, causing an error since MySQL does not allow direct insertion into a GENERATED ALWAYS column.

Background: The nullable generated column helps with the unique constraint over non-deleted rows.

Describe the solution you'd like

Properly detect and exclude GENERATED ALWAYS columns from the mutable columns so that the schema above can be used without causing SQL errors. This would allow GENERATED ALWAYS columns to work as intended, without manual workarounds in or around the generated code, which excludes the column manually.

[go-jet]

Agree, make sense.

[go-jet]

Support added in the Release v.2.14.0.