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
190
191
|
CREATE DATABASE mysql_TEST CHARACTER SET utf8;
SHOW CREATE DATABASE mysql_TEST;
Database Create Database
mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci */
CREATE DATABASE mysql_test CHARACTER SET latin2;
SHOW CREATE DATABASE mysql_test;
Database Create Database
mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin2 COLLATE latin2_general_ci */
SHOW CREATE DATABASE mysql_TEST;
Database Create Database
mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci */
DROP DATABASE mysql_test;
DROP DATABASE mysql_TEST;
#
# Start of 10.4 tests
#
#
# MDEV-33019 The database part is not case sensitive in SP names
#
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();
ret
This is DB1.sp
CALL db1.sp();
ret
This is db1.sp
DROP DATABASE DB1;
CALL DB1.sp();
ERROR 42000: PROCEDURE DB1.sp does not exist
CALL db1.sp();
ret
This is db1.sp
DROP DATABASE db1;
CREATE PROCEDURE SP() SELECT 'This is SP' AS ret;
CREATE PROCEDURE sp() SELECT 'This is sp' AS ret;
ERROR 42000: PROCEDURE sp already exists
CALL SP();
ret
This is SP
CALL sp();
ret
This is SP
DROP PROCEDURE SP;
#
# End of 10.4 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-33084 LASTVAL(t1) and LASTVAL(T1) do not work well with lower-case-table-names=0
#
CREATE SEQUENCE t1;
CREATE SEQUENCE T1;
SELECT nextval(t1), lastval(t1);
nextval(t1) lastval(t1)
1 1
SELECT nextval(T1), lastval(T1);
nextval(T1) lastval(T1)
1 1
SELECT lastval(t1), lastval(T1) l2;
lastval(t1) l2
1 1
DROP SEQUENCE t1, T1;
#
# MDEV-33086 SHOW OPEN TABLES IN DB1 -- is case insensitive with lower-case-table-names=0
#
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
SELECT * FROM db1.t1;
a
SHOW OPEN TABLES IN DB1;
Database Table In_use Name_locked
SHOW OPEN TABLES IN db1;
Database Table In_use Name_locked
db1 t1 0 0
DROP DATABASE db1;
#
# MDEV-33088 Cannot create triggers in the database `MYSQL`
#
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;
a
10
SELECT * FROM MYSQL.t2;
a
10
DROP DATABASE MYSQL;
#
# MDEV-33103 LOCK TABLE t1 AS t2 -- alias is not case sensitive with lower-case-table-names=0
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
LOCK TABLE t1 AS t2 READ;
SELECT * FROM t1 AS t2;
a
1
UNLOCK TABLES;
LOCK TABLE t1 AS t2 READ;
SELECT * FROM t1 AS T2;
ERROR HY000: Table 'T2' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP TABLE t1;
#
# MDEV-33108 TABLE_STATISTICS and INDEX_STATISTICS are case insensitive with lower-case-table-names=0
#
SET GLOBAL userstat=1;
CREATE TABLE t1 (a INT, KEY(a));
INSERT INTO t1 VALUES (1),(2),(3),(4);
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
CREATE TABLE T1 (a INT, KEY(a));
INSERT INTO T1 VALUES (1),(2),(3),(4);
SELECT * FROM T1 ORDER BY a;
a
1
2
3
4
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS ORDER BY BINARY TABLE_NAME;
TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES ROWS_INSERTED ROWS_UPDATED ROWS_DELETED KEY_READ_HITS KEY_READ_MISSES PAGES_ACCESSED PAGES_READ_FROM_DISK
test T1 4 4 4 4 0 0 0 0 0 0
test t1 4 4 4 4 0 0 0 0 0 0
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS ORDER BY BINARY TABLE_NAME;
TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ QUERIES
test T1 a 4 1
test t1 a 4 1
DROP TABLE t1;
DROP TABLE T1;
SET GLOBAL userstat=DEFAULT;
#
# MDEV-33109 DROP DATABASE MYSQL -- does not drop SP with lower-case-table-names=0
#
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';
db name body
#
# MDEV-33110 HANDLER commands are case insensitive with lower-case-table-names=0
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
HANDLER t1 OPEN;
HANDLER t1 READ FIRST;
a
1
CREATE OR REPLACE TABLE T1 (a INT);
DROP TABLE T1;
HANDLER t1 READ NEXT;
a
2
HANDLER t1 CLOSE;
DROP TABLE t1;
#
# MDEV-33120 System log table names are case insensitive with lower-cast-table-names=0
#
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;
#
# End of 10.5 tests
#
|
