List:Commits« Previous MessageNext Message »
From:Ingo Struewing Date:July 20 2007 6:27pm
Subject:bk commit into 6.0-falcon tree (istruewing:1.2636) BUG#29493
View as plain text  
Below is the list of changes that have just been committed into a local
6.0-falcon repository of istruewing. When istruewing does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-07-20 20:27:16+02:00, istruewing@stripped +5 -0
  Bug#29493 - Falcon: searches fail after partition alteration
  
  Incomplete result sets could be returned from a partitioned
  table on a storage engine that does not return keys in order
  in an index scan.
  
  The partition engine assumed that the first call of an index
  scan (handler::index_read()) returns the lowest key from the
  index. This is not guaranteed for engines that set HA_REC_NOT_IN_SEQ
  in their table flags.
  
  Solved in ha_partition::handle_unordered_scan_next_partition():
  When an engine with HA_REC_NOT_IN_SEQ returns a key above range
  from index_read(), continue with index_next() until a key in range
  is returned or no more keys are available.

  mysql-test/include/partition_1.inc@stripped, 2007-07-20 20:27:11+02:00, istruewing@stripped +11 -0
    Bug#29493 - Falcon: searches fail after partition alteration
    Added test.

  mysql-test/r/partition_02myisam.result@stripped, 2007-07-20 20:27:11+02:00, istruewing@stripped +8 -0
    Bug#29493 - Falcon: searches fail after partition alteration
    Added test result.

  mysql-test/r/partition_04falcon.result@stripped, 2007-07-20 20:27:12+02:00, istruewing@stripped +1733 -0
    Bug#29493 - Falcon: searches fail after partition alteration
    Test results for new test case for Falcon.

  mysql-test/r/partition_04falcon.result@stripped, 2007-07-20 20:27:12+02:00, istruewing@stripped +0 -0

  mysql-test/t/partition_04falcon.test@stripped, 2007-07-20 20:27:12+02:00, istruewing@stripped +27 -0
    Bug#29493 - Falcon: searches fail after partition alteration
    New test case for Falcon.

  mysql-test/t/partition_04falcon.test@stripped, 2007-07-20 20:27:12+02:00, istruewing@stripped +0 -0

  sql/ha_partition.cc@stripped, 2007-07-20 20:27:11+02:00, istruewing@stripped +8 -0
    Bug#29493 - Falcon: searches fail after partition alteration
    In ha_partition::handle_unordered_scan_next_partition(),
    when an engine with HA_REC_NOT_IN_SEQ returns a key above range
    from index_read(), continue with index_next() until a key in range
    is returned or no more keys are available.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	istruewing
# Host:	chilla.local
# Root:	/home/mydev/mysql-5.1-bug29493-falcon
--- New file ---
+++ mysql-test/r/partition_04falcon.result	07/07/20 20:27:12
SET SESSION storage_engine='Falcon';
SET @max_row = 200;
SET AUTOCOMMIT= 1;

#------------------------------------------------------------------------
# There are several testcases disabled because ouf the open bugs 
# #15407 , #15408 , #15890 , #15961 , #13447 , #15966 , #15968, #16370
#------------------------------------------------------------------------

#------------------------------------------------------------------------
#  0. Setting of auxiliary variables + Creation of an auxiliary table
#     needed in all testcases
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t0_template;
CREATE TABLE t0_template ( f1 INTEGER, f2 char(20), PRIMARY KEY(f1))
ENGINE = MEMORY;
#     Logging of <max_row> INSERTs into t0_template suppressed

#------------------------------------------------------------------------
#  1. Some syntax checks
#------------------------------------------------------------------------
#  1.1 Subpartioned table without subpartitioning rule must be rejected
DROP TABLE IF EXISTS t1;
#  FIXME Implement testcases, where it is checked that all create and
#  alter table statements
#  - with missing mandatory parameters are rejected
#  - with optional parameters are accepted
#  - with wrong combinations of optional parameters are rejected
#  - ............

