Debian Codegraph @

mysql-test/suite/innodb/r/temporary_table_optimization.result

raw
1 create temporary table t1 (i int) engine = innodb;
2 insert into t1 values (1), (2), (3), (4);
3 select * from t1;
4 i
5 1
6 2
7 3
8 4
9 select * from t1 where i = 4;
10 i
11 4
12 drop table t1;
13 create temporary table t1 (i int) engine = innodb;
14 insert into t1 values (1), (2), (3), (4);
15 select * from t1;
16 i
17 1
18 2
19 3
20 4
21 select * from t1 where i = 4;
22 i
23 4
24 drop table t1;
25 create temporary table t2 (i int) engine = innodb;
26 insert into t2 values (1), (2), (3), (4);
27 select * from t2;
28 i
29 1
30 2
31 3
32 4
33 select * from t2 where i = 4;
34 i
35 4
36 drop table t2;
37 create temporary table t1
38 (keyc int, c1 char(100), c2 char(100),
39 primary key(keyc)) engine = innodb;
40 create procedure populate_t1()
41 begin
42 declare i int default 1;
43 while (i <= 200) DO
44 insert into t1 values (i, 'a', 'b');
45 set i = i + 1;
46 end while;
47 end|
48 set autocommit=0;
49 select count(*) from t1;
50 count(*)
51 0
52 call populate_t1();
53 select count(*) from t1;
54 count(*)
55 200
56 select * from t1 limit 10;
57 keyc c1 c2
58 1 a b
59 2 a b
60 3 a b
61 4 a b
62 5 a b
63 6 a b
64 7 a b
65 8 a b
66 9 a b
67 10 a b
68 set autocommit=1;
69 truncate table t1;
70 select count(*) from t1;
71 count(*)
72 0
73 drop table t1;
74 create temporary table t1 (i int) engine = innodb;
75 insert into t1 values (1), (2), (3), (4);
76 select * from t1;
77 i
78 1
79 2
80 3
81 4
82 select * from t1 where i = 4;
83 i
84 4
85 drop table t1;
86 create temporary table t1
87 (keyc int, c1 char(100), c2 char(100),
88 primary key(keyc))
89 engine = innodb;
90 begin;
91 select count(*) from t1;
92 count(*)
93 0
94 call populate_t1();
95 select count(*) from t1;
96 count(*)
97 200
98 rollback;
99 select count(*) from t1;
100 count(*)
101 0
102 begin;
103 call populate_t1();
104 commit;
105 select count(*) from t1;
106 count(*)
107 200
108 truncate table t1;
109 select count(*) from t1;
110 count(*)
111 0
112 drop table t1;
113 drop procedure populate_t1;
114 create temporary table t1 (t1_i int, t1_f float) engine = innodb;
115 insert into t1 values (1, 1.1), (2, 2.5), (3, 2.5), (4, 4.4);
116 explain select * from t1 where t1_i = 1;
117 id select_type table type possible_keys key key_len ref rows Extra
118 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
119 alter table t1 add unique index pri_index(t1_i);
120 explain select * from t1 where t1_i = 1;
121 id select_type table type possible_keys key key_len ref rows Extra
122 1 SIMPLE t1 const pri_index pri_index 5 const 1
123 select * from t1 where t1_i = 1;
124 t1_i t1_f
125 1 1.1
126 alter table t1 add unique index sec_index(t1_f);
127 ERROR 23000: Duplicate entry '2.5' for key 'sec_index'
128 alter table t1 add index sec_index(t1_f);
129 explain select * from t1 where t1_f >= 2.5;
130 id select_type table type possible_keys key key_len ref rows Extra
131 1 SIMPLE t1 range sec_index sec_index 5 NULL 3 Using index condition
132 select * from t1 where t1_f >= 2.5;
133 t1_i t1_f
134 2 2.5
135 3 2.5
136 4 4.4
137 alter table t1 add column (t1_c char(10));
138 select * from t1;
139 t1_i t1_f t1_c
140 1 1.1 NULL
141 2 2.5 NULL
142 3 2.5 NULL
143 4 4.4 NULL
144 insert into t1 values (5, 5.5, 'krunal');
145 alter table t1 drop column t1_f;
146 show create table t1;
147 Table Create Table
148 t1 CREATE TEMPORARY TABLE `t1` (
149 `t1_i` int(11) DEFAULT NULL,
150 `t1_c` char(10) DEFAULT NULL,
151 UNIQUE KEY `pri_index` (`t1_i`)
152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
153 select * from t1 where t1_f >= 2.5;
154 ERROR 42S22: Unknown column 't1_f' in 'WHERE'
155 alter table t1 add index sec_index2(t1_c), algorithm=inplace;
156 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
157 drop table t1;
158 create temporary table t1 (i int, f float) engine = innodb;
159 insert into t1 values (10, 1.1), (20, 2.2);
160 select * from t1;
161 i f
162 10 1.1
163 20 2.2
164 alter table t1 discard tablespace;
165 ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
166 alter table t1 import tablespace;
167 ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
168 drop table t1;
169 create temporary table t1 (i int) engine=innodb;
170 insert into t1 values (1), (2), (3);
171 select * from t1;
172 i
173 1
174 2
175 3
176 alter table t1 rename t2;
177 select * from t1;
178 ERROR 42S02: Table 'test.t1' doesn't exist
179 select * from t2;
180 i
181 1
182 2
183 3
184 insert into t2 values (1), (2), (6), (7);
185 select * from t2;
186 i
187 1
188 2
189 3
190 1
191 2
192 6
193 7
194 drop table t2;
195 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
196 create temporary table t (
197 a int not null,
198 b blob not null,
199 index sk (b(3021))
200 ) row_format = dynamic engine=innodb;
201 drop table t;
202 create temporary table t (
203 a int not null,
204 b blob not null,
205 index sk (b(3021))
206 ) row_format = dynamic engine=innodb;
207 drop table t;
208 create temporary table t (
209 a int not null,
210 b blob not null,
211 index sk (b(3021))
212 ) row_format = dynamic engine=innodb;
213 drop table t;
214 SET innodb_strict_mode=OFF;
215 create temporary table t (
216 a int not null,
217 b blob not null,
218 index sk (b(3021))
219 ) row_format = compact engine=innodb;
220 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
221 create temporary table t (
222 a int not null,
223 b blob not null,
224 index sk (b(3021))
225 ) row_format = dynamic engine=innodb;
226 drop table t;
227 create temporary table t (
228 a int not null,
229 b blob not null,
230 index sk (b(3021))
231 ) row_format = compressed engine=innodb;
232 drop table t;
233 create temporary table t (
234 a int not null,
235 b blob not null,
236 index sk (b(3021))
237 ) row_format = compact engine=innodb;
238 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
239 create temporary table t (
240 a int not null,
241 b blob not null,
242 index sk (b(3021))
243 ) row_format = dynamic engine=innodb;
244 drop table t;
245 CREATE TABLE t1 ( i INT ) ENGINE = Innodb;
246 CREATE TEMPORARY TABLE t2 ( i INT ) ENGINE = Innodb;
247 SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
248 COUNT(*)
249 1
250 CREATE TEMPORARY table t3 ( i INT ) ENGINE = Innodb;
251 SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
252 COUNT(*)
253 1
254 DROP TABLE t1,t2,t3;
255 SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
256 COUNT(*)
257 0