CODEWITHSUNDEEP
mysqlalter
mklauber
mklauber

Reputation: 1144

Unable to set default on JSON column in mysql 8.0.31

I'm trying to set a default for a new JSON column in a mysql 8.0.31 db. As far as I am aware, this wasn't possible till version 8.0.13. Starting with version 8.0.13, it should be possible to set this as long as the value is an expression, e.g. DEFAULT ('[]').

However, I'm having issues with the following command.

ALTER TABLE `...` ALTER COLUMN `...` SET DEFAULT ('[]'), ALGORITHM=INSTANT When I run that, I get the error message

1101, "BLOB, TEXT, GEOMETRY or JSON column '...' can't have a default value")

I'm uncertain why that expression is still throwing a error message, and why it's saying that column can't have a default value. I've found if I do:

ALTER TABLE `...` ALTER COLUMN `...` SET DEFAULT (QUOTE('[]')), ALGORITHM=INSTANT

It will succeed, but the default in SHOW COLUMNS ... is quote(_utf8mb3\'[]\') not _utf8mb3\'[]\' as in other places.

Upvotes: -1

Views: 137

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562811

This seems to be an undocumented behavior of ALTER TABLE...ALTER COLUMN. In my test, if I use an expression that is simply a string literal, it's as if the parentheses around it are removed.

I'll show this with a VARCHAR column, which does accept either a literal value or an expression as a default.

mysql> create table mytable ( v varchar(10) );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table mytable alter column v set default ('dflt');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v     | varchar(10) | YES  |     | dflt    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table mytable modify column v varchar(10) default ('dflt');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+-------+-------------+------+-----+------------------+-------------------+
| Field | Type        | Null | Key | Default          | Extra             |
+-------+-------------+------+-----+------------------+-------------------+
| v     | varchar(10) | YES  |     | _utf8mb4\'dflt\' | DEFAULT_GENERATED |
+-------+-------------+------+-----+------------------+-------------------+
1 row in set (0.00 sec)

mysql> alter table mytable alter column v set default (concat('dflt'));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+-------+-------------+------+-----+--------------------------+-------------------+
| Field | Type        | Null | Key | Default                  | Extra             |
+-------+-------------+------+-----+--------------------------+-------------------+
| v     | varchar(10) | YES  |     | concat(_utf8mb4\'dflt\') | DEFAULT_GENERATED |
+-------+-------------+------+-----+--------------------------+-------------------+
1 row in set (0.00 sec)

This shows that it works differently if you use ALTER COLUMN versus MODIFY COLUMN. In the case of ALTER COLUMN, if the default is simply a string literal, even if it's within parentheses, it applies it as a literal default. If the default is any kind of expression, in my example just CONCAT(), it preserves the fact that it's an expression default.

Whereas MODIFY COLUMN doesn't have this undocumented behavior. If you set a default as a simple literal, but inside parentheses, it is saved as an expression default.

I haven't found anyplace in the MySQL documentation that explains this. I think it's just a weird inconsistency in the implementation of ALTER COLUMN.

Upvotes: 1

Related Questions