~linuxjedi/drizzle/trunk-bug-667053

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;

insert into t1 values(1,1),(2,2),(3,3),(4,4);

delete from t1 where a=1 or a=0;

show keys from t1;

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

t1 0 PRIMARY 1 a A NULL NULL NULL BTREE

select * from t1;

a b

2 2

3 3

4 4

select * from t1 where a=4;

a b

4 4

update t1 set b=5 where a=4;

update t1 set b=b+1 where a>=3;

replace t1 values (3,3);

select * from t1;

a b

2 2

3 3

4 6

alter table t1 add c int not null, add key using BTREE (c,a);

drop table t1;

create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps";

insert into t1 values(-2,-2),(-1,-1),(0,0),(1,1),(2,2),(3,3),(4,4);

delete from t1 where a > -3;

select * from t1;

a b

drop table t1;

create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps";

insert into t1 values(1,1),(2,2),(3,3),(4,4);

alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";

select * from t1;

a b

1 1

2 2

3 3

4 4

drop table t1;

create table t1 (a int not null) engine=heap;

insert into t1 values (869751),(736494),(226312),(802616),(728912);

select * from t1 where a > 736494;

869751

802616

alter table t1 add unique uniq_id using BTREE (a);

select * from t1 where a > 736494;

802616

869751

select * from t1 where a = 736494;

736494

select * from t1 where a=869751 or a=736494;

736494

869751

select * from t1 where a in (869751,736494,226312,802616);

226312

736494

802616

869751

alter table t1 engine=myisam;

explain select * from t1 where a in (869751,736494,226312,802616);

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index

drop table t1;

create table t1 (x int not null, y int not null, key x using BTREE (x,y), unique y using BTREE (y))

engine=heap;

insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);

explain select * from t1 where x=1;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 ref x x 4 const 1

select * from t1 where x=1;

x y

1 1

1 3

select * from t1,t1 as t2 where t1.x=t2.y;

x y x y

1 1 1 1

2 2 2 2

1 3 1 1

2 4 2 2

2 5 2 2

2 6 2 2

explain select * from t1,t1 as t2 where t1.x=t2.y;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 ALL x NULL NULL NULL 6

1 SIMPLE t2 eq_ref y y 4 test.t1.x 1

drop table t1;

create table t1 (a int) engine=heap;

insert into t1 values(1);

select max(a) from t1;

max(a)

drop table t1;

100

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;

101

insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);

102

select * from t1 where a=1;

103

a b

104

1 1

105

1 2

106

1 3

107

1 4

108

1 5

109

1 6

110

insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);

111

select * from t1 where a=1;

112

a b

113

1 1

114

1 1

115

1 2

116

1 2

117

1 3

118

1 3

119

1 4

120

1 4

121

1 5

122

1 5

123

1 6

124

1 6

125

explain select * from tx where a=x order by a,b;

126

id select_type table type possible_keys key key_len ref rows Extra

127

x SIMPLE tx ref a a x const x Using where

128

explain select * from tx where a=x order by b;

129

id select_type table type possible_keys key key_len ref rows Extra

130

x SIMPLE tx ref a a x const x Using where

131

select * from t1 where b=1;

132

a b

133

1 1

134

1 1

135

explain select * from tx where b=x;

136

id select_type table type possible_keys key key_len ref rows Extra

137

x SIMPLE tx ref b b x const x

138

drop table t1;

139

create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP;

140

insert into t1 values(1);

141

select max(id) from t1;

142

max(id)

143

144

insert into t1 values(2);

145

select max(id) from t1;

146

max(id)

147

148

replace into t1 values(1);

149

drop table t1;

150

create table t1 (n int) engine=heap;

151

drop table t1;

152

create table t1 (n int) engine=heap;

153

drop table if exists t1;

154

CREATE table t1(f1 int not null,f2 char(20) not

155

null,index(f2)) engine=heap;

156

INSERT into t1 set f1=12,f2="bill";

157

INSERT into t1 set f1=13,f2="bill";

158

INSERT into t1 set f1=14,f2="bill";

159

INSERT into t1 set f1=15,f2="bill";

160

INSERT into t1 set f1=16,f2="ted";

161

INSERT into t1 set f1=12,f2="ted";

162

INSERT into t1 set f1=12,f2="ted";

163

INSERT into t1 set f1=12,f2="ted";

164

INSERT into t1 set f1=12,f2="ted";

165

delete from t1 where f2="bill";

166

select * from t1;

167

f1 f2

168

16 ted

169

12 ted

170

12 ted

171

12 ted

172

12 ted

173

drop table t1;

174

create table t1 (btn char(10) not null, key using BTREE (btn)) engine=heap;

175

insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");

176

explain select * from t1 where btn like "i%";

177

id select_type table type possible_keys key key_len ref rows Extra

178

1 SIMPLE t1 range btn btn 10 NULL 1 Using where

179

explain select * from t1 where btn like "h%";

180

id select_type table type possible_keys key key_len ref rows Extra

181

1 SIMPLE t1 range btn btn 10 NULL # Using where

182

explain select * from t1 where btn like "a%";

183

id select_type table type possible_keys key key_len ref rows Extra

184

1 SIMPLE t1 range btn btn 10 NULL 1 Using where

185

explain select * from t1 where btn like "b%";

186

id select_type table type possible_keys key key_len ref rows Extra

187

1 SIMPLE t1 range btn btn 10 NULL 1 Using where

188

select * from t1 where btn like "ff%";

189

btn

190

select * from t1 where btn like " %";

191

btn

192

select * from t1 where btn like "q%";

