Unexpected syntax error when using AFTER clause in ALTER TABLE statement

[makiuchi-d]

I encountered an unexpected syntax error while attempting to add a new column to an existing table using the AFTER clause in an ALTER TABLE statement.

Steps to Reproduce

1. Create a table named table1 with the following structure:

CREATE TABLE `table1` (
  `id` int NOT NULL,
  `value` int NOT NULL,
  PRIMARY KEY (`id`)
)

2. Attempt to add a new column value2 after the value column:

ALTER TABLE table1 ADD COLUMN value2 TEXT AFTER value;

Expected Behavior

The new column value2 should be added after the value column without any errors.

Actual Behavior

The following error is returned:

ERROR 1105 (HY000): syntax error at position 54 near 'value'

Additional Information

• Removing the AFTER value clause allows the statement to execute successfully:

ALTER TABLE table1 ADD COLUMN value2 TEXT;

• Using a different existing column in the AFTER clause, such as AFTER id, also works as expected:

ALTER TABLE table1 ADD COLUMN value2 TEXT AFTER id;

• The original statement with AFTER value executes successfully in MySQL 8.0.

[makiuchi-d]

I found that the cause of this error was the use of the column name value.
When I quoted the column name, the statement executed successfully:

MySQL [mydb]> ALTER TABLE table1 ADD COLUMN value2 TEXT AFTER `value`;
Query OK, 0 rows affected (0.000 sec)

[timsehn]

Glad it wasn't us :-)

[makiuchi-d]

Yes, I agree. That said, it's a bit of an incompatibility with MySQL.