innodb.test 45.3 KB
Newer Older
1 2 3 4 5 6 7 8
#######################################################################
#                                                                     #
# Please, DO NOT TOUCH this file as well as the innodb.result file.   #
# These files are to be modified ONLY BY INNOBASE guys.               #
#                                                                     #
# Use innodb_mysql.[test|result] files instead.                       #
#                                                                     #
# If nevertheless you need to make some changes here, please, forward #
9 10 11
# your commit message                                                 #
# To: innodb_dev_ww@oracle.com                                        #
# Cc: dev-innodb@mysql.com                                            #
12 13 14 15
# (otherwise your changes may be erased).                             #
#                                                                     #
#######################################################################

unknown's avatar
unknown committed
16
-- source include/have_innodb.inc
unknown's avatar
unknown committed
17

18 19
let $MYSQLD_DATADIR= `select @@datadir`;

20 21 22 23 24 25 26 27
# Save the original values of some variables in order to be able to
# estimate how much they have changed during the tests. Previously this
# test assumed that e.g. rows_deleted is 0 here and after deleting 23
# rows it expected that rows_deleted will be 23. Now we do not make
# assumptions about the values of the variables at the beginning, e.g.
# rows_deleted should be 23 + "rows_deleted before the test". This allows
# the test to be run multiple times without restarting the mysqld server.
# See Bug#43309 Test main.innodb can't be run twice
28 29
-- disable_query_log
SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency;
30 31 32 33 34 35 36 37 38

SET @innodb_rows_deleted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted');
SET @innodb_rows_inserted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted');
SET @innodb_rows_updated_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated');
SET @innodb_row_lock_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits');
SET @innodb_row_lock_current_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits');
SET @innodb_row_lock_time_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time');
SET @innodb_row_lock_time_max_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max');
SET @innodb_row_lock_time_avg_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg');
39 40
-- enable_query_log

41
--disable_warnings
unknown's avatar
unknown committed
42
drop table if exists t1,t2,t3,t4;
43
drop database if exists mysqltest;
44 45
--enable_warnings

46 47 48 49
#
# Small basic test with ignore
#

unknown's avatar
unknown committed
50
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
unknown's avatar
unknown committed
51 52 53 54 55 56 57 58 59 60 61 62 63

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
select id, code, name from t1 order by id;
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
select id, code, name from t1 order by id;

drop table t1;

#
# A bit bigger test
64
# The 'replace_column' statements are needed because the cardinality calculated
unknown's avatar
unknown committed
65
# by innodb is not always the same between runs
unknown's avatar
unknown committed
66 67 68 69 70 71 72 73 74
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
unknown's avatar
unknown committed
75
) engine=innodb;
unknown's avatar
unknown committed
76 77 78 79
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
update t1 set id=id+1000;
80
-- error ER_DUP_ENTRY,1022
81
update t1 set id=1024 where id=1009; 
unknown's avatar
unknown committed
82 83 84 85 86
select * from t1;
update ignore t1 set id=id+1; # This will change all rows
select * from t1;
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
unknown's avatar
unknown committed
87 88 89 90 91 92
--replace_column 9 #
explain select level from t1 where level=1;
--replace_column 9 #
explain select level,id from t1 where level=1;
--replace_column 9 #
explain select level,id,parent_id from t1 where level=1;
unknown's avatar
unknown committed
93 94
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
95
optimize table t1;
96
--replace_column 7 #
97
show keys from t1;
unknown's avatar
unknown committed
98 99 100 101 102 103 104 105 106 107
drop table t1;

#
# Test replace
#

CREATE TABLE t1 (
  gesuchnr int(11) DEFAULT '0' NOT NULL,
  benutzer_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (gesuchnr,benutzer_id)
unknown's avatar
unknown committed
108
) engine=innodb;
unknown's avatar
unknown committed
109 110 111 112 113 114 115 116 117 118 119

