/
rocksdb.test
1798 lines (1481 loc) · 47.8 KB
/
rocksdb.test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
--source include/have_rocksdb.inc
#
# RocksDB Storage Engine tests
#
select ENGINE,COMMENT,TRANSACTIONS,XA,SAVEPOINTS from information_schema.engines where engine = 'rocksdb';
--disable_warnings
drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
drop table if exists t11,t12,t13,t14,t15,t16,t17,t18,t19,t20;
drop table if exists t21,t22,t23,t24,t25,t26,t27,t28,t29;
drop table if exists t30,t31,t32,t33,t34,t35,t36,t37,t38,t39;
drop table if exists t40,t41,t42,t43,t44,t45,t46,t47,t48,t49;
--enable_warnings
# Disable background compaction to prevent stats from affect explain output
SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
--echo #
--echo # Issue #1: Don't update indexes if index values have not changed
--echo #
# [Jay Edgar] I moved this test first because it uses the
# rocksdb_number_keys_written value, but this value is affected out of band
# by drop tables. There is a background thread that periodically processes
# through the list of dropped keys and if any are gone from the database it
# deletes information related to the key - and this delete causes this count
# to be incorrect. I moved this test first and made the whole test require
# a fresh server to hopefully avoid tihs.
create table t1 (
pk int primary key,
a int,
b int,
key(a)
) engine=rocksdb;
insert into t1 values
(1,1,1), (2,2,2), (3,3,3), (4,4,4);
set @var1=(select variable_value
from information_schema.global_status
where variable_name='rocksdb_number_keys_written');
--echo # Do an update that doesn't change the key 'a'.
update t1 set b=3334341 where a=2;
set @var2=(select variable_value
from information_schema.global_status
where variable_name='rocksdb_number_keys_written');
--echo # The following should produce 1
select @var2 - @var1;
--echo # Do an update that sets the key to the same value
update t1 set a=pk where a=3;
set @var3=(select variable_value
from information_schema.global_status
where variable_name='rocksdb_number_keys_written');
--echo # We have 'updated' column to the same value, so the following must return 0:
select @var3 - @var2;
drop table t1;
create table t0 (a int primary key) engine=rocksdb;
show create table t0;
drop table t0;
--echo # Try creating a table without PK:
--error ER_REQUIRES_PRIMARY_KEY
create table t1(a int) engine=rocksdb;
create table t1 (a int primary key, b int) engine=rocksdb;
insert into t1 values (1,1);
insert into t1 values (2,2);
select * from t1;
--echo # Check that we can create another table and insert there
create table t2 (a varchar(10) primary key, b varchar(10)) engine=rocksdb;
insert into t2 value ('abc','def');
insert into t2 value ('hijkl','mnopq');
select * from t2;
--echo # Select again from t1 to see that records from different tables dont mix
select * from t1;
explain select * from t2 where a='no-such-key';
--replace_column 9 #
explain select * from t2 where a='abc';
select * from t2 where a='abc';
--echo # Try a composite PK
create table t3 (
pk1 int,
pk2 varchar(10),
col1 varchar(10),
primary key(pk1, pk2)
) engine=rocksdb;
insert into t3 values (2,'two', 'row#2');
insert into t3 values (3,'three', 'row#3');
insert into t3 values (1,'one', 'row#1');
select * from t3;
select * from t3 where pk1=3 and pk2='three';
drop table t1, t2, t3;
--echo #
--echo # Test blob values
--echo #
create table t4 (a int primary key, b blob) engine=rocksdb;
insert into t4 values (1, repeat('quux-quux', 60));
insert into t4 values (10, repeat('foo-bar', 43));
insert into t4 values (5, repeat('foo-bar', 200));
insert into t4 values (2, NULL);
select
a,
(case a
when 1 then b=repeat('quux-quux', 60)
when 10 then b=repeat('foo-bar', 43)
when 5 then b=repeat('foo-bar', 200)
when 2 then b is null
else 'IMPOSSIBLE!' end) as CMP
from t4;
drop table t4;
--echo #
--echo # Test blobs of various sizes
--echo #
--echo # TINYBLOB
create table t5 (a int primary key, b tinyblob) engine=rocksdb;
insert into t5 values (1, repeat('quux-quux', 6));
insert into t5 values (10, repeat('foo-bar', 4));
insert into t5 values (5, repeat('foo-bar', 2));
select
a,
(case a
when 1 then b=repeat('quux-quux', 6)
when 10 then b=repeat('foo-bar', 4)
when 5 then b=repeat('foo-bar', 2)
else 'IMPOSSIBLE!' end) as CMP
from t5;
drop table t5;
--echo # MEDIUMBLOB
create table t6 (a int primary key, b mediumblob) engine=rocksdb;
insert into t6 values (1, repeat('AB', 65000));
insert into t6 values (10, repeat('bbb', 40000));
insert into t6 values (5, repeat('foo-bar', 2));
select
a,
(case a
when 1 then b=repeat('AB', 65000)
when 10 then b=repeat('bbb', 40000)
when 5 then b=repeat('foo-bar', 2)
else 'IMPOSSIBLE!' end) as CMP
from t6;
drop table t6;
--echo # LONGBLOB
create table t7 (a int primary key, b longblob) engine=rocksdb;
insert into t7 values (1, repeat('AB', 65000));
insert into t7 values (10, repeat('bbb', 40000));
insert into t7 values (5, repeat('foo-bar', 2));
select
a,
(case a
when 1 then b=repeat('AB', 65000)
when 10 then b=repeat('bbb', 40000)
when 5 then b=repeat('foo-bar', 2)
else 'IMPOSSIBLE!' end) as CMP
from t7;
drop table t7;
--echo #
--echo # Check if DELETEs work
--echo #
create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=rocksdb;
insert into t8 values
('one', 'eins'),
('two', 'zwei'),
('three', 'drei'),
('four', 'vier'),
('five', 'funf');
--echo # Delete by PK
--replace_column 9 #
explain delete from t8 where a='three';
delete from t8 where a='three';
select * from t8;
--echo # Delete while doing a full table scan
delete from t8 where col1='eins' or col1='vier';
select * from t8;
--echo # delete w/o WHERE:
delete from t8;
select * from t8;
--echo #
--echo # Test UPDATEs
--echo #
insert into t8 values
('one', 'eins'),
('two', 'zwei'),
('three', 'drei'),
('four', 'vier'),
('five', 'funf');
update t8 set col1='dva' where a='two';
update t8 set a='fourAAA' where col1='vier';
select * from t8;
delete from t8;
--echo #
--echo # Basic transactions tests
--echo #
begin;
insert into t8 values ('trx1-val1', 'data');
insert into t8 values ('trx1-val2', 'data');
rollback;
select * from t8;
begin;
insert into t8 values ('trx1-val1', 'data');
insert into t8 values ('trx1-val2', 'data');
commit;
select * from t8;
drop table t8;
--echo #
--echo # Check if DROP TABLE works
--echo #
create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=rocksdb;
select * from t8;
insert into t8 values ('foo','foo');
drop table t8;
create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=rocksdb;
select * from t8;
drop table t8;
--echo #
--echo # MDEV-3961: Assertion ... on creating a TEMPORARY RocksDB table
--echo #
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TEMPORARY TABLE t10 (pk INT PRIMARY KEY) ENGINE=RocksDB;
--echo #
--echo # MDEV-3963: JOIN or WHERE conditions involving keys on RocksDB tables don't work
--echo #
CREATE TABLE t10 (i INT PRIMARY KEY) ENGINE=RocksDB;
INSERT INTO t10 VALUES (1),(3);
CREATE TABLE t11 (j INT PRIMARY KEY) ENGINE=RocksDB;
INSERT INTO t11 VALUES (1),(4);
select * from t10;
select * from t11;
--replace_column 9 #
EXPLAIN
SELECT * FROM t10, t11 WHERE i=j;
SELECT * FROM t10, t11 WHERE i=j;
DROP TABLE t10,t11;
--echo #
--echo # MDEV-3962: SELECT with ORDER BY causes "ERROR 1030 (HY000): Got error 122
--echo #
CREATE TABLE t12 (pk INT PRIMARY KEY) ENGINE=RocksDB;
INSERT INTO t12 VALUES (2),(1);
SELECT * FROM t12 ORDER BY pk;
DROP TABLE t12;
--echo #
--echo # MDEV-3964: Assertion `!pk_descr' fails in ha_rocksdb::open on adding partitions ...
--echo #
create table t14 (pk int primary key) engine=RocksDB partition by hash(pk) partitions 2;
#--error ER_GET_ERRNO
#alter table t14 add partition partitions 2;
# ^^ works, but causes weird warnings in error log.
drop table t14;
--echo #
--echo # MDEV-3960: Server crashes on running DISCARD TABLESPACE on a RocksDB table
--echo #
create table t9 (i int primary key) engine=rocksdb;
--error ER_ILLEGAL_HA
alter table t9 discard tablespace;
drop table t9;
--echo #
--echo # MDEV-3959: Assertion `slice->size() == table->s->reclength' fails ...
--echo # on accessing a table after ALTER
--echo #
CREATE TABLE t15 (a INT, rocksdb_pk INT PRIMARY KEY) ENGINE=RocksDB;
INSERT INTO t15 VALUES (1,1),(5,2);
#--error ER_ILLEGAL_HA
ALTER TABLE t15 DROP COLUMN a;
DROP TABLE t15;
--echo #
--echo # MDEV-3968: UPDATE produces a wrong result while modifying a PK on a RocksDB table
--echo #
create table t16 (pk int primary key, a char(8)) engine=RocksDB;
insert into t16 values (1,'a'),(2,'b'),(3,'c'),(4,'d');
#
# Not anymore: The following query will still eat a record because of CANT-SEE-OWN-CHANGES
# property.
#
--error ER_DUP_ENTRY
update t16 set pk=100, a = 'updated' where a in ('b','c');
select * from t16;
drop table t16;
--echo #
--echo # MDEV-3970: A set of assorted crashes on inserting a row into a RocksDB table
--echo #
--disable_warnings
drop table if exists t_very_long_table_name;
--enable_warnings
CREATE TABLE `t_very_long_table_name` (
`c` char(1) NOT NULL,
`c0` char(0) NOT NULL,
`c1` char(1) NOT NULL,
`c20` char(20) NOT NULL,
`c255` char(255) NOT NULL,
PRIMARY KEY (`c255`)
) ENGINE=RocksDB DEFAULT CHARSET=latin1;
INSERT INTO t_very_long_table_name VALUES ('a', '', 'c', REPEAT('a',20), REPEAT('x',255));
drop table t_very_long_table_name;
--echo #
--echo # Test table locking and read-before-write checks.
--echo #
create table t17 (pk varchar(12) primary key, col1 varchar(12)) engine=rocksdb;
insert into t17 values ('row1', 'val1');
--error ER_DUP_ENTRY
insert into t17 values ('row1', 'val1-try2');
--error ER_DUP_ENTRY
insert into t17 values ('ROW1', 'val1-try2');
insert into t17 values ('row2', 'val2');
insert into t17 values ('row3', 'val3');
--echo # This is ok
update t17 set pk='row4' where pk='row1';
--echo # This will try to overwrite another row:
--error ER_DUP_ENTRY
update t17 set pk='row3' where pk='row2';
select * from t17;
--echo #
--echo # Locking tests
--echo #
connect (con1,localhost,root,,);
--echo # First, make sure there's no locking when transactions update different rows
connection con1;
set autocommit=0;
update t17 set col1='UPD1' where pk='row2';
connection default;
update t17 set col1='UPD2' where pk='row3';
connection con1;
commit;
connection default;
select * from t17;
--echo # Check the variable
show variables like 'rocksdb_lock_wait_timeout';
set rocksdb_lock_wait_timeout=2; # seconds
show variables like 'rocksdb_lock_wait_timeout';
--echo # Try updating the same row from two transactions
connection con1;
begin;
update t17 set col1='UPD2-AA' where pk='row2';
connection default;
--error ER_LOCK_WAIT_TIMEOUT
update t17 set col1='UPD2-BB' where pk='row2';
set rocksdb_lock_wait_timeout=1000; # seconds
--send
update t17 set col1='UPD2-CC' where pk='row2';
connection con1;
rollback;
connection default;
reap;
select * from t17 where pk='row2';
drop table t17;
disconnect con1;
--echo #
--echo # MDEV-4035: RocksDB: SELECT produces different results inside a transaction (read is not repeatable)
--echo #
--enable_connect_log
create table t18 (pk int primary key, i int) engine=RocksDB;
begin;
select * from t18;
select * from t18 where pk = 1;
--connect (con1,localhost,root,,)
insert into t18 values (1,100);
--connection default
select * from t18;
select * from t18 where pk = 1;
commit;
drop table t18;
--echo #
--echo # MDEV-4036: RocksDB: INSERT .. ON DUPLICATE KEY UPDATE does not work, produces ER_DUP_KEY
--echo #
create table t19 (pk int primary key, i int) engine=RocksDB;
insert into t19 values (1,1);
insert into t19 values (1,100) on duplicate key update i = 102;
select * from t19;
drop table t19;
--echo # MDEV-4037: RocksDB: REPLACE doesn't work, produces ER_DUP_KEY
create table t20 (pk int primary key, i int) engine=RocksDB;
insert into t20 values (1,1);
replace into t20 values (1,100);
select * from t20;
drop table t20;
--echo #
--echo # MDEV-4041: Server crashes in Primary_key_comparator::get_hashnr on INSERT
--echo #
create table t21 (v varbinary(16) primary key, i int) engine=RocksDB;
insert into t21 values ('a',1);
select * from t21;
drop table t21;
--echo #
--echo # MDEV-4047: RocksDB: Assertion `0' fails in Protocol::end_statement() on multi-table INSERT IGNORE
--echo #
CREATE TABLE t22 (a int primary key) ENGINE=RocksDB;
INSERT INTO t22 VALUES (1),(2);
CREATE TABLE t23 (b int primary key) ENGINE=RocksDB;
INSERT INTO t23 SELECT * FROM t22;
DELETE IGNORE t22.*, t23.* FROM t22, t23 WHERE b < a;
DROP TABLE t22,t23;
--echo #
--echo # MDEV-4046: RocksDB: Multi-table DELETE locks itself and ends with ER_LOCK_WAIT_TIMEOUT
--echo #
CREATE TABLE t24 (pk int primary key) ENGINE=RocksDB;
INSERT INTO t24 VALUES (1),(2);
CREATE TABLE t25 LIKE t24;
INSERT INTO t25 SELECT * FROM t24;
DELETE t25.* FROM t24, t25;
DROP TABLE t24,t25;
--echo #
--echo # MDEV-4044: RocksDB: UPDATE or DELETE with ORDER BY locks itself
--echo #
create table t26 (pk int primary key, c char(1)) engine=RocksDB;
insert into t26 values (1,'a'),(2,'b');
update t26 set c = 'x' order by pk limit 1;
delete from t26 order by pk limit 1;
select * from t26;
drop table t26;
--echo #
--echo # Test whether SELECT ... FOR UPDATE puts locks
--echo #
create table t27(pk varchar(10) primary key, col1 varchar(20)) engine=RocksDB;
insert into t27 values
('row1', 'row1data'),
('row2', 'row2data'),
('row3', 'row3data');
connection con1;
begin;
select * from t27 where pk='row3' for update;
connection default;
set rocksdb_lock_wait_timeout=1;
--error ER_LOCK_WAIT_TIMEOUT
update t27 set col1='row2-modified' where pk='row3';
connection con1;
rollback;
connection default;
disconnect con1;
drop table t27;
--echo #
--echo # MDEV-4060: RocksDB: Assertion `! trx->batch' fails in
--echo #
create table t28 (pk int primary key, a int) engine=RocksDB;
insert into t28 values (1,10),(2,20);
begin;
update t28 set a = 100 where pk = 3;
rollback;
select * from t28;
drop table t28;
--echo #
--echo # Secondary indexes
--echo #
create table t30 (
pk varchar(16) not null primary key,
key1 varchar(16) not null,
col1 varchar(16) not null,
key(key1)
) engine=rocksdb;
insert into t30 values ('row1', 'row1-key', 'row1-data');
insert into t30 values ('row2', 'row2-key', 'row2-data');
insert into t30 values ('row3', 'row3-key', 'row3-data');
--replace_column 9 #
explain
select * from t30 where key1='row2-key';
select * from t30 where key1='row2-key';
--replace_column 9 #
explain
select * from t30 where key1='row1';
--echo # This will produce nothing:
select * from t30 where key1='row1';
--replace_column 9 #
explain
select key1 from t30;
select key1 from t30;
--echo # Create a duplicate record
insert into t30 values ('row2a', 'row2-key', 'row2a-data');
--echo # Can we see it?
select * from t30 where key1='row2-key';
delete from t30 where pk='row2';
select * from t30 where key1='row2-key';
--echo #
--echo # Range scans on secondary index
--echo #
delete from t30;
insert into t30 values
('row1', 'row1-key', 'row1-data'),
('row2', 'row2-key', 'row2-data'),
('row3', 'row3-key', 'row3-data'),
('row4', 'row4-key', 'row4-data'),
('row5', 'row5-key', 'row5-data');
analyze table t30;
--replace_column 9 #
explain
select * from t30 where key1 <='row3-key';
select * from t30 where key1 <='row3-key';
--replace_column 9 #
explain
select * from t30 where key1 between 'row2-key' and 'row4-key';
select * from t30 where key1 between 'row2-key' and 'row4-key';
--replace_column 9 #
explain
select * from t30 where key1 in ('row2-key','row4-key');
select * from t30 where key1 in ('row2-key','row4-key');
--replace_column 9 #
explain
select key1 from t30 where key1 in ('row2-key','row4-key');
select key1 from t30 where key1 in ('row2-key','row4-key');
--replace_column 9 #
explain
select * from t30 where key1 > 'row1-key' and key1 < 'row4-key';
select * from t30 where key1 > 'row1-key' and key1 < 'row4-key';
--replace_column 9 #
explain
select * from t30 order by key1 limit 3;
select * from t30 order by key1 limit 3;
--replace_column 9 #
explain
select * from t30 order by key1 desc limit 3;
select * from t30 order by key1 desc limit 3;
--echo #
--echo # Range scans on primary key
--echo #
--replace_column 9 #
explain
select * from t30 where pk <='row3';
select * from t30 where pk <='row3';
--replace_column 9 #
explain
select * from t30 where pk between 'row2' and 'row4';
select * from t30 where pk between 'row2' and 'row4';
--replace_column 9 #
explain
select * from t30 where pk in ('row2','row4');
select * from t30 where pk in ('row2','row4');
--replace_column 9 #
explain
select * from t30 order by pk limit 3;
select * from t30 order by pk limit 3;
drop table t30;
--echo #
--echo # MDEV-3841: RocksDB: Reading by PK prefix does not work
--echo #
create table t31 (i int, j int, k int, primary key(i,j,k)) engine=RocksDB;
insert into t31 values (1,10,100),(2,20,200);
select * from t31 where i = 1;
select * from t31 where j = 10;
select * from t31 where k = 100;
select * from t31 where i = 1 and j = 10;
select * from t31 where i = 1 and k = 100;
select * from t31 where j = 10 and k = 100;
select * from t31 where i = 1 and j = 10 and k = 100;
drop table t31;
--echo #
--echo # MDEV-4055: RocksDB: UPDATE/DELETE by a multi-part PK does not work
--echo #
create table t32 (i int, j int, k int, primary key(i,j,k), a varchar(8)) engine=RocksDB;
insert into t32 values
(1,10,100,''),
(2,20,200,'');
select * from t32 where i = 1 and j = 10 and k = 100;
update t32 set a = 'updated' where i = 1 and j = 10 and k = 100;
select * from t32;
drop table t32;
--echo #
--echo # MDEV-3841: RocksDB: Assertion `0' fails in ha_rocksdb::index_read_map on range select with ORDER BY .. DESC
--echo #
CREATE TABLE t33 (pk INT PRIMARY KEY, a CHAR(1)) ENGINE=RocksDB;
INSERT INTO t33 VALUES (1,'a'),(2,'b');
SELECT * FROM t33 WHERE pk <= 10 ORDER BY pk DESC;
DROP TABLE t33;
--echo #
--echo # MDEV-4081: RocksDB throws error 122 on an attempt to create a table with unique index
--echo #
#--error ER_GET_ERRMSG
--echo # Unique indexes can be created, but uniqueness won't be enforced
create table t33 (pk int primary key, u int, unique index(u)) engine=RocksDB;
drop table t33;
--echo #
--echo # MDEV-4077: RocksDB: Wrong result (duplicate row) on select with range
--echo #
CREATE TABLE t34 (pk INT PRIMARY KEY) ENGINE=RocksDB;
INSERT INTO t34 VALUES (10),(11);
SELECT pk FROM t34 WHERE pk > 5 AND pk < 15;
SELECT pk FROM t34 WHERE pk BETWEEN 5 AND 15;
SELECT pk FROM t34 WHERE pk > 5;
SELECT pk FROM t34 WHERE pk < 15;
drop table t34;
--echo #
--echo # MDEV-4086: RocksDB does not allow a query with multi-part pk and index and ORDER BY .. DEC
--echo #
create table t35 (a int, b int, c int, d int, e int, primary key (a,b,c), key (a,c,d,e)) engine=RocksDB;
insert into t35 values (1,1,1,1,1),(2,2,2,2,2);
select * from t35 where a = 1 and c = 1 and d = 1 order by e desc;
drop table t35;
--echo #
--echo # MDEV-4084: RocksDB: Wrong result on IN subquery with index
--echo #
CREATE TABLE t36 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=RocksDB;
INSERT INTO t36 VALUES (1,10),(2,20);
SELECT 3 IN ( SELECT a FROM t36 );
drop table t36;
--echo #
--echo # MDEV-4084: RocksDB: Wrong result on IN subquery with index
--echo #
CREATE TABLE t37 (pk INT PRIMARY KEY, a INT, b CHAR(1), KEY(a), KEY(a,b))
ENGINE=RocksDB;
INSERT INTO t37 VALUES (1,10,'x'), (2,20,'y');
SELECT MAX(a) FROM t37 WHERE a < 100;
DROP TABLE t37;
--echo #
--echo # MDEV-4090: RocksDB: Wrong result (duplicate rows) on range access with secondary key and ORDER BY DESC
--echo #
CREATE TABLE t38 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=RocksDB;
INSERT INTO t38 VALUES (1,10), (2,20);
SELECT i FROM t38 WHERE i NOT IN (8) ORDER BY i DESC;
drop table t38;
--echo #
--echo # MDEV-4092: RocksDB: Assertion `in_table(pa, a_len)' fails in LDBSE_KEYDEF::cmp_full_keys
--echo # with a multi-part key and ORDER BY .. DESC
--echo #
CREATE TABLE t40 (pk1 INT PRIMARY KEY, a INT, b VARCHAR(1), KEY(b,a)) ENGINE=RocksDB;
INSERT INTO t40 VALUES (1, 7,'x'),(2,8,'y');
CREATE TABLE t41 (pk2 INT PRIMARY KEY) ENGINE=RocksDB;
INSERT INTO t41 VALUES (1),(2);
SELECT * FROM t40, t41 WHERE pk1 = pk2 AND b = 'o' ORDER BY a DESC;
DROP TABLE t40,t41;
--echo #
--echo # MDEV-4093: RocksDB: IN subquery by secondary key with NULL among values returns true instead of NULL
--echo #
CREATE TABLE t42 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=RocksDB;
INSERT INTO t42 VALUES (1, NULL),(2, 8);
SELECT ( 3 ) NOT IN ( SELECT a FROM t42 );
DROP TABLE t42;
--echo #
--echo # MDEV-4094: RocksDB: Wrong result on SELECT and ER_KEY_NOT_FOUND on
--echo # DELETE with search by NULL-able secondary key ...
--echo #
CREATE TABLE t43 (pk INT PRIMARY KEY, a INT, b CHAR(1), KEY(a)) ENGINE=RocksDB;
INSERT INTO t43 VALUES (1,8,'g'),(2,9,'x');
UPDATE t43 SET pk = 10 WHERE a = 8;
REPLACE INTO t43 ( a ) VALUES ( 8 );
REPLACE INTO t43 ( b ) VALUES ( 'y' );
SELECT * FROM t43 WHERE a = 8;
DELETE FROM t43 WHERE a = 8;
DROP TABLE t43;
--echo #
--echo # Basic AUTO_INCREMENT tests
--echo #
create table t44(pk int primary key auto_increment, col1 varchar(12)) engine=rocksdb;
insert into t44 (col1) values ('row1');
insert into t44 (col1) values ('row2');
insert into t44 (col1) values ('row3');
select * from t44;
drop table t44;
--echo #
--echo # ALTER TABLE tests
--echo #
create table t45 (pk int primary key, col1 varchar(12)) engine=rocksdb;
insert into t45 values (1, 'row1');
insert into t45 values (2, 'row2');
alter table t45 rename t46;
select * from t46;
drop table t46;
--error ER_BAD_TABLE_ERROR
drop table t45;
--echo #
--echo # Check Bulk loading
--echo #
show variables like 'rocksdb%';
create table t47 (pk int primary key, col1 varchar(12)) engine=rocksdb;
insert into t47 values (1, 'row1');
insert into t47 values (2, 'row2');
set rocksdb_bulk_load=1;
insert into t47 values (1, 'row1-NEW'),(2, 'row2-NEW');
set rocksdb_bulk_load=0;
select * from t47;
drop table t47;
--echo #
--echo # Fix TRUNCATE over empty table (transaction is committed when it wasn't
--echo # started)
--echo #
create table t48(pk int primary key auto_increment, col1 varchar(12)) engine=rocksdb;
set autocommit=0;
#--error ER_ILLEGAL_HA
truncate table t48;
set autocommit=1;
drop table t48;
--echo #
--echo # MDEV-4059: RocksDB: query waiting for a lock cannot be killed until query timeout exceeded
--echo #
--enable_connect_log
create table t49 (pk int primary key, a int) engine=RocksDB;
insert into t49 values (1,10),(2,20);
begin;
update t49 set a = 100 where pk = 1;
--connect (con1,localhost,root,,)
--let $con1_id = `SELECT CONNECTION_ID()`
set rocksdb_lock_wait_timeout=5000;
set @var1= to_seconds(now());
send update t49 set a = 1000 where pk = 1;
--connect (con2,localhost,root,,)
--echo kill query \$con1_id;
--disable_query_log
eval kill query $con1_id;
--enable_query_log
--connection con1
--error ER_QUERY_INTERRUPTED
--reap
set @var2= to_seconds(now());
select (@var2 -@var1) < 400;
--connection default
--disconnect con1
commit;
drop table t49;
--echo #
--echo # Index-only tests for INT-based columns
--echo #
create table t1 (pk int primary key, key1 int, col1 int, key(key1)) engine=rocksdb;
insert into t1 values (1,1,1);
insert into t1 values (2,2,2);
insert into t1 values (-5,-5,-5);
--echo # INT column uses index-only:
--replace_column 9 #
explain
select key1 from t1 where key1=2;
select key1 from t1 where key1=2;
select key1 from t1 where key1=-5;
drop table t1;
create table t2 (pk int primary key, key1 int unsigned, col1 int, key(key1)) engine=rocksdb;
insert into t2 values (1,1,1), (2,2,2);
--echo # INT UNSIGNED column uses index-only:
--replace_column 9 #
explain
select key1 from t2 where key1=2;
select key1 from t2 where key1=2;
drop table t2;
create table t3 (pk bigint primary key, key1 bigint, col1 int, key(key1)) engine=rocksdb;
insert into t3 values (1,1,1), (2,2,2);
--echo # BIGINT uses index-only:
--replace_column 9 #
explain
select key1 from t3 where key1=2;
select key1 from t3 where key1=2;
drop table t3;
--echo #
--echo # Index-only reads for string columns
--echo #
create table t1 (
pk int primary key,
key1 char(10) character set binary,
col1 int,
key (key1)
) engine=rocksdb;
insert into t1 values(1, 'one',11), (2,'two',22);
--replace_column 9 #
explain
select key1 from t1 where key1='one';
--echo # The following will produce no rows. This looks like a bug,
--echo # but it is actually correct behavior. Binary strings are end-padded
--echo # with \0 character (and not space). Comparison does not ignore
--echo # the tail of \0.
select key1 from t1 where key1='one';
--replace_column 9 #
explain
select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0';
select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0';
drop table t1;
create table t2 (
pk int primary key,
key1 char(10) collate latin1_bin,
col1 int,
key (key1)
) engine=rocksdb;
insert into t2 values(1, 'one',11), (2,'two',22);
--replace_column 9 #
explain
select key1 from t2 where key1='one';
select key1 from t2 where key1='one';
drop table t2;
create table t3 (
pk int primary key,
key1 char(10) collate utf8_bin,
col1 int,
key (key1)
) engine=rocksdb;
insert into t3 values(1, 'one',11), (2,'two',22);
--replace_column 9 #
explain
select key1 from t3 where key1='one';
select key1 from t3 where key1='one';
drop table t3;
--echo # a VARCHAR column
create table t4 (
pk int primary key,
key1 varchar(10) collate latin1_bin,
key(key1)
) engine=rocksdb;
insert into t4 values(1, 'one'), (2,'two'),(3,'threee'),(55,'fifty-five');
--replace_column 9 #
explain
select key1 from t4 where key1='two';
select key1 from t4 where key1='two';
select key1 from t4 where key1='fifty-five';
--replace_column 9 #
explain
select key1 from t4 where key1 between 's' and 'u';
select key1 from t4 where key1 between 's' and 'u';
drop table t4;
--echo #
--echo # MDEV-4305: RocksDB: Assertion `((keypart_map + 1) & keypart_map) == 0' fails in calculate_key_len
--echo #
CREATE TABLE t1 (pk1 INT, pk2 CHAR(32), i INT, PRIMARY KEY(pk1,pk2), KEY(i)) ENGINE=RocksDB;
INSERT INTO t1 VALUES (1,'test1',6),(2,'test2',8);
SELECT * FROM t1 WHERE i != 3 OR pk1 > 9;
DROP TABLE t1;
--echo #
--echo # MDEV-4298: RocksDB: Assertion `thd->is_error() || kill_errno' fails in ha_rows filesort
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=RocksDB;
INSERT INTO t1 VALUES (1,1),(2,2);
BEGIN;
UPDATE t1 SET i = 100;
--connect (con1,localhost,root,,test)
--error ER_LOCK_WAIT_TIMEOUT
DELETE IGNORE FROM t1 ORDER BY i;
--disconnect con1
--connection default
COMMIT;
DROP TABLE t1;
--echo #
--echo # MDEV-4313: RocksDB: Server crashes in LDBSE_KEYDEF::setup on dropping the primary key column
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT NOT NULL, KEY(i)) ENGINE=RocksDB;
--error ER_GET_ERRMSG
ALTER TABLE t1 DROP COLUMN `pk`;
DROP TABLE t1;
--echo #
--echo # MDEV-4324: RocksDB: Valgrind "Use of uninitialised value" warnings on inserting value into varchar field
--echo # (testcase only)
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, c VARCHAR(4)) ENGINE=RocksDB;
INSERT INTO t1 VALUES (1,'foo'), (2,'bar');
DROP TABLE t1;
--echo #
--echo # MDEV-4304: RocksDB: Index-only scan by a field with utf8_bin collation returns garbage symbols
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, c1 CHAR(1), c2 CHAR(1), KEY(c1)) ENGINE=RocksDB CHARSET utf8 COLLATE utf8_bin;
INSERT INTO t1 VALUES (1,'h','h');
SELECT * FROM t1;