MySQL Bugs: #115738: Modify column length, and the column name case was changed.

Bug #115738	Modify column length, and the column name case was changed.
Submitted:	1 Aug 2024 9:00	Modified:	2 Aug 2024 2:17
Reporter:	yongguang liao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	8.0.15, modify column

Description:
When I executed ALTER TABLE example_table MODIFY request_id VARCHAR(50) NOT NULL;, the column name case was changed. The column name changed from uppercase (REQUEST_ID) to lowercase (request_id).

How to repeat:
1、Use uppercase for defining database column names (REQUEST_ID).
CREATE TABLE example_table (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    REQUEST_ID VARCHAR(20) NOT NULL
);

2、Use lowercase column names (request_id) in the 'ALTER TABLE ... MODIFY' statement.

ALTER TABLE example_table MODIFY request_id VARCHAR(50) NOT NULL;

3、Describe the table structure.
desc example_table;
+-----------------+---------------+------+-----+----------------------+--------------------------------+
| Field           | Type          | Null | Key | Default              | Extra                          |
+-----------------+---------------+------+-----+----------------------+--------------------------------+
| ID              | int(11)       | NO   | PRI | NULL                 | auto_increment                 |
| request_id      | varchar(50)   | YES  |     | NULL                 |                                |
+-----------------+---------------+------+-----+----------------------+--------------------------------+
2 rows in set (0.00 sec)

The column length was modified, and at the same time, the column name changed from uppercase (REQUEST_ID) to lowercase (request_id).

Suggested fix:
I hope the ALTER TABLE ... MODIFY command does not change the column name. Regardless of whether the name following MODIFY is in uppercase or lowercase, the original column name should remain unchanged. Maintain the field name as it is.

Hi Mr. weew,

Thank you for your bug report.

However, this is not a bug.

Simply, SQL is a case-insensitive interpreter, so you can mix uppercase and lowercase letters in keywords and column names. When it comes to table names, it depends on the operating system and your MySQL settings.

Regarding string literals, whether they are case sensitive or not, depends on the collation chosen. But, column names, functions, keywords are case insensitive , as defined by the SQL Standard.

Not a bug.

Thank you for your prompt response.core_coupon_rec_inf

I understand that column names are case-insensitive at the SQL level, so using lowercase in ALTER TABLE can still modify the field length.

However, being case-insensitive does not mean that ALTER TABLE ... MODIFY can change the case of the column name, right? My understanding is that while SQL does not distinguish case when using the names, the column name itself should not be changed arbitrarily. To change the column name, should we use ALTER TABLE ... CHANGE? I'm not sure if my understanding is correct.

Hi Mr. weew,

Unfortunately, your understanding is not correct. 

SQL is case insensitive language, except for the cases that we already described.

Also, MySQL is trying, whenever it is possible,  to do what ever user commands.

This is a behaviour demanded by millions of our users.

So, to get the case of your choice, you should use the case of your choice.

Not a bug.

Thank you for your patient and detailed response.

I understand your point. MySQL is trying, whenever possible, to follow user commands. It tries to be as fault-tolerant as possible and do what the user intends.

In the MySQL development manual, at the address: https://dev.mysql.com/doc/refman/8.4/en/alter-table.html, in the section "Renaming, Redefining, and Reordering Columns," it mentions:

MODIFY:
Can change a column definition but not its name.
More convenient than CHANGE to change a column definition without renaming it.
With FIRST or AFTER, can reorder columns.
The document then provides an example:

To change a column definition but not its name, use CHANGE or MODIFY. With CHANGE, the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. For example, to change the definition of column b, do this:

ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY is more convenient to change the definition without changing the name because it requires the column name only once:

ALTER TABLE t1 MODIFY b INT NOT NULL;

Add the following:

In the MySQL development manual, at the address: https://dev.mysql.com/doc/refman/8.4/en/alter-table.html, in the section 15.1.9 ALTER TABLE Statement, in the part Renaming, Redefining, and Reordering Columns,