replace into t1 (gesuchnr,benutzer_id) values (2,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
select * from t1;
drop table t1;

#
# test delete using hidden_primary_key
#

unknown's avatar
unknown committed
120
create table t1 (a int) engine=innodb;
unknown's avatar
unknown committed
121
insert into t1 values (1), (2);
122
optimize table t1;
unknown's avatar
unknown committed
123 124
delete from t1 where a = 1;
select * from t1;
125 126 127
check table t1;
drop table t1;

unknown's avatar
unknown committed
128
create table t1 (a int,b varchar(20)) engine=innodb;
129 130 131 132 133 134
insert into t1 values (1,""), (2,"testing");
delete from t1 where a = 1;
select * from t1;
create index skr on t1 (a);
insert into t1 values (3,""), (4,"testing");
analyze table t1;
unknown's avatar
unknown committed
135
--replace_column 7 #
136 137 138 139 140 141
show keys from t1;
drop table t1;


# Test of reading on secondary key with may be null

unknown's avatar
unknown committed
142
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
143 144
insert into t1 values (1,""), (2,"testing");
select * from t1 where a = 1;
unknown's avatar
unknown committed
145 146 147 148 149 150
drop table t1;

#
# Test rollback
#

unknown's avatar
unknown committed
151
create table t1 (n int not null primary key) engine=innodb;
unknown's avatar
unknown committed
152 153 154 155 156 157 158 159 160
set autocommit=0;
insert into t1 values (4);
rollback;
select n, "after rollback" from t1;
insert into t1 values (4);
commit;
select n, "after commit" from t1;
commit;
insert into t1 values (5);
161
-- error ER_DUP_ENTRY
162
insert into t1 values (4);
unknown's avatar
unknown committed
163 164 165 166
commit;
select n, "after commit" from t1;
set autocommit=1;
insert into t1 values (6);
167
-- error ER_DUP_ENTRY
168
insert into t1 values (4);
unknown's avatar
unknown committed
169
select n from t1;
unknown's avatar
unknown committed
170
set autocommit=0;
171 172 173
#
# savepoints
#
unknown's avatar
unknown committed
174 175 176 177 178 179
begin;
savepoint `my_savepoint`;
insert into t1 values (7);
savepoint `savept2`;
insert into t1 values (3);
select n from t1;
180 181 182 183 184
savepoint savept3;
rollback to savepoint savept2;
--error 1305
rollback to savepoint savept3;
rollback to savepoint savept2;
unknown's avatar
unknown committed
185 186
release savepoint `my_savepoint`;
select n from t1;
unknown's avatar
unknown committed
187
-- error 1305
unknown's avatar
unknown committed
188
rollback to savepoint `my_savepoint`;
189 190
--error 1305
rollback to savepoint savept2;
191 192 193 194
insert into t1 values (8);
savepoint sv;
commit;
savepoint sv;
unknown's avatar
unknown committed
195
set autocommit=1;
unknown's avatar
unknown committed
196 197 198 199
# nop
rollback;
drop table t1;

200 201 202 203
#
# Test for commit and FLUSH TABLES WITH READ LOCK
#

unknown's avatar
unknown committed
204
create table t1 (n int not null primary key) engine=innodb;
205 206 207
start transaction;
insert into t1 values (4);
flush tables with read lock;
unknown's avatar
unknown committed
208 209 210
#
# Current code can't handle a read lock in middle of transaction
#--error 1223;
211 212 213 214 215 216
commit;
unlock tables;
commit;
select * from t1;
drop table t1;

unknown's avatar
unknown committed
217 218 219 220
#
# Testing transactions
#

unknown's avatar
unknown committed
221
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
unknown's avatar
unknown committed
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
begin;
insert into t1 values(1,'hamdouni');
select id as afterbegin_id,nom as afterbegin_nom from t1;
rollback;
select id as afterrollback_id,nom as afterrollback_nom from t1;
set autocommit=0;
insert into t1 values(2,'mysql');
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
rollback;
select id as afterrollback_id,nom as afterrollback_nom from t1;
set autocommit=1;
drop table t1;

#
# Simple not autocommit test
# 

unknown's avatar
unknown committed
239
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
unknown's avatar
unknown committed
240
insert into t1 values ('pippo', 12);
241
-- error ER_DUP_ENTRY
242
insert into t1 values ('pippo', 12); # Gives error
unknown's avatar
unknown committed
243 244 245 246 247 248 249 250 251 252 253 254 255
delete from t1;
delete from t1 where id = 'pippo';
select * from t1;

insert into t1 values ('pippo', 12);
set autocommit=0;
delete from t1;
rollback;
select * from t1;
delete from t1;
commit;
select * from t1;
drop table t1;
unknown's avatar
unknown committed
256 257 258 259 260

#
# Test of active transactions
#

unknown's avatar
unknown committed
261
create table t1 (a integer) engine=innodb;
unknown's avatar
unknown committed
262
start transaction;
unknown's avatar
unknown committed
263
rename table t1 to t2;
unknown's avatar
unknown committed
264
create table t1 (b integer) engine=innodb;
unknown's avatar
unknown committed
265 266 267 268 269
insert into t1 values (1);
rollback;
drop table t1;
rename table t2 to t1;
drop table t1;
unknown's avatar
unknown committed
270 271 272 273 274 275
set autocommit=1;

#
# The following simple tests failed at some point
#

unknown's avatar
unknown committed
276
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
unknown's avatar
unknown committed
277 278 279 280
INSERT INTO t1 VALUES (1, 'Jochen');
select * from t1;
drop table t1;

unknown's avatar
unknown committed
281
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
unknown's avatar
unknown committed
282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299
set autocommit=0;
INSERT INTO t1  SET _userid='marc@anyware.co.uk';
COMMIT;
SELECT * FROM t1;
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
drop table t1;
set autocommit=1;

#
# Test when reading on part of unique key
#
CREATE TABLE t1 (
  user_id int(10) DEFAULT '0' NOT NULL,
  name varchar(100),
  phone varchar(100),
  ref_email varchar(100) DEFAULT '' NOT NULL,
  detail varchar(200),
  PRIMARY KEY (user_id,ref_email)
unknown's avatar
unknown committed
300
)engine=innodb;
unknown's avatar
unknown committed
301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
select * from t1 where user_id=10292;
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
select * from t1 where user_id=10292;
select * from t1 where user_id>=10292;
select * from t1 where user_id>10292;
select * from t1 where user_id<10292;
drop table t1;

#
# Test that keys are created in right order
#

CREATE TABLE t1 (a int not null, b int not null,c int not null,
key(a),primary key(a,b), unique(c),key(a),unique(b));
unknown's avatar
unknown committed
317
--replace_column 7 #
unknown's avatar
unknown committed
318 319 320 321
show index from t1;
drop table t1;

#
unknown's avatar
unknown committed
322
# Test of ALTER TABLE and innodb tables
unknown's avatar
unknown committed
323 324 325
#

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
unknown's avatar
unknown committed
326
alter table t1 engine=innodb;
unknown's avatar
unknown committed
327 328 329 330 331 332 333 334 335 336 337
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
select * from t1;
update t1 set col2='7' where col1='4';
select * from t1;
alter table t1 add co3 int not null;
select * from t1;
update t1 set col2='9' where col1='2';
select * from t1;
drop table t1;

#
unknown's avatar
unknown committed
338
# INSERT INTO innodb tables
unknown's avatar
unknown committed
339 340
#

unknown's avatar
unknown committed
341 342
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
unknown's avatar
unknown committed
343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369
insert into t1 VALUES (1,3) , (2,3), (3,3);
select * from t1;
insert into t2 select * from t1;
select * from t2;
delete from t1 where b = 3;
select * from t1;
insert into t1 select * from t2;
select * from t1;
select * from t2;
drop table t1,t2;

#
# ORDER BY on not primary key
#

CREATE TABLE t1 (
  user_name varchar(12),
  password text,
  subscribed char(1),
  user_id int(11) DEFAULT '0' NOT NULL,
  quota bigint(20),
  weight double,
  access_date date,
  access_time time,
  approved datetime,
  dummy_primary_key int(11) NOT NULL auto_increment,
  PRIMARY KEY (dummy_primary_key)
unknown's avatar
unknown committed
370
) ENGINE=innodb;
unknown's avatar
unknown committed
371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
select  user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
drop table t1;

#
# Testing of tables without primary keys
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
unknown's avatar
unknown committed
390
) engine=innodb;
unknown's avatar
unknown committed
391 392 393 394 395 396 397 398 399 400 401
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
INSERT INTO t1 values (179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
update t1 set id=id+1000;
update t1 set id=1024 where id=1009; 
select * from t1;
update ignore t1 set id=id+1; # This will change all rows
select * from t1;
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
unknown's avatar
unknown committed
402 403
--replace_column 9 #
explain select level from t1 where level=1;
unknown's avatar
unknown committed
404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
select level,id from t1 where level=1 order by id;
delete from t1 where level=1;
select * from t1;
drop table t1;

#
# Test of index only reads
#
CREATE TABLE t1 (
   sca_code char(6) NOT NULL,
   cat_code char(6) NOT NULL,
   sca_desc varchar(50),
   lan_code char(2) NOT NULL,
   sca_pic varchar(100),
   sca_sdesc varchar(50),
   sca_sch_desc varchar(16),
422 423
   PRIMARY KEY (sca_code, cat_code, lan_code),
   INDEX sca_pic (sca_pic)
unknown's avatar
unknown committed
424
) engine = innodb ;
unknown's avatar
unknown committed
425

426
INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
unknown's avatar
unknown committed
427
select count(*) from t1 where sca_code = 'PD';
428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445
select count(*) from t1 where sca_code <= 'PD';
select count(*) from t1 where sca_pic is null;
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
select count(*) from t1 where cat_code='E';

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
select count(*) from t1 where sca_pic >= 'n';
select sca_pic from t1 where sca_pic is null;
update t1 set sca_pic="test" where sca_pic is null;
delete from t1 where sca_code='pd';
drop table t1;

#
# Test of opening table twice and timestamps
#
set @a:=now();
unknown's avatar
unknown committed
446
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
447
insert into t1 (a) values(1),(2),(3);
448
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
unknown's avatar
unknown committed
449
select a from t1 natural join t1 as t2 where b >= @a order by a;
450 451 452 453 454 455 456
update t1 set a=5 where a=1;
select a from t1;
drop table t1;

#
# Test with variable length primary key
#
unknown's avatar
unknown committed
457
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
458 459 460
insert into t1 values("hello",1),("world",2);
select * from t1 order by b desc;
optimize table t1;
unknown's avatar
unknown committed
461
--replace_column 7 #
462
show keys from t1;
unknown's avatar
unknown committed
463 464 465
drop table t1;

#
466
# Test of create index with NULL columns
unknown's avatar
unknown committed
467
#
unknown's avatar
unknown committed
468
create table t1 (i int, j int ) ENGINE=innodb;
469 470 471 472
insert into t1 values (1,2);
select * from t1 where i=1 and j=2;
create index ax1 on t1 (i,j);
select * from t1 where i=1 and j=2;
unknown's avatar
unknown committed
473
drop table t1;
474 475 476 477 478 479 480 481 482

#
# Test min-max optimization
#

CREATE TABLE t1 (
  a int3 unsigned NOT NULL,
  b int1 unsigned NOT NULL,
  UNIQUE (a, b)
unknown's avatar
unknown committed
483
) ENGINE = innodb;
484 485 486 487
 
INSERT INTO t1 VALUES (1, 1);
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
drop table t1;
488

unknown's avatar
unknown committed
489 490 491 492
#
# Test INSERT DELAYED
#

unknown's avatar
unknown committed
493
CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
unknown's avatar
unknown committed
494 495
# Can't test this in 3.23
# INSERT DELAYED INTO t1 VALUES (1);
unknown's avatar
unknown committed
496 497 498 499 500
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
DROP TABLE t1;


501 502 503 504
#
# Crash when using many tables (Test case by Jeremy D Zawodny)
#

unknown's avatar
unknown committed
505
create table t1 (a int  primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb;
506
insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
unknown's avatar
unknown committed
507
--replace_column 9 #
508 509
explain select * from t1 where a > 0 and a < 50;
drop table t1;
unknown's avatar
unknown committed
510 511 512 513 514

#
# Test lock tables
#

unknown's avatar
unknown committed
515
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
unknown's avatar
unknown committed
516 517
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
518
--error ER_DUP_ENTRY
unknown's avatar
unknown committed
519 520 521 522 523 524
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
select id from t1;
select id from t1;
UNLOCK TABLES;
DROP TABLE t1;

unknown's avatar
unknown committed
525
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
unknown's avatar
unknown committed
526 527 528
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
begin;
529
--error ER_DUP_ENTRY
unknown's avatar
unknown committed
530 531 532 533 534 535 536
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
select id from t1;
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
commit;
select id,id3 from t1;
UNLOCK TABLES;
DROP TABLE t1;
unknown's avatar
unknown committed
537 538 539 540

#
# Test prefix key
#
unknown's avatar
unknown committed
541
create table t1 (a char(20), unique (a(5))) engine=innodb;
unknown's avatar
unknown committed
542
drop table t1;
unknown's avatar
unknown committed
543
create table t1 (a char(20), index (a(5))) engine=innodb;
unknown's avatar
unknown committed
544 545 546 547 548 549 550
show create table t1;
drop table t1;

#
# Test using temporary table and auto_increment
#

unknown's avatar
unknown committed
551
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
unknown's avatar
unknown committed
552 553 554 555
insert into t1 values (NULL),(NULL),(NULL);
delete from t1 where a=3;
insert into t1 values (NULL);
select * from t1;
556 557
alter table t1 add b int;
select * from t1;
unknown's avatar
unknown committed
558
drop table t1;
559 560 561 562 563 564 565 566 567

#Slashdot bug
create table t1
 (
  id int auto_increment primary key,
  name varchar(32) not null,
  value text not null,
  uid int not null,
  unique key(name,uid)
unknown's avatar
unknown committed
568
 ) engine=innodb;
569 570 571 572 573 574 575 576 577 578 579 580
insert into t1 values (1,'one','one value',101),
 (2,'two','two value',102),(3,'three','three value',103);
set insert_id=5;
replace into t1 (value,name,uid) values ('other value','two',102);
delete from t1 where uid=102;
set insert_id=5;
replace into t1 (value,name,uid) values ('other value','two',102);
set insert_id=6;
replace into t1 (value,name,uid) values ('other value','two',102);
select * from t1;
drop table t1;

unknown's avatar
unknown committed
581 582 583 584
#
# Test DROP DATABASE
#

585
create database mysqltest;
unknown's avatar
unknown committed
586
create table mysqltest.t1 (a int not null) engine= innodb;
587
insert into mysqltest.t1 values(1);
unknown's avatar
unknown committed
588
create table mysqltest.t2 (a int not null) engine= myisam;
589
insert into mysqltest.t2 values(1);
unknown's avatar
unknown committed
590
create table mysqltest.t3 (a int not null) engine= heap;
591
insert into mysqltest.t3 values(1);
unknown's avatar
unknown committed
592
commit;
593
drop database mysqltest;
unknown's avatar
unknown committed
594
# Don't check error message
unknown's avatar
unknown committed
595
--error 1049
596
show tables from mysqltest;
unknown's avatar
unknown committed
597 598

#
unknown's avatar
unknown committed
599
# Test truncate table with and without auto_commit
unknown's avatar
unknown committed
600 601
#

unknown's avatar
unknown committed
602
set autocommit=0;
unknown's avatar
unknown committed
603
create table t1 (a int not null) engine= innodb;
unknown's avatar
unknown committed
604 605 606 607
insert into t1 values(1),(2);
truncate table t1;
commit;
truncate table t1;
608
truncate table t1;
unknown's avatar
unknown committed
609 610 611 612 613 614
select * from t1;
insert into t1 values(1),(2);
delete from t1;
select * from t1;
commit;
drop table t1;
unknown's avatar
unknown committed
615 616
set autocommit=1;

unknown's avatar
unknown committed
617
create table t1 (a int not null) engine= innodb;
unknown's avatar
unknown committed
618 619 620 621 622 623 624 625 626
insert into t1 values(1),(2);
truncate table t1;
insert into t1 values(1),(2);
select * from t1;
truncate table t1;
insert into t1 values(1),(2);
delete from t1;
select * from t1;
drop table t1;
unknown's avatar
unknown committed
627

628 629 630 631
#
# Test of how ORDER BY works when doing it on the whole table
#

unknown's avatar
unknown committed
632
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
633
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
unknown's avatar
unknown committed
634
--replace_column 9 #
635
explain select * from t1 order by a;
unknown's avatar
unknown committed
636
--replace_column 9 #
637
explain select * from t1 order by b;
unknown's avatar
unknown committed
638
--replace_column 9 #
639
explain select * from t1 order by c;
unknown's avatar
unknown committed
640
--replace_column 9 #
641
explain select a from t1 order by a;
unknown's avatar
unknown committed
642
--replace_column 9 #
643
explain select b from t1 order by b;
unknown's avatar
unknown committed
644
--replace_column 9 #
645
explain select a,b from t1 order by b;
unknown's avatar
unknown committed
646
--replace_column 9 #
647
explain select a,b from t1;
unknown's avatar
unknown committed
648
--replace_column 9 #
649 650
explain select a,b,c from t1;
drop table t1;
651 652

#
unknown's avatar
unknown committed
653
# Check describe
654 655
#

unknown's avatar
unknown committed
656
create table t1 (t int not null default 1, key (t)) engine=innodb;
657 658
desc t1;
drop table t1;
659 660 661 662 663 664 665 666 667 668

#
# Test of multi-table-delete
#

CREATE TABLE t1 (
  number bigint(20) NOT NULL default '0',
  cname char(15) NOT NULL default '',
  carrier_id smallint(6) NOT NULL default '0',
  privacy tinyint(4) NOT NULL default '0',
669
  last_mod_date timestamp NOT NULL,
670
  last_mod_id smallint(6) NOT NULL default '0',
671
  last_app_date timestamp NOT NULL,
672 673 674 675
  last_app_id smallint(6) default '-1',
  version smallint(6) NOT NULL default '0',
  assigned_scps int(11) default '0',
  status tinyint(4) default '0'
unknown's avatar
unknown committed
676
) ENGINE=InnoDB;
677 678 679 680 681 682 683 684 685 686 687
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
CREATE TABLE t2 (
  number bigint(20) NOT NULL default '0',
  cname char(15) NOT NULL default '',
  carrier_id smallint(6) NOT NULL default '0',
  privacy tinyint(4) NOT NULL default '0',
688
  last_mod_date timestamp NOT NULL,
689
  last_mod_id smallint(6) NOT NULL default '0',
690
  last_app_date timestamp NOT NULL,
691 692 693 694
  last_app_id smallint(6) default '-1',
  version smallint(6) NOT NULL default '0',
  assigned_scps int(11) default '0',
  status tinyint(4) default '0'
unknown's avatar
unknown committed
695
) ENGINE=InnoDB;
696 697 698 699 700 701 702 703 704 705 706
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
select * from t1;
select * from t2;
delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null);
select * from t1;
select * from t2; 
select * from t2;
drop table t1,t2;
unknown's avatar
unknown committed
707 708 709 710 711 712 713

#
# A simple test with some isolation levels
# TODO: Make this into a test using replication to really test how
# this works.
#

unknown's avatar
unknown committed
714
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
unknown's avatar
unknown committed
715 716 717 718 719 720 721 722 723 724 725 726 727 728

BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@tx_isolation,@@global.tx_isolation;
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
select id, code, name from t1 order by id;
COMMIT;

BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
select id, code, name from t1 order by id;
COMMIT;

729
SET binlog_format='MIXED';
unknown's avatar
unknown committed
730 731 732 733 734 735
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;
COMMIT;
DROP TABLE t1;
736 737 738 739

#
# Test of multi-table-update
#
unknown's avatar
unknown committed
740 741
create table t1 (n int(10), d int(10)) engine=innodb;
create table t2 (n int(10), d int(10)) engine=innodb;
742 743 744 745 746 747
insert into t1 values(1,1),(1,2);
insert into t2 values(1,10),(2,20);
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
select * from t1;
select * from t2;
drop table t1,t2;
748

749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780
#
# Bug #29136  	erred multi-delete on trans table does not rollback 
#

# prepare
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
CREATE TABLE t1 (a int, PRIMARY KEY (a));
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
create trigger trg_del_t2 after  delete on t2 for each row
       insert into t1 values (1);
insert into t1 values (1);
insert into t2 values (1),(2);


# exec cases A, B - see multi_update.test

# A. send_error() w/o send_eof() branch

--error ER_DUP_ENTRY
delete t2 from t2;

# check

select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;

# cleanup bug#29136

drop table t1, t2;


781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812
#
# Bug #29136  	erred multi-delete on trans table does not rollback 
#

# prepare
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
CREATE TABLE t1 (a int, PRIMARY KEY (a));
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
create trigger trg_del_t2 after  delete on t2 for each row
       insert into t1 values (1);
insert into t1 values (1);
insert into t2 values (1),(2);


# exec cases A, B - see multi_update.test

# A. send_error() w/o send_eof() branch

--error ER_DUP_ENTRY
delete t2 from t2;

# check

select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;

# cleanup bug#29136

drop table t1, t2;


813 814 815
#
# Testing of IFNULL
#
unknown's avatar
unknown committed
816
create table t1 (a int, b int) engine=innodb;
817 818 819 820 821 822 823 824 825 826 827 828 829 830
insert into t1 values(20,null);
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
t2.b=t3.a;
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
t2.b=t3.a order by 1;
insert into t1 values(10,null);
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
t2.b=t3.a order by 1;
drop table t1;

#
# Test of read_through not existing const_table
#

unknown's avatar
unknown committed
831 832
create table t1 (a varchar(10) not null) engine=myisam;
create table t2 (b varchar(10) not null unique) engine=innodb;
833 834
select t1.a from t1,t2 where t1.a=t2.b;
drop table t1,t2;
unknown's avatar
unknown committed
835 836
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
unknown's avatar
unknown committed
837 838 839 840
insert into t1 values (10, 20);
insert into t2 values (10, 20);
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
drop table t1,t2;
841 842 843 844 845

#
# Test of multi-table-delete with foreign key constraints
#

unknown's avatar
unknown committed
846 847
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE ) ENGINE=INNODB;
848 849 850 851 852
insert into t1 set id=1;
insert into t2 set id=1, t1_id=1;
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
select * from t1;
select * from t2;
unknown's avatar
unknown committed
853
drop table t2,t1;
unknown's avatar
unknown committed
854 855
CREATE TABLE t1(id INT NOT NULL,  PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2(id  INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id)  ) ENGINE=INNODB;
856 857 858 859 860
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, 1);
SELECT * from t1;
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
SELECT * from t1;
unknown's avatar
unknown committed
861
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
862
SELECT * from t1;
unknown's avatar
unknown committed
863
DROP TABLE t1,t2;
unknown's avatar
unknown committed
864 865 866 867 868 869 870

