2
DROP TABLE IF EXISTS t1;
3
# test without partitioning for reference
5
c1 INT NOT NULL AUTO_INCREMENT,
10
t1 CREATE TABLE `t1` (
11
`c1` int(11) NOT NULL AUTO_INCREMENT,
13
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
14
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
18
INSERT INTO t1 VALUES (2);
19
INSERT INTO t1 VALUES (4);
20
INSERT INTO t1 VALUES (NULL);
21
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
25
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
29
INSERT INTO t1 VALUES (0);
30
INSERT INTO t1 VALUES (5), (16);
31
INSERT INTO t1 VALUES (17);
32
INSERT INTO t1 VALUES (19), (NULL);
33
INSERT INTO t1 VALUES (NULL), (10), (NULL);
34
INSERT INTO t1 VALUES (NULL);
36
INSERT INTO t1 VALUES (NULL);
37
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
38
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
40
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
41
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
45
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
47
Warning 1048 Column 'c1' cannot be null
48
INSERT INTO t1 VALUES (NULL);
49
INSERT INTO t1 VALUES (NULL);
50
SELECT * FROM t1 ORDER BY c1;
68
c1 INT NOT NULL AUTO_INCREMENT,
73
t1 CREATE TABLE `t1` (
74
`c1` int(11) NOT NULL AUTO_INCREMENT,
76
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
80
t1 CREATE TABLE `t1` (
81
`c1` int(11) NOT NULL AUTO_INCREMENT,
83
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
84
INSERT INTO t1 VALUES (4);
88
t1 CREATE TABLE `t1` (
89
`c1` int(11) NOT NULL AUTO_INCREMENT,
91
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
92
INSERT INTO t1 VALUES (NULL);
96
t1 CREATE TABLE `t1` (
97
`c1` int(11) NOT NULL AUTO_INCREMENT,
99
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
101
INSERT INTO t1 VALUES (NULL);
102
SHOW CREATE TABLE t1;
104
t1 CREATE TABLE `t1` (
105
`c1` int(11) NOT NULL AUTO_INCREMENT,
107
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
108
SELECT * FROM t1 ORDER BY c1;
112
INSERT INTO t1 VALUES (NULL);
113
SHOW CREATE TABLE t1;
115
t1 CREATE TABLE `t1` (
116
`c1` int(11) NOT NULL AUTO_INCREMENT,
118
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
119
SELECT * FROM t1 ORDER BY c1;
122
INSERT INTO t1 VALUES (100);
123
INSERT INTO t1 VALUES (NULL);
124
DELETE FROM t1 WHERE c1 >= 100;
126
Table Op Msg_type Msg_text
127
test.t1 optimize note The storage engine for the table doesn't support optimize
128
SHOW CREATE TABLE t1;
130
t1 CREATE TABLE `t1` (
131
`c1` int(11) NOT NULL AUTO_INCREMENT,
133
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
135
# Simple test with NULL
137
c1 INT NOT NULL AUTO_INCREMENT,
140
PARTITION BY HASH(c1)
142
INSERT INTO t1 VALUES (NULL);
143
SHOW CREATE TABLE t1;
145
t1 CREATE TABLE `t1` (
146
`c1` int(11) NOT NULL AUTO_INCREMENT,
148
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
149
/*!50100 PARTITION BY HASH (c1)
155
# Test with sql_mode and first insert as 0
158
c2 INT NOT NULL AUTO_INCREMENT,
161
PARTITION BY HASH(c2)
163
INSERT INTO t1 VALUES (1, NULL);
164
INSERT INTO t1 VALUES (1, 1), (99, 99);
165
INSERT INTO t1 VALUES (1, NULL);
166
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
167
INSERT INTO t1 VALUES (1, 0);
168
SELECT * FROM t1 ORDER BY c1, c2;
176
c2 INT NOT NULL AUTO_INCREMENT,
179
PARTITION BY HASH(c2)
181
INSERT INTO t1 VALUES (1, 0);
182
INSERT INTO t1 VALUES (1, 1), (1, NULL);
183
INSERT INTO t1 VALUES (2, NULL), (4, 7);
184
INSERT INTO t1 VALUES (1, NULL);
185
SELECT * FROM t1 ORDER BY c1, c2;
193
SET @@session.sql_mode = '';
195
# Simple test with NULL, 0 and explicit values both incr. and desc.
197
c1 INT NOT NULL AUTO_INCREMENT,
200
PARTITION BY HASH(c1)
202
INSERT INTO t1 VALUES (2), (4), (NULL);
203
INSERT INTO t1 VALUES (0);
204
INSERT INTO t1 VALUES (5), (16);
205
INSERT INTO t1 VALUES (17), (19), (NULL);
206
INSERT INTO t1 VALUES (NULL), (10), (NULL);
207
INSERT INTO t1 VALUES (NULL), (9);
208
INSERT INTO t1 VALUES (59), (55);
209
INSERT INTO t1 VALUES (NULL), (90);
210
INSERT INTO t1 VALUES (NULL);
211
UPDATE t1 SET c1 = 150 WHERE c1 = 17;
212
UPDATE t1 SET c1 = 151 WHERE c1 = 19;
214
UPDATE t1 SET c1 = 140 WHERE c1 = 150;
215
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
219
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
221
Warning 1048 Column 'c1' cannot be null
222
INSERT INTO t1 VALUES (NULL);
223
INSERT INTO t1 VALUES (NULL);
224
SELECT * FROM t1 ORDER BY c1;
246
# Test with auto_increment_increment and auto_increment_offset.
248
c1 INT NOT NULL AUTO_INCREMENT,
251
PARTITION BY HASH(c1)
253
SET @@session.auto_increment_increment = 10;
254
SET @@session.auto_increment_offset = 5;
255
INSERT INTO t1 VALUES (1);
256
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
257
SET @@session.auto_increment_increment = 5;
258
SET @@session.auto_increment_offset = 3;
259
INSERT INTO t1 VALUES (NULL);
260
INSERT INTO t1 VALUES (33 + 1);
261
INSERT INTO t1 VALUES (NULL);
262
INSERT INTO t1 VALUES (38 + 2);
263
INSERT INTO t1 VALUES (NULL);
264
INSERT INTO t1 VALUES (43 + 3);
265
INSERT INTO t1 VALUES (NULL);
266
INSERT INTO t1 VALUES (48 + 4);
267
INSERT INTO t1 VALUES (NULL);
268
INSERT INTO t1 VALUES (53 + 5);
269
INSERT INTO t1 VALUES (NULL);
270
INSERT INTO t1 VALUES (63 + 6);
271
INSERT INTO t1 VALUES (NULL);
272
SET @@session.auto_increment_increment = 1;
273
SET @@session.auto_increment_offset = 1;
274
SELECT * FROM t1 ORDER BY c1;
294
# Test reported auto_increment value
296
c1 INT NOT NULL AUTO_INCREMENT,
299
PARTITION BY HASH (c1)
301
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
305
INSERT INTO t1 VALUES (2);
306
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
310
INSERT INTO t1 VALUES (4);
311
INSERT INTO t1 VALUES (NULL);
312
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
316
INSERT INTO t1 VALUES (NULL);
317
INSERT INTO t1 VALUES (17);
318
INSERT INTO t1 VALUES (19);
319
INSERT INTO t1 VALUES (NULL);
320
INSERT INTO t1 VALUES (NULL);
321
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
325
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
329
INSERT INTO t1 VALUES (10);
330
SELECT * FROM t1 ORDER BY c1;
341
INSERT INTO t1 VALUES (NULL);
342
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
346
INSERT INTO t1 VALUES (NULL);
347
INSERT INTO t1 VALUES (15);
348
INSERT INTO t1 VALUES (NULL);
349
SELECT * FROM t1 ORDER BY c1;
364
INSERT INTO t1 VALUES (NULL);
366
INSERT INTO t1 VALUES (NULL);
367
SHOW CREATE TABLE t1;
369
t1 CREATE TABLE `t1` (
370
`c1` int(11) NOT NULL AUTO_INCREMENT,
372
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
373
/*!50100 PARTITION BY HASH (c1)
375
SELECT * FROM t1 ORDER BY c1;
379
INSERT INTO t1 VALUES (NULL);
380
SHOW CREATE TABLE t1;
382
t1 CREATE TABLE `t1` (
383
`c1` int(11) NOT NULL AUTO_INCREMENT,
385
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
386
/*!50100 PARTITION BY HASH (c1)
388
SELECT * FROM t1 ORDER BY c1;
391
INSERT INTO t1 VALUES (100);
392
INSERT INTO t1 VALUES (NULL);
393
DELETE FROM t1 WHERE c1 >= 100;
395
Table Op Msg_type Msg_text
396
test.t1 optimize note The storage engine for the table doesn't support optimize
397
SHOW CREATE TABLE t1;
399
t1 CREATE TABLE `t1` (
400
`c1` int(11) NOT NULL AUTO_INCREMENT,
402
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
403
/*!50100 PARTITION BY HASH (c1)
406
# Test with two threads
408
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
410
PARTITION BY HASH(c1)
412
INSERT INTO t1 (c1) VALUES (2);
413
INSERT INTO t1 (c1) VALUES (4);
415
INSERT INTO t1 (c1) VALUES (NULL);
416
INSERT INTO t1 (c1) VALUES (10);
418
INSERT INTO t1 (c1) VALUES (NULL);
419
INSERT INTO t1 (c1) VALUES (NULL);
420
INSERT INTO t1 (c1) VALUES (19);
421
INSERT INTO t1 (c1) VALUES (21);
423
INSERT INTO t1 (c1) VALUES (NULL);
425
INSERT INTO t1 (c1) VALUES (16);
427
INSERT INTO t1 (c1) VALUES (NULL);
429
INSERT INTO t1 (c1) VALUES (NULL);
430
SELECT * FROM t1 ORDER BY c1;
445
# Test with two threads + start transaction NO PARTITIONING
447
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
450
INSERT INTO t1 (c1) VALUES (2);
451
INSERT INTO t1 (c1) VALUES (4);
454
INSERT INTO t1 (c1) VALUES (NULL);
455
INSERT INTO t1 (c1) VALUES (10);
457
INSERT INTO t1 (c1) VALUES (NULL);
458
INSERT INTO t1 (c1) VALUES (NULL);
459
INSERT INTO t1 (c1) VALUES (19);
460
INSERT INTO t1 (c1) VALUES (21);
462
INSERT INTO t1 (c1) VALUES (NULL);
464
INSERT INTO t1 (c1) VALUES (16);
466
INSERT INTO t1 (c1) VALUES (NULL);
467
SELECT * FROM t1 ORDER BY c1;
474
SELECT * FROM t1 ORDER BY c1;
481
INSERT INTO t1 (c1) VALUES (NULL);
482
SELECT * FROM t1 ORDER BY c1;
497
SELECT * FROM t1 ORDER BY c1;
512
# Test with two threads + start transaction
514
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
516
PARTITION BY HASH(c1)
519
INSERT INTO t1 (c1) VALUES (2);
520
INSERT INTO t1 (c1) VALUES (4);
523
INSERT INTO t1 (c1) VALUES (NULL), (10);
525
INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
526
INSERT INTO t1 (c1) VALUES (21);
528
INSERT INTO t1 (c1) VALUES (NULL);
530
INSERT INTO t1 (c1) VALUES (16);
532
INSERT INTO t1 (c1) VALUES (NULL);
533
SELECT * FROM t1 ORDER BY c1;
540
SELECT * FROM t1 ORDER BY c1;
547
INSERT INTO t1 (c1) VALUES (NULL);
548
SELECT * FROM t1 ORDER BY c1;
563
SELECT * FROM t1 ORDER BY c1;
578
# Test with another column after
580
c1 INT NOT NULL AUTO_INCREMENT,
584
PARTITION BY HASH(c2)
586
INSERT INTO t1 VALUES (1, 0);
587
INSERT INTO t1 VALUES (1, 1);
588
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
589
INSERT INTO t1 VALUES (NULL, 3);
590
INSERT INTO t1 VALUES (2, 0), (NULL, 2);
591
INSERT INTO t1 VALUES (2, 2);
592
INSERT INTO t1 VALUES (2, 22);
593
INSERT INTO t1 VALUES (NULL, 2);
594
SELECT * FROM t1 ORDER BY c1,c2;
608
# Test with another column before
611
c2 INT NOT NULL AUTO_INCREMENT,
614
PARTITION BY HASH(c2)
616
INSERT INTO t1 VALUES (1, 0);
617
INSERT INTO t1 VALUES (1, 1);
618
INSERT INTO t1 VALUES (1, NULL);
619
INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
620
INSERT INTO t1 VALUES (2, NULL);
621
INSERT INTO t1 VALUES (2, 2);
622
INSERT INTO t1 VALUES (2, 22);
623
INSERT INTO t1 VALUES (2, NULL);
624
SELECT * FROM t1 ORDER BY c1,c2;
636
# Test with auto_increment on secondary column in multi-column-index
639
c2 INT NOT NULL AUTO_INCREMENT,
642
PARTITION BY HASH(c2)
644
INSERT INTO t1 VALUES (1, 0);
645
INSERT INTO t1 VALUES (1, 1);
646
INSERT INTO t1 VALUES (1, NULL);
647
INSERT INTO t1 VALUES (2, NULL);
648
INSERT INTO t1 VALUES (3, NULL);
649
INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
650
INSERT INTO t1 VALUES (2, 2);
651
# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY
653
INSERT INTO t1 VALUES (2, 22), (2, NULL);
654
SELECT * FROM t1 ORDER BY c1,c2;
667
# Test AUTO_INCREMENT in CREATE
668
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
671
PARTITION BY HASH(c1)
673
SHOW CREATE TABLE t1;
675
t1 CREATE TABLE `t1` (
676
`c1` int(11) NOT NULL AUTO_INCREMENT,
678
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
679
/*!50100 PARTITION BY HASH (c1)
681
INSERT INTO t1 (c1) VALUES (4);
682
SHOW CREATE TABLE t1;
684
t1 CREATE TABLE `t1` (
685
`c1` int(11) NOT NULL AUTO_INCREMENT,
687
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
688
/*!50100 PARTITION BY HASH (c1)
690
INSERT INTO t1 (c1) VALUES (0);
691
SHOW CREATE TABLE t1;
693
t1 CREATE TABLE `t1` (
694
`c1` int(11) NOT NULL AUTO_INCREMENT,
696
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
697
/*!50100 PARTITION BY HASH (c1)
699
INSERT INTO t1 (c1) VALUES (NULL);
700
SHOW CREATE TABLE t1;
702
t1 CREATE TABLE `t1` (
703
`c1` int(11) NOT NULL AUTO_INCREMENT,
705
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
706
/*!50100 PARTITION BY HASH (c1)
708
SELECT * FROM t1 ORDER BY c1;
713
# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
714
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
715
INSERT INTO t1 (c1) VALUES (300);
716
SHOW CREATE TABLE t1;
718
t1 CREATE TABLE `t1` (
719
`c1` int(11) NOT NULL AUTO_INCREMENT,
721
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
722
/*!50100 PARTITION BY HASH (c1)
724
INSERT INTO t1 (c1) VALUES (0);
725
SHOW CREATE TABLE t1;
727
t1 CREATE TABLE `t1` (
728
`c1` int(11) NOT NULL AUTO_INCREMENT,
730
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
731
/*!50100 PARTITION BY HASH (c1)
733
INSERT INTO t1 (c1) VALUES (NULL);
734
SHOW CREATE TABLE t1;
736
t1 CREATE TABLE `t1` (
737
`c1` int(11) NOT NULL AUTO_INCREMENT,
739
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
740
/*!50100 PARTITION BY HASH (c1)
742
SELECT * FROM t1 ORDER BY c1;
750
SET @@session.sql_mode = '';
753
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
755
PARTITION BY HASH(c1)
757
SHOW CREATE TABLE t1;
759
t1 CREATE TABLE `t1` (
760
`c1` int(11) NOT NULL AUTO_INCREMENT,
762
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
763
/*!50100 PARTITION BY HASH (c1)
765
INSERT INTO t1 (c1) VALUES (NULL);
766
SHOW CREATE TABLE t1;
768
t1 CREATE TABLE `t1` (
769
`c1` int(11) NOT NULL AUTO_INCREMENT,
771
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
772
/*!50100 PARTITION BY HASH (c1)
778
SHOW CREATE TABLE t1;
780
t1 CREATE TABLE `t1` (
781
`c1` int(11) NOT NULL AUTO_INCREMENT,
783
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
784
/*!50100 PARTITION BY HASH (c1)
786
INSERT INTO t1 (c1) VALUES (NULL);
787
SHOW CREATE TABLE t1;
789
t1 CREATE TABLE `t1` (
790
`c1` int(11) NOT NULL AUTO_INCREMENT,
792
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
793
/*!50100 PARTITION BY HASH (c1)
795
SELECT * FROM t1 ORDER BY c1;
800
# Testing with FLUSH TABLE
802
c1 INT NOT NULL AUTO_INCREMENT,
805
PARTITION BY HASH(c1)
807
SHOW CREATE TABLE t1;
809
t1 CREATE TABLE `t1` (
810
`c1` int(11) NOT NULL AUTO_INCREMENT,
812
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
813
/*!50100 PARTITION BY HASH (c1)
816
SHOW CREATE TABLE t1;
818
t1 CREATE TABLE `t1` (
819
`c1` int(11) NOT NULL AUTO_INCREMENT,
821
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
822
/*!50100 PARTITION BY HASH (c1)
824
INSERT INTO t1 VALUES (4);
826
SHOW CREATE TABLE t1;
828
t1 CREATE TABLE `t1` (
829
`c1` int(11) NOT NULL AUTO_INCREMENT,
831
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
832
/*!50100 PARTITION BY HASH (c1)
834
INSERT INTO t1 VALUES (NULL);
836
SHOW CREATE TABLE t1;
838
t1 CREATE TABLE `t1` (
839
`c1` int(11) NOT NULL AUTO_INCREMENT,
841
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
842
/*!50100 PARTITION BY HASH (c1)
844
SELECT * FROM t1 ORDER BY c1;
849
#############################################################################
850
# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
851
# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
852
##############################################################################
853
# Inserting negative autoincrement values into a partition table (partitions >= 4)
854
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
855
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
856
INSERT INTO t(c2) VALUES (10);
857
INSERT INTO t(c2) VALUES (20);
858
INSERT INTO t VALUES (-1,-10);
859
INSERT INTO t(c2) VALUES (30);
860
INSERT INTO t(c2) VALUES (40);
861
SELECT * FROM t ORDER BY c1 ASC;
869
# Reading from a partition table (partitions >= 2 ) after inserting a negative
870
# value into the auto increment column
871
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
872
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 2;
873
INSERT INTO t VALUES (-2,-20);
874
INSERT INTO t(c2) VALUES (30);
875
SELECT * FROM t ORDER BY c1 ASC;
880
# Inserting negative auto increment value into a partition table (partitions >= 2)
881
# auto increment value > 2.
882
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
883
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 2;
884
INSERT INTO t VALUES (-4,-20);
885
INSERT INTO t(c2) VALUES (30);
886
INSERT INTO t(c2) VALUES (40);
887
SELECT * FROM t ORDER BY c1 ASC;
893
# Inserting -1 into autoincrement column of a partition table (partition >= 4)
894
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
895
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
896
INSERT INTO t(c2) VALUES (10);
897
INSERT INTO t(c2) VALUES (20);
898
INSERT INTO t VALUES (-1,-10);
899
SELECT * FROM t ORDER BY c1 ASC;
904
INSERT INTO t(c2) VALUES (30);
905
SELECT * FROM t ORDER BY c1 ASC;
912
# Deleting from an auto increment table after inserting negative values
913
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
914
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
915
INSERT INTO t(c2) VALUES (10);
916
INSERT INTO t(c2) VALUES (20);
917
INSERT INTO t VALUES (-1,-10);
918
INSERT INTO t(c2) VALUES (30);
919
INSERT INTO t VALUES (-3,-20);
920
INSERT INTO t(c2) VALUES (40);
921
SELECT * FROM t ORDER BY c1 ASC;
929
DELETE FROM t WHERE c1 > 1;
930
SELECT * FROM t ORDER BY c1 ASC;
936
# Inserting a positive value that exceeds maximum allowed value for an
937
# Auto Increment column (positive maximum)
938
CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
939
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
940
INSERT INTO t(c2) VALUES (10);
941
INSERT INTO t(c2) VALUES (20);
942
INSERT INTO t VALUES (126,30);
943
INSERT INTO t VALUES (127,40);
944
INSERT INTO t VALUES (128,50);
945
ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
946
INSERT INTO t VALUES (129,60);
947
ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
948
SELECT * FROM t ORDER BY c1 ASC;
955
# Inserting a negative value that goes below minimum allowed value for an
956
# Auto Increment column (negative minimum)
957
CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
958
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
959
INSERT INTO t(c2) VALUES (10);
960
INSERT INTO t(c2) VALUES (20);
961
INSERT INTO t VALUES (-127,30);
962
INSERT INTO t VALUES (-128,40);
963
INSERT INTO t VALUES (-129,50);
964
ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
965
INSERT INTO t VALUES (-130,60);
966
ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
967
SELECT * FROM t ORDER BY c1 ASC;
974
# Updating the partition table with a negative Auto Increment value
975
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
976
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
977
INSERT INTO t(c2) VALUES (10);
978
INSERT INTO t(c2) VALUES (20);
979
INSERT INTO t VALUES (-1,-10);
980
INSERT INTO t(c2) VALUES (30);
981
SELECT * FROM t ORDER BY c1 ASC;
987
UPDATE t SET c1 = -6 WHERE c1 = 2;
988
SELECT * FROM t ORDER BY c1 ASC;
994
INSERT INTO t(c2) VALUES (40);
995
INSERT INTO t(c2) VALUES (50);
996
UPDATE t SET c1 = -6 WHERE c1 = 2;
997
SELECT * FROM t ORDER BY c1 ASC;
1006
# Updating the partition table with a value that crosses the upper limits
1007
# on both the positive and the negative side.
1008
CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1009
c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4;
1010
INSERT INTO t(c2) VALUES (10);
1011
INSERT INTO t(c2) VALUES (20);
1012
INSERT INTO t VALUES (126,30);
1013
INSERT INTO t VALUES (127,40);
1014
SELECT * FROM t ORDER BY c1 ASC;
1020
UPDATE t SET c1 = 130 where c1 = 127;
1022
Warning 1264 Out of range value for column 'c1' at row 1
1023
SELECT * FROM t ORDER BY c1 ASC;
1029
UPDATE t SET c1 = -140 where c1 = 126;
1031
Warning 1264 Out of range value for column 'c1' at row 1
1032
SELECT * FROM t ORDER BY c1 ASC;
1039
##############################################################################