Debian Codegraph @

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

raw
1 create temporary table t1 (i int) engine = innodb;
2 show create table information_schema.innodb_temp_table_info;
3 Table Create Table
4 INNODB_TEMP_TABLE_INFO CREATE TEMPORARY TABLE `INNODB_TEMP_TABLE_INFO` (
5 `TABLE_ID` bigint unsigned NOT NULL DEFAULT '0',
6 `NAME` varchar(64) DEFAULT NULL,
7 `N_COLS` int unsigned NOT NULL DEFAULT '0',
8 `SPACE` int unsigned NOT NULL DEFAULT '0'
9 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
10 select count(*) from information_schema.innodb_temp_table_info;
11 count(*)
12 1
13 insert into t1 values (1), (2), (3), (4);
14 select * from t1;
15 i
16 1
17 2
18 3
19 4
20 select * from t1 where i = 4;
21 i
22 4
23 drop table t1;
24 create temporary table t1 (i int) engine = innodb;
25 select count(*) from information_schema.innodb_temp_table_info;
26 count(*)
27 1
28 insert into t1 values (1), (2), (3), (4);
29 select * from t1;
30 i
31 1
32 2
33 3
34 4
35 select * from t1 where i = 4;
36 i
37 4
38 drop table t1;
39 create temporary table t2 (i int) engine = innodb;
40 insert into t2 values (1), (2), (3), (4);
41 select * from t2;
42 i
43 1
44 2
45 3
46 4
47 select * from t2 where i = 4;
48 i
49 4
50 select count(*) from information_schema.innodb_temp_table_info;
51 count(*)
52 1
53 drop table t2;
54 create temporary table t1 (i int, primary key pk(i)) engine = innodb;
55 create temporary table t2 (i int, primary key pk(i)) engine = innodb;
56 create temporary table t3 (i int, primary key pk(i)) engine = innodb;
57 insert into t1 values (1), (2), (3), (4);
58 insert into t2 values (1), (2), (3), (4);
59 insert into t3 values (1), (2), (3), (4);
60 select * from t1;
61 i
62 1
63 2
64 3
65 4
66 select * from t1 where i = 4;
67 i
68 4
69 select count(*) from information_schema.innodb_temp_table_info;
70 count(*)
71 3
72 drop table t1;
73 select count(*) from information_schema.innodb_temp_table_info;
74 count(*)
75 2
76 drop table t2;
77 select count(*) from information_schema.innodb_temp_table_info;
78 count(*)
79 1
80 drop table t3;
81 create temporary table t1
82 (keyc int, c1 char(100), c2 char(100),
83 primary key(keyc)) engine = innodb;
84 create procedure populate_t1()
85 begin
86 declare i int default 1;
87 while (i <= 200) DO
88 insert into t1 values (i, 'a', 'b');
89 set i = i + 1;
90 end while;
91 end|
92 set autocommit=0;
93 select * from t1;
94 keyc c1 c2
95 call populate_t1();
96 select count(*) from t1;
97 count(*)
98 200
99 select * from t1 limit 10;
100 keyc c1 c2
101 1 a b
102 2 a b
103 3 a b
104 4 a b
105 5 a b
106 6 a b
107 7 a b
108 8 a b
109 9 a b
110 10 a b
111 set autocommit=1;
112 truncate table t1;
113 select * from t1;
114 keyc c1 c2
115 drop table t1;
116 create temporary table t1 (i int) engine = innodb;
117 insert into t1 values (1), (2), (3), (4);
118 select * from t1;
119 i
120 1
121 2
122 3
123 4
124 select * from t1 where i = 4;
125 i
126 4
127 drop table t1;
128 create temporary table t1
129 (keyc int, c1 char(100), c2 char(100),
130 primary key(keyc))
131 engine = innodb;
132 begin;
133 select * from t1;
134 keyc c1 c2
135 call populate_t1();
136 select count(*) from t1;
137 count(*)
138 200
139 rollback;
140 select * from t1;
141 keyc c1 c2
142 begin;
143 call populate_t1();
144 commit;
145 select count(*) from t1;
146 count(*)
147 200
148 truncate table t1;
149 select * from t1;
150 keyc c1 c2
151 drop table t1;
152 drop procedure populate_t1;
153 create temporary table t1 (t1_i int, t1_f float) engine = innodb;
154 insert into t1 values (1, 1.1), (2, 2.2), (3, 2.2), (4, 4.4);
155 explain select * from t1 where t1_i = 1;
156 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
157 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
158 Warnings:
159 Note 1003 /* select#1 */ select `test`.`t1`.`t1_i` AS `t1_i`,`test`.`t1`.`t1_f` AS `t1_f` from `test`.`t1` where (`test`.`t1`.`t1_i` = 1)
160 alter table t1 add unique index pri_index(t1_i);
161 explain select * from t1 where t1_i = 1;
162 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
163 1 SIMPLE t1 NULL const pri_index pri_index 5 const 1 100.00 NULL
164 Warnings:
165 Note 1003 /* select#1 */ select '1' AS `t1_i`,'1.1' AS `t1_f` from `test`.`t1` where true
166 select * from t1 where t1_i = 1;
167 t1_i t1_f
168 1 1.1
169 alter table t1 add unique index sec_index(t1_f);
170 ERROR 23000: Duplicate entry '2.2' for key 't1.sec_index'
171 alter table t1 add index sec_index(t1_f);
172 explain select * from t1 where t1_f > 2.2;
173 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
174 1 SIMPLE t1 NULL range sec_index sec_index 5 NULL 3 100.00 Using index condition
175 Warnings:
176 Note 1003 /* select#1 */ select `test`.`t1`.`t1_i` AS `t1_i`,`test`.`t1`.`t1_f` AS `t1_f` from `test`.`t1` where (`test`.`t1`.`t1_f` > 2.2)
177 select * from t1 where t1_f > 2.2;
178 t1_i t1_f
179 2 2.2
180 3 2.2
181 4 4.4
182 alter table t1 add column (t1_c char(10));
183 select * from t1;
184 t1_i t1_f t1_c
185 1 1.1 NULL
186 2 2.2 NULL
187 3 2.2 NULL
188 4 4.4 NULL
189 insert into t1 values (5, 5.5, 'krunal');
190 alter table t1 drop column t1_f;
191 show create table t1;
192 Table Create Table
193 t1 CREATE TEMPORARY TABLE `t1` (
194 `t1_i` int DEFAULT NULL,
195 `t1_c` char(10) DEFAULT NULL,
196 UNIQUE KEY `pri_index` (`t1_i`)
197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
198 select * from t1 where t1_f > 2.2;
199 ERROR 42S22: Unknown column 't1_f' in 'where clause'
200 alter table t1 add index sec_index2(t1_c), algorithm=inplace;
201 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
202 select count(*) from information_schema.innodb_temp_table_info;
203 count(*)
204 1
205 drop table t1;
206 create temporary table t1 (i int, f float) engine = innodb;
207 insert into t1 values (10, 1.1), (20, 2.2);
208 select * from t1;
209 i f
210 10 1.1
211 20 2.2
212 alter table t1 discard tablespace;
213 ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
214 alter table t1 import tablespace;
215 ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
216 drop table t1;
217 create temporary table t1 (i int) engine=innodb;
218 insert into t1 values (1), (2), (3);
219 select * from t1;
220 i
221 1
222 2
223 3
224 alter table t1 rename t2;
225 select * from t1;
226 ERROR 42S02: Table 'test.t1' doesn't exist
227 select * from t2;
228 i
229 1
230 2
231 3
232 insert into t2 values (1), (2), (6), (7);
233 select * from t2;
234 i
235 1
236 2
237 3
238 1
239 2
240 6
241 7
242 drop table t2;
243 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
244 create temporary table t (
245 a int not null,
246 b blob not null,
247 index sk (b(3021))
248 ) row_format = dynamic engine=innodb;
249 drop table t;
250 create temporary table t (
251 a int not null,
252 b blob not null,
253 index sk (b(3021))
254 ) row_format = dynamic engine=innodb;
255 drop table t;
256 create temporary table t (
257 a int not null,
258 b blob not null,
259 index sk (b(3021))
260 ) row_format = dynamic engine=innodb;
261 drop table t;
262 SET sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
263 SET innodb_strict_mode=OFF;
264 create temporary table t (
265 a int not null,
266 b blob not null,
267 index sk (b(3021))
268 ) row_format = compact engine=innodb;
269 ERROR 42000: Specified key was too long; max key length is 767 bytes
270 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
271 create temporary table t (
272 a int not null,
273 b blob not null,
274 index sk (b(3021))
275 ) row_format = dynamic engine=innodb;
276 drop table t;
277 create temporary table t (
278 a int not null,
279 b blob not null,
280 index sk (b(3021))
281 ) row_format = compressed engine=innodb;
282 drop table t;
283 SET sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
284 create temporary table t (
285 a int not null,
286 b blob not null,
287 index sk (b(3021))
288 ) row_format = compact engine=innodb;
289 ERROR 42000: Specified key was too long; max key length is 767 bytes
290 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
291 create temporary table t (
292 a int not null,
293 b blob not null,
294 index sk (b(3021))
295 ) row_format = dynamic engine=innodb;
296 drop table t;
297 DROP TABLE IF EXISTS t1,t2,t3;
298 CREATE TABLE t6469_1 ( i INT ) ENGINE = Innodb;
299 CREATE TEMPORARY TABLE t6469_2 ( i INT ) ENGINE = Innodb;
300 CREATE TEMPORARY table t6469_3 ( i INT ) ENGINE = Innodb ROW_FORMAT=compressed;
301 Warnings:
302 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
303 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
304 SELECT count(*) FROM information_schema.files WHERE file_name LIKE '%t6469%';
305 count(*)
306 1
307 SELECT count(*) FROM information_schema.INNODB_DATAFILES WHERE PATH LIKE '%t6469%';
308 count(*)
309 1
310 SELECT count(*) FROM information_schema.INNODB_TABLES WHERE NAME LIKE '%t6469%';
311 count(*)
312 1
313 DROP TABLE t6469_1,t6469_2,t6469_3;
314 SELECT * FROM information_schema.files WHERE file_name LIKE '%t6469%';
315 FILE_ID FILE_NAME FILE_TYPE TABLESPACE_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME LOGFILE_GROUP_NAME LOGFILE_GROUP_NUMBER ENGINE FULLTEXT_KEYS DELETED_ROWS UPDATE_COUNT FREE_EXTENTS TOTAL_EXTENTS EXTENT_SIZE INITIAL_SIZE MAXIMUM_SIZE AUTOEXTEND_SIZE CREATION_TIME LAST_UPDATE_TIME LAST_ACCESS_TIME RECOVER_TIME TRANSACTION_COUNTER VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM STATUS EXTRA
316 SELECT * FROM information_schema.INNODB_DATAFILES WHERE PATH LIKE '%t6469%';
317 SPACE PATH
318 SELECT TABLE_ID, NAME, FLAG, N_COLS, SPACE, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE,
319 INSTANT_COLS, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES WHERE NAME LIKE '%t6469%';
320 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE INSTANT_COLS TOTAL_ROW_VERSIONS
321 SET sql_mode=default;