MySQL Bugs: #104609: columns are case-sensitive while create functional index

Bug #104609	columns are case-sensitive while create functional index
Submitted:	13 Aug 2021 1:41	Modified:	13 Aug 2021 9:36
Reporter:	track ay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
 CREATE TABLE `t1` (
  `COL1` date NOT NULL,
  `col20` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  KEY `COL1` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

MySQL > alter table t1 add index ((col3 + 1));
(1054, "Unknown column 'col3' in 'functional index'")

MySQL root@172.16.5.103:new_data2> alter table t1 add index (col3);
Query OK, 0 rows affected
Time: 0.054s

How to repeat:
 CREATE TABLE `t1` (
  `COL1` date NOT NULL,
  `col20` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  KEY `COL1` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
alter table t1 add index ((col3 + 1));

Suggested fix:
Partition, subpartition, column, index, stored routine, event, and resource group names are not case-sensitive on any platform, nor are column aliases.

Hello track ay,

Thank you for the report and test case.
I quickly tried on latest GA 8.0.26 and confirmed that issue is not reproducible. Could you please try on 8.0.26 and report us back if you are still seeing the issue along with exact configuration file(my.cnf) in use? Thank you.

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql>  CREATE TABLE `t1` (
    ->   `COL1` date NOT NULL,
    ->   `col20` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    ->   `COL4` datetime DEFAULT NULL,
    ->   `COL3` bigint DEFAULT NULL,
    ->   `COL5` float DEFAULT NULL,
    ->   KEY `COL1` (`COL1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> alter table t1 add index ((col3 + 1));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `COL1` date NOT NULL,
  `col20` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  KEY `COL1` (`COL1`),
  KEY `functional_index` (((`COL3` + 1)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql>

regards,
Umesh

I can reproduce it on 8.0.23 but can't reproduce on 8.0.26.
It's maybe fixed.

Thank you for confirming.
Please note that we don't fix bugs in old versions, don't back port bug fixes. 
I suggest you to upgrade to latest available MySQL Server. Thank you!

regards,
Umesh