[MDEV-23852] alter table rename column to uppercase doesn't work Created: 2020-09-30  Updated: 2020-10-20  Resolved: 2020-10-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.5.5, 10.5
Fix Version/s: 10.5.7

Type: Bug Priority: Major
Reporter: Georg Richter Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax Closed
Relates
relates to MDEV-13671 InnoDB should use case-insensitive co... Closed

 Description   

When trying to change a column name (lowercase to uppercase) it has no effect:

MariaDB 10.5.5:

MariaDB [test]> create table t1 (columnname int);
 
Query OK, 0 rows affected (0.017 sec)
 
 
 
MariaDB [test]> alter table t1 rename column columnname to COLUMNNAME;
 
Query OK, 0 rows affected (0.001 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table t1\G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `columnname` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.001 sec)

MySQL 8.0.21:

MySQL [test]> create table t1 (columnname int);
 
Query OK, 0 rows affected (0.057 sec)
 
 
 
MySQL [test]> alter table t1 rename column columnname to COLUMNNAME;
 
Query OK, 0 rows affected (0.026 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MySQL [test]> show create table t1\G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `COLUMNNAME` int DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 
1 row in set (0.001 sec)



 Comments   
Comment by Oleksandr Byelkin [ 2020-10-14 ]

it is not case insensitive (as you call it during our call ) check it is per-byte-check (may be you know better term) which is different things for some languages, so fix comment:
1) add better description why you changed the comparison (field name shoud be changed even if name differ in case?)
2) add comment before comparison with the reson.

OK to push after changes.

Comment by Marko Mäkelä [ 2020-10-20 ]

sanja, midenok, is this bug really only affecting the 10.5 series? There was a somewhat related change in MariaDB 10.3.1 that changed some comparisons to case-insensitive, but not this one. Did anyone review those changes? For InnoDB, not only column names but also index and table names are case sensitive.

It turns out that this bug was there since the introduction of the FIELD_IS_RENAMED flag (MariaDB 10.0.10, MariaDB 10.1.0).

Comment by Marko Mäkelä [ 2020-10-20 ]

While the RENAME COLUMN syntax is new in MariaDB 10.5, renaming columns as a quick metadata-only operation was supported since MariaDB 10.0 (and MySQL 5.6). MDEV-13671 worked around this problem that would cause column case mismatch between the InnoDB internal dictionary and the .frm files under some circumstances.

The original syntax for renaming columns should still work:

--source include/have_innodb.inc
 
create table t1 (columnname int not null) engine=innodb;
 
alter table t1 change columnname ColumnName int not null;
 
show create table t1;
 
drop table t1;

I tested this in 10.2, and it appears that the column will be renamed. Maybe this test is covering some other code path?

Generated at Thu Feb 08 09:25:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.