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
|
#
# MDEV-12459: The information_schema tables for getting temporary tables
# info is missing, at least for innodb, there is no
# INNODB_TEMP_TABLE_INFO
#
# -------------------------------
# Test shadowing of a base table
# -------------------------------
connect con1,localhost,root,,test,,;
# Test the warning is raised
create table t(t int);
create temporary table t(t int);
Warnings:
Note 1050 Table 't' already exists
create user foo@localhost identified by 'bar';
# Test shadowing of the base table for user with no privileges - no warning
connect con2,localhost,foo,bar,test,,;
show grants for current_user();
Grants for foo@localhost
GRANT USAGE ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'
show tables;
Tables_in_test
t
create temporary table t(t int);
Warnings:
Note 1050 Table 't' already exists
show warnings;
Level Code Message
Note 1050 Table 't' already exists
show create table t;
Table Create Table
t CREATE TEMPORARY TABLE `t` (
`t` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
disconnect con2;
# Give the privileges to the user
connection con1;
grant all privileges on *.* to foo@localhost with grant option;
# Test shadowing of the base table for user that has valid privileges - warning
connect con2,localhost,foo,bar,test,,;
show grants for current_user();
Grants for foo@localhost
GRANT ALL PRIVILEGES ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' WITH GRANT OPTION
show tables;
Tables_in_test
t
create temporary table t(t int);
Warnings:
Note 1050 Table 't' already exists
show create table t;
Table Create Table
t CREATE TEMPORARY TABLE `t` (
`t` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show warnings;
Level Code Message
disconnect con2;
# Clean the user
connection con1;
drop table t;
drop table t;
drop user foo@localhost;
# ------------------------
# IS.tables tests
# ------------------------
connection default;
# Create first temporary table
create temporary table test.t_temp(t int);
insert into t_temp values (1),(2), (3);
# Show results
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
table_schema test
table_name t_temp
temporary Y
# Create the base table with the same name (both should be visible)
create table test.t_temp(t int);
insert into t_temp values (-1),(-2);
# Show results
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
table_schema test
table_name t_temp
temporary Y
create database my_db;
# Create the temporary table with the same name in new DB
create temporary table my_db.t_temp (t int);
insert into my_db.t_temp values (-2),(-1);
# Show results
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
table_schema my_db
table_name t_temp
temporary Y
table_schema test
table_name t_temp
temporary Y
connection con1;
# Create the temporary table with the same name in new connection
create temporary table test.t_temp(t int);
Warnings:
Level Note
Code 1050
Message Table 't_temp' already exists
insert into t_temp values (4),(5),(6), (7);
# Show results
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
table_schema test
table_name t_temp
temporary Y
connection default;
# Show results in default connection
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
table_schema my_db
table_name t_temp
temporary Y
table_schema test
table_name t_temp
temporary Y
# Create a warning on `Create` statement if the new temporary table
# shadows base table - note there is no warning vice versa.
create table test.my_t (t int);
create temporary table test.my_t (t int);
Warnings:
Level Note
Code 1050
Message Table 'my_t' already exists
show warnings;
Level Note
Code 1050
Message Table 'my_t' already exists
# Show results
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
table_schema test
table_name my_t
temporary Y
table_schema my_db
table_name t_temp
temporary Y
table_schema test
table_name t_temp
temporary Y
drop table test.my_t;
drop table test.my_t;
disconnect con1;
drop table test.t_temp;
drop table test.t_temp;
drop database my_db;
|