193

btn

194

alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;

195

update t1 set new_col=left(btn,1);

196

explain select * from t1 where btn="a";

197

id select_type table type possible_keys key key_len ref rows Extra

198

1 SIMPLE t1 ref btn btn 10 const 1 Using where

199

explain select * from t1 where btn="a" and new_col="a";

200

id select_type table type possible_keys key key_len ref rows Extra

201

1 SIMPLE t1 ref btn btn 11 const,const 1 Using where

202

drop table t1;

203

CREATE TABLE t1 (

204

a int default NULL,

205

b int default NULL,

206

KEY a using BTREE (a),

207

UNIQUE b using BTREE (b)

208

) engine=heap;

209

INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);

210

SELECT * FROM t1 WHERE a=NULL;

211

a b

212

explain SELECT * FROM t1 WHERE a IS NULL;

213

id select_type table type possible_keys key key_len ref rows Extra

214

1 SIMPLE t1 ref a a 5 const 1 Using where

215

SELECT * FROM t1 WHERE a<=>NULL;

216

a b

217

NULL 99

218

SELECT * FROM t1 WHERE b=NULL;

219

a b

220

explain SELECT * FROM t1 WHERE b IS NULL;

221

id select_type table type possible_keys key key_len ref rows Extra

222

1 SIMPLE t1 ref b b 5 const 1 Using where

223

SELECT * FROM t1 WHERE b<=>NULL;

224

a b

225

99 NULL

226

INSERT INTO t1 VALUES (1,3);

227

ERROR 23000: Duplicate entry '3' for key 'b'

228

DROP TABLE t1;

229

CREATE TABLE t1 (a int, b int, c int, key using BTREE (a, b, c)) engine=heap;

230

INSERT INTO t1 VALUES (1, NULL, NULL), (1, 1, NULL), (1, NULL, 1);

231

SELECT * FROM t1 WHERE a=1 and b IS NULL;

232

a b c

233

1 NULL NULL

234

1 NULL 1

235

SELECT * FROM t1 WHERE a=1 and c IS NULL;

236

a b c

237

1 NULL NULL

238

1 1 NULL

239

SELECT * FROM t1 WHERE a=1 and b IS NULL and c IS NULL;

240

a b c

241

1 NULL NULL

242

DROP TABLE t1;

243

CREATE TABLE t1 (a int not null, primary key using BTREE (a)) engine=heap;

244

INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

245

DELETE from t1 where a < 100;

246

SELECT * from t1;

247

248

DROP TABLE t1;

249

create table t1(a int not null, key using btree(a)) engine=heap;

250

insert into t1 values (2), (2), (2), (1), (1), (3), (3), (3), (3);

251

select a from t1 where a > 2 order by a;

252

253

254

255

256

257

delete from t1 where a < 4;

258

select a from t1 order by a;

259

260

insert into t1 values (2), (2), (2), (1), (1), (3), (3), (3), (3);

261

select a from t1 where a > 4 order by a;

262

263

delete from t1 where a > 4;

264

select a from t1 order by a;

265

266

267

268

269

270

271

272

273

274

275

select a from t1 where a > 3 order by a;

276

277

delete from t1 where a >= 2;

278

select a from t1 order by a;

279

280

281

282

drop table t1;

283

CREATE TABLE t1 (

284

c1 CHAR(3),

285

c2 INTEGER,

286

KEY USING BTREE(c1),

287

KEY USING BTREE(c2)

288

) ENGINE= MEMORY;

289

INSERT INTO t1 VALUES ('ABC',0), ('A',0), ('B',0), ('C',0);

290

UPDATE t1 SET c2= c2 + 1 WHERE c1 = 'A';

291

SELECT * FROM t1;

292

c1 c2

293

ABC 0

294

A 1

295

B 0

296

C 0

297

DROP TABLE t1;

298

CREATE TABLE t1 (

299

c1 ENUM('1', '2'),

300

UNIQUE USING BTREE(c1)

301

) ENGINE= MEMORY DEFAULT CHARSET= utf8;

302

INSERT INTO t1 VALUES('1'), ('2');

303

DROP TABLE t1;

304

CREATE TABLE t1 (

305

c1 SET('1', '2'),

306

UNIQUE USING BTREE(c1)

307

) ENGINE= MEMORY DEFAULT CHARSET= utf8;

308

INSERT INTO t1 VALUES('1'), ('2');

309

DROP TABLE t1;

310

CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY;

311

INSERT INTO t1 VALUES(1),(2),(2);

312

DELETE FROM t1 WHERE a=2;

313

SELECT * FROM t1;

314

315

316

DROP TABLE t1;

317

End of 4.1 tests

318

CREATE TABLE t1(val INT, KEY USING BTREE(val)) ENGINE=memory;

319

INSERT INTO t1 VALUES(0);

320

SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1';

321

INDEX_LENGTH

322

323

UPDATE t1 SET val=1;

324

SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1';

325

INDEX_LENGTH

326

327

DROP TABLE t1;

328

CREATE TABLE t1 (a INT, UNIQUE USING BTREE(a)) ENGINE=MEMORY;

329

INSERT INTO t1 VALUES(NULL),(NULL);

330

DROP TABLE t1;

331

create table t1(a varchar(255), b varchar(255),

332

key using btree (a,b)) engine=memory;

333

insert into t1 values (1, 1), (3, 3), (2, 2), (NULL, 1), (NULL, NULL), (0, 0);

334

select * from t1 where a is null;

335

a b

336

NULL NULL

337

NULL 1

338

drop table t1;

339

End of 5.0 tests

Older »