#At file:///home/horst/bzr/mysql-trunk/ based on revid:tor.didriksen@stripped
3710 Horst.Hunger 2011-02-28
Addidtional tests for wl#5217 with memory engine.
added:
mysql-test/suite/parts/r/partition-dml-1-1-memory.result
mysql-test/suite/parts/r/partition-dml-1-10-memory.result
mysql-test/suite/parts/r/partition-dml-1-11-memory.result
mysql-test/suite/parts/t/partition-dml-1-1-memory.test
mysql-test/suite/parts/t/partition-dml-1-10-memory.test
mysql-test/suite/parts/t/partition-dml-1-11-memory.test
modified:
mysql-test/suite/parts/inc/partition-dml-1-10.inc
mysql-test/suite/parts/r/partition-dml-1-10-innodb.result
mysql-test/suite/parts/r/partition-dml-1-10-myisam.result
=== modified file 'mysql-test/suite/parts/inc/partition-dml-1-10.inc'
--- a/mysql-test/suite/parts/inc/partition-dml-1-10.inc 2011-02-17 15:36:05 +0000
+++ b/mysql-test/suite/parts/inc/partition-dml-1-10.inc 2011-02-28 12:04:27 +0000
@@ -36,7 +36,7 @@ EXECUTE st1;
connection session2;
SET @var1= 'p0-29-upd-2';
-if ($engine == MyISAM)
+if ($engine != InnoDB)
{
EXECUTE st2 USING @var1;
}
@@ -54,10 +54,12 @@ SET @var1= 'p0-29-upd-2';
EXECUTE st2 USING @var1;
connection session1;
+--sorted_result
SELECT * FROM t1 PARTITION (`p0-29`);
DEALLOCATE PREPARE st1;
connection session2;
+--sorted_result
SELECT * FROM t1 PARTITION (`p0-29`);
COMMIT WORK;
DEALLOCATE PREPARE st2;
@@ -66,6 +68,7 @@ connection default;
disconnect session1;
disconnect session2;
+--sorted_result
SELECT * FROM t1 PARTITION (`p0-29`);
DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition-dml-1-1-memory.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-1-memory.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition-dml-1-1-memory.result 2011-02-28 12:04:27 +0000
@@ -0,0 +1,262 @@
+# Additional tests for WL#5217 by QA, testplan 1.1
+CREATE TABLE t1
+(a INT NOT NULL,
+b varchar (64),
+INDEX ind_t1 (b,a),
+PRIMARY KEY (a))
+ENGINE = MyISAM
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH (a) SUBPARTITIONS 3
+(PARTITION pNeg VALUES LESS THAN (0)
+(SUBPARTITION subp0 ,
+SUBPARTITION subp1 ,
+SUBPARTITION subp2 ),
+PARTITION `p0-29` VALUES LESS THAN (30)
+(SUBPARTITION subp3 ,
+SUBPARTITION subp4 ,
+SUBPARTITION subp5 ),
+PARTITION `p30-299` VALUES LESS THAN (300)
+(SUBPARTITION subp6 ,
+SUBPARTITION subp7 ,
+SUBPARTITION subp8 ),
+PARTITION `p300-2999` VALUES LESS THAN (3000)
+(SUBPARTITION subp9 ,
+SUBPARTITION subp10 ,
+SUBPARTITION subp11 ),
+PARTITION `p3000-299999` VALUES LESS THAN (300000)
+(SUBPARTITION subp12 ,
+SUBPARTITION subp13 ,
+SUBPARTITION subp14 ));
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
+GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw';
+GRANT ALL PRIVILEGES ON test.* TO test_user_2 IDENTIFIED BY 'testpw';
+connect session1, localhost, test_user_1,'testpw',test;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 (p0-29-)subp3
+2 (p0-29-)subp3
+21 (p0-29-)subp5
+22 (p0-29-)subp5
+23 (p0-29-)subp5
+24 (p0-29-)subp5
+3 (p0-29-)subp3
+4 (p0-29-)subp3
+send
+UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
+connect session2, localhost, test_user_2,'testpw',test;
+SELECT * FROM t1 PARTITION (`p30-299`);
+a b
+231 (p30-299-)subp8
+232 (p30-299-)subp8
+233 (p30-299-)subp8
+234 (p30-299-)subp8
+31 (p30-299-)subp6
+32 (p30-299-)subp6
+33 (p30-299-)subp6
+34 (p30-299-)subp6
+UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
+SELECT * FROM t1 PARTITION (`p30-299`);
+a b
+231 (p30-299-)subp8
+232 (p30-299-)subp8
+233 (p30-299-)subp8
+234 (p30-299-)subp8
+31 p30-299-upd
+32 p30-299-upd
+33 p30-299-upd
+34 p30-299-upd
+SELECT * FROM t1 PARTITION (`p300-2999`);
+a b
+301 (p300-2999-)subp8
+302 (p300-2999-)subp8
+303 (p300-2999-)subp8
+304 (p300-2999-)subp8
+UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
+SELECT * FROM t1 PARTITION (`p300-2999`);
+a b
+301 p300-2999-upd
+302 p300-2999-upd
+303 p300-2999-upd
+304 p300-2999-upd
+SELECT * FROM t1 PARTITION (`p3000-299999`);
+a b
+299996 (p3000-299999-)subp14
+299997 (p3000-299999-)subp14
+299998 (p3000-299999-)subp14
+299999 (p3000-299999-)subp14
+3001 (p3000-299999-)subp12
+3002 (p3000-299999-)subp12
+3003 (p3000-299999-)subp12
+3004 (p3000-299999-)subp12
+UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
+SELECT * FROM t1 PARTITION (`p3000-299999`);
+a b
+299996 (p3000-299999-)subp14
+299997 (p3000-299999-)subp14
+299998 (p3000-299999-)subp14
+299999 (p3000-299999-)subp14
+3001 p3000-299999-upd
+3002 p3000-299999-upd
+3003 p3000-299999-upd
+3004 p3000-299999-upd
+UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 p0-29-upd-2
+2 p0-29-upd-2
+21 (p0-29-)subp5
+22 (p0-29-)subp5
+23 (p0-29-)subp5
+24 (p0-29-)subp5
+3 p0-29-upd-2
+4 p0-29-upd-2
+UPDATE t1 PARTITION (subp3) SET b='subp3-upd';
+UPDATE t1 PARTITION (subp4) SET b='subp4-upd';
+UPDATE t1 PARTITION (subp5) SET b='subp5-upd';
+SELECT * FROM t1 PARTITION (subp3);
+a b
+21 subp3-upd
+24 subp3-upd
+3 subp3-upd
+SELECT * FROM t1 PARTITION (subp4);
+a b
+1 subp4-upd
+22 subp4-upd
+4 subp4-upd
+SELECT * FROM t1 PARTITION (subp5);
+a b
+2 subp5-upd
+23 subp5-upd
+SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a;
+a b
+3 subp3-upd
+21 subp3-upd
+24 subp3-upd
+1 subp4-upd
+4 subp4-upd
+22 subp4-upd
+2 subp5-upd
+23 subp5-upd
+connection session1;
+reap
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 subp4-upd
+2 subp5-upd
+21 subp3-upd
+22 subp4-upd
+23 subp5-upd
+24 subp3-upd
+3 subp3-upd
+4 subp4-upd
+connection session2;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 subp4-upd
+2 subp5-upd
+21 subp3-upd
+22 subp4-upd
+23 subp5-upd
+24 subp3-upd
+3 subp3-upd
+4 subp4-upd
+SELECT
+(SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) =
+(SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ;
+MUST BE TRUE (1)
+1
+CREATE TABLE t2
+(a INT NOT NULL,
+b varchar (64),
+INDEX ind_t2 (b,a),
+PRIMARY KEY (a))
+ENGINE = MyISAM
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH (a) SUBPARTITIONS 3
+(PARTITION pNeg VALUES LESS THAN (0)
+(SUBPARTITION subp0 ,
+SUBPARTITION subp1 ,
+SUBPARTITION subp2 ),
+PARTITION `p0-29` VALUES LESS THAN (30)
+(SUBPARTITION subp3 ,
+SUBPARTITION subp4 ,
+SUBPARTITION subp5 ),
+PARTITION `p30-299` VALUES LESS THAN (300)
+(SUBPARTITION subp6 ,
+SUBPARTITION subp7 ,
+SUBPARTITION subp8 ),
+PARTITION `p300-2999` VALUES LESS THAN (3000)
+(SUBPARTITION subp9 ,
+SUBPARTITION subp10 ,
+SUBPARTITION subp11 ),
+PARTITION `p3000-299999` VALUES LESS THAN (300000)
+(SUBPARTITION subp12 ,
+SUBPARTITION subp13 ,
+SUBPARTITION subp14 ));
+SELECT * FROM t2 PARTITION (`p30-299`);
+a b
+UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
+DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39;
+SELECT * FROM t2 PARTITION (`p300-2999`);
+a b
+UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
+DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309;
+SELECT * FROM t2 PARTITION (`p3000-299999`);
+a b
+UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
+DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009;
+SELECT * FROM t2 PARTITION (`p0-29`);
+a b
+UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
+DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9;
+UPDATE t2 PARTITION (subp3) SET b='subp3-upd';
+DELETE FROM t2 PARTITION (subp3);
+UPDATE t2 PARTITION (subp4) SET b='subp4-upd';
+DELETE FROM t2 PARTITION (subp4);
+UPDATE t2 PARTITION (subp5) SET b='subp5-upd';
+DELETE FROM t2 PARTITION (subp5);
+UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd';
+DELETE FROM t2 PARTITION (subp4,subp5,subp3);
+SELECT * FROM t2 PARTITION (subp3);
+a b
+SELECT * FROM t2 PARTITION (subp4);
+a b
+SELECT * FROM t2 PARTITION (subp5);
+a b
+disconnect session1;
+disconnect session2;
+connection default;
+DROP TABLE t1, t2;
+DROP USER test_user_1;
+DROP USER test_user_2;
=== modified file 'mysql-test/suite/parts/r/partition-dml-1-10-innodb.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-10-innodb.result 2011-02-17 15:36:05 +0000
+++ b/mysql-test/suite/parts/r/partition-dml-1-10-innodb.result 2011-02-28 12:04:27 +0000
@@ -93,12 +93,12 @@ EXECUTE st2 USING @var1;
connection session1;
SELECT * FROM t1 PARTITION (`p0-29`);
a b
+1 p0-29-upd-1
+2 p0-29-upd-1
21 (p0-29-)subp5
22 (p0-29-)subp5
23 (p0-29-)subp5
24 (p0-29-)subp5
-1 p0-29-upd-1
-2 p0-29-upd-1
3 p0-29-upd-1
4 p0-29-upd-1
DEALLOCATE PREPARE st1;
@@ -107,12 +107,12 @@ SELECT * FROM t1 PARTITION (`p0-29`);
a b
1 p0-29-upd-1
2 p0-29-upd-1
-3 p0-29-upd-1
-4 p0-29-upd-1
21 p0-29-upd-2
22 p0-29-upd-2
23 p0-29-upd-2
24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
COMMIT WORK;
DEALLOCATE PREPARE st2;
connection default;
@@ -122,12 +122,12 @@ SELECT * FROM t1 PARTITION (`p0-29`);
a b
1 p0-29-upd-1
2 p0-29-upd-1
-3 p0-29-upd-1
-4 p0-29-upd-1
21 p0-29-upd-2
22 p0-29-upd-2
23 p0-29-upd-2
24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
DROP TABLE t1;
DROP USER test_user_1;
DROP USER test_user_2;
=== added file 'mysql-test/suite/parts/r/partition-dml-1-10-memory.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-10-memory.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition-dml-1-10-memory.result 2011-02-28 12:04:27 +0000
@@ -0,0 +1,138 @@
+SET @auto= @@global.autocommit;
+SET @@global.autocommit= off;
+SET @iso= @@global.tx_isolation;
+SET @@global.tx_isolation= 'repeatable-read';
+SET @completion= @@global.completion_type;
+SET @@global.completion_type= 'no_chain';
+SET @lock_timeout= @@global.lock_wait_timeout;
+SET @@global.lock_wait_timeout= 1;
+SELECT @@global.lock_wait_timeout;
+@@global.lock_wait_timeout
+1
+SET @innodb_lock_timeout= @@global.innodb_lock_wait_timeout;
+SET @@global.innodb_lock_wait_timeout= 1;
+# Additional tests for WL#5217 by QA, testplan 1.1
+CREATE TABLE t1
+(a INT NOT NULL,
+b varchar (64),
+INDEX ind_t1 (b,a),
+PRIMARY KEY (a))
+ENGINE = Memory
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH (a) SUBPARTITIONS 3
+(PARTITION pNeg VALUES LESS THAN (0)
+(SUBPARTITION subp0 ,
+SUBPARTITION subp1 ,
+SUBPARTITION subp2 ),
+PARTITION `p0-29` VALUES LESS THAN (30)
+(SUBPARTITION subp3 ,
+SUBPARTITION subp4 ,
+SUBPARTITION subp5 ),
+PARTITION `p30-299` VALUES LESS THAN (300)
+(SUBPARTITION subp6 ,
+SUBPARTITION subp7 ,
+SUBPARTITION subp8 ),
+PARTITION `p300-2999` VALUES LESS THAN (3000)
+(SUBPARTITION subp9 ,
+SUBPARTITION subp10 ,
+SUBPARTITION subp11 ),
+PARTITION `p3000-299999` VALUES LESS THAN (300000)
+(SUBPARTITION subp12 ,
+SUBPARTITION subp13 ,
+SUBPARTITION subp14 ));
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
+GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw';
+GRANT ALL PRIVILEGES ON test.* TO test_user_2 IDENTIFIED BY 'testpw';
+GRANT ALL PRIVILEGES ON test.* TO test_user_3 IDENTIFIED BY 'testpw';
+connect session1, localhost, test_user_1,'testpw',test;
+PREPARE st1 FROM 'UPDATE t1 PARTITION (`p0-29`) SET b=''p0-29-upd-1'' WHERE a BETWEEN 0 AND 9' ;
+connect session2, localhost, test_user_2,'testpw',test;
+PREPARE st2 FROM 'UPDATE t1 PARTITION (`p0-29`) SET b=? WHERE a BETWEEN 20 AND 29' ;
+connection session1;
+EXECUTE st1;
+connection session2;
+SET @var1= 'p0-29-upd-2';
+EXECUTE st2 USING @var1;
+connection session1;
+COMMIT WORK;
+connection session2;
+SET @var1= 'p0-29-upd-2';
+EXECUTE st2 USING @var1;
+connection session1;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 p0-29-upd-1
+2 p0-29-upd-1
+21 p0-29-upd-2
+22 p0-29-upd-2
+23 p0-29-upd-2
+24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
+DEALLOCATE PREPARE st1;
+connection session2;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 p0-29-upd-1
+2 p0-29-upd-1
+21 p0-29-upd-2
+22 p0-29-upd-2
+23 p0-29-upd-2
+24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
+COMMIT WORK;
+DEALLOCATE PREPARE st2;
+connection default;
+disconnect session1;
+disconnect session2;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 p0-29-upd-1
+2 p0-29-upd-1
+21 p0-29-upd-2
+22 p0-29-upd-2
+23 p0-29-upd-2
+24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
+DROP TABLE t1;
+DROP USER test_user_1;
+DROP USER test_user_2;
+DROP USER test_user_3;
+SET @@global.autocommit= @auto;
+SET @@global.tx_isolation= @iso;
+SET @@global.completion_type= @completion;
+SET @@global.lock_wait_timeout= @lock_timeout;
+SET @@global.innodb_lock_wait_timeout= @innodb_lock_timeout;
=== modified file 'mysql-test/suite/parts/r/partition-dml-1-10-myisam.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-10-myisam.result 2011-02-17 15:36:05 +0000
+++ b/mysql-test/suite/parts/r/partition-dml-1-10-myisam.result 2011-02-28 12:04:27 +0000
@@ -94,24 +94,24 @@ SELECT * FROM t1 PARTITION (`p0-29`);
a b
1 p0-29-upd-1
2 p0-29-upd-1
-3 p0-29-upd-1
-4 p0-29-upd-1
21 p0-29-upd-2
22 p0-29-upd-2
23 p0-29-upd-2
24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
DEALLOCATE PREPARE st1;
connection session2;
SELECT * FROM t1 PARTITION (`p0-29`);
a b
1 p0-29-upd-1
2 p0-29-upd-1
-3 p0-29-upd-1
-4 p0-29-upd-1
21 p0-29-upd-2
22 p0-29-upd-2
23 p0-29-upd-2
24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
COMMIT WORK;
DEALLOCATE PREPARE st2;
connection default;
@@ -121,12 +121,12 @@ SELECT * FROM t1 PARTITION (`p0-29`);
a b
1 p0-29-upd-1
2 p0-29-upd-1
-3 p0-29-upd-1
-4 p0-29-upd-1
21 p0-29-upd-2
22 p0-29-upd-2
23 p0-29-upd-2
24 p0-29-upd-2
+3 p0-29-upd-1
+4 p0-29-upd-1
DROP TABLE t1;
DROP USER test_user_1;
DROP USER test_user_2;
=== added file 'mysql-test/suite/parts/r/partition-dml-1-11-memory.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-11-memory.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition-dml-1-11-memory.result 2011-02-28 12:04:27 +0000
@@ -0,0 +1,206 @@
+# Additional tests for WL#5217 by QA, testplan 1.1
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
+INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
+GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw';
+GRANT ALL PRIVILEGES ON test.* TO test_user_2 IDENTIFIED BY 'testpw';
+connect session1, localhost, test_user_1,'testpw',test;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 (p0-29-)subp3
+2 (p0-29-)subp3
+21 (p0-29-)subp5
+22 (p0-29-)subp5
+23 (p0-29-)subp5
+24 (p0-29-)subp5
+3 (p0-29-)subp3
+4 (p0-29-)subp3
+send
+UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
+connect session2, localhost, test_user_2,'testpw',test;
+SELECT * FROM t1 PARTITION (`p30-299`);
+a b
+231 (p30-299-)subp8
+232 (p30-299-)subp8
+233 (p30-299-)subp8
+234 (p30-299-)subp8
+31 (p30-299-)subp6
+32 (p30-299-)subp6
+33 (p30-299-)subp6
+34 (p30-299-)subp6
+UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
+SELECT * FROM t1 PARTITION (`p30-299`);
+a b
+231 (p30-299-)subp8
+232 (p30-299-)subp8
+233 (p30-299-)subp8
+234 (p30-299-)subp8
+31 p30-299-upd
+32 p30-299-upd
+33 p30-299-upd
+34 p30-299-upd
+SELECT * FROM t1 PARTITION (`p300-2999`);
+a b
+301 (p300-2999-)subp8
+302 (p300-2999-)subp8
+303 (p300-2999-)subp8
+304 (p300-2999-)subp8
+UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
+SELECT * FROM t1 PARTITION (`p300-2999`);
+a b
+301 p300-2999-upd
+302 p300-2999-upd
+303 p300-2999-upd
+304 p300-2999-upd
+SELECT * FROM t1 PARTITION (`p3000-299999`);
+a b
+299996 (p3000-299999-)subp14
+299997 (p3000-299999-)subp14
+299998 (p3000-299999-)subp14
+299999 (p3000-299999-)subp14
+3001 (p3000-299999-)subp12
+3002 (p3000-299999-)subp12
+3003 (p3000-299999-)subp12
+3004 (p3000-299999-)subp12
+UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
+SELECT * FROM t1 PARTITION (`p3000-299999`);
+a b
+299996 (p3000-299999-)subp14
+299997 (p3000-299999-)subp14
+299998 (p3000-299999-)subp14
+299999 (p3000-299999-)subp14
+3001 p3000-299999-upd
+3002 p3000-299999-upd
+3003 p3000-299999-upd
+3004 p3000-299999-upd
+UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 p0-29-upd-2
+2 p0-29-upd-2
+21 (p0-29-)subp5
+22 (p0-29-)subp5
+23 (p0-29-)subp5
+24 (p0-29-)subp5
+3 p0-29-upd-2
+4 p0-29-upd-2
+UPDATE t1 PARTITION (subp3) SET b='subp3-upd';
+UPDATE t1 PARTITION (subp4) SET b='subp4-upd';
+UPDATE t1 PARTITION (subp5) SET b='subp5-upd';
+SELECT * FROM t1 PARTITION (subp3);
+a b
+21 subp3-upd
+24 subp3-upd
+3 subp3-upd
+SELECT * FROM t1 PARTITION (subp4);
+a b
+1 subp4-upd
+22 subp4-upd
+4 subp4-upd
+SELECT * FROM t1 PARTITION (subp5);
+a b
+2 subp5-upd
+23 subp5-upd
+SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a;
+a b
+3 subp3-upd
+21 subp3-upd
+24 subp3-upd
+1 subp4-upd
+4 subp4-upd
+22 subp4-upd
+2 subp5-upd
+23 subp5-upd
+connection session1;
+reap
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 subp4-upd
+2 subp5-upd
+21 subp3-upd
+22 subp4-upd
+23 subp5-upd
+24 subp3-upd
+3 subp3-upd
+4 subp4-upd
+connection session2;
+SELECT * FROM t1 PARTITION (`p0-29`);
+a b
+1 subp4-upd
+2 subp5-upd
+21 subp3-upd
+22 subp4-upd
+23 subp5-upd
+24 subp3-upd
+3 subp3-upd
+4 subp4-upd
+SELECT
+(SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) =
+(SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ;
+MUST BE TRUE (1)
+1
+SELECT * FROM t2 PARTITION (`p30-299`);
+a b
+UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
+DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39;
+SELECT * FROM t2 PARTITION (`p300-2999`);
+a b
+UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
+DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309;
+SELECT * FROM t2 PARTITION (`p3000-299999`);
+a b
+UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
+DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009;
+SELECT * FROM t2 PARTITION (`p0-29`);
+a b
+UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
+DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9;
+UPDATE t2 PARTITION (subp3) SET b='subp3-upd';
+DELETE FROM t2 PARTITION (subp3);
+UPDATE t2 PARTITION (subp4) SET b='subp4-upd';
+DELETE FROM t2 PARTITION (subp4);
+UPDATE t2 PARTITION (subp5) SET b='subp5-upd';
+DELETE FROM t2 PARTITION (subp5);
+UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd';
+DELETE FROM t2 PARTITION (subp4,subp5,subp3);
+SELECT * FROM t2 PARTITION (subp3);
+a b
+SELECT * FROM t2 PARTITION (subp4);
+a b
+SELECT * FROM t2 PARTITION (subp5);
+a b
+disconnect session1;
+disconnect session2;
+connection default;
+DROP TABLE t1, t2;
+DROP USER test_user_1;
+DROP USER test_user_2;
=== added file 'mysql-test/suite/parts/t/partition-dml-1-1-memory.test'
--- a/mysql-test/suite/parts/t/partition-dml-1-1-memory.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition-dml-1-1-memory.test 2011-02-28 12:04:27 +0000
@@ -0,0 +1,7 @@
+# Test of explicit partition selection in dml statements (wl5217)
+# Creation date: 01-07-10
+# Author: Horst Hunger
+
+let $ENGINE=MyISAM;
+--source suite/parts/inc/partition-dml-1-1.inc
+
=== added file 'mysql-test/suite/parts/t/partition-dml-1-10-memory.test'
--- a/mysql-test/suite/parts/t/partition-dml-1-10-memory.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition-dml-1-10-memory.test 2011-02-28 12:04:27 +0000
@@ -0,0 +1,2 @@
+let $engine= Memory;
+--source suite/parts/inc/partition-dml-1-10.inc
=== added file 'mysql-test/suite/parts/t/partition-dml-1-11-memory.test'
--- a/mysql-test/suite/parts/t/partition-dml-1-11-memory.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition-dml-1-11-memory.test 2011-02-28 12:04:27 +0000
@@ -0,0 +1,7 @@
+# Test of explicit partition selection in dml statements (wl5217)
+# Creation date: 01-07-10
+# Author: Horst Hunger
+
+let $ENGINE=Memory;
+--source suite/parts/inc/partition-dml-1-11.inc
+
Attachment: [text/bzr-bundle] bzr/horst.hunger@sun.com-20110228120427-9r504mtv9lrpq3s6.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (horst.hunger:3710) WL#5217 | Horst.Hunger | 28 Feb |
