2
drop table if exists t1,t2,t3;
5
#----------------------------------------
6
# Bug with auto_increment settings
8
create table t1 (id int auto_increment key) engine=pbxt auto_increment=200;
11
alter table t1 add column text char(40);
14
alter table t1 auto_increment = 100;
17
#----------------------------------------
19
drop table if exists t1;
24
c_char_bin char(3) binary,
25
c_varchar_bin varchar(3) binary,
26
c_char_uni char(3) unicode,
27
c_varchar_uni varchar(3) unicode,
38
insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
39
insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
40
select c_char from t1 where c_char = "ab";
41
select c_varchar from t1 where c_varchar = "ab";
42
select c_char_bin from t1 where c_char_bin = "ab";
43
select c_varchar_bin from t1 where c_varchar_bin = "ab";
44
select c_char_uni from t1 where c_char_uni = "ab";
45
select c_varchar_uni from t1 where c_varchar_uni = "ab";
46
select c_dec from t1 where c_dec = 12;
47
select * from t1 where c_char = "ab ";
48
select * from t1 where c_varchar = "ab ";
49
select * from t1 where c_char_bin = "ab ";
50
select * from t1 where c_varchar_bin = "ab ";
51
select * from t1 where c_char_uni = "ab ";
52
select * from t1 where c_varchar_uni = "ab ";
53
select * from t1 where c_dec = 123;
56
#----------------------------------------
58
drop table if exists t1;
62
c_varchar varchar(100),
63
c_char_bin char(100) binary,
64
c_varchar_bin varchar(100) binary,
65
c_char_uni char(100) unicode,
66
c_varchar_uni varchar(100) unicode,
77
insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
78
insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
79
select * from t1 where c_char = "ab";
80
select * from t1 where c_varchar = "ab";
81
select * from t1 where c_char_bin = "ab";
82
select * from t1 where c_varchar_bin = "ab";
83
select * from t1 where c_char_uni = "ab";
84
select * from t1 where c_varchar_uni = "ab";
85
select * from t1 where c_dec = 12;
86
select c_char from t1 where c_char = "ab ";
87
select c_varchar from t1 where c_varchar = "ab ";
88
select c_char_bin from t1 where c_char_bin = "ab ";
89
select c_varchar_bin from t1 where c_varchar_bin = "ab ";
90
select c_char_uni from t1 where c_char_uni = "ab ";
91
select c_varchar_uni from t1 where c_varchar_uni = "ab ";
92
select c_dec from t1 where c_dec = 123;
95
#----------------------------------------
97
drop table if exists t1;
100
c_varchar varchar(300),
101
c_varchar_bin varchar(300) binary,
102
c_varchar_uni varchar(300) unicode,
108
index(c_varchar_bin),
109
index(c_varchar_uni),
116
insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", "ab");
117
insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", "ab ");
118
select c_varchar from t1 where c_varchar = "ab";
119
select c_varchar_bin from t1 where c_varchar_bin = "ab";
120
select c_varchar_uni from t1 where c_varchar_uni = "ab";
121
select c_ttext from t1 where c_ttext = "ab";
122
select c_text from t1 where c_text = "ab";
123
select c_mtext from t1 where c_mtext = "ab";
124
select c_ltext from t1 where c_ltext = "ab";
126
select * from t1 where c_varchar = "ab ";
127
select * from t1 where c_varchar_bin = "ab ";
128
select * from t1 where c_varchar_uni = "ab ";
129
select * from t1 where c_ttext = "ab ";
130
select * from t1 where c_text = "ab ";
131
select * from t1 where c_mtext = "ab ";
132
select * from t1 where c_ltext = "ab ";
134
#----------------------------------------
135
# Short fields NOT NULL
136
drop table if exists t1;
139
c_char char(3) not null,
140
c_varchar varchar(3) not null,
141
c_char_bin char(3) binary not null,
142
c_varchar_bin varchar(3) binary not null,
143
c_char_uni char(3) unicode not null,
144
c_varchar_uni varchar(3) unicode not null,
145
c_dec decimal(3) not null,
149
index(c_varchar_bin),
151
index(c_varchar_uni),
155
insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
156
insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
157
select * from t1 where c_char = "ab";
158
select * from t1 where c_varchar = "ab";
159
select * from t1 where c_char_bin = "ab";
160
select * from t1 where c_varchar_bin = "ab";
161
select * from t1 where c_char_uni = "ab";
162
select * from t1 where c_varchar_uni = "ab";
163
select * from t1 where c_dec = 12;
164
select c_char from t1 where c_char = "ab ";
165
select c_varchar from t1 where c_varchar = "ab ";
166
select c_char_bin from t1 where c_char_bin = "ab ";
167
select c_varchar_bin from t1 where c_varchar_bin = "ab ";
168
select c_char_uni from t1 where c_char_uni = "ab ";
169
select c_varchar_uni from t1 where c_varchar_uni = "ab ";
170
select c_dec from t1 where c_dec = 123;
173
#----------------------------------------
174
# Medium fields NOT NULL
175
drop table if exists t1;
178
c_char char(100) not null,
179
c_varchar varchar(100) not null,
180
c_char_bin char(100) binary not null,
181
c_varchar_bin varchar(100) binary not null,
182
c_char_uni char(100) unicode not null,
183
c_varchar_uni varchar(100) unicode not null,
188
index(c_varchar_bin),
190
index(c_varchar_uni),
194
insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
195
insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
196
select c_char from t1 where c_char = "ab";
197
select c_varchar from t1 where c_varchar = "ab";
198
select c_char_bin from t1 where c_char_bin = "ab";
199
select c_varchar_bin from t1 where c_varchar_bin = "ab";
200
select c_char_uni from t1 where c_char_uni = "ab";
201
select c_varchar_uni from t1 where c_varchar_uni = "ab";
202
select c_dec from t1 where c_dec = 12;
203
select * from t1 where c_char = "ab ";
204
select * from t1 where c_varchar = "ab ";
205
select * from t1 where c_char_bin = "ab ";
206
select * from t1 where c_varchar_bin = "ab ";
207
select * from t1 where c_char_uni = "ab ";
208
select * from t1 where c_varchar_uni = "ab ";
209
select * from t1 where c_dec = 123;
212
#----------------------------------------
213
# Large fields NOT NULL
214
drop table if exists t1;
217
c_varchar varchar(300) not null,
218
c_varchar_bin varchar(300) binary not null,
219
c_varchar_uni varchar(300) unicode not null,
220
c_ttext TINYTEXT not null,
221
c_text TEXT not null,
222
c_mtext MEDIUMTEXT not null,
223
c_ltext LONGTEXT not null,
225
index(c_varchar_bin),
226
index(c_varchar_uni),
233
insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", "ab");
234
insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", "ab ");
235
select * from t1 where c_varchar = "ab";
236
select * from t1 where c_varchar_bin = "ab";
237
select * from t1 where c_varchar_uni = "ab";
238
select * from t1 where c_ttext = "ab";
239
select * from t1 where c_text = "ab";
240
select * from t1 where c_mtext = "ab";
241
select * from t1 where c_ltext = "ab";
243
select c_varchar from t1 where c_varchar = "ab ";
244
select c_varchar_bin from t1 where c_varchar_bin = "ab ";
245
select c_varchar_uni from t1 where c_varchar_uni = "ab ";
246
select c_ttext from t1 where c_ttext = "ab ";
247
select c_text from t1 where c_text = "ab ";
248
select c_mtext from t1 where c_mtext = "ab ";
249
select c_ltext from t1 where c_ltext = "ab ";
251
#----------------------------------------
254
drop table if exists t1;
257
c_char_suni char(4) unicode,
258
c_varchar_suni varchar(4) unicode,
259
c_char_uni char(255) unicode,
260
c_varchar_uni varchar(300) unicode,
262
index(c_varchar_suni),
267
insert t1 values (1, "ab", "ab", "ab", "ab");
268
insert t1 values (1, "abcd", "abcd", "abcd", "abcd");
269
insert t1 values (1, "ab ", "ab ", "abcd123123123123123123123123123123123123", "abcd123123123123123123123123123123123123");
270
select * from t1 where c_char_suni = "ab";
271
select * from t1 where c_varchar_suni = "ab";
272
select * from t1 where c_char_uni = "ab";
273
select * from t1 where c_varchar_uni = "ab";
276
#----------------------------------------
277
# Duplicate key tests
279
drop table if exists t1;
280
create table t1 (id int, name char(10) key) engine=pbxt;
281
insert t1 values (1, "ab");
282
select * from t1 where name = "ab";
284
insert t1 values (2, "ab ");
286
drop table if exists t1;
287
create table t1 (id int, name char(100) key) engine=pbxt;
288
insert t1 values (1, "ab");
289
select * from t1 where name = "ab";
291
insert t1 values (2, "ab ");
293
drop table if exists t1;
294
create table t1 (id int, name varchar(10) key) engine=pbxt;
295
insert t1 values (1, "ab");
296
select * from t1 where name = "ab";
298
insert t1 values (2, "ab ");
300
drop table if exists t1;
301
create table t1 (id int, name varchar(100) key) engine=pbxt;
302
insert t1 values (1, "ab");
303
select * from t1 where name = "ab";
305
insert t1 values (2, "ab ");
307
drop table if exists t1;
308
create table t1 (id int, name varchar(400) key) engine=pbxt;
309
insert t1 values (1, "ab");
310
select * from t1 where name = "ab";
312
insert t1 values (2, "ab ");
314
drop table if exists t1;
315
create table t1 (id int, name char(10) key) engine=pbxt;
316
insert t1 values (1, "ab");
317
select * from t1 where name = "ab";
319
insert t1 values (2, "ab ");
321
drop table if exists t1;
322
create table t1 (id int, name char(100) key) engine=pbxt;
323
insert t1 values (1, "ab");
324
select * from t1 where name = "ab";
326
insert t1 values (2, "ab ");
329
drop table if exists t1;
330
create table t1 (id int, name varchar(10), index(name)) engine=pbxt;
331
insert t1 values (1, "ab");
332
select * from t1 where name = "ab";
333
insert t1 values (2, "ab ");
334
insert t1 values (3, "ab ");
335
select * from t1 where name = "ab";
336
insert t1 values (4, "ab ");
338
drop table if exists t1;
339
create table t1 (id int, name varchar(100), index(name)) engine=pbxt;
340
insert t1 values (1, "ab");
341
select * from t1 where name = "ab";
342
insert t1 values (2, "ab ");
343
insert t1 values (3, "ab ");
344
select * from t1 where name = "ab";
345
insert t1 values (4, "ab ");
347
drop table if exists t1;
348
create table t1 (id int, name varchar(400), index(name)) engine=pbxt;
349
insert t1 values (1, "ab");
350
select * from t1 where name = "ab";
351
insert t1 values (2, "ab ");
352
insert t1 values (3, "ab ");
353
select * from t1 where name = "ab";
354
insert t1 values (4, "ab ");
356
drop table if exists t1;
357
create table t1 (id int, name char(10), index(name)) engine=pbxt;
358
insert t1 values (1, "ab");
359
select * from t1 where name = "ab";
360
insert t1 values (2, "ab ");
361
insert t1 values (3, "ab ");
362
select * from t1 where name = "ab";
363
insert t1 values (4, "ab ");
365
drop table if exists t1;
366
create table t1 (id int, name char(100), index(name)) engine=pbxt;
367
insert t1 values (1, "ab");
368
select * from t1 where name = "ab";
369
insert t1 values (2, "ab ");
370
insert t1 values (3, "ab ");
371
select * from t1 where name = "ab";
372
insert t1 values (4, "ab ");
375
# BUG: "-49: Record format unknown, either corrupted or upgrade required"
377
DROP TABLE IF EXISTS t1;
378
CREATE TABLE t1 ( id int, name varchar(300)) engine=pbxt;
380
insert t1(id, name) values(1, "aaa");
381
update t1 set name=REPEAT('A', 300) where id = 1;
386
DROP TABLE IF EXISTS t1;
387
CREATE TABLE t1 ( id int, name varchar(300)) engine=pbxt;
389
insert t1(id, name) values(1, REPEAT('A', 300));
390
update t1 set name="aaa" where id = 1;
396
# BUG: failing TRUNCATE TABLE puts handler into an invalid state with later crash
399
drop table if exists t1, t2;
401
create table t1 (s1 int primary key) engine = pbxt;
402
insert into t1 values (1);
403
create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine = pbxt;
404
insert into t2 values (1);
405
# this should fail because of FK constraint
408
# this caused a crash
409
alter table t1 engine myisam;
410
show create table t1;
412
# BUG: Foreign Keys: missing row in table_constraints
415
drop table if exists t1, t2;
418
create table t1 (s1 int primary key, s2 int unique not null) engine = pbxt;
419
create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine = pbxt;
421
select * from information_schema.table_constraints
422
where constraint_type = 'FOREIGN KEY' and table_name = 't2';
424
select * from information_schema.referential_constraints where table_name = 't2';
428
create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt;
429
create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt;
431
select * from information_schema.table_constraints
432
where constraint_type = 'FOREIGN KEY' and table_name = 't2';
434
select * from information_schema.referential_constraints where table_name = 't2';
438
create table t1 (s1 int, s2 int, unique key ix1 (s1, s2), unique key ix2 (s1, s2)) engine = pbxt;
439
create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt;
441
select * from information_schema.table_constraints
442
where constraint_type = 'FOREIGN KEY' and table_name = 't2';
444
select * from information_schema.referential_constraints where table_name = 't2';
448
create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt;
449
create table t2 (s1 int, s2 int,
450
foreign key (s1, s2) references t1 (s1, s2) on delete cascade on update set null) engine = pbxt;
452
select * from information_schema.table_constraints
453
where constraint_type = 'FOREIGN KEY' and table_name = 't2';
455
select * from information_schema.referential_constraints where table_name = 't2';
459
create table t2 (s1 int, s2 int, s3 int, s4 int, unique key ix34 (s3, s4),
460
foreign key (s1, s2) references t2 (s3, s4)) engine = pbxt;
462
select * from information_schema.table_constraints
463
where constraint_type = 'FOREIGN KEY' and table_name = 't2';
465
select * from information_schema.referential_constraints where table_name = 't2';
469
create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt;
470
create table t2 (s1 int, s2 int,
471
foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt;
472
alter table t1 add constraint s2 foreign key (s1, s2) references t2 (s1, s2);
474
select * from information_schema.table_constraints
475
where constraint_type = 'FOREIGN KEY' and (table_name = 't1' or table_name = 't2');
477
select * from information_schema.referential_constraints where (table_name = 't1' or table_name = 't2');
479
set foreign_key_checks = 0;
481
set foreign_key_checks = 1;
483
create table t1 (id int primary key, s1 int, foreign key (s1) references t1 (id)) engine = pbxt;
485
select * from information_schema.table_constraints
486
where constraint_type = 'FOREIGN KEY' and table_name = 't1';
488
select * from information_schema.referential_constraints where table_name = 't1';
493
# 1. Foreign keys: crash if update cascade and autocommit=0,
494
# 2. Foreign keys: crash if update cascade and multi-level recursion
499
drop table if exists t2,t1;
502
create table t1 (s1 int primary key) engine = pbxt;
503
create table t2 (s1 int primary key, foreign key (s1) references t1 (s1) on update cascade) engine = pbxt;
505
set @@autocommit = 0;
506
insert into t1 values (1);
507
insert into t2 values (1);
508
update t1 set s1 = 2;
509
set @@autocommit = 1;
511
select * from t1, t2;
513
# test-case with multiple columns/indexes
517
create table t1 (s1 int primary key, s2 int, key (s1, s2)) engine = pbxt;
518
create table t2 (s1 int primary key, s2 int, key (s1, s2), foreign key (s1) references t1 (s1) on update cascade) engine = pbxt;
520
set @@autocommit = 0;
521
insert into t1 values (1, 5);
522
insert into t2 values (1, 6);
523
update t1 set s1 = 2;
524
set @@autocommit = 1;
526
select * from t1, t2;
530
# test case for the second bug
532
set @@autocommit = 1;
534
SET foreign_key_checks = 0;
536
DROP TABLE IF EXISTS t15,t14,t13,t12,t11,t10,t9,t8,t7,t6,t5,t4,t3,t2,t1;
538
SET foreign_key_checks = 1;
540
CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT) engine = pbxt;
541
CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE) engine = pbxt;
542
CREATE TABLE t3 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t2 (s1) ON UPDATE CASCADE) engine = pbxt;
543
CREATE TABLE t4 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t3 (s1) ON UPDATE CASCADE) engine = pbxt;
544
CREATE TABLE t5 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t4 (s1) ON UPDATE CASCADE) engine = pbxt;
545
CREATE TABLE t6 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t5 (s1) ON UPDATE CASCADE) engine = pbxt;
546
CREATE TABLE t7 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t6 (s1) ON UPDATE CASCADE) engine = pbxt;
547
CREATE TABLE t8 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t7 (s1) ON UPDATE CASCADE) engine = pbxt;
548
CREATE TABLE t9 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t8 (s1) ON UPDATE CASCADE) engine = pbxt;
549
CREATE TABLE t10(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t9 (s1) ON UPDATE CASCADE) engine = pbxt;
550
CREATE TABLE t11(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t10(s1) ON UPDATE CASCADE) engine = pbxt;
551
CREATE TABLE t12(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t11(s1) ON UPDATE CASCADE) engine = pbxt;
552
CREATE TABLE t13(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t12(s1) ON UPDATE CASCADE) engine = pbxt;
553
CREATE TABLE t14(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t13(s1) ON UPDATE CASCADE) engine = pbxt;
554
CREATE TABLE t15(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t14(s1) ON UPDATE CASCADE) engine = pbxt;
556
ALTER TABLE t1 ADD FOREIGN KEY (s2) REFERENCES t15(s1) ON UPDATE CASCADE;
558
SET foreign_key_checks = 0;
560
INSERT INTO t1 VALUES (1,NULL);
561
INSERT INTO t2 VALUES (1);
562
INSERT INTO t3 VALUES (1);
563
INSERT INTO t4 VALUES (1);
564
INSERT INTO t5 VALUES (1);
565
INSERT INTO t6 VALUES (1);
566
INSERT INTO t7 VALUES (1);
567
INSERT INTO t8 VALUES (1);
568
INSERT INTO t9 VALUES (1);
569
INSERT INTO t10 VALUES (1);
570
INSERT INTO t11 VALUES (1);
571
INSERT INTO t12 VALUES (1);
572
INSERT INTO t13 VALUES (1);
573
INSERT INTO t14 VALUES (1);
574
INSERT INTO t15 VALUES (1);
576
SET foreign_key_checks = 1;
578
UPDATE t1 SET s1 = 2;
579
select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15;
581
UPDATE t1 SET s2 = 2;
582
select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15;
584
# this query works with pbxt, fails with innodb, falcon 6.0.5 ignores constraint actions
585
UPDATE t1 SET s1 = 3;
586
select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15;
588
SET foreign_key_checks = 0;
589
DROP TABLE IF EXISTS t15,t14,t13,t12,t11,t10,t9,t8,t7,t6,t5,t4,t3,t2,t1;
590
SET foreign_key_checks = 1;
592
# BUG: Foreign keys: can't reference with enum
593
# correct behavior: references between ENUMs and SETs are allowed as long as they have equal number of members
596
DROP TABLE IF EXISTS t2,t1;
599
CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt;
600
CREATE TABLE t2 (s1 ENUM('A','B'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
604
CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt;
606
CREATE TABLE t2 (s1 ENUM('A','B', 'C'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
610
CREATE TABLE t1 (s1 ENUM('a','b','c') PRIMARY KEY) engine = pbxt;
611
CREATE TABLE t2 (s1 ENUM('d','e','f'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
615
CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt;
616
CREATE TABLE t2 (s1 SET('A','B'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
620
CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt;
622
CREATE TABLE t2 (s1 SET('A','B', 'C'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
626
CREATE TABLE t1 (s1 SET('a','b','c') PRIMARY KEY) engine = pbxt;
627
CREATE TABLE t2 (s1 SET('d','e','f'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
631
CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt;
633
CREATE TABLE t2 (s1 ENUM('a','b'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
637
CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt;
639
CREATE TABLE t2 (s1 SET('a','b'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
643
# RN155: Errors during cascade update of VARCHAR values with trailing spaces
645
DROP TABLE IF EXISTS t2, t1;
646
CREATE TABLE t1 (s1 VARCHAR(50) PRIMARY KEY) engine = pbxt;
647
CREATE TABLE t2 (s1 VARCHAR(50), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
649
INSERT INTO t1 VALUES ('A');
650
INSERT INTO t2 VALUES ('A ');
652
UPDATE t1 SET s1 = 'A ';
657
INSERT INTO t1 VALUES ('A');
658
INSERT INTO t2 VALUES ('A');
660
UPDATE t1 SET s1 = 'A ';
661
UPDATE t2 SET s1 = 'A ';
662
UPDATE t1 SET s1 = 'a';
663
UPDATE t2 SET s1 = 'a';
667
# SET DEFAULT produces error
669
DROP TABLE IF EXISTS t2,t1;
670
CREATE TABLE t1 (s1 INT PRIMARY KEY);
671
CREATE TABLE t2 (s1 INT DEFAULT 2, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
672
INSERT INTO t1 VALUES (1),(2);
673
INSERT INTO t2 VALUES (1);
674
DELETE FROM t1 WHERE s1 = 1;
677
DROP TABLE IF EXISTS t2,t1;
678
CREATE TABLE t1 (s1 DATE NOT NULL UNIQUE);
679
CREATE TABLE t2 (s1 DATE DEFAULT '2000-01-01', FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE SET DEFAULT);
681
INSERT INTO t1 VALUES ('2001-01-01');
682
INSERT INTO t2 VALUES ('2001-01-01');
683
UPDATE t1 SET s1 = '2001-01-02';
686
DROP TABLE IF EXISTS t2,t1;
687
CREATE TABLE t1 (s1 INT PRIMARY KEY);
688
CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
689
INSERT INTO t1 VALUES (1),(2);
690
INSERT INTO t2 VALUES (1);
691
DELETE FROM t1 WHERE s1 = 1;
694
DROP TABLE IF EXISTS t2,t1;
695
CREATE TABLE t1 (s1 VARCHAR(45) primary key);
696
CREATE TABLE t2 (s1 VARCHAR(45) DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
697
INSERT INTO t1 VALUES (1),(2);
698
INSERT INTO t2 VALUES (1);
699
DELETE FROM t1 WHERE s1 = 1;
702
DROP TABLE IF EXISTS t2,t1;
703
CREATE TABLE t1 (s1 VARCHAR(45), INDEX (s1(10)));
704
CREATE TABLE t2 (s1 VARCHAR(45) DEFAULT "12345678901", FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
705
INSERT INTO t1 VALUES (1),(2);
706
INSERT INTO t2 VALUES (1);
707
DELETE FROM t1 WHERE s1 = 1;
710
# A crash if inserting into a table that has an FK that references a column that has no index on it
712
DROP TABLE IF EXISTS t2,t1;
713
CREATE TABLE t1 (s1 INT);
714
CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
715
INSERT INTO t1 VALUES (1);
717
INSERT INTO t2 VALUES (1); # this used to crash
719
# A crash if cascade operation on child table triggers a subsequent cascade operation and that operation fails
720
# (e.g. because of contraint violation)
723
DROP TABLE IF EXISTS t3,t2,t1;
725
CREATE TABLE t1 (s1 INT primary key);
726
CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET NULL);
727
CREATE TABLE t3 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t2 (s1) ON DELETE SET NULL);
728
INSERT INTO t1 VALUES (1),(2);
729
INSERT INTO t2 VALUES (1);
730
INSERT INTO t3 VALUES (1);
732
# we don't have on-update action for t3 which means NO ACTION which is basically the same as RESTICT, and the constraints check fails
734
DELETE FROM t1 WHERE s1 = 1;
740
# Same as previous test but action is SET DEFAULT
743
DROP TABLE IF EXISTS t3,t2,t1;
745
CREATE TABLE t1 (s1 INT primary key);
746
CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
747
CREATE TABLE t3 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t2 (s1) ON DELETE SET DEFAULT);
748
INSERT INTO t1 VALUES (1),(2);
749
INSERT INTO t2 VALUES (1);
750
INSERT INTO t3 VALUES (1);
752
# we don't have on-update action for t3 which means NO ACTION which is basically the same as RESTICT, and the constraints check fails
754
DELETE FROM t1 WHERE s1 = 1;
760
DROP TABLE IF EXISTS t3,t2,t1;
762
# RN203: Fixed foreign key bug: REPLACE fails with 'on delete cascade'
765
DROP TABLE IF EXISTS t2,t1;
767
CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 char(1));
768
CREATE TABLE t2 (s1 INT, s2 INT PRIMARY KEY,
769
FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE CASCADE);
770
INSERT INTO t1 VALUES (1,'a');
771
INSERT INTO t2 VALUES (1,1);
772
REPLACE INTO t1 VALUES (1,'b');
773
SELECT COUNT(*) FROM t2;
775
set foreign_key_checks = 0;
776
DROP TABLE IF EXISTS t1;
777
CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 char(1));
778
set foreign_key_checks = 1;
779
INSERT INTO t1 VALUES (1,'a');
780
REPLACE INTO t1 VALUES (1,'b');
782
# RN204: Foreign key references are now checked on CREATE TABLE.
785
DROP TABLE IF EXISTS t3,t2,t1;
787
CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT);
788
CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE);
789
CREATE TABLE t3 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t2 (s1) ON UPDATE CASCADE);
791
DROP TABLE IF EXISTS t3,t2,t1;
792
CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY);
794
CREATE TABLE t2 (s1 ENUM('A','B','C'), FOREIGN KEY (s1) REFERENCES t1 (s1));
796
DROP TABLE IF EXISTS t2,t1;
798
CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT);
799
CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE);
801
DROP TABLE IF EXISTS t2,t1;
803
# Relax restrictions on data types
805
create table t1 (s1 varchar(40) primary key);
806
create table t2 (s1 VARCHAR(30), foreign key (s1) references t1 (s1));
807
insert into t1 values ("1");
808
insert into t2 values ("1");
810
DROP TABLE IF EXISTS t2,t1;
812
# bug 340316: Issue with bigint unsigned auto-increment field
815
DROP TABLE IF EXISTS t5;
818
c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
819
c2 BIGINT SIGNED NULL,
820
c3 BIGINT SIGNED NOT NULL,
821
c4 TINYINT, c5 SMALLINT,
825
PRIMARY KEY(c1,c2), UNIQUE INDEX(c3));
827
INSERT INTO t5 VALUES
828
(0,-9223372036854775808,1,2,3,4,5,5),
829
(255,-2147483648,6,7,8,9,10,10),
830
(65535,-8388608,11,12,13,14,15,15),
831
(16777215,-32768,16,17,18,19,20,20),
832
(4294967295,-128,21,22,23,24,25,25),
833
(18446744073709551615,9223372036854775807,26,27,28,29,30,30);
836
INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */;
838
INSERT INTO t5(c2,c3) VALUES(33,34);
844
/* same test as above with signed bigint */
846
c1 BIGINT SIGNED NOT NULL AUTO_INCREMENT,
847
c2 BIGINT SIGNED NULL,
848
c3 BIGINT SIGNED NOT NULL,
849
c4 TINYINT, c5 SMALLINT,
853
PRIMARY KEY(c1,c2), UNIQUE INDEX(c3));
855
INSERT INTO t5 VALUES
856
(0,-9223372036854775808,1,2,3,4,5,5),
857
(255,-2147483648,6,7,8,9,10,10),
858
(65535,-8388608,11,12,13,14,15,15),
859
(16777215,-32768,16,17,18,19,20,20),
860
(4294967295,-128,21,22,23,24,25,25),
861
(9223372036854775807,9223372036854775807,26,27,28,29,30,30);
864
INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */;
866
INSERT INTO t5(c2,c3) VALUES(33,34);
870
# bug 341115: wrong key comparison algorithm led to endless resultset
872
CREATE TABLE t2(c1 INT SIGNED NOT NULL, c2 INT UNSIGNED NULL, c3 INT, KEY(c1), KEY(c2));
873
INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
874
INSERT INTO t2 VALUES(-2147483648,0,13),(2147483647,4294967295,14),(0,2147483648,15),(2147483647,2147483647,16);
878
# make sure it uses index scan
879
EXPLAIN SELECT c1 FROM t2;
883
UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2;
885
SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2;
886
UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2;
888
SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC;
889
UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2;
891
SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2;
892
UPDATE t2 SET c1=-2147483648 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2;
894
SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2;
895
UPDATE t2 SET c1=-2147483648 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2;
897
SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2;
898
UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2;
900
SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC;
901
UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
903
SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
904
UPDATE t2 SET c1=-2147483648 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2;
906
SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2;
907
UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1;
909
SELECT * FROM t2 WHERE c1=-2147483648;
913
# make sure it uses index scan
914
EXPLAIN SELECT c1 FROM t2;
918
# bug 313391: LOAD DATA..REPLACE broken
921
DROP TABLE IF EXISTS t1;
923
CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 VARCHAR(255));
924
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
925
eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/std_data/pbxt_load_unique_error1.inc' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@c1,c2) SET c1 = @c1 % 2;
927
SELECT * FROM t1 ORDER BY c1;
930
create table parent (id int primary key);
931
create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id));
932
insert into parent values (2), (3), (4);
933
insert into child values (3), (4);
936
delete ignore from parent;
938
select * from parent;
940
drop table child, parent;
942
# bug 378222: Drop sakila causes error: Cannot delete or update a parent row: a foreign key constraint fails
944
create schema test378222;
946
create table t1 (id int primary key);
947
create table t2 (id int primary key);
948
alter table t1 add constraint foreign key (id) references t2 (id);
949
alter table t2 add constraint foreign key (id) references t1 (id);
950
drop schema test378222;
952
create schema test378222a;
953
create schema test378222b;
954
create table test378222a.t1 (id int primary key);
955
create table test378222b.t2 (id int primary key);
956
alter table test378222a.t1 add constraint foreign key (id) references test378222b.t2 (id);
957
alter table test378222b.t2 add constraint foreign key (id) references test378222a.t1 (id);
958
set foreign_key_checks = 1;
960
drop schema test378222a;
962
drop schema test378222b;
963
set foreign_key_checks = 0;
964
drop schema test378222a;
965
drop schema test378222b;
966
set foreign_key_checks = 1;
969
# bug 369086: Incosistent/Incorrect Truncate behavior
970
CREATE TABLE t1(c1 TINYINT AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10;
971
SHOW CREATE TABLE t1;
972
INSERT INTO t1 VALUES(null);
973
INSERT INTO t1 VALUES(null);
974
INSERT INTO t1 VALUES(null);
977
INSERT INTO t1 VALUES(null);