1SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
2SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
3DROP TABLE IF EXISTS t1;
4CREATE TABLE t1 (
5b BLOB,
6t TINYBLOB,
7m MEDIUMBLOB,
8l LONGBLOB,
9PRIMARY KEY b (b(32))
10) ENGINE=rocksdb;
11Warnings:
12Warning	1280	Name 'b' ignored for PRIMARY key.
13SHOW INDEX IN t1;
14Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
15t1	0	PRIMARY	1	b	A	1000	32	NULL		LSMTREE
16INSERT INTO t1 (b,t,m,l) VALUES
17('','','',''),
18('a','b','c','d'),
19('b','d','c','b'),
20('test1','test2','test3','test4'),
21(REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)),
22(HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')),
23('abc','def','ghi','jkl'),
24('test2','test3','test4','test5'),
25('test3','test4','test5','test6'),
26(REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)),
27(REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128));
28EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f;
29id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
30#	#	#	#	#	PRIMARY	#	#	#	#
31SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f;
32f
33
34
35EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f;
36id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
37#	#	#	#	#	NULL	#	#	#	#
38SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f;
39f
40
41
42DROP TABLE t1;
43CREATE TABLE t1 (
44b BLOB,
45t TINYBLOB,
46m MEDIUMBLOB,
47l LONGBLOB,
48pk INT AUTO_INCREMENT PRIMARY KEY,
49UNIQUE INDEX l_t (l(256),t(64))
50) ENGINE=rocksdb;
51SHOW INDEX IN t1;
52Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
53t1	0	PRIMARY	1	pk	#	#	NULL	NULL	#	#
54t1	0	l_t	1	l	#	#	256	NULL	#	#
55t1	0	l_t	2	t	#	#	64	NULL	#	#
56INSERT INTO t1 (b,t,m,l) VALUES
57('','','',''),
58('a','b','c','d'),
59('b','d','c','b'),
60('test1','test2','test3','test4'),
61(REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)),
62(HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')),
63('abc','def','ghi','jkl'),
64('test2','test3','test4','test5'),
65('test3','test4','test5','test6'),
66(REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)),
67(REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128));
68EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC;
69id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
701	SIMPLE	t1	range	l_t	l_t	259	NULL	#	Using where; Using filesort
71SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC;
72SUBSTRING(t,64)	SUBSTRING(l,256)
73
74
75bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
76bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
77
78
79fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
80
81
82
83EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 FORCE INDEX (l_t) WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC;
84id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
851	SIMPLE	t1	range	l_t	l_t	259	NULL	#	Using where; Using filesort
86SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 FORCE INDEX (l_t) WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC;
87SUBSTRING(t,64)	SUBSTRING(l,256)
88
89
90bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
91bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
92
93
94fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
95
96
97
98DROP TABLE t1;
99CREATE TABLE t1 (
100b BLOB,
101t TINYBLOB,
102m MEDIUMBLOB,
103l LONGBLOB,
104pk INT AUTO_INCREMENT PRIMARY KEY,
105INDEX (m(128))
106) ENGINE=rocksdb;
107SHOW INDEX IN t1;
108Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
109t1	0	PRIMARY	1	pk	A	1000	NULL	NULL		LSMTREE
110t1	1	m	1	m	A	500	128	NULL	YES	LSMTREE
111INSERT INTO t1 (b,t,m,l) VALUES
112('','','',''),
113('a','b','c','d'),
114('b','d','c','b'),
115('test1','test2','test3','test4'),
116(REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)),
117(HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')),
118('abc','def','ghi','jkl'),
119('test2','test3','test4','test5'),
120('test3','test4','test5','test6'),
121(REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)),
122(REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128));
123EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC;
124id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1251	SIMPLE	t1	ref	m	m	131	const	#	Using where; Using filesort
126SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC;
127f
128EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC;
129id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1301	SIMPLE	t1	ref	m	m	131	const	#	Using where; Using filesort
131SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC;
132f
133DROP TABLE t1;
134CREATE TABLE t1 (
135b BLOB,
136PRIMARY KEY b (b(32))
137) ENGINE=rocksdb;
138Warnings:
139Warning	1280	Name 'b' ignored for PRIMARY key.
140INSERT INTO t1 (b) VALUES
141('00000000000000000000000000000000'),
142('00000000000000000000000000000001'),
143('00000000000000000000000000000002');
144SELECT b FROM t1;
145b
14600000000000000000000000000000000
14700000000000000000000000000000001
14800000000000000000000000000000002
149DROP TABLE t1;
150CREATE TABLE t1 (
151b TINYBLOB,
152PRIMARY KEY b (b(32))
153) ENGINE=rocksdb;
154Warnings:
155Warning	1280	Name 'b' ignored for PRIMARY key.
156INSERT INTO t1 (b) VALUES
157('00000000000000000000000000000000'),
158('00000000000000000000000000000001'),
159('00000000000000000000000000000002');
160SELECT b FROM t1;
161b
16200000000000000000000000000000000
16300000000000000000000000000000001
16400000000000000000000000000000002
165DROP TABLE t1;
166CREATE TABLE t1 (
167b MEDIUMBLOB,
168PRIMARY KEY b (b(32))
169) ENGINE=rocksdb;
170Warnings:
171Warning	1280	Name 'b' ignored for PRIMARY key.
172INSERT INTO t1 (b) VALUES
173('00000000000000000000000000000000'),
174('00000000000000000000000000000001'),
175('00000000000000000000000000000002');
176SELECT b FROM t1;
177b
17800000000000000000000000000000000
17900000000000000000000000000000001
18000000000000000000000000000000002
181DROP TABLE t1;
182CREATE TABLE t1 (
183b LONGBLOB,
184PRIMARY KEY b (b(32))
185) ENGINE=rocksdb;
186Warnings:
187Warning	1280	Name 'b' ignored for PRIMARY key.
188INSERT INTO t1 (b) VALUES
189('00000000000000000000000000000000'),
190('00000000000000000000000000000001'),
191('00000000000000000000000000000002');
192SELECT b FROM t1;
193b
19400000000000000000000000000000000
19500000000000000000000000000000001
19600000000000000000000000000000002
197DROP TABLE t1;
198SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;
199