After Migration from MYSQL to Maria DB, query returns different cases for a result sets column names

Question

Were attempting to migrate mysql servers to MariaDB on a new AWS (amazon-linux-2) instance. After creating a new instance and installing mariadb we've dumped the old database via mysqldump ... --add-drop-database --triggers --routines --events and imported into the new system using mysql -u ... < dump.sql

The database has a table defined with mixture of uppercase and lower case columns e.g COLUMN1,COLUMN2,column3,etc

The original database, via mysqld -V, is mysqld Ver 14.14 Distrib 5.5.62, for Linux (x86_64)

The new database is, via mysqld -V, is mysqld Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) installed with yum install mariadb-server

I've verified the schema's columns match for the table in question.

Querying the table with a select statement requesting the columns as lowercase. (I've connected a local application and tested executing the same query against both database)

e.g. SELECT column1,column2,column3,etc from TABLE1 where ..

The results from the query from the original database respect the casing in the "select" statement, however in the new mariadb database, the query results match the column casing as defined in the table.

Is there a setting that can be applied that will cause the result column names to respect the casing in the "SELECT" statement, and therefore cause the result column names returned by the same select statement in both databases to match?

A testcase under MariaDB and Mysql

select cname from (select s.CName from (select 'A' as CNAME) s) t;

Under MariaDB the result set column casing matches the column from the intermediary temp table.

With the same query using the source mysql database,

The result set column name's casing matches the casing in the select statement

Answer 1

(Thanks for the complete test case.)

Different Optimizers:

mysql> explain
    -> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 | NULL           |
|  2 | DERIVED     | <derived3> | system | NULL          | NULL | NULL    | NULL |    1 | NULL           |
|  3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
3 rows in set (0.01 sec)

mysql> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 5.6.22-71.0-log |
+-----------------+

versus

mysql> explain
    -> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+------+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id   | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|    1 | PRIMARY     | <derived3> | system | NULL          | NULL | NULL    | NULL |    1 |                |
|    3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+------+-------------+------------+--------+---------------+------+---------+------+------+----------------+
2 rows in set (0.00 sec)

mysql> select @@version;
+--------------------------------------+
| @@version                            |
+--------------------------------------+
| 10.4.1-MariaDB-1:10.4.1+maria~bionic |
+--------------------------------------+

Notice how MariaDB was smart enough to throw away one layer of your query.

(MySQL/MariaDB questions are better handled on stackoverflow.com or dba.stackexchange.com .)

But Oracle did catch up at some point:

mysql> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+-------+
| CName |
+-------+
| A     |
+-------+
1 row in set (0.00 sec)

mysql> explain
    -> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  3 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.00 sec)