Modifying a column is order-sensitive for NOT NULL and COLLATE

[dphil]

In MySQL, when modifying a column in an ALTER TABLE operation, you can specify the column as being NOT NULL as well as its collation in any order.

In Dolt, it reports a syntax error if NOT NULL is defined before COLLATE.

MySQL:

mysql> CREATE TABLE `users` (`name` varchar(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `users` modify `name` varchar(255) NOT NULL COLLATE 'utf8mb4_0900_ai_ci';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show full columns from users;
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| name  | varchar(255) | utf8mb4_0900_ai_ci | NO   |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

Dolt:

dolt> CREATE TABLE `users` (`name` varchar(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

dolt> ALTER TABLE `users` modify `name` varchar(255) NOT NULL COLLATE 'utf8mb4_0900_ai_ci';
Error parsing SQL
syntax error at position 64 near 'COLLATE'
ALTER TABLE `users` modify `name` varchar(255) NOT NULL COLLATE 'utf8mb4_0900_ai_ci'
                                                                ^

dolt> ALTER TABLE `users` modify `name` varchar(255) COLLATE 'utf8mb4_0900_ai_ci' NOT NULL;

dolt> describe users;
+-------+-----------------------------------------+------+-----+---------+-------+
| Field | Type                                    | Null | Key | Default | Extra |
+-------+-----------------------------------------+------+-----+---------+-------+
| name  | varchar(255) COLLATE utf8mb4_0900_ai_ci | NO   |     | NULL    |       |
+-------+-----------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

[dphil]

Easily worked around by just ordering the column properties as Dolt requires, though can be a little inconvenient with some connectors such as knex's schema builder which apparently always orders NOT NULL before COLLATE, thus requiring to break out into a raw sql statement to reorder them.

[timsehn]

Thanks @Hydrocharged will pick this up tonight.

[Hydrocharged]

I apologize for the delay, I thought this would take 30 min but ended up taking almost 2 days. To put it short, allowing NOT NULL to come before COLLATE made the parser ambiguous, and finding the issue was not obvious at all (along with figuring out a fix). Turns out our handling of DEFAULT values was completely wrong, and we accounted for it in go-mysql-server, but that incorrect-ness was the root cause of the ambiguity in our parser.

The PR fixing this issue is linked in the sidebar. We'll do another release once the change makes its way into Dolt later today.