Alter table behavior (5.5 and 5.6 with strict_trans_tables)

gistfile1.txt

MySQL 5.5 and below default behaviour (can be changed with sql_mode):
————————————

mysql [localhost] {msandbox} (test) > select * from test_table;
+-----+------------+
| id  | some_value |
+-----+------------+
| 100 |          2 |
| 101 |       NULL |
| 102 |          2 |
| 103 |          2 |
+-----+------------+
4 rows in set (0.01 sec)

mysql [localhost] {msandbox} (test) > alter table test_table change `some_value` `some_value` int(3) NOT NULL DEFAULT 2;
Query OK, 4 rows affected, 1 warning (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'some_value' at row 2
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test_table;+-----+------------+
| id  | some_value |
+-----+------------+
| 100 |          2 |
| 101 |          0 |
| 102 |          2 |
| 103 |          2 |
+-----+------------+
4 rows in set (0.00 sec)

mysql 5.6 and above default behaviour (for new installs):
————

mysql [localhost] {msandbox} (test) > CREATE TABLE `test_table` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `some_value` int(3) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > insert into test_table (id, some_value) values (100,2);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test_table (id) values (101);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test_table (id, some_value) values (102, 2);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test_table (id, some_value) values (103, 2);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > select * from test_table;
+-----+------------+
| id  | some_value |
+-----+------------+
| 100 |          2 |
| 101 |       NULL |
| 102 |          2 |
| 103 |          2 |
+-----+------------+
4 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > alter table test_table change `some_value` `some_value` int(3) NOT NULL DEFAULT 2;
ERROR 1138 (22004): Invalid use of NULL value