#------------------------------------------------------------------------
#  2. Checks where the engine is assigned on all supported (CREATE TABLE
#     statement) positions + basic operations on the tables
#        Storage engine mixups are currently (2005-12-23) not supported
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 2.1 non partitioned table (for comparison)
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'Falcon';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
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.2 Assignment of storage engine just after column list only
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'Falcon'
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=Falcon DEFAULT CHARSET=latin1 /*!50100 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;
# 2.3 Assignment of storage engine just after partition or subpartition
#     name only
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1)
( PARTITION part1 STORAGE ENGINE = 'Falcon',
PARTITION part2 STORAGE ENGINE = 'Falcon'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
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)
( PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart12 STORAGE ENGINE = 'Falcon'),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart22 STORAGE ENGINE = 'Falcon')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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.4 Some but not all named partitions or subpartitions get a storage
#     engine assigned
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1)
( PARTITION part1 STORAGE ENGINE = 'Falcon',
PARTITION part2
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
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)
( PARTITION part1                         ,
PARTITION part2 STORAGE ENGINE = 'Falcon'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
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)
( PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11,
SUBPARTITION subpart12 STORAGE ENGINE = 'Falcon'),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart22 STORAGE ENGINE = 'Falcon')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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)
( PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart12 STORAGE ENGINE = 'Falcon'),
PARTITION part2 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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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.5 Storage engine assignment after partition name + after name of
#     subpartitions belonging to another partition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
( PARTITION part1 VALUES LESS THAN (100) ENGINE = 'Falcon'
(SUBPARTITION subpart11,
SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart22 STORAGE ENGINE = 'Falcon')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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)
( PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart12 STORAGE ENGINE = 'Falcon'),
PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = 'Falcon'
(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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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.6 Precedence of storage engine assignments
# 2.6.1 Storage engine assignment after column list + after partition
#       or subpartition name
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'Falcon'
PARTITION BY HASH(f1)
( PARTITION part1 STORAGE ENGINE = 'Falcon',
PARTITION part2 STORAGE ENGINE = 'Falcon'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
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)) ENGINE = 'Falcon'
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
( PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart12 STORAGE ENGINE = 'Falcon'),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart22 STORAGE ENGINE = 'Falcon')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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.6.2 Storage engine assignment after partition name + after 
#       subpartition name
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
( PARTITION part1 VALUES LESS THAN (100) STORAGE ENGINE = 'Falcon'
(SUBPARTITION subpart11 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart12 STORAGE ENGINE = 'Falcon'),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 STORAGE ENGINE = 'Falcon',
SUBPARTITION subpart22 STORAGE ENGINE = 'Falcon')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
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.7 Session default engine differs from engine used within create table
SET SESSION storage_engine='MyISAM';
SET SESSION storage_engine='Falcon';

#------------------------------------------------------------------------
#  3. Check assigning the number of partitions and subpartitions
#     with and without named partitions/subpartitions
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 3.1 (positive) without partition/subpartition number assignment
# 3.1.1 no partition number, no named partitions
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=Falcon DEFAULT CHARSET=latin1 /*!50100 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
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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
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 (100), PARTITION part2 VALUES LESS THAN (200), PARTITION part3 VALUES LESS THAN (2147483647)) ;
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (200)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (200) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon), PARTITION part3 VALUES LESS THAN (2147483647) (SUBPARTITION subpart31 ENGINE = Falcon, SUBPARTITION subpart32 ENGINE = Falcon)) */
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=Falcon DEFAULT CHARSET=latin1 /*!50100 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 (100), 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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (100) ENGINE = Falcon, PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = Falcon) */
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
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) PARTITIONS 1  */
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 1
(PARTITION part1 VALUES LESS THAN (100), 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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (100) ENGINE = Falcon, PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = Falcon) */
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 0;
ERROR HY000: Number of partitions = 0 is not an allowed value
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 0
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR HY000: Number of subpartitions = 0 is not an allowed value
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS -1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS -1
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1000000;
ERROR HY000: Too many partitions (including subpartitions) were defined
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 1000000
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR HY000: Too many partitions (including subpartitions) were defined
# 3.2.4 partition/subpartition numbers STRING notation
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '2';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '2.0';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '2.0'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN ' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '0.2E+1';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '0.2E+1'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS TH' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '2A';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '2A'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 'A2';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 'A2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS ''
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 'GARBAGE';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 'GARBAGE'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
# 3.2.5 partition/subpartition numbers other notations
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2A;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 2A
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS A2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS A2
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS GARBAGE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS GARBAGE
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THA' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "2A";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "2A"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "A2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "A2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "GARBAGE";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "GARBAGE"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
# 3.3 Mixups of assigned partition/subpartition numbers and names
# 3.3.1 (positive) number of partition/subpartition
#                         = number of named partition/subpartition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2 ( 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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) PARTITIONS 2
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = Falcon, SUBPARTITION subpart12 ENGINE = Falcon), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = Falcon, SUBPARTITION subpart22 ENGINE = Falcon)) */
DROP TABLE t1;
# 3.3.2 (positive) number of partition/subpartition ,
#                  0 (= no) named partition/subpartition
#                  already checked above
# 3.3.3 (negative) number of partitions/subpartitions
#                         > number of named partitions/subpartitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ;
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11 ),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPAR' at line 5
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21 ),
PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart31, SUBPARTITION subpart32)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart31, SUBPAR' at line 7
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) PARTITIONS 2
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21                        )
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
)' at line 7
# 3.3.4 (negative) number of partitions < number of named partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPAR' at line 5
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21                        ),
PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart31, SUBPARTITION subpart32)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21              ' at line 5
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPAR' at line 5

