Will MODIFY COLUMN reset attributes that are not specified in the statement?

Question

Will the MODIFY COLUMN statement discard attributes that are not specified in MariaDB?

E.g.; considering the following table and statement, will the default value be preserved after executing the statement?

t
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | int(11) | NO   | PRI | 5       |       |
+-------+---------+------+-----+---------+-------+

ALTER TABLE t
MODIFY COLUMN c INT(11) NULL

The documentation is a little unclear in my opinion;

Allows you to modify the type of a column. The column will be at the same place as the original column and all indexes on the column will be kept. Note that when modifying column, you should specify all attributes for the new column.

Doest this mean that all attributes should be specified or will be lost?

Answer 1

The default attribute will be lost.

MariaDB [test]> DESC test_table;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| column_a | int(11) | NO   | PRI | 5       |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.002 sec)

MariaDB [test]> ALTER TABLE test_table
    -> MODIFY COLUMN column_a INT(11) NULL;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC test_table;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| column_a | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.000 sec)

As per documentation, you need to specify all attributes, and in your example you do not explicitly specify the default value. Compare:

MariaDB [test]> ALTER TABLE test_table
    -> MODIFY COLUMN column_a INT(11) NULL DEFAULT 5;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC test_table;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| column_a | int(11) | NO   | PRI | 5       |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.001 sec)

Hope this helps.