Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23852

alter table rename column to uppercase doesn't work

Details

    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)

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13671 InnoDB should use case-insensitive column name comparisons like the rest of the server

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            sanja Oleksandr Byelkin added a comment -

            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.

            sanja Oleksandr Byelkin added a comment - 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.
            marko Marko Mäkelä added a comment -

            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).

            marko Marko Mäkelä added a comment - 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).
            marko Marko Mäkelä added a comment -

            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?

            marko Marko Mäkelä added a comment - 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?

            People

              midenok Aleksey Midenkov
              georg Georg Richter
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.