partition_03ndb.result

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11 STORAGE ENGINE = 'NDB' ,
SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ),
PARTITION part2 VALUES LESS THAN (2000) ENGINE = 'NDB' 
(SUBPARTITION subpart21                         ,
SUBPARTITION subpart22                         )
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster))
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 2.8 Session default engine differs from engine used within create table
SET SESSION storage_engine='MEMORY';
SET SESSION storage_engine='NDB' ;
#------------------------------------------------------------------------
#  3. Check number of partitions and subpartitions
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 3.1 (positive) without partition/subpartition number assignment
# 3.1.1     no partition number, no named partitions, no subpartitions mentioned
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY HASH(f1);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) 
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 3.1.2     no partition number, named partitions, no subpartitions mentioned
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY HASH(f1) (PARTITION part1, PARTITION part2);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = ndbcluster, PARTITION part2  ENGINE = ndbcluster)
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 3.1.3 variations on no partition/subpartition number, named partitions,
#       different subpartitions are/are not named
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2000), PARTITION part3 VALUES LESS THAN (2147483647)) ;
DROP TABLE t1;
#  FIXME several subtestcases of 3.1.3 disabled because of server crashes
# Bug#15407 Partitions: crash if subpartition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 )) ;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000)  (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster), PARTITION part3 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster))
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 3.2 partition/subpartition numbers good and bad values and notations
DROP TABLE IF EXISTS t1;
# 3.2.1 partition/subpartition numbers INTEGER notation
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) PARTITIONS 2 
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 2
(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (1000) , PARTITION part2 VALUES LESS THAN (2147483647) )
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1;
SHOW CREATE TABLE t1;
Table	Create Table