Debian Codegraph @

mysql-test/suite/heap/heap_btree.test

raw
1 --source include/have_sequence.inc
2
3 #
4 # Test of heap tables.
5 #
6
7 --disable_warnings
8 drop table if exists t1;
9 --enable_warnings
10
11 create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
12 insert into t1 values(1,1),(2,2),(3,3),(4,4);
13 delete from t1 where a=1 or a=0;
14 #show table status like "t1";
15 show keys from t1;
16 select * from t1;
17 select * from t1 where a=4;
18 update t1 set b=5 where a=4;
19 update t1 set b=b+1 where a>=3;
20 replace t1 values (3,3);
21 select * from t1;
22 alter table t1 add c int not null, add key using BTREE (c,a);
23 drop table t1;
24
25 create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps";
26 insert into t1 values(-2,-2),(-1,-1),(0,0),(1,1),(2,2),(3,3),(4,4);
27 delete from t1 where a > -3;
28 select * from t1;
29 drop table t1;
30
31 create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps";
32 insert into t1 values(1,1),(2,2),(3,3),(4,4);
33 alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
34 #show table status like "t1";
35 select * from t1;
36 drop table t1;
37
38 create table t1 (a int not null) engine=heap;
39 insert into t1 values (869751),(736494),(226312),(802616),(728912);
40 select * from t1 where a > 736494;
41 alter table t1 add unique uniq_id using BTREE (a);
42 --sorted_result
43 select * from t1 where a > 736494;
44 select * from t1 where a = 736494;
45 select * from t1 where a=869751 or a=736494;
46 --sorted_result
47 select * from t1 where a in (869751,736494,226312,802616);
48 explain select * from t1 where a in (869751,736494,226312,802616);
49 alter table t1 engine=myisam;
50 explain select * from t1 where a in (869751,736494,226312,802616);
51 insert into t1 values (1),(2),(3),(4),(5),(6);
52 explain select * from t1 where a in (869751,736494,226312,802616);
53 drop table t1;
54
55 create table t1 (x int not null, y int not null, key x using BTREE (x,y), unique y using BTREE (y))
56 engine=heap;
57 insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
58 explain select * from t1 where x=1;
59 select * from t1 where x=1;
60 select * from t1,t1 as t2 where t1.x=t2.y;
61 explain select * from t1,t1 as t2 where t1.x=t2.y;
62 drop table t1;
63
64 create table t1 (a int) engine=heap;
65 insert into t1 values(1);
66 select max(a) from t1;
67 drop table t1;
68
69 CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using BTREE (a,b), key using BTREE (b) ) ENGINE=HEAP;
70 insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
71 select * from t1 where a=1;
72 insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
73 select * from t1 where a=1;
74 --replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x
75 explain select * from t1 where a=1 order by a,b;
76 --replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x
77 explain select * from t1 where a=1 order by b;
78 select * from t1 where b=1;
79 --replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x
80 explain select * from t1 where b=1;
81 drop table t1;
82
83 create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP;
84 insert into t1 values(1);
85 select max(id) from t1;
86 insert into t1 values(2);
87 select max(id) from t1;
88 replace into t1 values(1);
89 drop table t1;
90
91 create table t1 (n int) engine=heap;
92 drop table t1;
93
94 create table t1 (n int) engine=heap;
95 drop table if exists t1;
96
97 # Test of non unique index
98
99 CREATE table t1(f1 int not null,f2 char(20) not
100 null,index(f2)) engine=heap;
101 INSERT into t1 set f1=12,f2="bill";
102 INSERT into t1 set f1=13,f2="bill";
103 INSERT into t1 set f1=14,f2="bill";
104 INSERT into t1 set f1=15,f2="bill";
105 INSERT into t1 set f1=16,f2="ted";
106 INSERT into t1 set f1=12,f2="ted";
107 INSERT into t1 set f1=12,f2="ted";
108 INSERT into t1 set f1=12,f2="ted";
109 INSERT into t1 set f1=12,f2="ted";
110 delete from t1 where f2="bill";
111 select * from t1;
112 drop table t1;
113
114 #
115 # Test when using part key searches
116 #
117
118 create table t1 (btn char(10) not null, key using BTREE (btn)) engine=heap;
119 insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("hello"),("hello"), ("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
120 explain select * from t1 where btn like "i%";
121 --replace_column 9 #
122 explain select * from t1 where btn like "h%";
123 explain select * from t1 where btn like "a%";
124 explain select * from t1 where btn like "b%";
125 # For the following the BTREE MAY notice that there is no possible matches
126 select * from t1 where btn like "ff%";
127 select * from t1 where btn like " %";
128 select * from t1 where btn like "q%";
129 alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
130 update t1 set new_col=left(btn,1);
131 explain select * from t1 where btn="a";
132 explain select * from t1 where btn="a" and new_col="a";
133 drop table t1;
134
135 #
136 # Test of NULL keys
137 #
138
139 CREATE TABLE t1 (
140 a int default NULL,
141 b int default NULL,
142 KEY a using BTREE (a),
143 UNIQUE b using BTREE (b)
144 ) engine=heap;
145 INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
146 SELECT * FROM t1 WHERE a=NULL;
147 explain SELECT * FROM t1 WHERE a IS NULL;
148 SELECT * FROM t1 WHERE a<=>NULL;
149 SELECT * FROM t1 WHERE b=NULL;
150 explain SELECT * FROM t1 WHERE b IS NULL;
151 SELECT * FROM t1 WHERE b<=>NULL;
152
153 --error ER_DUP_ENTRY
154 INSERT INTO t1 VALUES (1,3);
155 DROP TABLE t1;
156
157 CREATE TABLE t1 (a int, b int, c int, key using BTREE (a, b, c)) engine=heap;
158 INSERT INTO t1 VALUES (1, NULL, NULL), (1, 1, NULL), (1, NULL, 1);
159 SELECT * FROM t1 WHERE a=1 and b IS NULL;
160 SELECT * FROM t1 WHERE a=1 and c IS NULL;
161 SELECT * FROM t1 WHERE a=1 and b IS NULL and c IS NULL;
162 DROP TABLE t1;
163
164 #
165 # Test when deleting all rows
166 #
167
168 CREATE TABLE t1 (a int not null, primary key using BTREE (a)) engine=heap;
169 INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
170 DELETE from t1 where a < 100;
171 SELECT * from t1;
172 DROP TABLE t1;
173
174 #
175 # Bug #9719: problem with delete
176 #
177
178 create table t1(a int not null, key using btree(a)) engine=heap;
179 insert into t1 values (2), (2), (2), (1), (1), (3), (3), (3), (3);
180 select a from t1 where a > 2 order by a;
181 delete from t1 where a < 4;
182 select a from t1 order by a;
183 insert into t1 values (2), (2), (2), (1), (1), (3), (3), (3), (3);
184 select a from t1 where a > 4 order by a;
185 delete from t1 where a > 4;
186 select a from t1 order by a;
187 select a from t1 where a > 3 order by a;
188 delete from t1 where a >= 2;
189 select a from t1 order by a;
190 drop table t1;
191
192 #
193 # Bug#26996 - Update of a Field in a Memory Table ends with wrong result
194 #
195 CREATE TABLE t1 (
196 c1 CHAR(3),
197 c2 INTEGER,
198 KEY USING BTREE(c1),
199 KEY USING BTREE(c2)
200 ) ENGINE= MEMORY;
201 INSERT INTO t1 VALUES ('ABC',0), ('A',0), ('B',0), ('C',0);
202 UPDATE t1 SET c2= c2 + 1 WHERE c1 = 'A';
203 SELECT * FROM t1;
204 DROP TABLE t1;
205
206 #
207 # Bug#24985 - UTF8 ENUM primary key on MEMORY using BTREE
208 # causes incorrect duplicate entries
209 #
210 CREATE TABLE t1 (
211 c1 ENUM('1', '2'),
212 UNIQUE USING BTREE(c1)
213 ) ENGINE= MEMORY DEFAULT CHARSET= utf8;
214 INSERT INTO t1 VALUES('1'), ('2');
215 DROP TABLE t1;
216 CREATE TABLE t1 (
217 c1 SET('1', '2'),
218 UNIQUE USING BTREE(c1)
219 ) ENGINE= MEMORY DEFAULT CHARSET= utf8;
220 INSERT INTO t1 VALUES('1'), ('2');
221 DROP TABLE t1;
222
223 #
224 # BUG#30590 - delete from memory table with composite btree primary key
225 #
226 CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY;
227 INSERT INTO t1 VALUES(1),(2),(2);
228 DELETE FROM t1 WHERE a=2;
229 SELECT * FROM t1;
230 DROP TABLE t1;
231
232 --echo End of 4.1 tests
233
234 #
235 # BUG#18160 - Memory-/HEAP Table endless growing indexes
236 #
237 CREATE TABLE t1(val INT, KEY USING BTREE(val)) ENGINE=memory;
238 INSERT INTO t1 VALUES(0);
239 --replace_result 37 21
240 SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1';
241 UPDATE t1 SET val=1;
242 --replace_result 37 21
243 SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1';
244 DROP TABLE t1;
245
246 #
247 # BUG#12873 - BTREE index on MEMORY table with multiple NULL values doesn't
248 # work properly
249 #
250 CREATE TABLE t1 (a INT, UNIQUE USING BTREE(a)) ENGINE=MEMORY;
251 INSERT INTO t1 VALUES(NULL),(NULL);
252 DROP TABLE t1;
253
254 #
255 # Bug #30885: MEMORY returns incorrect data if BTREE index is used for NULL lookup
256 #
257 create table t1(a varchar(255), b varchar(255),
258 key using btree (a,b)) engine=memory;
259 insert into t1 values (1, 1), (3, 3), (2, 2), (NULL, 1), (NULL, NULL), (0, 0);
260 select * from t1 where a is null;
261 drop table t1;
262
263 -- echo #
264 -- echo # bug#39918 - memory (heap) engine crashing while executing self join with delete
265 -- echo #
266
267 CREATE TABLE t1(a INT, KEY USING BTREE (a)) ENGINE=MEMORY;
268 INSERT INTO t1 VALUES(1),(1);
269 DELETE a1 FROM t1 AS a1, t1 AS a2 WHERE a1.a=a2.a;
270 DROP TABLE t1;
271 --echo End of 5.0 tests
272
273 -- echo # bit index in heap tables
274
275 create table t1 (a bit(63) not null) engine=heap;
276 insert into t1 values (869751),(736494),(226312),(802616),(728912);
277 alter table t1 add unique uniq_id using BTREE (a);
278 --sorted_result
279 select 0+a from t1 where a > 736494;
280 explain select 0+a from t1 where a > 736494;
281 select 0+a from t1 where a = 736494;
282 explain select 0+a from t1 where a = 736494;
283 select 0+a from t1 where a=869751 or a=736494;
284 explain select 0+a from t1 where a=869751 or a=736494;
285 insert into t1 (a) select * from seq_1_to_100;
286 select 0+a from t1 where a in (869751,736494,226312,802616);
287 explain select 0+a from t1 where a in (869751,736494,226312,802616);
288 drop table t1;
289
290 --echo End of 5.3 tests
291
292 #
293 # Bug#27799513: POTENTIAL DOUBLE FREE OR CORRUPTION OF HEAP INFO (HP_INFO)
294 #
295 create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap;
296 insert t1 values (1, repeat('a', 300));
297 drop table t1;
298
299 --echo End of 5.5 tests