1
SET SESSION storage_engine='MYISAM';
5
#------------------------------------------------------------------------
6
# There are several testcases disabled because ouf the open bugs
7
# #15407 , #15408 , #15890 , #15961 , #13447 , #15966 , #15968, #16370
8
#------------------------------------------------------------------------
10
#------------------------------------------------------------------------
11
# 0. Setting of auxiliary variables + Creation of an auxiliary table
12
# needed in all testcases
13
#------------------------------------------------------------------------
14
DROP TABLE IF EXISTS t0_template;
15
CREATE TABLE t0_template ( f1 INTEGER, f2 char(20), PRIMARY KEY(f1))
17
# Logging of <max_row> INSERTs into t0_template suppressed
19
#------------------------------------------------------------------------
20
# 1. Some syntax checks
21
#------------------------------------------------------------------------
22
# 1.1 Subpartioned table without subpartitioning rule must be rejected
23
DROP TABLE IF EXISTS t1;
24
# FIXME Implement testcases, where it is checked that all create and
25
# alter table statements
26
# - with missing mandatory parameters are rejected
27
# - with optional parameters are accepted
28
# - with wrong combinations of optional parameters are rejected
31
#------------------------------------------------------------------------
32
# 2. Checks where the engine is assigned on all supported (CREATE TABLE
33
# statement) positions + basic operations on the tables
34
# Storage engine mixups are currently (2005-12-23) not supported
35
#------------------------------------------------------------------------
36
DROP TABLE IF EXISTS t1;
37
# 2.1 non partitioned table (for comparison)
38
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM';
41
t1 CREATE TABLE `t1` (
42
`f1` int(11) DEFAULT NULL,
43
`f2` char(20) DEFAULT NULL
44
) ENGINE=MyISAM DEFAULT CHARSET=latin1
45
SELECT COUNT(*) = 0 AS my_value FROM t1;
48
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
49
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
53
UPDATE t1 SET f1 = f1 + 200
54
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
55
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
60
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
61
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
65
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
66
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
69
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
70
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
73
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
74
WHERE f1 = 0 AND f2 = '#######';
75
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
78
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
79
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
83
SELECT COUNT(*) = 0 AS my_value FROM t1;
87
# 2.2 Assignment of storage engine just after column list only
88
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
89
PARTITION BY HASH(f1) PARTITIONS 2;
92
t1 CREATE TABLE `t1` (
93
`f1` int(11) DEFAULT NULL,
94
`f2` char(20) DEFAULT NULL
95
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) PARTITIONS 2 */
96
SELECT COUNT(*) = 0 AS my_value FROM t1;
99
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
100
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
104
UPDATE t1 SET f1 = f1 + 200
105
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
106
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
111
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
112
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
116
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
117
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
120
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
121
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
124
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
125
WHERE f1 = 0 AND f2 = '#######';
126
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
129
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
130
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
134
SELECT COUNT(*) = 0 AS my_value FROM t1;
138
# 2.3 Assignment of storage engine just after partition or subpartition
140
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
141
PARTITION BY HASH(f1)
142
( PARTITION part1 STORAGE ENGINE = 'MYISAM',
143
PARTITION part2 STORAGE ENGINE = 'MYISAM'
145
SHOW CREATE TABLE t1;
147
t1 CREATE TABLE `t1` (
148
`f1` int(11) DEFAULT NULL,
149
`f2` char(20) DEFAULT NULL
150
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
151
SELECT COUNT(*) = 0 AS my_value FROM t1;
154
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
155
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
159
UPDATE t1 SET f1 = f1 + 200
160
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
161
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
166
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
167
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
171
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
172
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
175
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
176
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
179
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
180
WHERE f1 = 0 AND f2 = '#######';
181
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
184
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
185
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
189
SELECT COUNT(*) = 0 AS my_value FROM t1;
193
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
194
PARTITION BY RANGE(f1)
195
SUBPARTITION BY HASH(f1)
196
( PARTITION part1 VALUES LESS THAN (100)
197
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
198
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
199
PARTITION part2 VALUES LESS THAN (2147483647)
200
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
201
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
203
SHOW CREATE TABLE t1;
205
t1 CREATE TABLE `t1` (
206
`f1` int(11) DEFAULT NULL,
207
`f2` char(20) DEFAULT NULL
208
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
209
SELECT COUNT(*) = 0 AS my_value FROM t1;
212
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
213
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
217
UPDATE t1 SET f1 = f1 + 200
218
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
219
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
224
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
225
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
229
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
230
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
233
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
234
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
237
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
238
WHERE f1 = 0 AND f2 = '#######';
239
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
242
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
243
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
247
SELECT COUNT(*) = 0 AS my_value FROM t1;
251
# 2.4 Some but not all named partitions or subpartitions get a storage
253
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
254
PARTITION BY HASH(f1)
255
( PARTITION part1 STORAGE ENGINE = 'MYISAM',
258
SHOW CREATE TABLE t1;
260
t1 CREATE TABLE `t1` (
261
`f1` int(11) DEFAULT NULL,
262
`f2` char(20) DEFAULT NULL
263
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
264
SELECT COUNT(*) = 0 AS my_value FROM t1;
267
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
268
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
272
UPDATE t1 SET f1 = f1 + 200
273
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
274
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
279
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
280
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
284
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
285
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
288
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
289
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
292
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
293
WHERE f1 = 0 AND f2 = '#######';
294
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
297
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
298
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
302
SELECT COUNT(*) = 0 AS my_value FROM t1;
306
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
307
PARTITION BY HASH(f1)
309
PARTITION part2 STORAGE ENGINE = 'MYISAM'
311
SHOW CREATE TABLE t1;
313
t1 CREATE TABLE `t1` (
314
`f1` int(11) DEFAULT NULL,
315
`f2` char(20) DEFAULT NULL
316
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
317
SELECT COUNT(*) = 0 AS my_value FROM t1;
320
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
321
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
325
UPDATE t1 SET f1 = f1 + 200
326
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
327
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
332
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
333
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
337
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
338
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
341
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
342
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
345
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
346
WHERE f1 = 0 AND f2 = '#######';
347
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
350
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
351
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
355
SELECT COUNT(*) = 0 AS my_value FROM t1;
359
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
360
PARTITION BY RANGE(f1)
361
SUBPARTITION BY HASH(f1)
362
( PARTITION part1 VALUES LESS THAN (100)
363
(SUBPARTITION subpart11,
364
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
365
PARTITION part2 VALUES LESS THAN (2147483647)
366
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
367
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
369
SHOW CREATE TABLE t1;
371
t1 CREATE TABLE `t1` (
372
`f1` int(11) DEFAULT NULL,
373
`f2` char(20) DEFAULT NULL
374
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
375
SELECT COUNT(*) = 0 AS my_value FROM t1;
378
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
379
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
383
UPDATE t1 SET f1 = f1 + 200
384
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
385
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
390
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
391
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
395
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
396
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
399
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
400
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
403
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
404
WHERE f1 = 0 AND f2 = '#######';
405
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
408
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
409
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
413
SELECT COUNT(*) = 0 AS my_value FROM t1;
417
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
418
PARTITION BY RANGE(f1)
419
SUBPARTITION BY HASH(f1)
420
( PARTITION part1 VALUES LESS THAN (100)
421
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
422
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
423
PARTITION part2 VALUES LESS THAN (2147483647)
424
(SUBPARTITION subpart21,
425
SUBPARTITION subpart22 )
427
SHOW CREATE TABLE t1;
429
t1 CREATE TABLE `t1` (
430
`f1` int(11) DEFAULT NULL,
431
`f2` char(20) DEFAULT NULL
432
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
433
SELECT COUNT(*) = 0 AS my_value FROM t1;
436
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
437
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
441
UPDATE t1 SET f1 = f1 + 200
442
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
443
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
448
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
449
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
453
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
454
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
457
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
458
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
461
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
462
WHERE f1 = 0 AND f2 = '#######';
463
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
466
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
467
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
471
SELECT COUNT(*) = 0 AS my_value FROM t1;
475
# 2.5 Storage engine assignment after partition name + after name of
476
# subpartitions belonging to another partition
477
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
478
PARTITION BY RANGE(f1)
479
SUBPARTITION BY HASH(f1)
480
( PARTITION part1 VALUES LESS THAN (100) ENGINE = 'MYISAM'
481
(SUBPARTITION subpart11,
482
SUBPARTITION subpart12),
483
PARTITION part2 VALUES LESS THAN (2147483647)
484
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
485
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
487
SHOW CREATE TABLE t1;
489
t1 CREATE TABLE `t1` (
490
`f1` int(11) DEFAULT NULL,
491
`f2` char(20) DEFAULT NULL
492
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
493
SELECT COUNT(*) = 0 AS my_value FROM t1;
496
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
497
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
501
UPDATE t1 SET f1 = f1 + 200
502
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
503
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
508
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
509
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
513
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
514
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
517
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
518
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
521
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
522
WHERE f1 = 0 AND f2 = '#######';
523
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
526
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
527
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
531
SELECT COUNT(*) = 0 AS my_value FROM t1;
535
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
536
PARTITION BY RANGE(f1)
537
SUBPARTITION BY HASH(f1)
538
( PARTITION part1 VALUES LESS THAN (100)
539
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
540
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
541
PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = 'MYISAM'
542
(SUBPARTITION subpart21,
543
SUBPARTITION subpart22)
545
SHOW CREATE TABLE t1;
547
t1 CREATE TABLE `t1` (
548
`f1` int(11) DEFAULT NULL,
549
`f2` char(20) DEFAULT NULL
550
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
551
SELECT COUNT(*) = 0 AS my_value FROM t1;
554
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
555
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
559
UPDATE t1 SET f1 = f1 + 200
560
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
561
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
566
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
567
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
571
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
572
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
575
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
576
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
579
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
580
WHERE f1 = 0 AND f2 = '#######';
581
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
584
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
585
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
589
SELECT COUNT(*) = 0 AS my_value FROM t1;
593
# 2.6 Precedence of storage engine assignments
594
# 2.6.1 Storage engine assignment after column list + after partition
595
# or subpartition name
596
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
597
PARTITION BY HASH(f1)
598
( PARTITION part1 STORAGE ENGINE = 'MYISAM',
599
PARTITION part2 STORAGE ENGINE = 'MYISAM'
601
SHOW CREATE TABLE t1;
603
t1 CREATE TABLE `t1` (
604
`f1` int(11) DEFAULT NULL,
605
`f2` char(20) DEFAULT NULL
606
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
607
SELECT COUNT(*) = 0 AS my_value FROM t1;
610
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
611
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
615
UPDATE t1 SET f1 = f1 + 200
616
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
617
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
622
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
623
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
627
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
628
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
631
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
632
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
635
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
636
WHERE f1 = 0 AND f2 = '#######';
637
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
640
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
641
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
645
SELECT COUNT(*) = 0 AS my_value FROM t1;
649
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
650
PARTITION BY RANGE(f1)
651
SUBPARTITION BY HASH(f1)
652
( PARTITION part1 VALUES LESS THAN (100)
653
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
654
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
655
PARTITION part2 VALUES LESS THAN (2147483647)
656
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
657
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
659
SHOW CREATE TABLE t1;
661
t1 CREATE TABLE `t1` (
662
`f1` int(11) DEFAULT NULL,
663
`f2` char(20) DEFAULT NULL
664
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
665
SELECT COUNT(*) = 0 AS my_value FROM t1;
668
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
669
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
673
UPDATE t1 SET f1 = f1 + 200
674
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
675
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
680
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
681
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
685
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
686
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
689
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
690
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
693
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
694
WHERE f1 = 0 AND f2 = '#######';
695
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
698
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
699
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
703
SELECT COUNT(*) = 0 AS my_value FROM t1;
707
# 2.6.2 Storage engine assignment after partition name + after
709
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
710
PARTITION BY RANGE(f1)
711
SUBPARTITION BY HASH(f1)
712
( PARTITION part1 VALUES LESS THAN (100) STORAGE ENGINE = 'MYISAM'
713
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
714
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
715
PARTITION part2 VALUES LESS THAN (2147483647)
716
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
717
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
719
SHOW CREATE TABLE t1;
721
t1 CREATE TABLE `t1` (
722
`f1` int(11) DEFAULT NULL,
723
`f2` char(20) DEFAULT NULL
724
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
725
SELECT COUNT(*) = 0 AS my_value FROM t1;
728
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
729
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
733
UPDATE t1 SET f1 = f1 + 200
734
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
735
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
740
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
741
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
745
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
746
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
749
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
750
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
753
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
754
WHERE f1 = 0 AND f2 = '#######';
755
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
758
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
759
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
763
SELECT COUNT(*) = 0 AS my_value FROM t1;
767
# 2.7 Session default engine differs from engine used within create table
768
SET SESSION storage_engine='MEMORY';
769
SET SESSION storage_engine='MYISAM';
771
#------------------------------------------------------------------------
772
# 3. Check assigning the number of partitions and subpartitions
773
# with and without named partitions/subpartitions
774
#------------------------------------------------------------------------
775
DROP TABLE IF EXISTS t1;
776
# 3.1 (positive) without partition/subpartition number assignment
777
# 3.1.1 no partition number, no named partitions
778
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
779
PARTITION BY HASH(f1);
780
SHOW CREATE TABLE t1;
782
t1 CREATE TABLE `t1` (
783
`f1` int(11) DEFAULT NULL,
784
`f2` char(20) DEFAULT NULL
785
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) */
786
SELECT COUNT(*) = 0 AS my_value FROM t1;
789
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
790
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
794
UPDATE t1 SET f1 = f1 + 200
795
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
796
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
801
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
802
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
806
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
807
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
810
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
811
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
814
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
815
WHERE f1 = 0 AND f2 = '#######';
816
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
819
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
820
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
824
SELECT COUNT(*) = 0 AS my_value FROM t1;
828
# 3.1.2 no partition number, named partitions
829
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
830
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2);
831
SHOW CREATE TABLE t1;
833
t1 CREATE TABLE `t1` (
834
`f1` int(11) DEFAULT NULL,
835
`f2` char(20) DEFAULT NULL
836
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
837
SELECT COUNT(*) = 0 AS my_value FROM t1;
840
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
841
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
845
UPDATE t1 SET f1 = f1 + 200
846
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
847
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
852
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
853
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
857
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
858
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
861
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
862
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
865
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
866
WHERE f1 = 0 AND f2 = '#######';
867
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
870
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
871
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
875
SELECT COUNT(*) = 0 AS my_value FROM t1;
879
# 3.1.3 variations on no partition/subpartition number, named partitions,
880
# different subpartitions are/are not named
881
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
882
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)) ;
884
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
885
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (100)
886
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (200)
887
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483647)
888
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
889
SHOW CREATE TABLE t1;
891
t1 CREATE TABLE `t1` (
892
`f1` int(11) DEFAULT NULL,
893
`f2` char(20) DEFAULT NULL
894
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (200) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM), PARTITION part3 VALUES LESS THAN (2147483647) (SUBPARTITION subpart31 ENGINE = MyISAM, SUBPARTITION subpart32 ENGINE = MyISAM)) */
895
SELECT COUNT(*) = 0 AS my_value FROM t1;
898
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
899
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
903
UPDATE t1 SET f1 = f1 + 200
904
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
905
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
910
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
911
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
915
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
916
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
919
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
920
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
923
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
924
WHERE f1 = 0 AND f2 = '#######';
925
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
928
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
929
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
933
SELECT COUNT(*) = 0 AS my_value FROM t1;
937
# 3.2 partition/subpartition numbers good and bad values and notations
938
DROP TABLE IF EXISTS t1;
939
# 3.2.1 partition/subpartition numbers INTEGER notation
940
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
941
PARTITION BY HASH(f1) PARTITIONS 2;
942
SHOW CREATE TABLE t1;
944
t1 CREATE TABLE `t1` (
945
`f1` int(11) DEFAULT NULL,
946
`f2` char(20) DEFAULT NULL
947
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) PARTITIONS 2 */
948
SELECT COUNT(*) = 0 AS my_value FROM t1;
951
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
952
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
956
UPDATE t1 SET f1 = f1 + 200
957
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
958
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
963
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
964
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
968
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
969
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
972
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
973
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
976
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
977
WHERE f1 = 0 AND f2 = '#######';
978
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
981
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
982
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
986
SELECT COUNT(*) = 0 AS my_value FROM t1;
990
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
991
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
993
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
994
SHOW CREATE TABLE t1;
996
t1 CREATE TABLE `t1` (
997
`f1` int(11) DEFAULT NULL,
998
`f2` char(20) DEFAULT NULL
999
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = MyISAM) */
1000
SELECT COUNT(*) = 0 AS my_value FROM t1;
1003
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
1004
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1005
AS my_value FROM t1;
1008
UPDATE t1 SET f1 = f1 + 200
1009
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1010
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1011
AS my_value FROM t1;
1015
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1016
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1017
AS my_value FROM t1;
1020
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1021
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1024
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1025
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1028
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1029
WHERE f1 = 0 AND f2 = '#######';
1030
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1033
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1034
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1038
SELECT COUNT(*) = 0 AS my_value FROM t1;
1042
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1043
PARTITION BY HASH(f1) PARTITIONS 1;
1044
SHOW CREATE TABLE t1;
1046
t1 CREATE TABLE `t1` (
1047
`f1` int(11) DEFAULT NULL,
1048
`f2` char(20) DEFAULT NULL
1049
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) PARTITIONS 1 */
1050
SELECT COUNT(*) = 0 AS my_value FROM t1;
1053
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
1054
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1055
AS my_value FROM t1;
1058
UPDATE t1 SET f1 = f1 + 200
1059
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1060
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1061
AS my_value FROM t1;
1065
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1066
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1067
AS my_value FROM t1;
1070
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1071
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1074
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1075
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1078
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1079
WHERE f1 = 0 AND f2 = '#######';
1080
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1083
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1084
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1088
SELECT COUNT(*) = 0 AS my_value FROM t1;
1092
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1093
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1095
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1096
SHOW CREATE TABLE t1;
1098
t1 CREATE TABLE `t1` (
1099
`f1` int(11) DEFAULT NULL,
1100
`f2` char(20) DEFAULT NULL
1101
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION part2 VALUES LESS THAN (2147483647) ENGINE = MyISAM) */
1102
SELECT COUNT(*) = 0 AS my_value FROM t1;
1105
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
1106
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1107
AS my_value FROM t1;
1110
UPDATE t1 SET f1 = f1 + 200
1111
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1112
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1113
AS my_value FROM t1;
1117
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1118
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1119
AS my_value FROM t1;
1122
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1123
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1126
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1127
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1130
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1131
WHERE f1 = 0 AND f2 = '#######';
1132
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1135
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1136
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1140
SELECT COUNT(*) = 0 AS my_value FROM t1;
1144
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1145
PARTITION BY HASH(f1) PARTITIONS 0;
1146
ERROR HY000: Number of partitions = 0 is not an allowed value
1147
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1148
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1150
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1151
ERROR HY000: Number of subpartitions = 0 is not an allowed value
1152
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1153
PARTITION BY HASH(f1) PARTITIONS -1;
1154
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
1155
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1156
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1158
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1159
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
1160
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
1161
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1162
PARTITION BY HASH(f1) PARTITIONS 1000000;
1163
ERROR HY000: Too many partitions (including subpartitions) were defined
1164
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1165
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1166
SUBPARTITIONS 1000000
1167
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1168
ERROR HY000: Too many partitions (including subpartitions) were defined
1169
# 3.2.4 partition/subpartition numbers STRING notation
1170
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1171
PARTITION BY HASH(f1) PARTITIONS '2';
1172
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
1173
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1174
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1176
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1177
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'
1178
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
1179
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1180
PARTITION BY HASH(f1) PARTITIONS '2.0';
1181
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
1182
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1183
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1185
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1186
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'
1187
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN ' at line 3
1188
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1189
PARTITION BY HASH(f1) PARTITIONS '0.2E+1';
1190
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
1191
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1192
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1193
SUBPARTITIONS '0.2E+1'
1194
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1195
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'
1196
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS TH' at line 3
1197
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1198
PARTITION BY HASH(f1) PARTITIONS '2A';
1199
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
1200
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1201
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1203
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1204
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'
1205
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
1206
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1207
PARTITION BY HASH(f1) PARTITIONS 'A2';
1208
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
1209
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1210
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1212
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1213
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'
1214
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
1215
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1216
PARTITION BY HASH(f1) PARTITIONS '';
1217
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
1218
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1219
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1221
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1222
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 '''
1223
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
1224
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1225
PARTITION BY HASH(f1) PARTITIONS 'GARBAGE';
1226
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
1227
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1228
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1229
SUBPARTITIONS 'GARBAGE'
1230
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1231
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'
1232
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
1233
# 3.2.5 partition/subpartition numbers other notations
1234
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1235
PARTITION BY HASH(f1) PARTITIONS 2A;
1236
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
1237
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1238
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1240
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1241
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
1242
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
1243
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1244
PARTITION BY HASH(f1) PARTITIONS A2;
1245
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
1246
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1247
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1249
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1250
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
1251
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
1252
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1253
PARTITION BY HASH(f1) PARTITIONS GARBAGE;
1254
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
1255
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1256
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1257
SUBPARTITIONS GARBAGE
1258
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1259
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
1260
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THA' at line 3
1261
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1262
PARTITION BY HASH(f1) PARTITIONS "2";
1263
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
1264
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1265
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1267
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1268
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"
1269
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
1270
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1271
PARTITION BY HASH(f1) PARTITIONS "2A";
1272
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
1273
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1274
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1276
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1277
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"
1278
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
1279
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1280
PARTITION BY HASH(f1) PARTITIONS "A2";
1281
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
1282
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1283
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1285
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1286
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"
1287
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
1288
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1289
PARTITION BY HASH(f1) PARTITIONS "GARBAGE";
1290
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
1291
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1292
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1)
1293
SUBPARTITIONS "GARBAGE"
1294
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
1295
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"
1296
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
1297
# 3.3 Mixups of assigned partition/subpartition numbers and names
1298
# 3.3.1 (positive) number of partition/subpartition
1299
# = number of named partition/subpartition
1300
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1301
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
1302
SHOW CREATE TABLE t1;
1304
t1 CREATE TABLE `t1` (
1305
`f1` int(11) DEFAULT NULL,
1306
`f2` char(20) DEFAULT NULL
1307
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
1309
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1310
PARTITION BY RANGE(f1) PARTITIONS 2
1311
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
1312
( PARTITION part1 VALUES LESS THAN (1000)
1313
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1314
PARTITION part2 VALUES LESS THAN (2147483647)
1315
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1317
SHOW CREATE TABLE t1;
1319
t1 CREATE TABLE `t1` (
1320
`f1` int(11) DEFAULT NULL,
1321
`f2` char(20) DEFAULT NULL
1322
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
1324
# 3.3.2 (positive) number of partition/subpartition ,
1325
# 0 (= no) named partition/subpartition
1326
# already checked above
1327
# 3.3.3 (negative) number of partitions/subpartitions
1328
# > number of named partitions/subpartitions
1329
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1330
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ;
1331
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
1332
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1333
PARTITION BY RANGE(f1)
1334
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
1335
( PARTITION part1 VALUES LESS THAN (1000)
1336
(SUBPARTITION subpart11 ),
1337
PARTITION part2 VALUES LESS THAN (2147483647)
1338
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1340
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1341
PARTITION part2 VALUES LESS THAN (2147483647)
1342
(SUBPARTITION subpart21, SUBPAR' at line 5
1343
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1344
PARTITION BY RANGE(f1)
1345
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
1346
( PARTITION part1 VALUES LESS THAN (1000)
1347
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1348
PARTITION part2 VALUES LESS THAN (2000)
1349
(SUBPARTITION subpart21 ),
1350
PARTITION part3 VALUES LESS THAN (2147483647)
1351
(SUBPARTITION subpart31, SUBPARTITION subpart32)
1353
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1354
PARTITION part3 VALUES LESS THAN (2147483647)
1355
(SUBPARTITION subpart31, SUBPAR' at line 7
1356
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1357
PARTITION BY RANGE(f1) PARTITIONS 2
1358
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
1359
( PARTITION part1 VALUES LESS THAN (1000)
1360
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1361
PARTITION part2 VALUES LESS THAN (2147483647)
1362
(SUBPARTITION subpart21 )
1364
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
1366
# 3.3.4 (negative) number of partitions < number of named partitions
1367
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1368
PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
1369
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
1370
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1371
PARTITION BY RANGE(f1)
1372
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
1373
( PARTITION part1 VALUES LESS THAN (1000)
1374
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1375
PARTITION part2 VALUES LESS THAN (2147483647)
1376
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1378
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1379
PARTITION part2 VALUES LESS THAN (2147483647)
1380
(SUBPARTITION subpart21, SUBPAR' at line 5
1381
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1382
PARTITION BY RANGE(f1)
1383
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
1384
( PARTITION part1 VALUES LESS THAN (1000)
1385
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1386
PARTITION part2 VALUES LESS THAN (2000)
1387
(SUBPARTITION subpart21 ),
1388
PARTITION part3 VALUES LESS THAN (2147483647)
1389
(SUBPARTITION subpart31, SUBPARTITION subpart32)
1391
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1392
PARTITION part2 VALUES LESS THAN (2000)
1393
(SUBPARTITION subpart21 ' at line 5
1394
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1395
PARTITION BY RANGE(f1)
1396
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
1397
( PARTITION part1 VALUES LESS THAN (1000)
1398
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1399
PARTITION part2 VALUES LESS THAN (2147483647)
1400
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1402
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1403
PARTITION part2 VALUES LESS THAN (2147483647)
1404
(SUBPARTITION subpart21, SUBPAR' at line 5
1406
#------------------------------------------------------------------------
1407
# 4. Checks of logical partition/subpartition name
1408
# file name clashes during CREATE TABLE
1409
#------------------------------------------------------------------------
1410
DROP TABLE IF EXISTS t1;
1411
# 4.1 (negative) A partition name used more than once
1412
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1413
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1);
1414
ERROR HY000: Duplicate partition name part1
1415
# FIXME Implement testcases with filename problems
1416
# existing file of other table --- partition/subpartition file name
1417
# partition/subpartition file name --- file of the same table
1419
#------------------------------------------------------------------------
1420
# 5. Alter table experiments
1421
#------------------------------------------------------------------------
1422
DROP TABLE IF EXISTS t1;
1423
# 5.1 alter table add partition
1424
# 5.1.1 (negative) add partition to non partitioned table
1425
CREATE TABLE t1 ( f1 INTEGER, f2 char(20));
1426
SHOW CREATE TABLE t1;
1428
t1 CREATE TABLE `t1` (
1429
`f1` int(11) DEFAULT NULL,
1430
`f2` char(20) DEFAULT NULL
1431
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1432
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
1433
Got one of the listed errors
1434
SHOW CREATE TABLE t1;
1436
t1 CREATE TABLE `t1` (
1437
`f1` int(11) DEFAULT NULL,
1438
`f2` char(20) DEFAULT NULL
1439
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1441
# 5.1.2 Add one partition to a table with one partition
1442
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1443
PARTITION BY HASH(f1);
1444
SHOW CREATE TABLE t1;
1446
t1 CREATE TABLE `t1` (
1447
`f1` int(11) DEFAULT NULL,
1448
`f2` char(20) DEFAULT NULL
1449
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) */
1450
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
1451
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
1452
SHOW CREATE TABLE t1;
1454
t1 CREATE TABLE `t1` (
1455
`f1` int(11) DEFAULT NULL,
1456
`f2` char(20) DEFAULT NULL
1457
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM) */
1458
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
1459
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1460
AS my_value FROM t1;
1463
UPDATE t1 SET f1 = f1 + 200
1464
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1465
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1466
AS my_value FROM t1;
1470
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1471
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1472
AS my_value FROM t1;
1475
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1476
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1479
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1480
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1483
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1484
WHERE f1 = 0 AND f2 = '#######';
1485
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1488
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1489
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1493
SELECT COUNT(*) = 0 AS my_value FROM t1;
1497
# 5.1.3 Several times add one partition to a table with some partitions
1498
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1499
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
1500
SHOW CREATE TABLE t1;
1502
t1 CREATE TABLE `t1` (
1503
`f1` int(11) DEFAULT NULL,
1504
`f2` char(20) DEFAULT NULL
1505
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part3 ENGINE = MyISAM) */
1506
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
1507
ALTER TABLE t1 ADD PARTITION (PARTITION part0);
1508
SHOW CREATE TABLE t1;
1510
t1 CREATE TABLE `t1` (
1511
`f1` int(11) DEFAULT NULL,
1512
`f2` char(20) DEFAULT NULL
1513
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part3 ENGINE = MyISAM, PARTITION part0 ENGINE = MyISAM) */
1514
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
1515
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1516
AS my_value FROM t1;
1519
UPDATE t1 SET f1 = f1 + 200
1520
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1521
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1522
AS my_value FROM t1;
1526
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1527
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1528
AS my_value FROM t1;
1531
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1532
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1535
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1536
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1539
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1540
WHERE f1 = 0 AND f2 = '#######';
1541
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1544
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1545
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1549
SELECT COUNT(*) = 0 AS my_value FROM t1;
1553
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
1554
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
1555
SHOW CREATE TABLE t1;
1557
t1 CREATE TABLE `t1` (
1558
`f1` int(11) DEFAULT NULL,
1559
`f2` char(20) DEFAULT NULL
1560
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part3 ENGINE = MyISAM, PARTITION part0 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
1561
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
1562
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1563
AS my_value FROM t1;
1566
UPDATE t1 SET f1 = f1 + 200
1567
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1568
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1569
AS my_value FROM t1;
1573
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1574
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1575
AS my_value FROM t1;
1578
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1579
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1582
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1583
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1586
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1587
WHERE f1 = 0 AND f2 = '#######';
1588
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1591
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1592
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1596
SELECT COUNT(*) = 0 AS my_value FROM t1;
1600
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
1601
SHOW CREATE TABLE t1;
1603
t1 CREATE TABLE `t1` (
1604
`f1` int(11) DEFAULT NULL,
1605
`f2` char(20) DEFAULT NULL
1606
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part3 ENGINE = MyISAM, PARTITION part0 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
1607
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
1608
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1609
AS my_value FROM t1;
1612
UPDATE t1 SET f1 = f1 + 200
1613
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1614
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1615
AS my_value FROM t1;
1619
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1620
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1621
AS my_value FROM t1;
1624
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1625
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1628
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1629
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1632
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1633
WHERE f1 = 0 AND f2 = '#######';
1634
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1637
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1638
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1642
SELECT COUNT(*) = 0 AS my_value FROM t1;
1646
# 5.1.4 Add several partitions to a table with some partitions
1647
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1648
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
1649
SHOW CREATE TABLE t1;
1651
t1 CREATE TABLE `t1` (
1652
`f1` int(11) DEFAULT NULL,
1653
`f2` char(20) DEFAULT NULL
1654
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part3 ENGINE = MyISAM) */
1655
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
1656
SHOW CREATE TABLE t1;
1658
t1 CREATE TABLE `t1` (
1659
`f1` int(11) DEFAULT NULL,
1660
`f2` char(20) DEFAULT NULL
1661
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = MyISAM, PARTITION part3 ENGINE = MyISAM) */
1662
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
1663
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1664
AS my_value FROM t1;
1667
UPDATE t1 SET f1 = f1 + 200
1668
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
1669
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
1670
AS my_value FROM t1;
1674
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
1675
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
1676
AS my_value FROM t1;
1679
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
1680
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
1683
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
1684
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
1687
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
1688
WHERE f1 = 0 AND f2 = '#######';
1689
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1692
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
1693
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
1697
SELECT COUNT(*) = 0 AS my_value FROM t1;
1701
# 5.1.5 (negative) Add partitions to a table with some partitions
1702
# clash on new and already existing partition names
1703
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1704
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
1705
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
1706
ERROR HY000: Duplicate partition name part1
1707
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
1708
ERROR HY000: Duplicate partition name part2
1709
ALTER TABLE t1 ADD PARTITION (PARTITION part3);
1710
ERROR HY000: Duplicate partition name part3
1711
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part2, PARTITION part3);
1712
ERROR HY000: Duplicate partition name part1
1714
# 5.2 alter table add subpartition
1715
# 5.2.1 Add one subpartition to a table with subpartitioning rule and
1716
# no explicit defined subpartitions
1717
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
1718
PARTITION BY RANGE(f1)
1719
SUBPARTITION BY HASH(f1)
1720
(PARTITION part1 VALUES LESS THAN (100));
1721
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
1722
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (2147483647)
1723
(SUBPARTITION subpart21));
1725
DROP TABLE if exists t0_template;