#
# Test of range_optimizer
#

set autocommit=0;

unknown's avatar
unknown committed
871
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
unknown's avatar
unknown committed
872

unknown's avatar
unknown committed
873
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
unknown's avatar
unknown committed
874

unknown's avatar
unknown committed
875
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
unknown's avatar
unknown committed
876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895

INSERT INTO t3 VALUES("my-test-1", "my-test-2");
COMMIT;

INSERT INTO t1 VALUES("this-key", "will disappear");
INSERT INTO t2 VALUES("this-key", "will also disappear");
DELETE FROM t3 WHERE id1="my-test-1";

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
ROLLBACK;

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
COMMIT;
set autocommit=1;
DROP TABLE t1,t2,t3;
896

897 898 899 900
#
# Check update with conflicting key
#

unknown's avatar
unknown committed
901
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
902 903 904 905 906
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
# We need the a < 1000 test here to quard against the halloween problems
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
SELECT * from t1;
drop table t1;
907

unknown's avatar
unknown committed
908 909
CREATE TABLE t2 (   NEXT_T         BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE t1 (  B_ID           INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
910 911 912 913 914 915
SET AUTOCOMMIT=0;
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
ROLLBACK;
SELECT * FROM t1;
drop table  t1,t2;
unknown's avatar
unknown committed
916
create table t1  ( pk         int primary key,    parent     int not null,    child      int not null,       index (parent)  ) engine = innodb;
unknown's avatar
unknown committed
917 918
insert into t1 values   (1,0,4),  (2,1,3),  (3,2,1),  (4,1,2);
select distinct  parent,child   from t1   order by parent;
unknown's avatar
unknown committed
919
drop table t1;
920

unknown's avatar
unknown committed
921 922 923
#
# Test that MySQL priorities clustered indexes
#
unknown's avatar
unknown committed
924
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
unknown's avatar
unknown committed
925 926 927 928 929 930 931 932 933 934 935 936 937
create table t2 (a int not null auto_increment primary key, b int);
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
insert into t2 (a) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
select count(*) from t1;
unknown's avatar
unknown committed
938
--replace_column 9 #
unknown's avatar
unknown committed
939
explain select * from t1 where c between 1 and 2500;
unknown's avatar
unknown committed
940
update t1 set c=a;
unknown's avatar
unknown committed
941
--replace_column 9 #
unknown's avatar
unknown committed
942
explain select * from t1 where c between 1 and 2500;
943
drop table t1,t2;
unknown's avatar
unknown committed
944 945 946 947 948

#
# Test of UPDATE ... ORDER BY
#

unknown's avatar
unknown committed
949
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
unknown's avatar
unknown committed
950 951 952 953 954 955 956 957 958 959 960 961 962 963

insert into t1 (id) values (null),(null),(null),(null),(null);
update t1 set fk=69 where fk is null order by id limit 1;
SELECT * from t1;
drop table t1;

create table t1 (a int not null, b int not null, key (a));
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
SET @tmp=0;
update t1 set b=(@tmp:=@tmp+1) order by a;
update t1 set b=99 where a=1 order by b asc limit 1;
update t1 set b=100 where a=1 order by b desc limit 2;
update t1 set a=a+10+b where a=1 order by b;
select * from t1 order by a,b;
unknown's avatar
unknown committed
964
drop table t1;
unknown's avatar
unknown committed
965

unknown's avatar
unknown committed
966 967 968 969
#
# Test of multi-table-updates (bug #1980).
#

unknown's avatar
unknown committed
970
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
971 972 973 974 975 976 977 978
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

alter table t1 add b char(8) not null;
alter table t1 add a char(8) not null;
alter table t1 add primary key (a,b,c);
update t1 set a=c, b=c;

unknown's avatar
unknown committed
979
create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;
unknown's avatar
unknown committed
980 981 982 983
insert into t2 select * from t1;

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
drop table t1,t2;
984 985 986 987 988 989

#
# test autoincrement with TRUNCATE
#

SET AUTOCOMMIT=1;
unknown's avatar
unknown committed
990
create table t1 (a integer auto_increment primary key) engine=innodb;
991 992 993 994 995
insert into t1 (a) values (NULL),(NULL);
truncate table t1;
insert into t1 (a) values (NULL),(NULL);
SELECT * from t1;
drop table t1;
unknown's avatar
unknown committed
996

997 998 999 1000
#
# Test dictionary handling with spaceand quoting
#

unknown's avatar
unknown committed
1001 1002
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`)  ON DELETE CASCADE ) ENGINE=INNODB;
1003
#show create table t2;
unknown's avatar
unknown committed
1004
drop table t2,t1;
unknown's avatar
unknown committed
1005

1006 1007 1008 1009
#
# Test of multi updated and foreign keys
#

unknown's avatar
unknown committed
1010
create table `t1` (`id` int( 11 ) not null  ,primary key ( `id` )) engine = innodb;
1011
insert into `t1`values ( 1 ) ;
unknown's avatar
unknown committed
1012
create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;
1013
insert into `t2`values ( 1 ) ;
unknown's avatar
unknown committed
1014
create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
1015
insert into `t3`values ( 1 ) ;
1016
--error 1451
1017
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1018
--error 1451
1019
update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
unknown's avatar
unknown committed
1020
--error 1054
1021 1022
update t3 set  t3.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
drop table t3,t2,t1;
unknown's avatar
unknown committed
1023

unknown's avatar
unknown committed
1024 1025 1026
#
# test for recursion depth limit
#
1027 1028 1029 1030
create table t1(
	id int primary key,
	pid int,
	index(pid),
unknown's avatar
unknown committed
1031
	foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1032 1033
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
	(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1034
-- error 1451
1035 1036 1037 1038 1039
delete from t1 where id=0;
delete from t1 where id=15;
delete from t1 where id=0;

drop table t1;
unknown's avatar
unknown committed
1040

unknown's avatar
unknown committed
1041 1042 1043
#
# Test timestamps
#
unknown's avatar
unknown committed
1044

unknown's avatar
unknown committed
1045
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1046
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
unknown's avatar
unknown committed
1047
(stamp))ENGINE=InnoDB;
unknown's avatar
unknown committed
1048
insert into t1 values (1),(2),(3);
unknown's avatar
unknown committed
1049 1050
# Note that timestamp 3 is wrong
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
unknown's avatar
unknown committed
1051 1052 1053
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
'20020204120000' GROUP BY col1;
drop table t1,t2;
1054 1055 1056 1057 1058 1059 1060 1061 1062

#
# Test by Francois MASUREL
#

CREATE TABLE t1 (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_object` int(10) unsigned default '0',
  `id_version` int(10) unsigned NOT NULL default '1',
unknown's avatar
unknown committed
1063
  `label` varchar(100) NOT NULL default '',
1064 1065 1066 1067
  `description` text,
  PRIMARY KEY  (`id`),
  KEY `id_object` (`id_object`),
  KEY `id_version` (`id_version`)
unknown's avatar
unknown committed
1068
) ENGINE=InnoDB;
1069 1070 1071 1072 1073 1074 1075 1076

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

CREATE TABLE t2 (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_version` int(10) unsigned NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `id_version` (`id_version`)
unknown's avatar
unknown committed
1077
) ENGINE=InnoDB;
1078 1079 1080

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

unknown's avatar
unknown committed
1081 1082
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 
1083 1084
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
drop table t1,t2;
1085

unknown's avatar
unknown committed
1086 1087 1088
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1089 1090 1091 1092 1093 1094 1095 1096 1097
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
insert t2 select * from t1;
insert t3 select * from t1;
checksum table t1, t2, t3, t4 quick;
checksum table t1, t2, t3, t4;
checksum table t1, t2, t3, t4 extended;
#show table status;
drop table t1,t2,t3;

1098 1099 1100 1101 1102 1103
#
# Test problem with refering to different fields in same table in UNION
# (Bug #2552)
#
create table t1 (id int,  name char(10) not null,  name2 char(10) not null) engine=innodb;
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1104
select trim(name2) from t1  union all  select trim(name) from t1 union all select trim(id) from t1;
1105
drop table t1;
1106 1107 1108 1109 1110 1111 1112

#
# Bug2160
#
create table t1 (a int) engine=innodb;
create table t2 like t1;
drop table t1,t2;
1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150

#
# Test of automaticly created foreign keys
#

create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
show create table t1;
show create table t2;
create index id on t2 (id);
show create table t2;
create index id2 on t2 (id);
show create table t2;
drop index id2 on t2;
--error 1025,1025
drop index id on t2;
show create table t2;
drop table t2;

create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;
show create table t2;
create unique index id on t2 (id,id2);
show create table t2;
drop table t2;

# Check foreign key columns created in different order than key columns
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
show create table t2;
drop table t2;

create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;
show create table t2;
drop table t2;

create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
show create table t2;
drop table t2;

1151 1152 1153 1154 1155 1156 1157 1158 1159 1160
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;
show create table t2;
drop table t2;

create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;
show create table t2;
alter table t2 add index id_test (id), add index id_test2 (id,id2);
show create table t2;
drop table t2;

1161
# Test error handling
1162

1163
# Embedded server doesn't chdir to data directory
1164
--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
1165
--error ER_WRONG_FK_DEF
1166 1167
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;

1168 1169 1170 1171 1172 1173 1174 1175 1176 1177
# bug#3749

create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
show create table t2;
drop table t2;
create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb;
show create table t2;
drop table t2, t1;


1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198
#
# Bug #6126: Duplicate columns in keys gives misleading error message
#
--error 1060
create table t1 (c char(10), index (c,c)) engine=innodb;
--error 1060
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
--error 1060
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
--error 1060
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
--error 1060
alter table t1 add key (c1,c1);
--error 1060
alter table t1 add key (c2,c1,c1);
--error 1060
alter table t1 add key (c1,c2,c1);
--error 1060
alter table t1 add key (c1,c1,c2);
drop table t1;
unknown's avatar
unknown committed
1199

1200 1201 1202 1203 1204 1205 1206 1207
#
# Bug #4082: integer truncation
#

create table t1(a int(1) , b int(1)) engine=innodb;
insert into t1 values ('1111', '3333');
select distinct concat(a, b) from t1;
drop table t1;
unknown's avatar
unknown committed
1208

1209 1210 1211 1212 1213 1214
#
# BUG#7709 test case - Boolean fulltext query against unsupported 
#                      engines does not fail
#

CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1215
--error 1214
1216
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
unknown's avatar
unknown committed
1217
DROP TABLE t1;
1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232

#
# check null values #1
#

--disable_warnings
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY  (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY  (b_id), KEY  (b_a), 
                CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--enable_warnings
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;
DROP TABLE t2;
DROP TABLE t1;
unknown's avatar
unknown committed
1233

1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251
#
# Bug#11816 - Truncate table doesn't work with temporary innodb tables
# This is not an innodb bug, but we test it using innodb.
#
create temporary table t1 (a int) engine=innodb;
insert into t1 values (4711);
truncate t1;
insert into t1 values (42);
select * from t1;
drop table t1;
# Show that it works with permanent tables too.
create table t1 (a int) engine=innodb;
insert into t1 values (4711);
truncate t1;
insert into t1 values (42);
select * from t1;
drop table t1;

unknown's avatar
unknown committed
1252 1253 1254 1255 1256 1257 1258 1259 1260 1261
#
# Bug #13025  Server crash during filesort	
#

create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb;
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
select * from t1 order by a,b,c,d;
explain select * from t1 order by a,b,c,d;
drop table t1;

1262 1263 1264 1265 1266 1267 1268 1269 1270
#
# BUG#11039,#13218 Wrong key length in min()
#

create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
insert into t1 values ('8', '6'), ('4', '7');
select min(a) from t1;
select min(b) from t1 where a='8';
drop table t1;
1271

1272
# End of 4.1 tests
unknown's avatar
unknown committed
1273

unknown's avatar
unknown committed
1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292
#
# range optimizer problem
#

create table t1 (x bigint unsigned not null primary key) engine=innodb;
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
select * from t1;
select count(*) from t1 where x>0;
select count(*) from t1 where x=0;
select count(*) from t1 where x<0;
select count(*) from t1 where x < -16;
select count(*) from t1 where x = -16;
explain select count(*) from t1 where x > -16;
select count(*) from t1 where x > -16;
select * from t1 where x > -16;
select count(*) from t1 where x = 18446744073709551601;
drop table t1;


1293 1294
# Test for testable InnoDB status variables. This test
# uses previous ones(pages_created, rows_deleted, ...).
1295 1296 1297 1298 1299
SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted';
SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
unknown's avatar
unknown committed
1300 1301

# Test for row locks InnoDB status variables.
1302 1303 1304 1305 1306
SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';
SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';
SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';
SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';
SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';
unknown's avatar
unknown committed
1307

1308 1309 1310 1311 1312 1313 1314 1315 1316
# Test for innodb_sync_spin_loops variable
show variables like "innodb_sync_spin_loops";
set global innodb_sync_spin_loops=1000;
show variables like "innodb_sync_spin_loops";
set global innodb_sync_spin_loops=0;
show variables like "innodb_sync_spin_loops";
set global innodb_sync_spin_loops=20;
show variables like "innodb_sync_spin_loops";

unknown's avatar
unknown committed
1317
# Test for innodb_thread_concurrency variable
1318
SET @old_innodb_thread_concurrency= @@global.innodb_thread_concurrency;
unknown's avatar
unknown committed
1319
show variables like "innodb_thread_concurrency";
1320
set global innodb_thread_concurrency=1001;
unknown's avatar
unknown committed
1321 1322 1323 1324 1325
show variables like "innodb_thread_concurrency";
set global innodb_thread_concurrency=0;
show variables like "innodb_thread_concurrency";
set global innodb_thread_concurrency=16;
show variables like "innodb_thread_concurrency";
1326
SET @@global.innodb_thread_concurrency= @old_innodb_thread_concurrency;
unknown's avatar
unknown committed
1327

unknown's avatar
unknown committed
1328 1329 1330 1331 1332 1333 1334 1335
# Test for innodb_concurrency_tickets variable
show variables like "innodb_concurrency_tickets";
set global innodb_concurrency_tickets=1000;
show variables like "innodb_concurrency_tickets";
set global innodb_concurrency_tickets=0;
show variables like "innodb_concurrency_tickets";
set global innodb_concurrency_tickets=500;
show variables like "innodb_concurrency_tickets";
unknown's avatar
unknown committed
1336 1337 1338 1339 1340 1341 1342 1343 1344 1345

# Test for innodb_thread_sleep_delay variable
show variables like "innodb_thread_sleep_delay";
set global innodb_thread_sleep_delay=100000;
show variables like "innodb_thread_sleep_delay";
set global innodb_thread_sleep_delay=0;
show variables like "innodb_thread_sleep_delay";
set global innodb_thread_sleep_delay=10000;
show variables like "innodb_thread_sleep_delay";

1346 1347 1348 1349
#
# Test varchar
#

1350 1351 1352 1353 1354 1355 1356 1357
let $default=`select @@storage_engine`;
set storage_engine=INNODB;
source include/varchar.inc;

#
# Some errors/warnings on create
#

1358
# Embedded server doesn't chdir to data directory
1359
--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
1360
create table t1 (v varchar(65530), key(v));
1361
drop table t1;
1362 1363 1364 1365 1366 1367 1368 1369
create table t1 (v varchar(65536));
show create table t1;
drop table t1;
create table t1 (v varchar(65530) character set utf8);
show create table t1;
drop table t1;

eval set storage_engine=$default;
1370

1371
-- disable_query_log
1372 1373
SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;

unknown's avatar
unknown committed
1374 1375 1376 1377 1378 1379 1380 1381
#######################################################################
#                                                                     #
# Please, DO NOT TOUCH this file as well as the innodb.result file.   #
# These files are to be modified ONLY BY INNOBASE guys.               #
#                                                                     #
# Use innodb_mysql.[test|result] files instead.                       #
#                                                                     #
# If nevertheless you need to make some changes here, please, forward #
1382 1383 1384
# your commit message                                                 #
# To: innodb_dev_ww@oracle.com                                        #
# Cc: dev-innodb@mysql.com                                            #
unknown's avatar
unknown committed
1385 1386 1387
# (otherwise your changes may be erased).                             #
#                                                                     #
#######################################################################