#------------------------------------------------------------------------
#  4. Checks of logical partition/subpartition name
#     file name clashes during CREATE TABLE
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
#  4.1 (negative) A partition name used more than once
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1);
ERROR HY000: Duplicate partition name part1
# FIXME Implement testcases with filename problems
#       existing file of other table --- partition/subpartition file name
#       partition/subpartition file name  --- file of the same table

#------------------------------------------------------------------------
#  5. Alter table experiments
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 5.1 alter table add partition
# 5.1.1 (negative) add partition to non partitioned table
CREATE TABLE t1 ( f1 INTEGER, f2 char(20));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
Got one of the listed errors
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
DROP TABLE t1;
# 5.1.2 Add one partition to a table with one partition
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=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1)  */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION p0 ENGINE = Falcon, PARTITION part1 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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;
# 5.1.3 Several times add one partition to a table with some partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part3 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part0);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part3 ENGINE = Falcon, PARTITION part0 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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
DELETE FROM t1;
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part3 ENGINE = Falcon, PARTITION part0 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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
DELETE FROM t1;
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part3 ENGINE = Falcon, PARTITION part0 ENGINE = Falcon, PARTITION part2 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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;
# 5.1.4 Add several partitions to a table with some partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part3 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` char(20) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = Falcon, PARTITION part3 ENGINE = Falcon) */
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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;
# 5.1.5 (negative) Add partitions to a table with some partitions
#       clash on new and already existing partition names
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
ERROR HY000: Duplicate partition name part1
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
ERROR HY000: Duplicate partition name part2
ALTER TABLE t1 ADD PARTITION (PARTITION part3);
ERROR HY000: Duplicate partition name part3
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part2, PARTITION part3);
ERROR HY000: Duplicate partition name part1
DROP TABLE t1;
# 5.2 alter table add subpartition
# 5.2.1 Add one subpartition to a table with subpartitioning rule and
#       no explicit defined subpartitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
(PARTITION part1 VALUES LESS THAN (100));
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21));
DROP TABLE t1;
DROP TABLE if exists t0_template;
CREATE TABLE t1 (c1 INT UNIQUE)
PARTITION BY KEY(c1)
PARTITIONS 1;
INSERT INTO t1 VALUES (2),(1),(0);
SELECT COUNT(*) FROM t1 WHERE c1 BETWEEN 0 AND 1;
COUNT(*)
2
DROP TABLE t1;

