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