From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/lowercase_table2.result | 379 ++++++++++++++++++++++++++++++++ 1 file changed, 379 insertions(+) create mode 100755 mysql-test/main/lowercase_table2.result (limited to 'mysql-test/main/lowercase_table2.result') diff --git a/mysql-test/main/lowercase_table2.result b/mysql-test/main/lowercase_table2.result new file mode 100755 index 00000000..3a4c46eb --- /dev/null +++ b/mysql-test/main/lowercase_table2.result @@ -0,0 +1,379 @@ +DROP TABLE IF EXISTS t1,t2,t3,t2aA,t1Aa; +DROP DATABASE IF EXISTS `TEST_$1`; +DROP DATABASE IF EXISTS `test_$1`; +DROP DATABASE IF EXISTS mysqltest_LC2; +CREATE TABLE T1 (a int) ENGINE=MyISAM; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SHOW TABLES LIKE "t1"; +Tables_in_test (t1) +T1 +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +SELECT * FROM t2; +a +1 +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +Tables_in_test (T3) +t3 +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SELECT * from T1; +a +1 +DROP TABLE T1; +CREATE DATABASE `TEST_$1`; +SHOW DATABASES LIKE "TEST%"; +Database (TEST%) +TEST_$1 +test +DROP DATABASE `test_$1`; +CREATE TABLE T1 (a int) engine=innodb; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SHOW TABLES LIKE "t1"; +Tables_in_test (t1) +T1 +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +SELECT * FROM t2; +a +1 +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +Tables_in_test (T3) +t3 +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SELECT * from T1; +a +1 +DROP TABLE T1; +create database mysqltest_UPPERCASE; +CREATE TABLE mysqltest_UPPERCASE.T1 (a int) engine=innodb; +CREATE TABLE mysqltest_uppercase.t2 (a int) engine=innodb; +CREATE TABLE mysqltest_UPPERCASE.t3 (a int) engine=innodb; +drop table mysqltest_UPPERCASE.T1; +drop table mysqltest_UPPERCASE.T2; +drop table mysqltest_uppercase.t3; +drop database mysqltest_UPPERCASE; +create table T1 (EVENT_ID int auto_increment primary key, LOCATION char(20)); +insert into T1 values (NULL,"Mic-4"),(NULL,"Mic-5"),(NULL,"Mic-6"); +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +LOCATION +Mic-5 +Mic-6 +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +LOCATION +Mic-5 +Mic-6 +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +LOCATION +Mic-5 +Mic-6 +drop table T1; +create table T1 (A int); +alter table T1 add index (A); +show tables like 'T1%'; +Tables_in_test (T1%) +T1 +alter table t1 add index (A); +Warnings: +Note 1831 Duplicate index `A_2`. This is deprecated and will be disallowed in a future release +show tables like 't1%'; +Tables_in_test (t1%) +t1 +drop table t1; +create temporary table T1(a int(11), b varchar(8)); +insert into T1 values (1, 'abc'); +select * from T1; +a b +1 abc +alter table T1 add index (a); +select * from T1; +a b +1 abc +drop table T1; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +show tables; +Tables_in_mysqltest_LC2 +myUC +insert into myUC values (1),(2),(3); +select * from myUC; +i +1 +2 +3 +use test; +drop database mysqltest_LC2; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select * from myUC; +i +use test; +drop database mysqltest_LC2; +create table t2aA (col1 int); +create table t1Aa (col1 int); +select t1Aa.col1 from t1aA,t2Aa where t1Aa.col1 = t2aA.col1; +col1 +drop table t2aA, t1Aa; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES +where TABLE_SCHEMA ='mysqltest_LC2'; +TABLE_SCHEMA TABLE_NAME +mysqltest_lc2 myUC +use test; +drop database mysqltest_LC2; +# +# Bug #11758687: 50924: object names not resolved correctly +# on lctn2 systems +# +CREATE DATABASE BUP_XPFM_COMPAT_DB2; +CREATE TABLE BUP_XPFM_COMPAT_DB2.TABLE2 (c13 INT) DEFAULT CHARSET latin1; +CREATE TABLE BUP_XPFM_COMPAT_DB2.table1 (c13 INT) DEFAULT CHARSET latin1; +CREATE TABLE bup_xpfm_compat_db2.table3 (c13 INT) DEFAULT CHARSET latin1; +CREATE TRIGGER BUP_XPFM_COMPAT_DB2.trigger1 AFTER INSERT +ON BUP_XPFM_COMPAT_DB2.table1 FOR EACH ROW +update BUP_XPFM_COMPAT_DB2.table1 set c13=12; +| +CREATE TRIGGER BUP_XPFM_COMPAT_DB2.TRIGGER2 AFTER INSERT +ON BUP_XPFM_COMPAT_DB2.TABLE2 FOR EACH ROW +update BUP_XPFM_COMPAT_DB2.table1 set c13=12; +| +CREATE TRIGGER BUP_XPFM_COMPAT_DB2.TrigGer3 AFTER INSERT +ON BUP_XPFM_COMPAT_DB2.TaBle3 FOR EACH ROW +update BUP_XPFM_COMPAT_DB2.table1 set c13=12; +| +SELECT trigger_schema, trigger_name, event_object_table FROM +INFORMATION_SCHEMA.TRIGGERS +WHERE trigger_schema COLLATE utf8_bin = 'BUP_XPFM_COMPAT_DB2' + ORDER BY trigger_schema, trigger_name; +trigger_schema trigger_name event_object_table +BUP_XPFM_COMPAT_DB2 trigger1 table1 +BUP_XPFM_COMPAT_DB2 TRIGGER2 TABLE2 +BUP_XPFM_COMPAT_DB2 TrigGer3 table3 +DROP DATABASE BUP_XPFM_COMPAT_DB2; +# End of 5.1 tests +# +# Test for bug #44738 "fill_schema_table_from_frm() opens tables without +# lowercasing table name". Due to not properly normalizing table names +# in lower_case_table_names modes in this function queries to I_S which +# were executed through it left entries with incorrect key in table +# definition cache. As result further queries to I_S that used this +# function produced stale results in cases when table definition was +# changed by a DDL statement. Also combination of this issue and a +# similar problem in CREATE TABLE (it also has peeked into table +# definition cache using non-normalized key) led to spurious +# ER_TABLE_EXISTS_ERROR errors when one tried to create table with the +# same name as a previously existing but dropped table. +# +drop database if exists mysqltest_UPPERCASE; +drop table if exists t_bug44738_UPPERCASE; +create database mysqltest_UPPERCASE; +use mysqltest_UPPERCASE; +select database(); +database() +mysqltest_UPPERCASE +create table t_bug44738_UPPERCASE (i int) comment='Old comment'; +create table t_bug44738_lowercase (i int) comment='Old comment'; +create table t_bug44738_UPPERCASE2 (i int) comment='Old comment'; +create table t_bug44738_lowercase2 (i int) comment='Old comment'; +select table_schema, table_name, table_comment from information_schema.tables +where table_schema like 'mysqltest_%' and table_name like 't_bug44738_%' + order by table_name; +table_schema table_name table_comment +mysqltest_UPPERCASE t_bug44738_lowercase Old comment +mysqltest_UPPERCASE t_bug44738_lowercase2 Old comment +mysqltest_UPPERCASE t_bug44738_UPPERCASE Old comment +mysqltest_UPPERCASE t_bug44738_UPPERCASE2 Old comment +alter table t_bug44738_UPPERCASE comment='New comment'; +alter table t_bug44738_lowercase comment='New comment'; +alter table mysqltest_uppercase.t_bug44738_UPPERCASE2 comment='New comment'; +alter table mysqltest_UPPERCASE.t_bug44738_lowercase2 comment='New comment'; +# There should be no stale entries in TDC for our tables after the +# above ALTER TABLE statements so new version of comments should be +# returned by the below query to I_S. +select table_schema, table_name, table_comment from information_schema.tables +where table_schema like 'mysqltest_%' and table_name like 't_bug44738_%' + order by table_name; +table_schema table_name table_comment +mysqltest_UPPERCASE t_bug44738_lowercase New comment +mysqltest_UPPERCASE t_bug44738_lowercase2 New comment +mysqltest_UPPERCASE t_bug44738_UPPERCASE New comment +mysqltest_UPPERCASE t_bug44738_UPPERCASE2 New comment +drop database mysqltest_UPPERCASE; +use test; +# Let us check that the original test case which led to discovery +# of this problem also works. +create table t_bug44738_UPPERCASE (i int); +select table_schema, table_name, table_comment from information_schema.tables +where table_schema = 'test' and table_name like 't_bug44738_%'; +table_schema table_name table_comment +test t_bug44738_UPPERCASE +drop table t_bug44738_UPPERCASE; +# After the above DROP TABLE there are no entries in TDC which correspond +# to our table and therefore the below statement should succeed. +create table t_bug44738_UPPERCASE (i int); +drop table t_bug44738_UPPERCASE; +# Finally, let us check that another issue which was exposed by +# the original test case is solved. I.e. that the table is not +# created if there is an entry for it in TDC even though it was +# removed from disk. +create table t_bug44738_UPPERCASE (i int) engine = myisam; +# Load table definition in TDC. +select table_schema, table_name, table_comment from information_schema.tables +where table_schema = 'test' and table_name like 't_bug44738_%'; +table_schema table_name table_comment +test t_bug44738_UPPERCASE +# Simulate manual removal of the table. +# Check that still there is an entry for table in TDC. +show open tables like 't_bug44738_%'; +Database Table In_use Name_locked +test t_bug44738_uppercase 0 0 +# So attempt to create table with the same name should fail. +create table t_bug44738_UPPERCASE (i int); +ERROR 42S01: Table 't_bug44738_uppercase' already exists +# And should succeed after FLUSH TABLES. +flush tables; +create table t_bug44738_UPPERCASE (i int); +drop table t_bug44738_UPPERCASE; +# +# Bug #21317 SHOW CREATE DATABASE does not obey to +# lower_case_table_names +# +create database mysql_TEST; +create table mysql_TEST.T1(a int); +show databases like 'mysql%'; +Database (mysql%) +mysql +mysql_TEST +show databases like 'mysql_T%'; +Database (mysql_T%) +mysql_TEST +show databases like 'mysql_t%'; +Database (mysql_t%) +mysql_TEST +show create database mysql_test; +Database Create Database +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 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 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 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, db1; +show tables; +Tables_in_db1 +B +a +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; +# +# MDEV-32025 Crashes in MDL_key::mdl_key_init with lower-case-table-names=2 +# +CREATE DATABASE `#mysql50#D+b1`; +ALTER DATABASE `#mysql50#D+b1` UPGRADE DATA DIRECTORY NAME; +SHOW CREATE DATABASE `D+b1`; +Database Create Database +D+b1 CREATE DATABASE `D+b1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ +SHOW CREATE DATABASE `d+b1`; +Database Create Database +d+b1 CREATE DATABASE `d+b1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ +DROP DATABASE `D+b1`; +CREATE DATABASE Db1; +ALTER DATABASE Db1 DEFAULT CHARACTER SET utf8; +SHOW CREATE DATABASE Db1; +Database Create Database +Db1 CREATE DATABASE `Db1` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */ +SHOW CREATE DATABASE db1; +Database Create Database +db1 CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */ +DROP DATABASE Db1; -- cgit v1.2.3