MySQL Bugs: #103952: The column name letters capitalized while query from view

Bug #103952	The column name letters capitalized while query from view
Submitted:	9 Jun 2021 4:01	Modified:	9 Jun 2021 13:00
Reporter:	Xuming Zhang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In lower case database. Query from view of simple query and view of union, with default column name, will have different case.
If set optimizer_switch='derived_merge=off', the column name will all lower case.
 

Test through mysql command client.

How to repeat:
create table t1(a int);
create table t2(a int);
insert into t1 values(1);
insert into t2 values(2);

create view v1 as select a A from t1;
create view v2 as select a A from t1 union select a A from t2;

select a from v1;  // return with capital letter of column name， like 'A'
select a from v2;  // return with lower case letter of column name, like 'a'

set optimizer_switch='derived_merge=off';
select a from v1;
select a from v2;
// both has lower case letter of column name

mysql> show create table v1;
+------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                          | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a1` AS `A1`,`t1`.`a2` AS `A2` from `t1` | utf8mb4              | utf8mb4_0900_ai_ci   |
+------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show create table v2;
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                     | character_set_client | collation_connection |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a1` AS `A1`,`t1`.`a2` AS `A2` from `t1` union select `t2`.`a1` AS `A1`,`t2`.`a2` AS `A2` from `t2` | utf8mb4              | utf8mb4_0900_ai_ci   |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select a1 from v1;
+------+
| A1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select a1 from v2;
+------+
| a1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

Hi Mr. Zhang,

Thank you for your bug report.

We managed to repeat your behaviour:

A
1

a
1
2

a
1

a
1
2

We think that column names should not depend on the optimiser switches. However, you should know that this is a small and quite insignificant bug.

However, thank you for it !!!!!

Verified as reported.