2
# Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS
4
CREATE TABLE t1 (a int)
6
PARTITION BY HASH (a) PARTITIONS 2;
7
INSERT INTO t1 VALUES (0), (1), (2), (3);
8
CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0);
10
View Create View character_set_client collation_connection
11
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` PARTITION (`p0`) latin1 latin1_swedish_ci
17
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
18
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
19
VARIABLE_NAME VARIABLE_VALUE
21
HANDLER_EXTERNAL_LOCK 4
24
HANDLER_READ_RND_NEXT 3
26
# 4 locks (1 table, 1 partition lock/unlock)
28
SELECT a FROM t1 PARTITION (p0);
32
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
33
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
34
VARIABLE_NAME VARIABLE_VALUE
36
HANDLER_EXTERNAL_LOCK 4
39
HANDLER_READ_RND_NEXT 3
41
# 4 locks (1 table, 1 partition lock/unlock)
43
INSERT INTO v1 VALUES (10);
44
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
45
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
46
VARIABLE_NAME VARIABLE_VALUE
48
HANDLER_EXTERNAL_LOCK 4
50
# 4 locks (1 table, 1 partition lock/unlock)
52
INSERT INTO v1 VALUES (11);
53
ERROR HY000: Found a row not matching the given partition set
54
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
55
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
56
VARIABLE_NAME VARIABLE_VALUE
57
HANDLER_EXTERNAL_LOCK 2
59
# 2 locks (1 table, all partitions pruned)
66
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
67
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
68
VARIABLE_NAME VARIABLE_VALUE
70
HANDLER_EXTERNAL_LOCK 4
73
HANDLER_READ_RND_NEXT 4
75
# 4 locks (1 table, 1 partition lock/unlock)
77
SELECT a FROM t1 PARTITION (p0);
82
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
83
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
84
VARIABLE_NAME VARIABLE_VALUE
86
HANDLER_EXTERNAL_LOCK 4
89
HANDLER_READ_RND_NEXT 4
91
# 4 locks (1 table, 1 partition lock/unlock)
100
CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION;
102
INSERT INTO v1 VALUES (20);
103
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
104
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
105
VARIABLE_NAME VARIABLE_VALUE
107
HANDLER_EXTERNAL_LOCK 4
109
# 4 locks (1 table, 1 partition lock/unlock)
111
INSERT INTO v1 VALUES (21);
112
ERROR HY000: Found a row not matching the given partition set
113
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
114
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
115
VARIABLE_NAME VARIABLE_VALUE
116
HANDLER_EXTERNAL_LOCK 2
118
# 2 locks (1 table, all partitions pruned)
135
SELECT a FROM t1 PARTITION (p0) WHERE a = 30 WITH CHECK OPTION;
137
INSERT INTO v1 VALUES (30);
138
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
139
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
140
VARIABLE_NAME VARIABLE_VALUE
142
HANDLER_EXTERNAL_LOCK 4
144
# 4 locks (1 table, 1 partition lock/unlock)
146
INSERT INTO v1 VALUES (31);
147
ERROR HY000: CHECK OPTION failed 'test.v1'
148
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
149
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
150
VARIABLE_NAME VARIABLE_VALUE
151
HANDLER_EXTERNAL_LOCK 2
153
# 2 locks (1 table, all partitions pruned)
155
INSERT INTO v1 VALUES (32);
156
ERROR HY000: CHECK OPTION failed 'test.v1'
157
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
158
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
159
VARIABLE_NAME VARIABLE_VALUE
160
HANDLER_EXTERNAL_LOCK 4
163
# 4 locks (1 table, 1 partition lock/unlock)
178
# Original tests for WL#5217
179
# Must have InnoDB as engine to get the same statistics results.
180
# embedded uses MyISAM as default. CREATE SELECT uses the default engine.
181
SET @old_default_storage_engine = @@default_storage_engine;
182
SET @@default_storage_engine = 'InnoDB';
183
# Test to show if I_S affects HANDLER_ counts
185
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
186
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
187
VARIABLE_NAME VARIABLE_VALUE
189
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
190
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
191
VARIABLE_NAME VARIABLE_VALUE
192
HANDLER_READ_RND_NEXT 19
194
# OK, seems to add number of variables processed before HANDLER_WRITE
195
# and number of variables + 1 evaluated in the previous call in RND_NEXT
202
PARTITION BY RANGE (a)
203
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
204
(PARTITION pNeg VALUES LESS THAN (0)
207
PARTITION `p0-9` VALUES LESS THAN (10)
210
PARTITION `p10-99` VALUES LESS THAN (100)
213
PARTITION `p100-99999` VALUES LESS THAN (100000)
215
SUBPARTITION subp7));
216
SHOW CREATE TABLE t1;
218
t1 CREATE TABLE `t1` (
219
`a` int(11) NOT NULL,
220
`b` varchar(64) DEFAULT NULL,
223
) ENGINE=InnoDB DEFAULT CHARSET=latin1
224
/*!50100 PARTITION BY RANGE (a)
225
SUBPARTITION BY HASH (a)
226
(PARTITION pNeg VALUES LESS THAN (0)
227
(SUBPARTITION subp0 ENGINE = InnoDB,
228
SUBPARTITION subp1 ENGINE = InnoDB),
229
PARTITION `p0-9` VALUES LESS THAN (10)
230
(SUBPARTITION subp2 ENGINE = InnoDB,
231
SUBPARTITION subp3 ENGINE = InnoDB),
232
PARTITION `p10-99` VALUES LESS THAN (100)
233
(SUBPARTITION subp4 ENGINE = InnoDB,
234
SUBPARTITION subp5 ENGINE = InnoDB),
235
PARTITION `p100-99999` VALUES LESS THAN (100000)
236
(SUBPARTITION subp6 ENGINE = InnoDB,
237
SUBPARTITION subp7 ENGINE = InnoDB)) */
238
# First test that the syntax is OK
239
SHOW CREATE TABLE t1 PARTITION (subp0);
240
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 (subp0)' at line 1
241
# Not a correct partition list
242
INSERT INTO t1 PARTITION () VALUES (1, "error");
243
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 ') VALUES (1, "error")' at line 1
244
INSERT INTO t1 PARTITION (pNonExisting) VALUES (1, "error");
245
ERROR HY000: Unknown partition 'pNonExisting' in table 't1'
246
INSERT INTO t1 PARTITION (pNeg, pNonExisting) VALUES (1, "error");
247
ERROR HY000: Unknown partition 'pNonExisting' in table 't1'
248
# Duplicate partitions and overlapping partitions and subpartitios is OK
250
INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (-1, "pNeg(-subp1)");
251
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
252
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
253
VARIABLE_NAME VARIABLE_VALUE
255
HANDLER_EXTERNAL_LOCK 4
258
# 4 external locks (due to pruning of locks)
259
# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
260
# and 18 write (1 ha_innobase + 17 internal I_S write)
261
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, "pNeg(-subp1)");
262
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, "(pNeg-)subp0");
264
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (100, "`p100-99999`(-subp6)"), (101, "`p100-99999`(-subp7)"), (1000, "`p100-99999`(-subp6)");
265
INSERT INTO t1 PARTITION(`p10-99`,subp3) VALUES (1, "subp3"), (10, "p10-99");
267
INSERT INTO t1 PARTITION(subp3) VALUES (3, "subp3");
268
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
269
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
270
VARIABLE_NAME VARIABLE_VALUE
272
HANDLER_EXTERNAL_LOCK 4
276
# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
277
# and 18 write (1 ha_innobase + 17 internal I_S write)
280
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
281
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
282
VARIABLE_NAME VARIABLE_VALUE
284
HANDLER_EXTERNAL_LOCK 9
287
# 9 locks (1 ha_partition + 8 ha_innobase)
288
# 17 writes (internal I_S)
289
INSERT INTO t1 PARTITION(`p0-9`) VALUES (5, "p0-9:subp3");
290
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
291
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
292
VARIABLE_NAME VARIABLE_VALUE
294
HANDLER_EXTERNAL_LOCK 9
295
HANDLER_READ_RND_NEXT 19
298
# + 19 rnd next (internal I_S)
299
# + 19 write (18 internal I_S + 1 insert)
301
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
302
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
303
VARIABLE_NAME VARIABLE_VALUE
305
HANDLER_EXTERNAL_LOCK 18
306
HANDLER_READ_RND_NEXT 38
308
# + 9 locks (unlocks)
309
# + 19 rnd next (internal I_S)
310
# + 18 write (internal I_S)
311
# Not matching partitions with inserted value
312
INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (1, "error");
313
ERROR HY000: Found a row not matching the given partition set
314
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (1, "error");
315
ERROR HY000: Found a row not matching the given partition set
316
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1, "error"), (10, "error");
317
ERROR HY000: Found a row not matching the given partition set
318
INSERT INTO t1 VALUES (1000000, "error"), (9999999, "error");
319
ERROR HY000: Table has no partition for value 1000000
320
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1000000, "error"), (9999999, "error");
321
ERROR HY000: Table has no partition for value 1000000
322
INSERT INTO t1 PARTITION (pNeg, subp4) VALUES (-7, "pNeg(-subp1)"), (-10, "pNeg(-subp0)"), (-1, "pNeg(-subp1)"), (-99, "pNeg(-subp1)");
323
Got one of the listed errors
324
SELECT * FROM t1 ORDER BY a;
333
100 `p100-99999`(-subp6)
334
101 `p100-99999`(-subp7)
335
1000 `p100-99999`(-subp6)
337
Table Op Msg_type Msg_text
338
test.t1 analyze status OK
339
SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
340
SET @@global.innodb_stats_on_metadata=ON;
341
SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
342
FROM INFORMATION_SCHEMA.PARTITIONS
343
WHERE TABLE_SCHEMA = 'test'
344
AND TABLE_NAME = 't1' ORDER BY SUBPARTITION_NAME;
345
PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS
354
SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;
356
SELECT * FROM t1 PARTITION (pNonexistent);
357
ERROR HY000: Unknown partition 'pNonexistent' in table 't1'
358
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
359
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
360
VARIABLE_NAME VARIABLE_VALUE
362
# should have failed before locking (only 17 internal I_S writes)
364
SELECT * FROM t1 PARTITION (subp2);
366
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
367
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
368
VARIABLE_NAME VARIABLE_VALUE
370
HANDLER_EXTERNAL_LOCK 4
375
# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
376
# 1 read first (also calls index_read)
377
# 2 read key (first from innobase_get_index and second from index first)
378
# 17 writes (internal I_S)
380
SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias;
385
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
386
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
387
VARIABLE_NAME VARIABLE_VALUE
389
HANDLER_EXTERNAL_LOCK 8
395
# 8 locks (1 ha_partition + 2 + 1 ha_innobase) x 2
396
# 3 read first (one for each partition)
397
# 6 read key (3 from read first and 3 from innobase_get_index)
398
# 3 read next (one next call after each read row)
399
# 17 writes (internal I_S)
401
LOCK TABLE t1 READ, t1 as TableAlias READ;
402
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
403
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
404
VARIABLE_NAME VARIABLE_VALUE
406
HANDLER_EXTERNAL_LOCK 18
410
# 18 READ KEY from opening a new partition table instance,
411
# (1 innobase_get_index for each index, per partition, 1 x 2 x 8 = 16
412
# + info(HA_STATUS_CONST) call on the partition with the most number
413
# of rows, 2 innobase_get_index for updating both index statistics)
414
# 17 writes (internal I_S)
415
SELECT * FROM t1 PARTITION (subp3) AS TableAlias;
420
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
421
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
422
VARIABLE_NAME VARIABLE_VALUE
424
HANDLER_EXTERNAL_LOCK 18
428
HANDLER_READ_RND_NEXT 19
431
# + 1 read first (read first key from index in one partition)
432
# + 2 read key (innobase_get_index from index_init + from index_first)
433
# + 3 read next (one after each row)
434
# + 19 rnd next (from the last I_S query)
435
# + 18 write (internal I_S)
436
SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
439
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
440
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
441
VARIABLE_NAME VARIABLE_VALUE
443
HANDLER_EXTERNAL_LOCK 18
447
HANDLER_READ_RND_NEXT 38
450
# + 2 read first (one for each subpart)
451
# + 4 read key (innobase_get_index from index_init + from index_first)
452
# + 1 read next (one after each row)
453
# + 19 rnd next (from the last I_S query)
454
# + 18 write (internal I_S)
455
SELECT * FROM t1 WHERE a = 1000000;
457
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
458
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
459
VARIABLE_NAME VARIABLE_VALUE
461
HANDLER_EXTERNAL_LOCK 18
465
HANDLER_READ_RND_NEXT 57
467
# No matching partition, only internal I_S.
468
SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
471
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
472
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
473
VARIABLE_NAME VARIABLE_VALUE
475
HANDLER_EXTERNAL_LOCK 36
479
HANDLER_READ_RND_NEXT 76
481
# + 18 for unlock (same as lock above) (100 is not in pNeg, no match)
482
# Test that EXPLAIN PARTITION works
483
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNonexistent);
484
ERROR HY000: Unknown partition 'pNonexistent' in table 't1'
485
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2);
486
id select_type table partitions type possible_keys key key_len ref rows Extra
487
1 SIMPLE t1 p0-9_subp2 index NULL b 71 NULL 2 Using index
489
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias;
490
id select_type table partitions type possible_keys key key_len ref rows Extra
491
1 SIMPLE TableAlias pNeg_subp0,pNeg_subp1,p0-9_subp2 index NULL b 71 NULL 4 Using index
492
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
493
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
494
VARIABLE_NAME VARIABLE_VALUE
496
HANDLER_EXTERNAL_LOCK 8
498
# 8 locks (1 ha_partition + 3 ha_innobase) x 2 (lock/unlock)
499
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp3) AS TableAlias;
500
id select_type table partitions type possible_keys key key_len ref rows Extra
501
1 SIMPLE TableAlias p0-9_subp3 index NULL b 71 NULL 3 Using index
502
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
503
id select_type table partitions type possible_keys key key_len ref rows Extra
504
1 SIMPLE t1 p10-99_subp4,p10-99_subp5 index NULL PRIMARY 4 NULL 2 Using index
505
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1000000;
506
id select_type table partitions type possible_keys key key_len ref rows Extra
507
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
508
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
509
id select_type table partitions type possible_keys key key_len ref rows Extra
510
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
511
# Test how it changes the alias/keywords/reserved words
512
SELECT * FROM t1 PARTITION;
513
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 1
514
SELECT * FROM t1 `PARTITION`;
523
100 `p100-99999`(-subp6)
524
1000 `p100-99999`(-subp6)
525
101 `p100-99999`(-subp7)
526
SELECT * FROM t1 AS PARTITION;
527
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' at line 1
528
SELECT * FROM t1 AS `PARTITION`;
537
100 `p100-99999`(-subp6)
538
1000 `p100-99999`(-subp6)
539
101 `p100-99999`(-subp7)
544
REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!');
545
ERROR HY000: Found a row not matching the given partition set
546
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
547
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
548
VARIABLE_NAME VARIABLE_VALUE
549
HANDLER_EXTERNAL_LOCK 2
551
# 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443
552
# explicit pruning says part_id 0 and implicit pruning says part_id 1
553
# so no partition will be locked!
554
# 0 rollback (since no locked partition)
555
# 17 writes (I_S internal)
557
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'Insert by REPLACE');
558
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
559
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
560
VARIABLE_NAME VARIABLE_VALUE
562
HANDLER_EXTERNAL_LOCK 4
565
# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
566
# 18 writes (17 I_S internal, 1 ha_innobase)
567
SELECT * FROM t1 PARTITION (pNeg);
570
-21 Insert by REPLACE
574
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE');
575
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
576
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
577
VARIABLE_NAME VARIABLE_VALUE
579
HANDLER_EXTERNAL_LOCK 4
584
# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
585
# 2 read key (1 innobase_get_index when init the index + 1 index read
586
# to get the position to update)
587
# 1 update (updated one row, since there is no delete trigger, update
588
# is used instead of delete+insert)
589
# 18 write (17 from I_S, 1 for the failed insert)
590
SELECT * FROM t1 PARTITION (pNeg);
595
-21 REPLACEd by REPLACE
598
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
599
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
600
VARIABLE_NAME VARIABLE_VALUE
602
HANDLER_EXTERNAL_LOCK 9
606
# 17 write (internal I_S)
607
DELETE FROM t1 PARTITION(subp1) WHERE b = "REPLACEd by REPLACE";
608
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
609
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
610
VARIABLE_NAME VARIABLE_VALUE
613
HANDLER_EXTERNAL_LOCK 9
616
HANDLER_READ_RND_NEXT 19
619
# + 1 delete (one row deleted)
620
# + 3 read key (1 innodb_get_index in records_in_range,
621
# 1 innodb_get_index in index_init, 1 index_read in index_read_first)
622
# + 1 read next (search for another row in secondary index)
623
# + 19 rnd next (internal I_S)
624
# + 18 write (internal I_S)
625
REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!');
626
ERROR HY000: Found a row not matching the given partition set
627
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
628
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
629
VARIABLE_NAME VARIABLE_VALUE
632
HANDLER_EXTERNAL_LOCK 9
635
HANDLER_READ_RND_NEXT 38
637
# Failed before start_stmt/execution.
638
# + 19 rnd next (internal I_S)
639
# 0 rollback (No partition had called start_stmt, all parts pruned)
640
# + 18 write (internal I_S)
641
REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE');
642
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
643
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
644
VARIABLE_NAME VARIABLE_VALUE
647
HANDLER_EXTERNAL_LOCK 9
650
HANDLER_READ_RND_NEXT 57
653
# + 19 rnd next (internal I_S)
654
# + 19 write (18 internal I_S + 1 real write)
655
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE');
656
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
657
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
658
VARIABLE_NAME VARIABLE_VALUE
661
HANDLER_EXTERNAL_LOCK 9
664
HANDLER_READ_RND_NEXT 76
668
# + 2 read key (see non locked query)
669
# + 19 rnd next (internal I_S)
670
# + 1 update (see non locked query)
671
# + 19 write (18 internal I_S + 1 failed write)
672
SELECT * FROM t1 PARTITION (subp1);
676
-21 REPLACEd by REPLACE
677
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
678
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
679
VARIABLE_NAME VARIABLE_VALUE
682
HANDLER_EXTERNAL_LOCK 9
686
HANDLER_READ_RND_NEXT 95
693
# + 19 rnd next (internal I_S)
694
# + 18 write (internal I_S)
696
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
697
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
698
VARIABLE_NAME VARIABLE_VALUE
701
HANDLER_EXTERNAL_LOCK 18
705
HANDLER_READ_RND_NEXT 114
709
# + 19 rnd next (internal I_S)
710
# + 18 write (internal I_S)
714
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
719
-21 REPLACEd by REPLACE
722
SELECT * FROM t1 PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtest.txt';
723
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
724
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
725
VARIABLE_NAME VARIABLE_VALUE
727
HANDLER_EXTERNAL_LOCK 10
733
# 10 locks (1 ha_partition + 4 ha_innobase) x 2 (lock/unlock)
734
# 4 read first (for reading the first row in 4 partitions)
735
# 8 read key (4 from read first + 4 for index init)
736
# 5 read next (one after each row)
737
# 17 write (internal I_S)
739
ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`;
740
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
741
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
742
VARIABLE_NAME VARIABLE_VALUE
744
HANDLER_EXTERNAL_LOCK 10
746
# 10 locks (table + 4 partition) x (lock + unlock)
747
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
750
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg);
751
ERROR HY000: Found a row not matching the given partition set
752
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
753
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
754
VARIABLE_NAME VARIABLE_VALUE
755
HANDLER_EXTERNAL_LOCK 6
758
# 6 locks (1 ha_partition + 2 ha_innobase) x 2 (lock+unlock)
760
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
763
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, subp4, subp5);
764
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
765
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
766
VARIABLE_NAME VARIABLE_VALUE
768
HANDLER_EXTERNAL_LOCK 10
770
# 10 lock (1 ha_partition + 4 ha_innobase) x 2 (lock + unlock)
771
ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`;
774
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
775
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
776
VARIABLE_NAME VARIABLE_VALUE
778
HANDLER_EXTERNAL_LOCK 9
781
# 18 read key (ALTER forces table to be closed, see above for open)
782
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, `p10-99`);
783
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
784
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
785
VARIABLE_NAME VARIABLE_VALUE
787
HANDLER_EXTERNAL_LOCK 9
788
HANDLER_READ_RND_NEXT 19
790
# + 23 write (18 internal I_S + 5 rows)
792
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
793
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
794
VARIABLE_NAME VARIABLE_VALUE
796
HANDLER_EXTERNAL_LOCK 18
797
HANDLER_READ_RND_NEXT 38
804
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
805
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
806
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
807
VARIABLE_NAME VARIABLE_VALUE
809
HANDLER_EXTERNAL_LOCK 4
812
HANDLER_READ_RND_NEXT 2
816
# 4 lock (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
817
# 1 read first (read first row, called from first rnd_next)
818
# 2 read key (innobase_get_index from rnd_init +
819
# read next row from second rnd_next)
820
# 1 update (update the row)
821
EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
822
id select_type table partitions type possible_keys key key_len ref rows Extra
823
1 SIMPLE t1 pNeg_subp0 index NULL PRIMARY 4 NULL 2 NULL
824
SELECT * FROM t1 PARTITION (subp0) ORDER BY a;
826
-2 (pNeg-)subp0, Updated
828
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2;
829
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
830
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
831
VARIABLE_NAME VARIABLE_VALUE
833
HANDLER_EXTERNAL_LOCK 4
841
EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2;
842
id select_type table partitions type possible_keys key key_len ref rows Extra
843
1 SIMPLE t1 pNeg_subp0 range PRIMARY PRIMARY 4 const 1 Using where
845
UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2')
847
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
848
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
849
VARIABLE_NAME VARIABLE_VALUE
851
HANDLER_EXTERNAL_LOCK 4
858
# 2 read key - (2 index read)
859
# 1 read rnd - rnd_pos
861
EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2');
862
id select_type table partitions type possible_keys key key_len ref rows Extra
863
1 SIMPLE t1 pNeg_subp0 index NULL PRIMARY 4 NULL 2 Using temporary
865
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
866
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
867
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
868
VARIABLE_NAME VARIABLE_VALUE
870
# Nothing, since impossible PARTITION+WHERE clause.
871
EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
872
id select_type table partitions type possible_keys key key_len ref rows Extra
873
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
875
UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100')
877
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
878
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
879
VARIABLE_NAME VARIABLE_VALUE
881
# Nothing, since impossible PARTITION+WHERE clause.
882
EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0)
883
SET a = -2, b = concat(b, ', Updated from a = 100') WHERE a = 100;
884
id select_type table partitions type possible_keys key key_len ref rows Extra
885
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
887
UPDATE t1 PARTITION(`p100-99999`) SET a = -2, b = concat(b, ', Updated from a = 100')
889
ERROR HY000: Found a row not matching the given partition set
890
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
891
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
892
VARIABLE_NAME VARIABLE_VALUE
893
HANDLER_EXTERNAL_LOCK 6
899
# 4 read key (1 index init + 1 index read + 1 rnd init + 1 rnd pos)
900
# 1 read rnd (rnd pos)
903
UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -4, b = concat(b, ', Updated from a = 100')
905
Got one of the listed errors
906
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
907
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
908
VARIABLE_NAME VARIABLE_VALUE
909
HANDLER_EXTERNAL_LOCK 10
918
# 18 write (17 internal I_S + 1 failed insert)
920
UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -222, b = concat(b, ', Updated from a = 100')
922
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
923
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
924
VARIABLE_NAME VARIABLE_VALUE
927
HANDLER_EXTERNAL_LOCK 10
935
# 18 write (17 internal I_S + 1 insert)
936
SELECT * FROM t1 ORDER BY a;
938
-222 `p100-99999`(-subp6), Updated from a = 100
939
-21 REPLACEd by REPLACE
940
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
947
101 `p100-99999`(-subp7)
948
1000 `p100-99999`(-subp6)
949
# Test of non matching partition (i.e ER_NO_PARTITION_FOUND)
951
UPDATE t1 SET b = concat(b, ', Updated2') WHERE a = 1000000;
952
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
953
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
954
VARIABLE_NAME VARIABLE_VALUE
956
# Nothing (no matching partition found)
958
UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000;
959
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
960
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
961
VARIABLE_NAME VARIABLE_VALUE
963
# Nothing (no matching partition found)
966
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
967
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
968
VARIABLE_NAME VARIABLE_VALUE
970
HANDLER_EXTERNAL_LOCK 9
973
UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101;
974
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
975
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
976
VARIABLE_NAME VARIABLE_VALUE
978
HANDLER_EXTERNAL_LOCK 9
981
HANDLER_READ_RND_NEXT 19
987
UPDATE t1 PARTITION (`p100-99999`) SET b = concat(b, ', Updated to 110'), a = 110 WHERE a = 103;
988
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
989
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
990
VARIABLE_NAME VARIABLE_VALUE
993
HANDLER_EXTERNAL_LOCK 9
996
HANDLER_READ_RND_NEXT 38
1002
# + 19 write (18 internal I_S + 1 insert)
1004
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1005
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1006
VARIABLE_NAME VARIABLE_VALUE
1009
HANDLER_EXTERNAL_LOCK 18
1012
HANDLER_READ_RND_NEXT 57
1019
SELECT * FROM t1 ORDER BY b, a;
1021
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
1026
-21 REPLACEd by REPLACE
1029
1000 `p100-99999`(-subp6)
1030
-222 `p100-99999`(-subp6), Updated from a = 100
1031
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1033
DELETE FROM t1 PARTITION (pNeg) WHERE a = -1;
1034
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1035
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1036
VARIABLE_NAME VARIABLE_VALUE
1039
HANDLER_EXTERNAL_LOCK 4
1043
# 4 locks (pruning works!).
1044
# 1 read key (index read)
1046
DELETE FROM t1 PARTITION (subp1) WHERE b like '%subp1%';
1047
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1048
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1049
VARIABLE_NAME VARIABLE_VALUE
1052
HANDLER_EXTERNAL_LOCK 4
1053
HANDLER_READ_FIRST 1
1055
HANDLER_READ_RND_NEXT 3
1063
LOCK TABLE t1 WRITE;
1064
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1065
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1066
VARIABLE_NAME VARIABLE_VALUE
1068
HANDLER_EXTERNAL_LOCK 9
1071
DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3';
1072
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1073
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1074
VARIABLE_NAME VARIABLE_VALUE
1076
HANDLER_EXTERNAL_LOCK 9
1078
HANDLER_READ_RND_NEXT 19
1080
# + 3 read key (1 innodb_get_index in records_in_range
1081
# + 1 innobase_get_index in index_init + 1 index read)
1082
DELETE FROM t1 PARTITION (`p0-9`) WHERE b = 'p0-9:subp3';
1083
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1084
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1085
VARIABLE_NAME VARIABLE_VALUE
1088
HANDLER_EXTERNAL_LOCK 9
1091
HANDLER_READ_RND_NEXT 38
1094
# + 6 read key (same as above, but for two subpartitions)
1095
# + 1 read next (read next after found row)
1097
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1098
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1099
VARIABLE_NAME VARIABLE_VALUE
1102
HANDLER_EXTERNAL_LOCK 18
1105
HANDLER_READ_RND_NEXT 57
1108
# Test multi-table DELETE
1109
# Can be expressed in two different ways.
1110
CREATE TABLE t2 LIKE t1;
1112
INSERT INTO t2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
1113
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1114
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1115
VARIABLE_NAME VARIABLE_VALUE
1117
HANDLER_EXTERNAL_LOCK 24
1118
HANDLER_READ_FIRST 5
1122
# 24 locks (2 table, 5 + 5 subpartitions lock/unlock)
1124
ALTER TABLE t2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`;
1125
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1126
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1127
VARIABLE_NAME VARIABLE_VALUE
1129
HANDLER_EXTERNAL_LOCK 14
1131
# 14 locks (1 table, 6 subpartitions lock/unlock)
1133
INSERT INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
1134
ERROR HY000: Found a row not matching the given partition set
1135
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1136
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1137
VARIABLE_NAME VARIABLE_VALUE
1138
HANDLER_EXTERNAL_LOCK 16
1139
HANDLER_READ_FIRST 2
1144
# 16 locks (2 tables, 1 + 5 subpartitions lock/unlock)
1146
INSERT IGNORE INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
1147
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1148
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1149
VARIABLE_NAME VARIABLE_VALUE
1151
HANDLER_EXTERNAL_LOCK 16
1152
HANDLER_READ_FIRST 5
1156
# 16 locks (2 tables, 1 + 5 subpartitions lock/unlock)
1159
INSERT INTO t2 SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
1160
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1161
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1162
VARIABLE_NAME VARIABLE_VALUE
1164
HANDLER_EXTERNAL_LOCK 30
1165
HANDLER_READ_FIRST 5
1169
# 30 locks (2 table, 8 + 5 subpartitions lock/unlock)
1171
CREATE TABLE t3 SELECT * FROM t1 PARTITION (pNeg,subp3,`p100-99999`);
1172
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1173
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1174
VARIABLE_NAME VARIABLE_VALUE
1176
HANDLER_EXTERNAL_LOCK 14
1177
HANDLER_READ_FIRST 5
1181
# 14 locks (2 table, 5 subpartitions lock/unlock)
1182
SHOW CREATE TABLE t1;
1184
t1 CREATE TABLE `t1` (
1185
`a` int(11) NOT NULL,
1186
`b` varchar(64) DEFAULT NULL,
1189
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1190
/*!50100 PARTITION BY RANGE (a)
1191
SUBPARTITION BY HASH (a)
1192
(PARTITION pNeg VALUES LESS THAN (0)
1193
(SUBPARTITION subp0 ENGINE = InnoDB,
1194
SUBPARTITION subp1 ENGINE = InnoDB),
1195
PARTITION `p0-9` VALUES LESS THAN (10)
1196
(SUBPARTITION subp2 ENGINE = InnoDB,
1197
SUBPARTITION subp3 ENGINE = InnoDB),
1198
PARTITION `p10-99` VALUES LESS THAN (100)
1199
(SUBPARTITION subp4 ENGINE = InnoDB,
1200
SUBPARTITION subp5 ENGINE = InnoDB),
1201
PARTITION `p100-99999` VALUES LESS THAN (100000)
1202
(SUBPARTITION subp6 ENGINE = InnoDB,
1203
SUBPARTITION subp7 ENGINE = InnoDB)) */
1206
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
1207
-222 `p100-99999`(-subp6), Updated from a = 100
1208
-21 REPLACEd by REPLACE
1212
1000 `p100-99999`(-subp6)
1213
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1214
SHOW CREATE TABLE t2;
1216
t2 CREATE TABLE `t2` (
1217
`a` int(11) NOT NULL,
1218
`b` varchar(64) DEFAULT NULL,
1221
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1222
/*!50100 PARTITION BY RANGE (a)
1223
SUBPARTITION BY HASH (a)
1224
(PARTITION pNeg VALUES LESS THAN (0)
1225
(SUBPARTITION subp0 ENGINE = InnoDB,
1226
SUBPARTITION subp1 ENGINE = InnoDB),
1227
PARTITION `p0-9` VALUES LESS THAN (10)
1228
(SUBPARTITION subp2 ENGINE = InnoDB,
1229
SUBPARTITION subp3 ENGINE = InnoDB),
1230
PARTITION `p10-99` VALUES LESS THAN (100)
1231
(SUBPARTITION subp4 ENGINE = InnoDB,
1232
SUBPARTITION subp5 ENGINE = InnoDB),
1233
PARTITION `p100-99999` VALUES LESS THAN (100000)
1234
(SUBPARTITION subp6 ENGINE = InnoDB,
1235
SUBPARTITION subp7 ENGINE = InnoDB)) */
1241
1000 `p100-99999`(-subp6)
1242
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1243
SHOW CREATE TABLE t3;
1245
t3 CREATE TABLE `t3` (
1246
`a` int(11) NOT NULL,
1247
`b` varchar(64) DEFAULT NULL
1248
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1251
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
1252
-222 `p100-99999`(-subp6), Updated from a = 100
1253
-21 REPLACEd by REPLACE
1256
1000 `p100-99999`(-subp6)
1257
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1259
DELETE t1 PARTITION (pNeg), t3 FROM t1, t3
1260
WHERE t1.a = t3.a AND t3.b = 'subp3';
1261
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 (pNeg), t3 FROM t1, t3
1262
WHERE t1.a = t3.a AND t3.b = 'subp3'' at line 1
1263
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1264
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1265
VARIABLE_NAME VARIABLE_VALUE
1267
# Multi table delete without any matching rows
1269
DELETE t1, t2 FROM t1 PARTITION (pNeg), t3, t2 PARTITION (subp3)
1270
WHERE t1.a = t3.a AND t3.b = 'subp3' AND t3.a = t2.a;
1271
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1272
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1273
VARIABLE_NAME VARIABLE_VALUE
1275
HANDLER_EXTERNAL_LOCK 12
1276
HANDLER_READ_FIRST 1
1278
HANDLER_READ_RND_NEXT 3
1280
# 12 locks (3 in t1, 1 in t3, 2 in t2) x 2 (lock + unlock)
1281
# 1 read first (first rnd_next in t2)
1282
# 4 read key (1 innodb_get_index in rnd_init in t2 + index read in t2
1283
# + 2 innodb_get_index in index_init in t1)
1284
# 3 read rnd next (3 rnd next in t2, 2 rows + 1 empty)
1285
# Multi table delete matching all rows in subp3 (2 rows in per table)
1287
DELETE FROM t2, t3 USING t2 PARTITION (`p0-9`), t3, t1 PARTITION (subp3)
1288
WHERE t1.a = t3.a AND t3.b = 'subp3' AND t2.a = t1.a;
1289
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1290
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1291
VARIABLE_NAME VARIABLE_VALUE
1294
HANDLER_EXTERNAL_LOCK 12
1295
HANDLER_READ_FIRST 3
1299
HANDLER_READ_RND_NEXT 16
1301
# 4 delete (2 in t2 + 2 in t3)
1302
# 12 locks (3 in t2, 1 in t3, 2 in t1) x 2 (lock + unlock)
1303
# 3 read first (1 in t1 + 1 in t3 + 1 in t3, for second row in t1)
1304
# 17 read key (1 index_init in t1 + 1 read first in t1 +
1305
# 2 index_init in t2 + 1 index read in t2 +
1306
# 1 index_init in t3 + 1 index read in t3 +
1307
# 1 index read in t2 +
1308
# 1 index_init in t3 + 1 index read in t3 +
1309
# 2 index_init in t2 + 2 index read in t2 (from rnd_pos)
1310
# 1 index_init in t3 + 2 index read in t3 (from rnd_pos))
1311
# 2 read next (1 in t1 + 1 in t1, second row)
1312
# 4 read rnd (position on 4 found rows to delete)
1313
# 16 rnd next (8 in t3 + 8 in t3, for second row)
1314
SELECT * FROM t1 ORDER BY a;
1316
-222 `p100-99999`(-subp6), Updated from a = 100
1317
-21 REPLACEd by REPLACE
1318
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
1322
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1323
1000 `p100-99999`(-subp6)
1324
SELECT * FROM t2 ORDER BY a;
1327
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1328
1000 `p100-99999`(-subp6)
1329
SELECT * FROM t3 ORDER BY a;
1331
-222 `p100-99999`(-subp6), Updated from a = 100
1332
-21 REPLACEd by REPLACE
1333
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
1334
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
1335
1000 `p100-99999`(-subp6)
1336
# Test TRUNCATE TABLE (should fail, since one should use
1337
# ALTER TABLE ... TRUNCATE PARTITION instead)
1338
TRUNCATE TABLE t1 PARTITION(`p10-99`);
1339
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(`p10-99`)' at line 1
1340
# Test of locking in TRUNCATE PARTITION
1341
# Note that it does not support truncating subpartitions
1343
ALTER TABLE t1 TRUNCATE PARTITION pNeg;
1344
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
1345
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
1346
VARIABLE_NAME VARIABLE_VALUE
1348
HANDLER_EXTERNAL_LOCK 6
1350
# 6 locks (lock/unlock two subpartitions + table)
1351
# Test on non partitioned table
1352
SELECT * FROM t3 PARTITION (pNeg);
1353
ERROR HY000: PARTITION () clause on non partitioned table
1354
DROP TABLE t1, t2, t3;
1355
# Test from superseeded WL# 2682
1356
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
1358
`id` int(11) default NULL
1359
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1360
PARTITION BY RANGE (id) (
1361
PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
1362
PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
1363
PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
1364
PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);
1365
INSERT INTO `t1` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
1366
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
1389
SELECT * FROM t1 PARTITION (p0);
1396
SELECT * FROM t1 PARTITION (p1);
1403
SELECT * FROM t1 PARTITION (p2);
1410
SELECT * FROM t1 PARTITION (p3);
1417
SELECT * FROM t1 PARTITION (p3) WHERE id = 2;
1419
SELECT * FROM t1 PARTITION (foo);
1420
ERROR HY000: Unknown partition 'foo' in table 't1'
1422
`id` int(11) NOT NULL DEFAULT 0,
1424
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1425
PARTITION BY RANGE (id) (
1426
PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
1427
PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
1428
PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
1429
PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);
1430
INSERT INTO `t2` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
1431
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
1454
SELECT * FROM t2 PARTITION (p0);
1461
SELECT * FROM t2 PARTITION (p1);
1468
SELECT * FROM t2 PARTITION (p2);
1475
SELECT * FROM t2 PARTITION (p3);
1482
SELECT * FROM t2 PARTITION (p3) ORDER BY id;
1489
SELECT * FROM t2 PARTITION (p3) WHERE id = 2;
1491
SELECT * FROM t2 PARTITION (foo);
1492
ERROR HY000: Unknown partition 'foo' in table 't2'
1494
`id` int(32) default NULL,
1495
`name` varchar(32) default NULL
1496
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1497
PARTITION BY LIST (id) (
1498
PARTITION p0 VALUES IN (1,3,5,7),
1499
PARTITION p1 VALUES IN (0,2,4,6,8),
1500
PARTITION p2 VALUES IN (9,10,11,12,13)
1502
INSERT INTO `t3` VALUES (1,'first'), (3,'third'),(5,'fifth'),(7,'seventh'),(0,'zilch'),(2,'second'),(4,'fourth'),(6,'sixth'),(8,'eighth'),(9,'ninth'),(10,'tenth'),(11,'eleventh'),(12,'twelfth'),(13,'thirteenth');
1519
SELECT * FROM `t3` PARTITION (p0);
1525
SELECT * FROM `t3` PARTITION (p1);
1532
SELECT * FROM `t3` PARTITION (p2);
1539
SELECT * FROM `t3` PARTITION (p2) ORDER BY id;
1546
DROP TABLE IF EXISTS `t4`;
1548
Note 1051 Unknown table 'test.t4'
1550
`id` int(32) default NULL
1551
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;
1552
INSERT INTO `t4` SELECT * FROM `t2`;
1553
INSERT INTO `t4` SELECT * FROM `t2` ORDER BY id;
1558
PARTITION BY RANGE( YEAR(purchased) )
1559
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
1560
PARTITION p0 VALUES LESS THAN (1990) (
1564
PARTITION p1 VALUES LESS THAN (2000) (
1568
PARTITION p2 VALUES LESS THAN MAXVALUE (
1573
INSERT INTO `t5` VALUES (1, 'aaaaaaa', '2006-01-05 00:00:00');
1574
INSERT INTO `t5` VALUES (2, 'bbbbbbb', '2005-08-05 00:00:00');
1575
INSERT INTO `t5` VALUES (3, 'ccccccc', '1985-08-07 00:00:00');
1576
INSERT INTO `t5` VALUES (4, 'ddddddd', '2000-01-01 00:00:00');
1577
INSERT INTO `t5` VALUES (5, 'eeeeeee', '1999-12-01 00:00:00');
1578
INSERT INTO `t5` VALUES (6, 'fffffff', '2003-11-12 00:00:00');
1579
INSERT INTO `t5` VALUES (7, 'ggggggg', '1990-01-05 00:00:00');
1580
INSERT INTO `t5` VALUES (8, 'hhhhhhh', '1978-01-05 00:00:00');
1581
INSERT INTO `t5` VALUES (9, 'iiiiiii', '1979-01-05 00:00:00');
1582
INSERT INTO `t5` VALUES (10, 'jjjjjjj', '1992-01-05 00:00:00');
1583
INSERT INTO `t5` VALUES (11, 'kkkkkkk', '1993-01-05 00:00:00');
1584
INSERT INTO `t5` VALUES (12, 'mmmmmmm', '1994-01-05 00:00:00');
1585
INSERT INTO `t5` VALUES (13, 'nnnnnnn', '1989-01-05 00:00:00');
1586
INSERT INTO `t5` VALUES (14, 'ooooooo', '1983-12-05 00:00:00');
1587
INSERT INTO `t5` VALUES (15, 'ppppppp', '1986-06-05 00:00:00');
1588
INSERT INTO `t5` VALUES (16, 'qqqqqqq', '1974-04-11 00:00:00');
1589
INSERT INTO `t5` VALUES (17, 'qqqqqqq', '1960-03-15 00:00:00');
1590
INSERT INTO `t5` VALUES (18, 'sssssss', '1950-09-23 00:00:00');
1591
INSERT INTO `t5` VALUES (19, 'ttttttt', '1999-08-02 00:00:00');
1592
INSERT INTO `t5` VALUES (20, 'uuuuuuu', '1994-05-28 00:00:00');
1595
8 hhhhhhh 1978-01-05
1596
13 nnnnnnn 1989-01-05
1597
14 ooooooo 1983-12-05
1598
18 sssssss 1950-09-23
1599
3 ccccccc 1985-08-07
1600
9 iiiiiii 1979-01-05
1601
15 ppppppp 1986-06-05
1602
16 qqqqqqq 1974-04-11
1603
17 qqqqqqq 1960-03-15
1604
5 eeeeeee 1999-12-01
1605
12 mmmmmmm 1994-01-05
1606
7 ggggggg 1990-01-05
1607
10 jjjjjjj 1992-01-05
1608
11 kkkkkkk 1993-01-05
1609
19 ttttttt 1999-08-02
1610
20 uuuuuuu 1994-05-28
1611
2 bbbbbbb 2005-08-05
1612
6 fffffff 2003-11-12
1613
1 aaaaaaa 2006-01-05
1614
4 ddddddd 2000-01-01
1615
SELECT * FROM `t5` PARTITION(p0) ORDER BY id;
1617
3 ccccccc 1985-08-07
1618
8 hhhhhhh 1978-01-05
1619
9 iiiiiii 1979-01-05
1620
13 nnnnnnn 1989-01-05
1621
14 ooooooo 1983-12-05
1622
15 ppppppp 1986-06-05
1623
16 qqqqqqq 1974-04-11
1624
17 qqqqqqq 1960-03-15
1625
18 sssssss 1950-09-23
1626
SELECT * FROM `t5` PARTITION(s0) ORDER BY id;
1628
8 hhhhhhh 1978-01-05
1629
13 nnnnnnn 1989-01-05
1630
14 ooooooo 1983-12-05
1631
18 sssssss 1950-09-23
1632
SELECT * FROM `t5` PARTITION(s1) ORDER BY id;
1634
3 ccccccc 1985-08-07
1635
9 iiiiiii 1979-01-05
1636
15 ppppppp 1986-06-05
1637
16 qqqqqqq 1974-04-11
1638
17 qqqqqqq 1960-03-15
1639
SELECT * FROM `t5` PARTITION(p1) ORDER BY id;
1641
5 eeeeeee 1999-12-01
1642
7 ggggggg 1990-01-05
1643
10 jjjjjjj 1992-01-05
1644
11 kkkkkkk 1993-01-05
1645
12 mmmmmmm 1994-01-05
1646
19 ttttttt 1999-08-02
1647
20 uuuuuuu 1994-05-28
1648
SELECT * FROM `t5` PARTITION(s2) ORDER BY id;
1650
5 eeeeeee 1999-12-01
1651
12 mmmmmmm 1994-01-05
1652
SELECT * FROM `t5` PARTITION(s3) ORDER BY id;
1654
7 ggggggg 1990-01-05
1655
10 jjjjjjj 1992-01-05
1656
11 kkkkkkk 1993-01-05
1657
19 ttttttt 1999-08-02
1658
20 uuuuuuu 1994-05-28
1659
SELECT * FROM `t5` PARTITION(p2) ORDER BY id;
1661
1 aaaaaaa 2006-01-05
1662
2 bbbbbbb 2005-08-05
1663
4 ddddddd 2000-01-01
1664
6 fffffff 2003-11-12
1665
SELECT * FROM `t5` PARTITION(s4) ORDER BY id;
1667
2 bbbbbbb 2005-08-05
1668
6 fffffff 2003-11-12
1669
SELECT * FROM `t5` PARTITION(s5) ORDER BY id;
1671
1 aaaaaaa 2006-01-05
1672
4 ddddddd 2000-01-01
1673
drop table t1,t2,t3,t4,t5;
1674
create table t1 (a int) partition by hash(a) partitions 3;
1675
insert into t1 values(1),(2),(3);
1676
explain partitions select * from t1 where a=1;
1677
id select_type table partitions type possible_keys key key_len ref rows Extra
1678
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
1679
explain partitions select * from t1 partition (p1) where a=1;
1680
id select_type table partitions type possible_keys key key_len ref rows Extra
1681
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
1682
explain partitions select * from t1 partition (p1) where a=1 or a=2;
1683
id select_type table partitions type possible_keys key key_len ref rows Extra
1684
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
1685
explain partitions select * from t1 partition (p2) where a=1;
1686
id select_type table partitions type possible_keys key key_len ref rows Extra
1687
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 0 Using where
1690
# Bug#59864: Crash if table empty: DELETE FROM t2 PARTITION (subp3).
1697
PARTITION BY RANGE (a)
1698
SUBPARTITION BY HASH (a) SUBPARTITIONS 3
1699
(PARTITION pNeg VALUES LESS THAN (0)
1700
(SUBPARTITION subp0,
1702
SUBPARTITION subp2),
1703
PARTITION `p0-29` VALUES LESS THAN (30)
1704
(SUBPARTITION subp3,
1706
SUBPARTITION subp5),
1707
PARTITION `p30-299` VALUES LESS THAN (300)
1708
(SUBPARTITION subp6,
1710
SUBPARTITION subp8),
1711
PARTITION `p300-2999` VALUES LESS THAN (3000)
1712
(SUBPARTITION subp9,
1713
SUBPARTITION subp10,
1714
SUBPARTITION subp11),
1715
PARTITION `p3000-299999` VALUES LESS THAN (300000)
1716
(SUBPARTITION subp12,
1717
SUBPARTITION subp13,
1718
SUBPARTITION subp14));
1719
SHOW CREATE TABLE t1;
1721
t1 CREATE TABLE `t1` (
1722
`a` int(11) NOT NULL,
1723
`b` varchar(64) DEFAULT NULL,
1726
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1727
/*!50100 PARTITION BY RANGE (a)
1728
SUBPARTITION BY HASH (a)
1729
(PARTITION pNeg VALUES LESS THAN (0)
1730
(SUBPARTITION subp0 ENGINE = InnoDB,
1731
SUBPARTITION subp1 ENGINE = InnoDB,
1732
SUBPARTITION subp2 ENGINE = InnoDB),
1733
PARTITION `p0-29` VALUES LESS THAN (30)
1734
(SUBPARTITION subp3 ENGINE = InnoDB,
1735
SUBPARTITION subp4 ENGINE = InnoDB,
1736
SUBPARTITION subp5 ENGINE = InnoDB),
1737
PARTITION `p30-299` VALUES LESS THAN (300)
1738
(SUBPARTITION subp6 ENGINE = InnoDB,
1739
SUBPARTITION subp7 ENGINE = InnoDB,
1740
SUBPARTITION subp8 ENGINE = InnoDB),
1741
PARTITION `p300-2999` VALUES LESS THAN (3000)
1742
(SUBPARTITION subp9 ENGINE = InnoDB,
1743
SUBPARTITION subp10 ENGINE = InnoDB,
1744
SUBPARTITION subp11 ENGINE = InnoDB),
1745
PARTITION `p3000-299999` VALUES LESS THAN (300000)
1746
(SUBPARTITION subp12 ENGINE = InnoDB,
1747
SUBPARTITION subp13 ENGINE = InnoDB,
1748
SUBPARTITION subp14 ENGINE = InnoDB)) */
1749
INSERT INTO t1 VALUES (-9, "negative nine"), (-8, "-8"), (-7, "-7"), (-6, "-6"), (-5, "-5"), (-4, "-4"), (-3, "-3"), (-2, "-2"), (-1, "-1");
1750
INSERT INTO t1 VALUES (9, "nine"), (8, "8"), (7, "7"), (6, "6"), (5, "5"), (4, "4"), (3, "3"), (2, "2"), (1, "1");
1751
INSERT INTO t1 VALUES (39, "Thirty nine"), (38, "38"), (37, "37"), (36, "36"), (35, "35"), (34, "34"), (33, "33"), (32, "32"), (31, "31");
1752
INSERT INTO t1 VALUES (339, "Three hundred thirty nine"), (338, "338"), (337, "337"), (336, "336"), (335, "335"), (334, "334"), (333, "333"), (332, "332"), (331, "331");
1753
INSERT INTO t1 VALUES (3339, "Three thousand three hundred thirty nine"), (3338, "3338"), (3337, "3337"), (3336, "3336"), (3335, "3335"), (3334, "3334"), (3333, "3333"), (3332, "3332"), (3331, "3331");
1782
3339 Three thousand three hundred thirty nine
1788
339 Three hundred thirty nine
1801
SELECT * FROM t1 PARTITION (subp3);
1806
DELETE FROM t1 PARTITION (subp3);
1834
3339 Three thousand three hundred thirty nine
1840
339 Three hundred thirty nine
1851
SELECT * FROM t1 PARTITION (subp3);
1853
DELETE FROM t1 PARTITION (`p0-29`);
1879
3339 Three thousand three hundred thirty nine
1885
339 Three hundred thirty nine
1892
SELECT * FROM t1 PARTITION (`p0-29`);
1894
ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 3;
1895
DELETE FROM t1 PARTITION (p2);
1913
3339 Three thousand three hundred thirty nine
1917
339 Three hundred thirty nine
1922
SELECT * FROM t1 PARTITION (p2);
1926
# Test explicit partition selection on a non partitioned temp table
1928
CREATE TEMPORARY TABLE t1 (a INT);
1929
SELECT * FROM t1 PARTITION(pNonexisting);
1930
ERROR HY000: PARTITION () clause on non partitioned table
1931
DROP TEMPORARY TABLE t1;
1933
# Test CREATE LIKE does not take PARTITION clause
1935
CREATE TABLE t1 (a INT) PARTITION BY HASH (a) PARTITIONS 3;
1936
CREATE TABLE t2 LIKE t1 PARTITION (p0, p2);
1937
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 (p0, p2)' at line 1
1939
SET @@default_storage_engine = @old_default_storage_engine;