1# 2# Test of --lower-case-table-names 3# 4 5create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); 6create table t4 (id int primary key, Word varchar(40) not null); 7INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); 8INSERT INTO T4 VALUES(1,'match'); 9SELECT * FROM t1; 10SELECT T1.id from T1 LIMIT 1; 11SELECT T2.id from t1 as T2 LIMIT 1; 12SELECT * from t1 left join t4 on (test.t1.id= TEST.t4.id) where TEST.t1.id >= test.t4.id; 13# This gave an error in 4.0, but it's fixed in 4.1 14SELECT T2.id from t1 as t2 LIMIT 1; 15RENAME TABLE T1 TO T2; 16ALTER TABLE T2 ADD new_col int not null; 17ALTER TABLE T2 RENAME T3; 18show tables like 't_'; 19drop table t3,t4; 20# 21# Test alias 22# 23create table t1 (a int); 24select count(*) from T1; 25select count(*) from t1; 26select count(T1.a) from t1; 27select count(bags.a) from t1 as Bags; 28drop table t1; 29 30# 31# Test all caps database name 32# 33create database mysqltest; 34use MYSQLTEST; 35create table t1 (a int); 36select T1.a from MYSQLTEST.T1; 37select t1.a from MYSQLTEST.T1; 38select mysqltest.t1.* from MYSQLTEST.t1; 39select MYSQLTEST.t1.* from MYSQLTEST.t1; 40select MYSQLTEST.T1.* from MYSQLTEST.T1; 41select MYSQLTEST.T1.* from T1; 42alter table t1 rename to T1; 43select MYSQLTEST.t1.* from MYSQLTEST.t1; 44drop database mysqltest; 45use test; 46 47# 48# multiupdate/delete & --lower-case-table-names 49# 50create table t1 (a int); 51create table t2 (a int); 52delete p1.*,P2.* from t1 as p1, t2 as p2 where p1.a=P2.a; 53delete P1.*,p2.* from t1 as P1, t2 as P2 where P1.a=p2.a; 54update t1 as p1, t2 as p2 SET p1.a=1,P2.a=1 where p1.a=P2.a; 55update t1 as P1, t2 as P2 SET P1.a=1,p2.a=1 where P1.a=p2.a; 56drop table t1,t2; 57 58# 59# aliases case insensitive 60# 61create table t1 (a int); 62create table t2 (a int); 63--error ER_NONUNIQ_TABLE 64select * from t1 c, t2 C; 65--error ER_NONUNIQ_TABLE 66select C.a, c.a from t1 c, t2 C; 67drop table t1, t2; 68 69--echo # 70--echo # Bug #9761: CREATE TABLE ... LIKE ... not handled correctly when lower_case_table_names is set 71--echo # 72 73create table t1 (a int); 74create table t2 like T1; 75drop table t1, t2; 76 77show tables; 78--echo # 79--echo # End of 4.1 tests 80--echo # 81 82--echo # 83--echo # Bug#20404: SHOW CREATE TABLE fails with Turkish I 84--echo # 85set names utf8; 86create table İ (s1 int); 87show create table İ; 88show tables; 89drop table İ; 90create table İİ (s1 int); 91show create table İİ; 92show tables; 93drop table İİ; 94set names latin1; 95 96--echo # 97--echo # End of 5.0 tests 98--echo # 99 100--echo # 101--echo # Bug#21317: SHOW CREATE DATABASE does not obey to lower_case_table_names 102--echo # 103create database mysql_TEST character set latin2; 104create table mysql_TEST.T1 (a int); 105show create database mysql_TEST; 106show create table mysql_TEST.T1; 107show databases like "mysql%"; 108show databases like "mysql_TE%"; 109drop database mysql_TEST; 110 111--echo # 112--echo # End of 10.0 tests 113--echo # 114 115--echo # 116--echo # MDEV-17148 DROP DATABASE throw "Directory not empty" after changed lower_case_table_names. 117--echo # 118 119let $datadir=`select @@datadir`; 120create database db1; 121create table t1 (a int); 122copy_file $datadir/test/t1.frm $datadir/db1/T1.frm; 123drop database db1; 124drop table t1; 125 126--echo # 127--echo # End of 10.2 tests 128--echo # 129 130--echo # 131--echo # MDEV-25109 Server crashes in sp_name::sp_name upon invalid data in mysql.proc 132--echo # 133call mtr.add_suppression("Stored routine ''.'': invalid value in column"); 134insert ignore into mysql.proc () values (); 135--error ER_SP_WRONG_NAME 136show function status; 137delete from mysql.proc where name = ''; 138 139--echo # 140--echo # End of 10.3 tests 141--echo # 142