--- New file ---
+++ mysql-test/t/partition_04falcon.test	07/07/20 20:27:12
###############################################
#                                             #
#  Partition tests MyISAM tables              #
#                                             #
###############################################

#    
# NOTE: PLEASE DO NOT ADD NOT ENGINE SPECIFIC TESTCASES HERE !
#       NON STORAGE SPECIFIC TESTCASES SHOULD BE ADDED IN
#       THE SOURCED FIELS ONLY.
#

--source include/have_falcon.inc

# Storage engine to be tested
let $engine= 'Falcon';
eval SET SESSION storage_engine=$engine;


# Other storage engine <> storage engine to be tested
let $engine_other= 'MyISAM';
# number of rows for the INSERT/UPDATE/DELETE/SELECT experiments
#                on partioned tables
# Attention: In the moment the result files fit to @max_row = 200 only
SET @max_row = 200;

-- source include/partition_1.inc


--- 1.4/mysql-test/include/partition_1.inc	2007-07-20 20:27:25 +02:00
+++ 1.5/mysql-test/include/partition_1.inc	2007-07-20 20:27:25 +02:00
@@ -748,3 +748,14 @@ if ($fixed_bug16370)
 }
 DROP TABLE t1;
 DROP TABLE if exists t0_template;
+
+#
+# Bug#29493 - Falcon: searches fail after partition alteration
+#
+CREATE TABLE t1 (c1 INT UNIQUE)
+  PARTITION BY KEY(c1)
+  PARTITIONS 1;
+INSERT INTO t1 VALUES (2),(1),(0);
+SELECT COUNT(*) FROM t1 WHERE c1 BETWEEN 0 AND 1;
+DROP TABLE t1;
+

--- 1.8/mysql-test/r/partition_02myisam.result	2007-07-20 20:27:25 +02:00
+++ 1.9/mysql-test/r/partition_02myisam.result	2007-07-20 20:27:25 +02:00
@@ -1723,3 +1723,11 @@ ALTER TABLE t1 ADD PARTITION (PARTITION 
 (SUBPARTITION subpart21));
 DROP TABLE t1;
 DROP TABLE if exists t0_template;
+CREATE TABLE t1 (c1 INT UNIQUE)
+PARTITION BY KEY(c1)
+PARTITIONS 1;
+INSERT INTO t1 VALUES (2),(1),(0);
+SELECT COUNT(*) FROM t1 WHERE c1 BETWEEN 0 AND 1;
+COUNT(*)
+2
+DROP TABLE t1;

--- 1.90/sql/ha_partition.cc	2007-07-20 20:27:25 +02:00
+++ 1.91/sql/ha_partition.cc	2007-07-20 20:27:25 +02:00
@@ -3918,6 +3918,14 @@ int ha_partition::handle_unordered_scan_
       error= file->index_read(buf, m_start_key.key,
                               m_start_key.keypart_map,
                               m_start_key.flag);
+      /*
+        We cannot trust to get the lowest key first if the engine sets
+        HA_REC_NOT_IN_SEQ. Repeat the read until a key in range is
+        returned or no more keys are available.
+      */
+      if (file->table_flags() & HA_REC_NOT_IN_SEQ)
+        while (!error && compare_key(end_range) > 0)
+          error= file->index_next(buf);
       break;
     case partition_index_first:
       DBUG_PRINT("info", ("index_first on partition %d", i));
Thread
bk commit into 6.0-falcon tree (istruewing:1.2636) BUG#29493Ingo Struewing20 Jul
  • Re: bk commit into 6.0-falcon tree (istruewing:1.2636) BUG#29493Sergei Golubchik30 Jul