diff options
Diffstat (limited to '')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result | 389 |
1 files changed, 389 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result new file mode 100644 index 00000000..713c7e92 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result @@ -0,0 +1,389 @@ +CREATE TABLE t1 ( +c1 INT, +c2 INT, +PRIMARY KEY (`c1`), +KEY kc2 (`c2`) +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=1;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 4, 7), +PARTITION custom_p1 VALUES IN (2, 5, 8), +PARTITION custom_p2 VALUES IN (3, 6, 9) +); +set global rocksdb_debug_ttl_rec_ts = -3600; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (2, 2); +INSERT INTO t1 values (3, 3); +INSERT INTO t1 values (4, 4); +INSERT INTO t1 values (5, 5); +INSERT INTO t1 values (6, 6); +INSERT INTO t1 values (7, 7); +INSERT INTO t1 values (8, 8); +INSERT INTO t1 values (9, 9); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +name VARCHAR(25) NOT NULL, +PRIMARY KEY (`c1`, `c2`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=my_custom_cf;custom_p2_cfname=baz', +KEY kc2 (`c2`) +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=1;custom_p1_ttl_duration=7;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 4, 7), +PARTITION custom_p1 VALUES IN (2, 5, 8), +PARTITION custom_p2 VALUES IN (3, 6, 9) +); +set global rocksdb_debug_ttl_rec_ts = -1200; +INSERT INTO t1 values (1,1,'a'); +INSERT INTO t1 values (4,4,'aaaa'); +INSERT INTO t1 values (7,7,'aaaaaaa'); +set global rocksdb_debug_ttl_rec_ts = 1200; +INSERT INTO t1 values (2,2,'aa'); +INSERT INTO t1 values (3,3,'aaa'); +INSERT INTO t1 values (5,5,'aaaaa'); +INSERT INTO t1 values (6,6,'aaaaaa'); +INSERT INTO t1 values (8,8,'aaaaaaaa'); +INSERT INTO t1 values (9,9,'aaaaaaaaa'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +1 1 a +2 2 aa +3 3 aaa +4 4 aaaa +5 5 aaaaa +6 6 aaaaaa +7 7 aaaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +1 1 a +2 2 aa +3 3 aaa +4 4 aaaa +5 5 aaaaa +6 6 aaaaaa +7 7 aaaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'foo'; +set @@global.rocksdb_compact_cf = 'my_custom_cf'; +set @@global.rocksdb_compact_cf = 'default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +set global rocksdb_debug_ttl_snapshot_ts = 3600; +set @@global.rocksdb_compact_cf = 'foo'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +set @@global.rocksdb_compact_cf = 'my_custom_cf'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +3 3 aaa +6 6 aaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +3 3 aaa +6 6 aaaaaa +9 9 aaaaaaaaa +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +name VARCHAR(25) NOT NULL, +event DATE, +PRIMARY KEY (`c1`, `c2`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;', +KEY kc2 (c2) +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=9999;custom_p2_ttl_duration=5;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 2, 3), +PARTITION custom_p1 VALUES IN (4, 5, 6), +PARTITION custom_p2 VALUES IN (7, 8, 9) +); +INSERT INTO t1 VALUES (1, 1, "one", null); +INSERT INTO t1 VALUES (2, 2, "two", null); +INSERT INTO t1 VALUES (3, 3, "three", null); +INSERT INTO t1 VALUES (4, 4, "four", null); +INSERT INTO t1 VALUES (5, 5, "five", null); +INSERT INTO t1 VALUES (6, 6, "six", null); +INSERT INTO t1 VALUES (7, 7, "seven", null); +INSERT INTO t1 VALUES (8, 8, "eight", null); +INSERT INTO t1 VALUES (9, 9, "nine", null); +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +set global rocksdb_debug_ttl_rec_ts = 600; +ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(`c2`,`c1`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;'; +set global rocksdb_debug_ttl_rec_ts = 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` int(11) NOT NULL, + `name` varchar(25) NOT NULL, + `event` date DEFAULT NULL, + PRIMARY KEY (`c2`,`c1`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;', + KEY `kc2` (`c2`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COMMENT='custom_p0_ttl_duration=9999;custom_p2_ttl_duration=5;' + PARTITION BY LIST (`c1`) +(PARTITION `custom_p0` VALUES IN (1,2,3) ENGINE = ROCKSDB, + PARTITION `custom_p1` VALUES IN (4,5,6) ENGINE = ROCKSDB, + PARTITION `custom_p2` VALUES IN (7,8,9) ENGINE = ROCKSDB) +set global rocksdb_debug_ttl_snapshot_ts = 100; +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'baz'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +set global rocksdb_debug_ttl_snapshot_ts = 1200; +set @@global.rocksdb_compact_cf = 'foo'; +set @@global.rocksdb_compact_cf = 'baz'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +DROP TABLE t1; +CREATE TABLE t1 ( +c1 BIGINT, +c2 BIGINT UNSIGNED NOT NULL, +name VARCHAR(25) NOT NULL, +event DATE, +PRIMARY KEY (`c1`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;', +KEY kc2 (`c2`) +) ENGINE=ROCKSDB +COMMENT="ttl_duration=1;custom_p1_ttl_duration=100;custom_p1_ttl_col=c2;custom_p2_ttl_duration=5000;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 2, 3), +PARTITION custom_p1 VALUES IN (4, 5, 6), +PARTITION custom_p2 VALUES IN (7, 8, 9) +); +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 VALUES (1, UNIX_TIMESTAMP(), "one", null); +INSERT INTO t1 VALUES (2, UNIX_TIMESTAMP(), "two", null); +INSERT INTO t1 VALUES (3, UNIX_TIMESTAMP(), "three", null); +set global rocksdb_debug_ttl_rec_ts = 0; +INSERT INTO t1 VALUES (4, UNIX_TIMESTAMP(), "four", null); +INSERT INTO t1 VALUES (5, UNIX_TIMESTAMP(), "five", null); +INSERT INTO t1 VALUES (6, UNIX_TIMESTAMP(), "six", null); +INSERT INTO t1 VALUES (7, UNIX_TIMESTAMP(), "seven", null); +INSERT INTO t1 VALUES (8, UNIX_TIMESTAMP(), "eight", null); +INSERT INTO t1 VALUES (9, UNIX_TIMESTAMP(), "nine", null); +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'foo'; +set @@global.rocksdb_compact_cf = 'baz'; +set @@global.rocksdb_compact_cf = 'bar'; +set @@global.rocksdb_compact_cf = 'default'; +SELECT c1 FROM t1 FORCE INDEX (PRIMARY); +c1 +4 +5 +6 +7 +8 +9 +SELECT c1 FROM t1 FORCE INDEX (kc2); +c1 +4 +5 +6 +7 +8 +9 +set global rocksdb_debug_ttl_snapshot_ts = 600; +set @@global.rocksdb_compact_cf = 'bar'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT c1 FROM t1 FORCE INDEX (PRIMARY); +c1 +7 +8 +9 +SELECT c1 FROM t1 FORCE INDEX (kc2); +c1 +7 +8 +9 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +PRIMARY KEY (`c1`) COMMENT 'custom_p0_cfname=foo;' +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=1;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 4, 7), +PARTITION custom_p1 VALUES IN (2, 5, 8), +PARTITION custom_p2 VALUES IN (3, 6, 9) +); +set global rocksdb_debug_ttl_rec_ts = -3600; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (4, 4); +INSERT INTO t1 values (7, 7); +set global rocksdb_debug_ttl_rec_ts = 0; +INSERT INTO t1 values (2, 2); +INSERT INTO t1 values (3, 3); +INSERT INTO t1 values (5, 5); +INSERT INTO t1 values (6, 6); +INSERT INTO t1 values (8, 8); +INSERT INTO t1 values (9, 9); +SELECT * FROM t1; +c1 c2 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +CREATE INDEX kc2 on t1 (c2); +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='foo'; +set global rocksdb_compact_cf='default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +DROP TABLE t1; |