3 files changed, 57 insertions, 6 deletions
diff --git a/mysql-test/main/lowercase_table2.opt b/mysql-test/main/lowercase_table2.opt
new file mode 100644
index 00000000000..ac4d3211e89
--- /dev/null
+++ b/mysql-test/main/lowercase_table2.opt
@@ -0,0 +1 @@
+--lower-case-table-names=2
diff --git a/mysql-test/main/lowercase_table2.result b/mysql-test/main/lowercase_table2.result
index 9194638a4d2..fe06fb671a2 100644
--- a/mysql-test/main/lowercase_table2.result
+++ b/mysql-test/main/lowercase_table2.result
@@ -14,7 +14,7 @@ SHOW CREATE TABLE T1;
 Table	Create Table
 T1	CREATE TABLE `T1` (
   `a` int(11) DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 RENAME TABLE T1 TO T2;
 SHOW TABLES LIKE "T2";
 Tables_in_test (T2)
@@ -70,7 +70,7 @@ SHOW CREATE TABLE T1;
 Table	Create Table
 T1	CREATE TABLE `T1` (
   `a` int(11) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 RENAME TABLE T1 TO T2;
 SHOW TABLES LIKE "T2";
 Tables_in_test (T2)
@@ -319,18 +319,42 @@ Database (mysql_t%)
 mysql_TEST
 show create database mysql_test;
 Database	Create Database
-mysql_test	CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 */
+mysql_test	CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */
 show create database mysql_TEST;
 Database	Create Database
-mysql_TEST	CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 */
+mysql_TEST	CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */
 show create table mysql_TEST.T1;
 Table	Create Table
 T1	CREATE TABLE `T1` (
   `a` int(11) DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 show create table mysql_test.t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 drop database mysql_TEST;
+# MDEV-30765 SHOW TABLES not working properly with
+# lower_case_table_names=2
+#
+create database db1;
+use db1;
+# lowercase table name
+create table `a` (a int);
+# uppercase table name
+create table `B` (a int);
+create user 'mysqltest_1'@'localhost' identified by 'password';
+grant select, show view on db1.`a` to 'mysqltest_1'@'localhost';
+grant select, show view on db1.`B` to 'mysqltest_1'@'localhost';
+connect  conn1, localhost, mysqltest_1, password, test;
+connection conn1;
+use db1;
+show tables;
+Tables_in_db1
+B
+a
+connection default;
+disconnect conn1;
+drop user 'mysqltest_1'@'localhost';
+drop tables a, B;
+drop database db1;
diff --git a/mysql-test/main/lowercase_table2.test b/mysql-test/main/lowercase_table2.test
index 601089ca760..82c07bf9345 100644
--- a/mysql-test/main/lowercase_table2.test
+++ b/mysql-test/main/lowercase_table2.test
@@ -288,3 +288,29 @@ show create database mysql_TEST;
 show create table mysql_TEST.T1;
 show create table mysql_test.t1;
 drop database mysql_TEST;
+
+--echo # MDEV-30765 SHOW TABLES not working properly with
+--echo # lower_case_table_names=2
+--echo #
+create database db1;
+use db1;
+--echo # lowercase table name
+create table `a` (a int);
+--echo # uppercase table name
+create table `B` (a int);
+
+create user 'mysqltest_1'@'localhost' identified by 'password';
+
+grant select, show view on db1.`a` to 'mysqltest_1'@'localhost';
+grant select, show view on db1.`B` to 'mysqltest_1'@'localhost';
+
+connect (conn1, localhost, mysqltest_1, password, test);
+connection conn1;
+use db1;
+show tables;
+
+connection default;
+disconnect conn1;
+drop user 'mysqltest_1'@'localhost';
+drop tables a, B;
+drop database db1;
+\ No newline at end of file