2499
2499
id select_type table type possible_keys key key_len ref rows Extra
2500
2500
1 SIMPLE t1 range f2,f4 f4 1 NULL 11 Using where
2503
# Bug#54117 crash in thr_multi_unlock, temporary table
2505
CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB;
2506
LOCK TABLES t1 READ;
2507
ALTER TABLE t1 COMMENT 'test';
2511
# Bug#55826: create table .. select crashes with when KILL_BAD_DATA
2514
CREATE TABLE t1(a INT) ENGINE=innodb;
2515
INSERT INTO t1 VALUES (0);
2516
SET SQL_MODE='STRICT_ALL_TABLES';
2518
SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
2519
ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 1
2521
SET SQL_MODE=DEFAULT;
2523
# Bug#55580: segfault in read_view_sees_trx_id
2525
CREATE TABLE t1 (a INT) ENGINE=Innodb;
2526
CREATE TABLE t2 (a INT) ENGINE=Innodb;
2527
INSERT INTO t1 VALUES (1),(2);
2528
INSERT INTO t2 VALUES (1),(2);
2530
SELECT * FROM t2 LOCK IN SHARE MODE;
2535
SELECT * FROM t1 LOCK IN SHARE MODE;
2539
SELECT * FROM t1 FOR UPDATE;
2541
SELECT * FROM t1 GROUP BY (SELECT a FROM t2 LIMIT 1 FOR UPDATE) + t1.a;
2542
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
2545
# Bug#55656: mysqldump can be slower after bug #39653 fix
2547
CREATE TABLE t1 (a INT , b INT, c INT, d INT,
2548
KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB;
2549
INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
2550
EXPLAIN SELECT COUNT(*) FROM t1;
2562
CREATE INDEX b ON t1(a,b);
2563
EXPLAIN SELECT COUNT(*) FROM t1;
2575
CREATE INDEX b ON t1(a,b,c);
2576
EXPLAIN SELECT COUNT(*) FROM t1;
2588
CREATE INDEX b ON t1(a,b,c,d);
2589
EXPLAIN SELECT COUNT(*) FROM t1;
2602
# Bug#56862 Execution of a query that uses index merge returns a wrong result
2605
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
2610
INSERT INTO t1(a,b) VALUES
2611
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
2612
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
2613
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
2614
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
2615
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
2616
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
2617
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
2618
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2619
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2620
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2621
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2622
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2623
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2624
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2625
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2626
INSERT INTO t1 VALUES (1000000, 0, 0);
2627
SET SESSION sort_buffer_size = 1024*36;
2629
SELECT COUNT(*) FROM
2630
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
2631
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
2632
id select_type table type possible_keys key key_len ref rows Extra
2633
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2634
2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where
2635
SELECT COUNT(*) FROM
2636
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
2637
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
2640
SET SESSION sort_buffer_size = DEFAULT;
2643
# Test for bug #39932 "create table fails if column for FK is in different
2644
# case than in corr index".
2646
drop tables if exists t1, t2;
2647
create table t1 (pk int primary key) engine=InnoDB;
2648
# Even although the below statement uses uppercased field names in
2649
# foreign key definition it still should be able to find explicitly
2650
# created supporting index. So it should succeed and should not
2651
# create any additional supporting indexes.
2652
create table t2 (fk int, key x (fk),
2653
constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
2654
show create table t2;
2656
t2 CREATE TABLE `t2` (
2657
`fk` int(11) DEFAULT NULL,
2659
CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
2660
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2663
# Test for bug #56619 - Assertion failed during
2664
# ALTER TABLE RENAME, DISABLE KEYS
2666
DROP TABLE IF EXISTS t1, t2;
2667
CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb;
2668
ALTER TABLE t1 RENAME TO t2, DISABLE KEYS;
2669
DROP TABLE IF EXISTS t1, t2;
2671
# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::
2672
# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
2674
DROP TABLE IF EXISTS t1;
2675
CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB;
2676
INSERT INTO t1 VALUES (1);
2677
LOCK TABLES t1 READ;
2678
# Even though temporary table was locked for READ we
2679
# still allow writes to it to be compatible with MyISAM.
2680
# This is possible since due to fact that temporary tables
2681
# are specific to connection and therefore locking for them
2683
UPDATE t1 SET c = 5;
2685
DROP TEMPORARY TABLE t1;
2502
2686
End of 5.1 tests