4
# 2006-07-26 ML test refactored (MySQL 5.1)
5
# main testing code t/innodb_mysql.test -> include/mix1.inc
8
-- source include/have_innodb.inc
9
let $engine_type= InnoDB;
10
let $other_engine_type= MEMORY;
11
# InnoDB does support FOREIGN KEYFOREIGN KEYs
12
let $test_foreign_keys= 1;
13
set global innodb_support_xa=default;
14
set session innodb_support_xa=default;
15
--source include/mix1.inc
18
drop table if exists t1, t2, t3;
21
# BUG#35850: Performance regression in 5.1.23/5.1.24
23
create table t1(a int);
24
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
25
create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
26
insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
27
--echo this must use key 'a', not PRIMARY:
29
explain select a from t2 where a=b;
33
# Bug #40360: Binlog related errors with binlog off
35
# This bug is triggered when the binlog format is STATEMENT and the
36
# binary log is turned off. In this case, no error should be shown for
37
# the statement since there are no replication issues.
39
SET SESSION BINLOG_FORMAT=STATEMENT;
40
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
41
query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation;
42
CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
43
INSERT INTO t1 VALUES(1);
47
# Bug#37284 Crash in Field_string::type()
50
DROP TABLE IF EXISTS t1;
52
CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
53
CREATE INDEX i1 on t1 (a(3));
54
SELECT * FROM t1 WHERE a = 'abcde';
58
# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
62
CREATE TABLE foo (a int, b int, c char(10),
67
CREATE TABLE foo2 (a int, b int, c char(10),
72
CREATE TABLE bar (a int, b int, c char(10),
77
INSERT INTO foo VALUES
78
(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
79
(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
81
INSERT INTO bar SELECT * FROM foo;
82
INSERT INTO foo2 SELECT * FROM foo;
84
--query_vertical EXPLAIN SELECT c FROM bar WHERE b>2;
85
--query_vertical EXPLAIN SELECT c FROM foo WHERE b>2;
86
--query_vertical EXPLAIN SELECT c FROM foo2 WHERE b>2;
88
--query_vertical EXPLAIN SELECT c FROM bar WHERE c>2;
89
--query_vertical EXPLAIN SELECT c FROM foo WHERE c>2;
90
--query_vertical EXPLAIN SELECT c FROM foo2 WHERE c>2;
92
DROP TABLE foo, bar, foo2;
96
# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
100
DROP TABLE IF EXISTS t1,t3,t2;
101
DROP FUNCTION IF EXISTS f1;
105
CREATE FUNCTION f1() RETURNS VARCHAR(250)
111
CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
115
CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
116
CREATE TEMPORARY TABLE t3 LIKE t2;
118
INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
120
SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
121
PREPARE stmt1 FROM @stmt;
123
SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
124
PREPARE stmt3 FROM @stmt;
130
DEALLOCATE PREPARE stmt1;
131
DEALLOCATE PREPARE stmt3;
137
# Bug#37016: TRUNCATE TABLE removes some rows but not all
141
DROP TABLE IF EXISTS t1,t2;
144
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
145
CREATE TABLE t2 (id INT PRIMARY KEY,
146
t1_id INT, INDEX par_ind (t1_id),
147
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
148
INSERT INTO t1 VALUES (1),(2);
149
INSERT INTO t2 VALUES (3,2);
154
--error ER_ROW_IS_REFERENCED_2
161
--error ER_ROW_IS_REFERENCED_2
173
--error ER_ROW_IS_REFERENCED_2
176
DELETE FROM t2 WHERE id = 3;
189
--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
192
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
197
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
198
aid INT UNSIGNED NOT NULL,
200
FOREIGN KEY (aid) REFERENCES t1 (id)
204
bid INT UNSIGNED NOT NULL,
205
FOREIGN KEY (bid) REFERENCES t2 (id)
216
INSERT INTO t1 (id) VALUES (1);
217
INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
218
INSERT INTO t3 (bid) VALUES (1);
220
INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
221
INSERT INTO t5 VALUES (1);
223
DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
225
--error ER_ROW_IS_REFERENCED_2
226
DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
227
--error ER_ROW_IS_REFERENCED_2
228
DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
230
DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
237
--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
238
--echo # Testing for any side effects of IGNORE on AFTER DELETE triggers used with
239
--echo # transactional tables.
241
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
242
CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
243
CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
244
CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
245
FOREIGN KEY (t1i) REFERENCES t1(i))
248
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
250
SET @b:='EXECUTED TRIGGER';
251
INSERT INTO t2 VALUES (@b);
252
SET @a:= error_happens_here;
258
INSERT INTO t1 VALUES (1),(2),(3),(4);
259
INSERT INTO t3 SELECT * FROM t1;
260
--echo ** An error in a trigger causes rollback of the statement.
261
--error ER_BAD_FIELD_ERROR
262
DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
265
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
267
--echo ** Same happens with the IGNORE option
268
--error ER_BAD_FIELD_ERROR
269
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
271
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
274
--echo ** The following is an attempt to demonstrate
275
--echo ** error handling inside a row iteration.
282
INSERT INTO t1 VALUES (1),(2),(3),(4);
283
INSERT INTO t3 VALUES (1),(2),(3),(4);
284
INSERT INTO t4 VALUES (3,3),(4,4);
287
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
289
SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
290
INSERT INTO t2 VALUES (@b);
294
--echo ** DELETE is prevented by foreign key constrains but errors are silenced.
295
--echo ** The AFTER trigger isn't fired.
296
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
297
--echo ** Tables are modified by best effort:
298
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
299
--echo ** The AFTER trigger was only executed on successful rows:
305
--echo ** Induce an error midway through an AFTER-trigger
310
INSERT INTO t1 VALUES (1),(2),(3),(4);
311
INSERT INTO t3 VALUES (1),(2),(3),(4);
313
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
317
INSERT INTO t4 VALUES (5,5);
323
--echo ** Errors in the trigger causes the statement to abort.
324
--error ER_NO_REFERENCED_ROW_2
325
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
326
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
336
# Bug#43580: Issue with Innodb on multi-table update
338
CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
339
CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
341
CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
342
CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
344
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
345
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
347
INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
348
INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
350
UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10
351
WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
355
UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10
356
WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
360
DROP TABLE t1, t2, t3, t4;
363
--echo # Bug#44886: SIGSEGV in test_if_skip_sort_order() -
364
--echo # uninitialized variable used as subscript
367
CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
369
INSERT INTO t1 VALUES (1,1,1,0);
371
CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
372
INSERT INTO t2 VALUES (1,1,2);
374
CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
375
INSERT INTO t3 VALUES (1, 1);
377
SELECT * FROM t1, t2, t3
378
WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
381
DROP TABLE t1, t2, t3;
384
--echo # Bug #45828: Optimizer won't use partial primary key if another
385
--echo # index can prevent filesort
398
INSERT INTO t1 VALUES (5,2,1246276747);
399
INSERT INTO t1 VALUES (2,1,1246281721);
400
INSERT INTO t1 VALUES (7,3,1246281756);
401
INSERT INTO t1 VALUES (4,2,1246282139);
402
INSERT INTO t1 VALUES (3,1,1246282230);
403
INSERT INTO t1 VALUES (1,0,1246282712);
404
INSERT INTO t1 VALUES (8,3,1246282765);
405
INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
406
INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
407
INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
408
INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
409
INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
410
INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
412
# query and no rows will match the c1 condition, whereas all will match c3
413
SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
416
# index on c3 will be used instead of primary key
417
EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
419
# if we force the primary key, we can see the estimate is 1
420
EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
432
# if we switch it from a primary key to a regular index, it works correctly as well
433
explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
439
--echo # 36259: Optimizing with ORDER BY
443
a INT NOT NULL AUTO_INCREMENT,
448
PRIMARY KEY (a), KEY i2 (b,c,d)
451
INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
452
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
453
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
454
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
455
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
456
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
457
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
458
EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
459
EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
460
EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
465
--echo # Bug #47963: Wrong results when index is used
468
a VARCHAR(5) NOT NULL,
469
b VARCHAR(5) NOT NULL,
473
INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
474
SELECT * FROM t1 WHERE a = 'TEST' AND
475
c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
476
SELECT * FROM t1 WHERE a = 'TEST' AND
477
c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
478
SELECT * FROM t1 WHERE a = 'TEST' AND
479
c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
480
SELECT * FROM t1 WHERE a = 'TEST' AND
481
c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
482
SELECT * FROM t1 WHERE a = 'TEST' AND
483
c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
484
SELECT * FROM t1 WHERE a = 'TEST' AND
485
c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
486
SELECT * FROM t1 WHERE a = 'TEST' AND
487
c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
488
EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
489
c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
493
--echo # Bug #46175: NULL read_view and consistent read assertion
496
CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
497
CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
498
INSERT INTO t1 VALUES (),();
499
INSERT INTO t2 VALUES (),();
500
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
501
WHERE b =(SELECT a FROM t1 LIMIT 1);
503
CONNECT (con1, localhost, root,,);
507
CREATE PROCEDURE p1(num INT)
509
DECLARE i INT DEFAULT 0;
513
UNTIL i>num END REPEAT;
517
--echo # Should not crash
522
--echo # Should not crash
537
--echo # Bug #49324: more valgrind errors in test_if_skip_sort_order
539
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
540
--echo #should not cause valgrind warnings
541
SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
545
--echo # Bug#50843: Filesort used instead of clustered index led to
546
--echo # performance degradation.
548
create table t1(f1 int not null primary key, f2 int) engine=innodb;
549
create table t2(f1 int not null, key (f1)) engine=innodb;
550
insert into t1 values (1,1),(2,2),(3,3);
551
insert into t2 values (1),(2),(3);
552
explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
558
--echo # Bug #39653: find_shortest_key in sql_select.cc does not consider
559
--echo # clustered primary keys
562
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
563
KEY (b,c)) ENGINE=INNODB;
565
INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
566
(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
567
(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
570
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
575
--echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
576
--echo # corrupt definition at engine
579
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b))
582
ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
584
--query_vertical SHOW INDEXES FROM t1;
590
--echo # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when
591
--echo # JOINed during an UPDATE
594
CREATE TABLE t1 (d INT) ENGINE=InnoDB;
595
CREATE TABLE t2 (a INT, b INT,
596
c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
597
ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
599
--echo set up our data elements
600
INSERT INTO t1 (d) VALUES (1);
601
INSERT INTO t2 (a,b) VALUES (1,1);
602
SELECT SECOND(c) INTO @bug47453 FROM t2;
604
SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
605
UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
606
SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
610
UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
613
SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
618
--echo # Bug#38999 valgrind warnings for update statement in function compare_record()
621
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
622
CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
623
INSERT INTO t1 values (1),(2),(3),(4),(5);
624
INSERT INTO t2 values (1);
626
SELECT * FROM t1 WHERE a = 2;
627
UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1;
632
--echo # Bug #53334: wrong result for outer join with impossible ON condition
633
--echo # (see the same test case for MyISAM in join.test)
636
CREATE TABLE t1 (id INT PRIMARY KEY);
637
CREATE TABLE t2 (id INT);
639
INSERT INTO t1 VALUES (75);
640
INSERT INTO t1 VALUES (79);
641
INSERT INTO t1 VALUES (78);
642
INSERT INTO t1 VALUES (77);
643
REPLACE INTO t1 VALUES (76);
644
REPLACE INTO t1 VALUES (76);
645
INSERT INTO t1 VALUES (104);
646
INSERT INTO t1 VALUES (103);
647
INSERT INTO t1 VALUES (102);
648
INSERT INTO t1 VALUES (101);
649
INSERT INTO t1 VALUES (105);
650
INSERT INTO t1 VALUES (106);
651
INSERT INTO t1 VALUES (107);
653
INSERT INTO t2 VALUES (107),(75),(1000);
655
SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
656
WHERE t2.id=75 AND t1.id IS NULL;
657
EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
658
WHERE t2.id=75 AND t1.id IS NULL;
663
--echo # Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index))
666
CREATE TABLE t1 (a INT, b INT, c INT, d INT,
667
PRIMARY KEY(a,b,c), KEY(b,d))
669
INSERT INTO t1 VALUES (0, 77, 1, 3);
671
UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
676
--echo # Bug#50389 Using intersect does not return all rows
690
LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
692
SELECT * FROM t1 WHERE f1 IN
693
(3305028,3353871,3772880,3346860,4228206,3336022,
694
3470988,3305175,3329875,3817277,3856380,3796193,
695
3784744,4180925,4559596,3963734,3856391,4494153)
696
AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
698
EXPLAIN SELECT * FROM t1 WHERE f1 IN
699
(3305028,3353871,3772880,3346860,4228206,3336022,
700
3470988,3305175,3329875,3817277,3856380,3796193,
701
3784744,4180925,4559596,3963734,3856391,4494153)
702
AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
707
--echo # Bug#51431 Wrong sort order after import of dump file
714
f4 tinyint(1) NOT NULL,
720
INSERT INTO t1 VALUES
721
(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
722
(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
723
(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
724
(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
725
(26,1,9921,1), (27,1,9922,1);
729
SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
730
ORDER BY f1 DESC LIMIT 5;
731
EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
732
ORDER BY f1 DESC LIMIT 5;
738
--echo # Bug#54117 crash in thr_multi_unlock, temporary table
741
CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB;
744
ALTER TABLE t1 COMMENT 'test';
751
--echo # Bug#55826: create table .. select crashes with when KILL_BAD_DATA
754
CREATE TABLE t1(a INT) ENGINE=innodb;
755
INSERT INTO t1 VALUES (0);
756
SET SQL_MODE='STRICT_ALL_TABLES';
757
--error ER_TRUNCATED_WRONG_VALUE
759
SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
761
SET SQL_MODE=DEFAULT;
766
--echo # Bug#55580: segfault in read_view_sees_trx_id
768
CREATE TABLE t1 (a INT) ENGINE=Innodb;
769
CREATE TABLE t2 (a INT) ENGINE=Innodb;
770
INSERT INTO t1 VALUES (1),(2);
771
INSERT INTO t2 VALUES (1),(2);
773
connect (con1,localhost,root,,test);
774
connect (con2,localhost,root,,test);
778
SELECT * FROM t2 LOCK IN SHARE MODE;
782
SELECT * FROM t1 LOCK IN SHARE MODE;
785
let $conn_id= `SELECT CONNECTION_ID()`;
786
--send SELECT * FROM t1 FOR UPDATE
789
let $wait_timeout= 2;
790
let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
791
WHERE ID=$conn_id AND STATE='Sending data';
792
--source include/wait_condition.inc
793
--echo # should not crash
794
--error ER_LOCK_DEADLOCK
795
SELECT * FROM t1 GROUP BY (SELECT a FROM t2 LIMIT 1 FOR UPDATE) + t1.a;
804
--echo # Bug#55656: mysqldump can be slower after bug #39653 fix
807
CREATE TABLE t1 (a INT , b INT, c INT, d INT,
808
KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB;
809
INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
810
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
813
CREATE INDEX b ON t1(a,b);
814
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
817
CREATE INDEX b ON t1(a,b,c);
818
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
821
CREATE INDEX b ON t1(a,b,c,d);
822
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
827
--echo # Bug#56862 Execution of a query that uses index merge returns a wrong result
831
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
837
INSERT INTO t1(a,b) VALUES
838
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
839
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
840
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
841
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
842
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
843
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
844
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
845
INSERT INTO t1(a,b) SELECT a,b FROM t1;
846
INSERT INTO t1(a,b) SELECT a,b FROM t1;
847
INSERT INTO t1(a,b) SELECT a,b FROM t1;
848
INSERT INTO t1(a,b) SELECT a,b FROM t1;
849
INSERT INTO t1(a,b) SELECT a,b FROM t1;
850
INSERT INTO t1(a,b) SELECT a,b FROM t1;
851
INSERT INTO t1(a,b) SELECT a,b FROM t1;
852
INSERT INTO t1(a,b) SELECT a,b FROM t1;
853
INSERT INTO t1 VALUES (1000000, 0, 0);
855
SET SESSION sort_buffer_size = 1024*36;
859
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
860
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
863
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
864
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
866
SET SESSION sort_buffer_size = DEFAULT;
871
--echo End of 5.1 tests
875
--echo # Test for bug #39932 "create table fails if column for FK is in different
876
--echo # case than in corr index".
879
drop tables if exists t1, t2;
881
create table t1 (pk int primary key) engine=InnoDB;
882
--echo # Even although the below statement uses uppercased field names in
883
--echo # foreign key definition it still should be able to find explicitly
884
--echo # created supporting index. So it should succeed and should not
885
--echo # create any additional supporting indexes.
886
create table t2 (fk int, key x (fk),
887
constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
888
show create table t2;
892
--echo # Test for bug #56619 - Assertion failed during
893
--echo # ALTER TABLE RENAME, DISABLE KEYS
896
DROP TABLE IF EXISTS t1, t2;
898
CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb;
900
ALTER TABLE t1 RENAME TO t2, DISABLE KEYS;
901
DROP TABLE IF EXISTS t1, t2;