2391
2391
UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1;
2392
2392
DROP TABLE t1,t2;
2394
# Bug#54606 innodb fast alter table + pack_keys=0
2395
# prevents adding new indexes
2397
CREATE TABLE t1 (a INT, b CHAR(9), c INT, key(b))
2400
CREATE INDEX a ON t1 (a);
2401
CREATE INDEX c on t1 (c);
2404
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
2409
INSERT INTO t1(a,b) VALUES
2410
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
2411
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
2412
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
2413
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
2414
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
2415
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
2416
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
2417
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2418
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2419
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2420
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2421
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2422
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2423
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2424
INSERT INTO t1(a,b) SELECT a,b FROM t1;
2425
INSERT INTO t1 VALUES (1000000, 0, 0);
2426
SET SESSION sort_buffer_size = 1024*36;
2428
SELECT COUNT(*) FROM
2429
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
2430
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
2431
id select_type table type possible_keys key key_len ref rows Extra
2432
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2433
2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where
2434
SELECT COUNT(*) FROM
2435
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
2436
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
2439
SET SESSION sort_buffer_size = DEFAULT;
2442
# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::
2443
# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
2445
DROP TABLE IF EXISTS t1;
2446
CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB;
2447
INSERT INTO t1 VALUES (1);
2448
LOCK TABLES t1 READ;
2449
# Even though temporary table was locked for READ we
2450
# still allow writes to it to be compatible with MyISAM.
2451
# This is possible since due to fact that temporary tables
2452
# are specific to connection and therefore locking for them
2454
UPDATE t1 SET c = 5;
2456
DROP TEMPORARY TABLE t1;
2393
2457
End of 5.1 tests