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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
#
# Tests that requires lower_case_table_names to be 0
# (Normal case on Unix)
#
--source include/have_lowercase0.inc
--source include/have_case_sensitive_file_system.inc
#
# Check caching of database options
#
CREATE DATABASE mysql_TEST CHARACTER SET utf8;
SHOW CREATE DATABASE mysql_TEST;
CREATE DATABASE mysql_test CHARACTER SET latin2;
SHOW CREATE DATABASE mysql_test;
SHOW CREATE DATABASE mysql_TEST;
DROP DATABASE mysql_test;
DROP DATABASE mysql_TEST;
# End of 10.0 tests
--echo #
--echo # Start of 10.4 tests
--echo #
--echo #
--echo # MDEV-33019 The database part is not case sensitive in SP names
--echo #
CREATE DATABASE DB1;
CREATE DATABASE db1;
CREATE PROCEDURE DB1.sp() SELECT 'This is DB1.sp' AS ret;
CREATE PROCEDURE db1.sp() SELECT 'This is db1.sp' AS ret;
CALL DB1.sp();
CALL db1.sp();
DROP DATABASE DB1;
--error ER_SP_DOES_NOT_EXIST
CALL DB1.sp();
CALL db1.sp();
DROP DATABASE db1;
CREATE PROCEDURE SP() SELECT 'This is SP' AS ret;
--error ER_SP_ALREADY_EXISTS
CREATE PROCEDURE sp() SELECT 'This is sp' AS ret;
CALL SP();
CALL sp();
DROP PROCEDURE SP;
--echo #
--echo # End of 10.4 tests
--echo #
--echo #
--echo # Start of 10.5 tests
--echo #
--echo #
--echo # MDEV-33084 LASTVAL(t1) and LASTVAL(T1) do not work well with lower-case-table-names=0
--echo #
CREATE SEQUENCE t1;
CREATE SEQUENCE T1;
--disable_ps2_protocol
SELECT nextval(t1), lastval(t1);
SELECT nextval(T1), lastval(T1);
SELECT lastval(t1), lastval(T1) l2;
--enable_ps2_protocol
DROP SEQUENCE t1, T1;
--echo #
--echo # MDEV-33086 SHOW OPEN TABLES IN DB1 -- is case insensitive with lower-case-table-names=0
--echo #
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
SELECT * FROM db1.t1;
SHOW OPEN TABLES IN DB1;
SHOW OPEN TABLES IN db1;
DROP DATABASE db1;
--echo #
--echo # MDEV-33088 Cannot create triggers in the database `MYSQL`
--echo #
CREATE DATABASE MYSQL;
CREATE TABLE MYSQL.t1 (a INT);
CREATE TABLE MYSQL.t2 (a INT);
CREATE TRIGGER MYSQL.tr1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (new.a);
INSERT INTO MYSQL.t1 VALUES (10);
SELECT * FROM MYSQL.t1;
SELECT * FROM MYSQL.t2;
DROP DATABASE MYSQL;
--echo #
--echo # MDEV-33103 LOCK TABLE t1 AS t2 -- alias is not case sensitive with lower-case-table-names=0
--echo #
--disable_view_protocol
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
LOCK TABLE t1 AS t2 READ;
SELECT * FROM t1 AS t2;
UNLOCK TABLES;
LOCK TABLE t1 AS t2 READ;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t1 AS T2;
UNLOCK TABLES;
DROP TABLE t1;
--enable_view_protocol
--echo #
--echo # MDEV-33108 TABLE_STATISTICS and INDEX_STATISTICS are case insensitive with lower-case-table-names=0
--echo #
SET GLOBAL userstat=1;
CREATE TABLE t1 (a INT, KEY(a));
INSERT INTO t1 VALUES (1),(2),(3),(4);
--disable_ps2_protocol
SELECT * FROM t1 ORDER BY a;
CREATE TABLE T1 (a INT, KEY(a));
INSERT INTO T1 VALUES (1),(2),(3),(4);
SELECT * FROM T1 ORDER BY a;
--enable_ps2_protocol
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS ORDER BY BINARY TABLE_NAME;
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS ORDER BY BINARY TABLE_NAME;
DROP TABLE t1;
DROP TABLE T1;
SET GLOBAL userstat=DEFAULT;
--echo #
--echo # MDEV-33109 DROP DATABASE MYSQL -- does not drop SP with lower-case-table-names=0
--echo #
CREATE DATABASE MYSQL;
CREATE FUNCTION MYSQL.f1() RETURNS INT RETURN 1;
DROP DATABASE MYSQL;
SELECT db, name, body FROM mysql.proc WHERE db=BINARY 'MYSQL' AND name='f1';
--echo #
--echo # MDEV-33110 HANDLER commands are case insensitive with lower-case-table-names=0
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
HANDLER t1 OPEN;
HANDLER t1 READ FIRST;
CREATE OR REPLACE TABLE T1 (a INT);
DROP TABLE T1;
HANDLER t1 READ NEXT;
HANDLER t1 CLOSE;
DROP TABLE t1;
--echo #
--echo # MDEV-33120 System log table names are case insensitive with lower-cast-table-names=0
--echo #
CREATE TABLE mysql.GENERAL_log (a INT);
INSERT INTO mysql.GENERAL_log VALUES (1),(2);
DROP TABLE mysql.GENERAL_log;
CREATE TABLE mysql.SLOW_log (a INT);
INSERT INTO mysql.SLOW_log VALUES (1),(2);
DROP TABLE mysql.SLOW_log;
CREATE TABLE mysql.TRANSACTION_registry (a INT);
INSERT INTO mysql.TRANSACTION_registry VALUES (1),(2);
DROP TABLE mysql.TRANSACTION_registry;
CREATE DATABASE MYSQL;
CREATE TABLE MYSQL.general_log (a INT);
INSERT INTO MYSQL.general_log VALUES (1),(2);
DROP TABLE MYSQL.general_log;
CREATE TABLE MYSQL.slow_log (a INT);
INSERT INTO MYSQL.slow_log VALUES (1),(2);
DROP TABLE MYSQL.slow_log;
CREATE TABLE MYSQL.transaction_registry (a INT);
INSERT INTO MYSQL.transaction_registry VALUES (1),(2);
DROP TABLE MYSQL.transaction_registry;
DROP DATABASE MYSQL;
--echo #
--echo # End of 10.5 tests
--echo #
|
