[Commits] 505b119: Add more tests to mysql-test/suite/rocksdb/

Sergei Petrunia psergey at askmonty.org
Wed Jul 16 20:36:09 EEST 2014


revision-id: 505b11931851c95086e88f252244c8c1cce17c6f
parent(s): 97bce6ed61e00f9a80a49cf1735eacf164944b84
committer: Sergei Petrunia
branch nick: webscalesql-5.6
timestamp: 2014-07-16 21:36:09 +0400
message:

Add more tests to mysql-test/suite/rocksdb/

-----------------------

 .../suite/rocksdb/type_binary_indexes.result       |   77 +++++++++++
 mysql-test/suite/rocksdb/type_binary_indexes.test  |   86 ++++++++++++
 mysql-test/suite/rocksdb/type_bit_indexes.result   |   55 ++++++++
 mysql-test/suite/rocksdb/type_bit_indexes.test     |  103 ++++++++++++++
 mysql-test/suite/rocksdb/type_char_indexes.result  |   70 ++++++++++
 mysql-test/suite/rocksdb/type_char_indexes.test    |   92 +++++++++++++
 .../suite/rocksdb/type_date_time_indexes.result    |  116 ++++++++++++++++
 .../suite/rocksdb/type_date_time_indexes.test      |  143 ++++++++++++++++++++
 mysql-test/suite/rocksdb/type_enum_indexes.result  |   66 +++++++++
 mysql-test/suite/rocksdb/type_enum_indexes.test    |   81 +++++++++++
 mysql-test/suite/rocksdb/type_fixed_indexes.result |  126 +++++++++++++++++
 mysql-test/suite/rocksdb/type_fixed_indexes.test   |   96 +++++++++++++
 mysql-test/suite/rocksdb/type_float_indexes.result |  123 +++++++++++++++++
 mysql-test/suite/rocksdb/type_float_indexes.test   |  107 +++++++++++++++
 mysql-test/suite/rocksdb/type_set_indexes.result   |   77 +++++++++++
 mysql-test/suite/rocksdb/type_set_indexes.test     |   94 +++++++++++++
 16 files changed, 1512 insertions(+)

diff --git a/mysql-test/suite/rocksdb/type_binary_indexes.result b/mysql-test/suite/rocksdb/type_binary_indexes.result
new file mode 100644
index 0000000..d4a15fb
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_binary_indexes.result
@@ -0,0 +1,77 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (b BINARY,
+b20 BINARY(20) PRIMARY KEY,
+v16 VARBINARY(16),
+v128 VARBINARY(128)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	b20	A	NULL	NULL	NULL		BTREE		
+INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
+EXPLAIN SELECT HEX(b20) FROM t1 ORDER BY b20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	20	NULL	1000	Using index
+SELECT HEX(b20) FROM t1 ORDER BY b20;
+HEX(b20)
+6368617231000000000000000000000000000000
+6368617232000000000000000000000000000000
+6368617233000000000000000000000000000000
+6368617234000000000000000000000000000000
+EXPLAIN SELECT HEX(b20) FROM t1 IGNORE INDEX (PRIMARY) ORDER BY b20 DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using filesort
+SELECT HEX(b20) FROM t1 ORDER BY b20 DESC;
+HEX(b20)
+6368617234000000000000000000000000000000
+6368617233000000000000000000000000000000
+6368617232000000000000000000000000000000
+6368617231000000000000000000000000000000
+DROP TABLE t1;
+CREATE TABLE t1 (b BINARY,
+b20 BINARY(20),
+v16 VARBINARY(16),
+v128 VARBINARY(128),
+pk VARBINARY(10) PRIMARY KEY,
+INDEX (v16(10))
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	v16	1	v16	A	NULL	10	NULL	YES	BTREE		
+INSERT INTO t1 (b,b20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b',1),('a','char2','varchar2a','varchar2b',2),('b','char3','varchar1a','varchar1b',3),('c','char4','varchar3a','varchar3b',4),('d','char5','varchar4a','varchar3b',5),('e','char6','varchar2a','varchar3b',6);
+INSERT INTO t1 (b,b20,v16,v128,pk) SELECT b,b20,v16,v128,pk+100 FROM t1;
+EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	v16	v16	13	NULL	10	Using where
+SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 WHERE v16 LIKE 'varchar%';
+HEX(SUBSTRING(v16,7,3))
+723161
+723161
+723161
+723161
+723261
+723261
+723261
+723261
+723361
+723361
+723461
+723461
+EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	v16	v16	13	NULL	10	Using where
+SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%';
+HEX(SUBSTRING(v16,7,3))
+723161
+723161
+723161
+723161
+723261
+723261
+723261
+723261
+723361
+723361
+723461
+723461
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_binary_indexes.test b/mysql-test/suite/rocksdb/type_binary_indexes.test
new file mode 100644
index 0000000..47d459d
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_binary_indexes.test
@@ -0,0 +1,86 @@
+#
+# BINARY and VARBINARY columns with indexes
+#
+
+#######################################
+# TODO: 
+# A part of the test is disabled 
+# because unique keys are not supported
+#######################################
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (b BINARY,
+  b20 BINARY(20) PRIMARY KEY,
+  v16 VARBINARY(16),
+  v128 VARBINARY(128)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
+
+EXPLAIN SELECT HEX(b20) FROM t1 ORDER BY b20;
+SELECT HEX(b20) FROM t1 ORDER BY b20;
+
+EXPLAIN SELECT HEX(b20) FROM t1 IGNORE INDEX (PRIMARY) ORDER BY b20 DESC;
+SELECT HEX(b20) FROM t1 ORDER BY b20 DESC;
+
+DROP TABLE t1;
+
+--disable_parsing
+--error ER_GET_ERRMSG
+CREATE TABLE t1 (b BINARY,
+  b20 BINARY(20),
+  v16 VARBINARY(16),
+  v128 VARBINARY(128),
+  UNIQUE INDEX b_v (b,v128),
+  pk VARBINARY(10) PRIMARY KEY
+) ENGINE=rocksdb;
+
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
+
+EXPLAIN SELECT HEX(b), HEX(v128) FROM t1 WHERE b != 'a' AND v128 > 'varchar';
+--sorted_result
+SELECT HEX(b), HEX(v128) FROM t1 WHERE b != 'a' AND v128 > 'varchar';
+
+EXPLAIN SELECT HEX(b), HEX(v128) FROM t1 USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar';
+--sorted_result
+SELECT HEX(b), HEX(v128) FROM t1 USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar';
+
+EXPLAIN SELECT HEX(v128), COUNT(*) FROM t1 GROUP BY HEX(v128);
+--sorted_result
+SELECT HEX(v128), COUNT(*) FROM t1 GROUP BY HEX(v128);
+
+DROP TABLE t1;
+
+--enable_parsing
+
+CREATE TABLE t1 (b BINARY,
+  b20 BINARY(20),
+  v16 VARBINARY(16),
+  v128 VARBINARY(128),
+  pk VARBINARY(10) PRIMARY KEY,
+  INDEX (v16(10))
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (b,b20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b',1),('a','char2','varchar2a','varchar2b',2),('b','char3','varchar1a','varchar1b',3),('c','char4','varchar3a','varchar3b',4),('d','char5','varchar4a','varchar3b',5),('e','char6','varchar2a','varchar3b',6);
+INSERT INTO t1 (b,b20,v16,v128,pk) SELECT b,b20,v16,v128,pk+100 FROM t1;
+
+EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%';
+--sorted_result
+SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 WHERE v16 LIKE 'varchar%';
+
+EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%';
+--sorted_result
+SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%';
+
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/rocksdb/type_bit_indexes.result b/mysql-test/suite/rocksdb/type_bit_indexes.result
new file mode 100644
index 0000000..a5e40f8
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_bit_indexes.result
@@ -0,0 +1,55 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+a BIT,
+b BIT(20) PRIMARY KEY,
+c BIT(32),
+d BIT(64)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	b	A	NULL	NULL	NULL		BTREE		
+INSERT INTO t1 (a,b,c,d) VALUES 
+(0,0xFFFFF,0,1),(0,256,0xAAA,0x12345),(1,16,0,0xFFFFFFF),(0,11,12,13),
+(1,100,101,102),(0,12,13,14),(1,13,14,15),(0,101,201,202),(1,1000,1001,1002),
+(1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF);
+EXPLAIN SELECT b+0 FROM t1 ORDER BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	3	NULL	1000	Using index
+SELECT b+0 FROM t1 ORDER BY b;
+b+0
+11
+12
+13
+16
+100
+101
+256
+1000
+65535
+1048575
+DROP TABLE t1;
+# TODO: Unique indexes are not enforced
+CREATE TABLE t1 (
+a BIT,
+b BIT(20),
+c BIT(32),
+d BIT(64),
+pk BIT(10) PRIMARY KEY,
+INDEX(a)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (a,b,c,d,pk) VALUES
+(0,0xFFFFF,0,1,1),(0,256,0xAAA,0x12345,2),(1,16,0,0xFFFFFFF,3),(0,11,12,13,4),
+(1,100,101,102,5),(0,12,13,14,6),(1,13,14,15,7),(0,101,201,202,8),(1,1000,1001,1002,9),
+(1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF,10);
+EXPLAIN SELECT DISTINCT a+0 FROM t1 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT DISTINCT a+0 FROM t1 ORDER BY a;
+a+0
+0
+1
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_bit_indexes.test b/mysql-test/suite/rocksdb/type_bit_indexes.test
new file mode 100644
index 0000000..6343ed8
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_bit_indexes.test
@@ -0,0 +1,103 @@
+#
+# BIT columns with indexes
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  a BIT,
+  b BIT(20) PRIMARY KEY,
+  c BIT(32),
+  d BIT(64)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c,d) VALUES 
+(0,0xFFFFF,0,1),(0,256,0xAAA,0x12345),(1,16,0,0xFFFFFFF),(0,11,12,13),
+(1,100,101,102),(0,12,13,14),(1,13,14,15),(0,101,201,202),(1,1000,1001,1002),
+(1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF);
+
+EXPLAIN SELECT b+0 FROM t1 ORDER BY b;
+SELECT b+0 FROM t1 ORDER BY b;
+
+DROP TABLE t1;
+
+--echo # TODO: Unique indexes are not enforced
+--disable_parsing
+--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  a BIT,
+  b BIT(20),
+  c BIT(32),
+  d BIT(64),
+  pk BIT(10) PRIMARY KEY,
+UNIQUE INDEX b_c (b,c)
+) ENGINE=rocksdb;
+
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c,d,pk) VALUES
+(0,0xFFFFF,0,1,1),(0,256,0xAAA,0x12345,2),(1,16,0,0xFFFFFFF,3),(0,11,12,13,4),
+(1,100,101,102,5),(0,12,13,14,6),(1,13,14,15,7),(0,101,201,202,8),(1,1000,1001,1002,9),
+(1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF,10);
+
+EXPLAIN SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF;
+--sorted_result
+SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF;
+
+DROP TABLE t1;
+
+--enable_parsing
+
+CREATE TABLE t1 (
+  a BIT,
+  b BIT(20),
+  c BIT(32),
+  d BIT(64),
+  pk BIT(10) PRIMARY KEY,
+  INDEX(a)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c,d,pk) VALUES
+(0,0xFFFFF,0,1,1),(0,256,0xAAA,0x12345,2),(1,16,0,0xFFFFFFF,3),(0,11,12,13,4),
+(1,100,101,102,5),(0,12,13,14,6),(1,13,14,15,7),(0,101,201,202,8),(1,1000,1001,1002,9),
+(1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF,10);
+
+EXPLAIN SELECT DISTINCT a+0 FROM t1 ORDER BY a;
+SELECT DISTINCT a+0 FROM t1 ORDER BY a;
+
+DROP TABLE t1;
+
+--disable_parsing
+--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  a BIT,
+  b BIT(20),
+  c BIT(32),
+  d BIT(64),
+  pk BIT(10) PRIMARY KEY,
+  UNIQUE INDEX (d)
+) ENGINE=rocksdb;
+
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c,d,pk) VALUES
+(0,0xFFFFF,0,1,1),(0,256,0xAAA,0x12345,2),(1,16,0,0xFFFFFFF,3),(0,11,12,13,4),
+(1,100,101,102,5),(0,12,13,14,6),(1,13,14,15,7),(0,101,201,202,8),(1,1000,1001,1002,9),
+(1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF,10);
+
+EXPLAIN SELECT d FROM t1 WHERE d BETWEEN 1 AND 10000;
+--sorted_result
+SELECT d+0 FROM t1 WHERE d BETWEEN 1 AND 10000;
+
+DROP TABLE t1;
+
+--enable_parsing
+
diff --git a/mysql-test/suite/rocksdb/type_char_indexes.result b/mysql-test/suite/rocksdb/type_char_indexes.result
new file mode 100644
index 0000000..14d902b
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_char_indexes.result
@@ -0,0 +1,70 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+c CHAR,
+c20 CHAR(20) PRIMARY KEY,
+v16 VARCHAR(16),
+v128 VARCHAR(128)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	c20	A	NULL	NULL	NULL		BTREE		
+INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
+EXPLAIN SELECT c20 FROM t1 ORDER BY c20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	20	NULL	1000	Using index
+SELECT c20 FROM t1 ORDER BY c20;
+c20
+char1
+char2
+char3
+char4
+EXPLAIN SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	20	NULL	1000	Using index
+SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20;
+c20
+char1
+char2
+char3
+char4
+DROP TABLE t1;
+CREATE TABLE t1 (
+c CHAR,
+c20 CHAR(20),
+v16 VARCHAR(16),
+v128 VARCHAR(128),
+pk VARCHAR(64) PRIMARY KEY,
+INDEX (v16)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	v16	1	v16	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (c,c20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b','1'),('a','char2','varchar2a','varchar2b','2'),('b','char3','varchar1a','varchar1b','3'),('c','char4','varchar3a','varchar3b','4');
+EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 WHERE v16 LIKE 'varchar%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	v16	v16	19	NULL	10	Using where
+SELECT SUBSTRING(v16,7,3) FROM t1 WHERE v16 LIKE 'varchar%';
+SUBSTRING(v16,7,3)
+r1a
+r1a
+r2a
+r3a
+EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
+SELECT SUBSTRING(v16,7,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%';
+SUBSTRING(v16,7,3)
+r1a
+r1a
+r2a
+r3a
+EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	v16	v16	19	NULL	20	Using where
+SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;
+c	c20	v16	v128
+a	char1	varchar1a	varchar1b
+b	char3	varchar1a	varchar1b
+c	char4	varchar3a	varchar3b
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_char_indexes.test b/mysql-test/suite/rocksdb/type_char_indexes.test
new file mode 100644
index 0000000..b1804a6
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_char_indexes.test
@@ -0,0 +1,92 @@
+#
+# CHAR and VARCHAR columns with indexes
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  c CHAR,
+  c20 CHAR(20) PRIMARY KEY,
+  v16 VARCHAR(16),
+  v128 VARCHAR(128)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
+
+EXPLAIN SELECT c20 FROM t1 ORDER BY c20;
+SELECT c20 FROM t1 ORDER BY c20;
+
+EXPLAIN SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20;
+SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20;
+
+DROP TABLE t1;
+
+--disable_parsing
+--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  c CHAR,
+  c20 CHAR(20),
+  v16 VARCHAR(16),
+  v128 VARCHAR(128),
+  pk CHAR(64) PRIMARY KEY,
+  UNIQUE INDEX c_v (c,v128)
+) ENGINE=rocksdb;
+
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
+
+EXPLAIN SELECT c, v128 FROM t1 WHERE c != 'a' AND v128 > 'varchar';
+--sorted_result
+SELECT c, v128 FROM t1 WHERE c != 'a' AND v128 > 'varchar';
+
+EXPLAIN SELECT v128, COUNT(*) FROM t1 GROUP BY v128;
+--sorted_result
+SELECT v128, COUNT(*) FROM t1 GROUP BY v128;
+
+EXPLAIN SELECT v128, COUNT(*) FROM t1 USE INDEX FOR GROUP BY (c_v) GROUP BY v128;
+--sorted_result
+SELECT v128, COUNT(*) FROM t1 USE INDEX FOR GROUP BY (c_v) GROUP BY v128;
+
+SET SESSION optimizer_switch = 'engine_condition_pushdown=on';
+EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE c > 'a';
+--sorted_result
+SELECT c,c20,v16,v128 FROM t1 WHERE c > 'a';
+SET SESSION optimizer_switch = @@global.optimizer_switch;
+
+DROP TABLE t1;
+
+--enable_parsing
+
+CREATE TABLE t1 (
+  c CHAR,
+  c20 CHAR(20),
+  v16 VARCHAR(16),
+  v128 VARCHAR(128),
+  pk VARCHAR(64) PRIMARY KEY,
+  INDEX (v16)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (c,c20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b','1'),('a','char2','varchar2a','varchar2b','2'),('b','char3','varchar1a','varchar1b','3'),('c','char4','varchar3a','varchar3b','4');
+
+EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 WHERE v16 LIKE 'varchar%';
+--sorted_result
+SELECT SUBSTRING(v16,7,3) FROM t1 WHERE v16 LIKE 'varchar%';
+
+EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%';
+--sorted_result
+SELECT SUBSTRING(v16,7,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%';
+
+EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;
+--sorted_result
+SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;
+
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/rocksdb/type_date_time_indexes.result b/mysql-test/suite/rocksdb/type_date_time_indexes.result
new file mode 100644
index 0000000..6f4b5fa
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_date_time_indexes.result
@@ -0,0 +1,116 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+d DATE,
+dt DATETIME PRIMARY KEY,
+ts TIMESTAMP,
+t TIME,
+y YEAR
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	dt	A	NULL	NULL	NULL		BTREE		
+SET @tm = '2012-04-09 05:27:00';
+INSERT INTO t1 (d,dt,ts,t,y) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm));
+EXPLAIN SELECT dt FROM t1 ORDER BY dt LIMIT 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	3	Using index
+SELECT dt FROM t1 ORDER BY dt LIMIT 3;
+dt
+2010-11-22 11:43:14
+2010-11-22 12:33:54
+2011-08-27 21:33:56
+EXPLAIN SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	3	Using index
+SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3;
+dt
+2010-11-22 11:43:14
+2010-11-22 12:33:54
+2011-08-27 21:33:56
+INSERT INTO t1 (d,dt,ts,t,y) VALUES
+('2012-01-11', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000');
+ERROR 23000: Duplicate entry '2010-11-22 12:33:54' for key 'PRIMARY'
+DROP TABLE t1;
+CREATE TABLE t1 (
+d DATE,
+dt DATETIME,
+ts TIMESTAMP,
+t TIME,
+y YEAR,
+pk TIME PRIMARY KEY,
+INDEX (ts)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	ts	1	ts	A	NULL	NULL	NULL	YES	BTREE		
+SET @tm = '2012-04-09 05:27:00';
+INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','12:00:00'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','12:01:00'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','12:02:00'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','12:03:00'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','12:04:00'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'12:05:00');
+EXPLAIN SELECT ts FROM t1 WHERE ts > NOW();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	ts	ts	5	NULL	10	Using where
+SELECT ts FROM t1 WHERE ts > NOW();
+ts
+EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
+SELECT ts FROM t1 USE INDEX () WHERE ts > NOW();
+ts
+DROP TABLE t1;
+CREATE TABLE t1 (
+d DATE,
+dt DATETIME,
+ts TIMESTAMP,
+t TIME,
+y YEAR,
+pk TIME PRIMARY KEY,
+INDEX (y,t)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	y	1	y	A	NULL	NULL	NULL	YES	BTREE		
+t1	1	y	2	t	A	NULL	NULL	NULL	YES	BTREE		
+SET @tm = '2012-04-09 05:27:00';
+INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','18:18:18'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','19:18:18'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','20:18:18'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','21:18:18'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','22:18:18'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'23:18:18');
+EXPLAIN SELECT y, COUNT(*) FROM t1 GROUP BY y;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT y, COUNT(*) FROM t1 GROUP BY y;
+y	COUNT(*)
+1994	1
+1998	1
+1999	1
+2000	1
+2001	1
+2012	1
+EXPLAIN SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y;
+y	COUNT(*)
+1994	1
+1998	1
+1999	1
+2000	1
+2001	1
+2012	1
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_date_time_indexes.test b/mysql-test/suite/rocksdb/type_date_time_indexes.test
new file mode 100644
index 0000000..2676e8c
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_date_time_indexes.test
@@ -0,0 +1,143 @@
+#
+# Date and time columns with indexes
+# (DATE, DATETIME, TIMESTAMP, TIME, YEAR)
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+
+CREATE TABLE t1 (
+  d DATE,
+  dt DATETIME PRIMARY KEY,
+  ts TIMESTAMP,
+  t TIME,
+  y YEAR
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+SET @tm = '2012-04-09 05:27:00';
+
+INSERT INTO t1 (d,dt,ts,t,y) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm)); 
+
+EXPLAIN SELECT dt FROM t1 ORDER BY dt LIMIT 3;
+SELECT dt FROM t1 ORDER BY dt LIMIT 3;
+
+EXPLAIN SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3;
+SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3;
+
+--error ER_DUP_ENTRY
+INSERT INTO t1 (d,dt,ts,t,y) VALUES
+('2012-01-11', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000');
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  d DATE,
+  dt DATETIME,
+  ts TIMESTAMP,
+  t TIME,
+  y YEAR,
+  pk TIME PRIMARY KEY,
+  INDEX (ts)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+SET @tm = '2012-04-09 05:27:00';
+
+INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','12:00:00'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','12:01:00'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','12:02:00'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','12:03:00'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','12:04:00'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'12:05:00');
+
+EXPLAIN SELECT ts FROM t1 WHERE ts > NOW();
+--sorted_result
+SELECT ts FROM t1 WHERE ts > NOW();
+
+EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW();
+--sorted_result
+SELECT ts FROM t1 USE INDEX () WHERE ts > NOW();
+
+DROP TABLE t1;
+
+--disable_parsing
+--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  d DATE,
+  dt DATETIME,
+  ts TIMESTAMP,
+  t TIME,
+  y YEAR,
+  pk YEAR PRIMARY KEY,
+  UNIQUE INDEX d_t (d,t)
+) ENGINE=rocksdb;
+
+
+SHOW INDEX IN t1;
+SET @tm = '2012-04-09 05:27:00';
+
+INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','1990'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','1991'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','1992'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','1993'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','1994'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'1995');
+
+EXPLAIN SELECT d, t FROM t1 WHERE CONCAT(d,' ',t) != CURRENT_DATE();
+--sorted_result
+SELECT d, t FROM t1 WHERE CONCAT(d,' ',t) != CURRENT_DATE();
+
+EXPLAIN SELECT d, t FROM t1 IGNORE INDEX (d_t) WHERE CONCAT(d,' ',t) != CURRENT_DATE();
+--sorted_result
+SELECT d, t FROM t1 IGNORE INDEX (d_t) WHERE CONCAT(d,' ',t) != CURRENT_DATE();
+
+--error ER_DUP_ENTRY
+INSERT INTO t1 (d,dt,ts,t,y) VALUES
+('2012-01-12', '2010-11-22 12:33:53', '2011-11-14 21:45:55', '00:12:33', '2000');
+
+DROP TABLE t1;
+
+--enable_parsing
+
+CREATE TABLE t1 (
+  d DATE,
+  dt DATETIME,
+  ts TIMESTAMP,
+  t TIME,
+  y YEAR,
+  pk TIME PRIMARY KEY,
+  INDEX (y,t)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+SET @tm = '2012-04-09 05:27:00';
+
+INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
+('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','18:18:18'),
+('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','19:18:18'),
+('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','20:18:18'),
+('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','21:18:18'),
+('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','22:18:18'),
+(DATE(@tm), at tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'23:18:18');
+
+EXPLAIN SELECT y, COUNT(*) FROM t1 GROUP BY y;
+--sorted_result
+SELECT y, COUNT(*) FROM t1 GROUP BY y;
+
+EXPLAIN SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y;
+--sorted_result
+SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y;
+
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/rocksdb/type_enum_indexes.result b/mysql-test/suite/rocksdb/type_enum_indexes.result
new file mode 100644
index 0000000..f75f07f
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_enum_indexes.result
@@ -0,0 +1,66 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+a ENUM('N.America','S.America','Africa','Europe','Australia','Asia','Antarctica'),
+b ENUM('test1','test2','test3','test4','test5'),
+c ENUM('1a','1b','1d','1j','4a','4z','5a','5b','6v','6z') PRIMARY KEY
+) ENGINE=rocksdb;
+INSERT INTO t1 (a,b,c) VALUES
+('N.America','test1','5a'),('Europe','test1','5b'),('Europe','test2','6v'),
+('Africa','test3','4z'),('Africa','test4','1j'),('Antarctica','test4','1d');
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	c	A	NULL	NULL	NULL		BTREE		
+EXPLAIN SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	1	NULL	1000	Using where; Using index
+SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u';
+c
+1d
+1j
+4z
+5a
+5b
+EXPLAIN SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
+SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u';
+c
+1d
+1j
+4z
+5a
+5b
+DROP TABLE t1;
+CREATE TABLE t1 (
+a ENUM('N.America','S.America','Africa','Europe','Australia','Asia','Antarctica'),
+b ENUM('test1','test2','test3','test4','test5'),
+c ENUM('1a','1b','1d','1j','4a','4z','5a','5b','6v','6z'),
+pk ENUM('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
+INDEX(b)
+) ENGINE=rocksdb;
+INSERT INTO t1 (a,b,c,pk) VALUES
+('N.America','test1','5a',1),('Europe','test1','5b',2),('Europe','test2','6v',3),
+('Africa','test3','4z',4),('Africa','test4','1j',5),('Antarctica','test4','1d',6);
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE		
+EXPLAIN SELECT DISTINCT b FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary
+SELECT DISTINCT b FROM t1;
+b
+test1
+test2
+test3
+test4
+EXPLAIN SELECT DISTINCT b FROM t1 IGNORE INDEX (b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary
+SELECT DISTINCT b FROM t1 IGNORE INDEX (b);
+b
+test1
+test2
+test3
+test4
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_enum_indexes.test b/mysql-test/suite/rocksdb/type_enum_indexes.test
new file mode 100644
index 0000000..ec0633e
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_enum_indexes.test
@@ -0,0 +1,81 @@
+#
+# ENUM columns with indexes
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--disable_parsing
+--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  a ENUM('N.America','S.America','Africa','Europe','Australia','Asia','Antarctica'),
+  b ENUM('test1','test2','test3','test4','test5'),
+  c ENUM('1a','1b','1d','1j','4a','4z','5a','5b','6v','6z'),
+  pk ENUM('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
+  UNIQUE KEY a_b (a,b)
+) ENGINE=rocksdb;
+
+
+INSERT INTO t1 (a,b,c,pk) VALUES
+('N.America','test1','5a',1),('Europe','test1','5b',2),('Europe','test2','6v',3),
+('Africa','test3','4z',4),('Africa','test4','1j',5),('Antarctica','test4','1d',6);
+
+SHOW INDEX IN t1;
+
+EXPLAIN SELECT a FROM t1 WHERE b > 'test2' ORDER BY a;
+SELECT a FROM t1 WHERE b > 'test2' ORDER BY a;
+
+EXPLAIN SELECT a FROM t1 FORCE INDEX (a_b) WHERE b > 'test2' ORDER BY a;
+SELECT a FROM t1 FORCE INDEX (a_b) WHERE b > 'test2' ORDER BY a;
+
+DROP TABLE t1;
+
+--enable_parsing
+
+CREATE TABLE t1 (
+  a ENUM('N.America','S.America','Africa','Europe','Australia','Asia','Antarctica'),
+  b ENUM('test1','test2','test3','test4','test5'),
+  c ENUM('1a','1b','1d','1j','4a','4z','5a','5b','6v','6z') PRIMARY KEY
+) ENGINE=rocksdb;
+
+INSERT INTO t1 (a,b,c) VALUES
+('N.America','test1','5a'),('Europe','test1','5b'),('Europe','test2','6v'),
+('Africa','test3','4z'),('Africa','test4','1j'),('Antarctica','test4','1d');
+
+SHOW INDEX IN t1;
+
+EXPLAIN SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u';
+--sorted_result
+SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u';
+
+EXPLAIN SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u';
+--sorted_result
+SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u';
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  a ENUM('N.America','S.America','Africa','Europe','Australia','Asia','Antarctica'),
+  b ENUM('test1','test2','test3','test4','test5'),
+  c ENUM('1a','1b','1d','1j','4a','4z','5a','5b','6v','6z'),
+  pk ENUM('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
+  INDEX(b)
+) ENGINE=rocksdb;
+
+INSERT INTO t1 (a,b,c,pk) VALUES
+('N.America','test1','5a',1),('Europe','test1','5b',2),('Europe','test2','6v',3),
+('Africa','test3','4z',4),('Africa','test4','1j',5),('Antarctica','test4','1d',6);
+
+SHOW INDEX IN t1;
+
+EXPLAIN SELECT DISTINCT b FROM t1;
+--sorted_result
+SELECT DISTINCT b FROM t1;
+
+EXPLAIN SELECT DISTINCT b FROM t1 IGNORE INDEX (b);
+--sorted_result
+SELECT DISTINCT b FROM t1 IGNORE INDEX (b);
+
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/rocksdb/type_fixed_indexes.result b/mysql-test/suite/rocksdb/type_fixed_indexes.result
new file mode 100644
index 0000000..085e402
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_fixed_indexes.result
@@ -0,0 +1,126 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+d1 DECIMAL(10,2) PRIMARY KEY,
+d2 DECIMAL(60,10),
+n1 NUMERIC,
+n2 NUMERIC(65,4)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	d1	A	NULL	NULL	NULL		BTREE		
+INSERT INTO t1 (d1,d2,n1,n2) VALUES 
+(10.22,60.12345,123456,14.3456),
+(10.0,60.12345,123456,14),
+(11.14,15,123456,13),
+(100,100,1,2),
+(0,0,0,0),
+(4540424564.23,3343303441.0,12,13),
+(15,17,23,100000);
+Warnings:
+Warning	1264	Out of range value for column 'd1' at row 6
+EXPLAIN SELECT d1 FROM t1 ORDER BY d1 DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	1000	Using index
+SELECT d1 FROM t1 ORDER BY d1 DESC;
+d1
+99999999.99
+100.00
+15.00
+11.14
+10.22
+10.00
+0.00
+EXPLAIN SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	1000	Using index; Using filesort
+SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC;
+d1
+99999999.99
+100.00
+15.00
+11.14
+10.22
+10.00
+0.00
+DROP TABLE t1;
+CREATE TABLE t1 (
+d1 DECIMAL(10,2),
+d2 DECIMAL(60,10),
+n1 NUMERIC,
+n2 NUMERIC(65,4),
+pk NUMERIC PRIMARY KEY,
+UNIQUE INDEX n1_n2 (n1,n2)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	0	n1_n2	1	n1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	n1_n2	2	n2	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES
+(10.22,60.12345,123456,14.3456,1),
+(10.0,60.12345,123456,14,2),
+(11.14,15,123456,13,3),
+(100,100,1,2,4),
+(0,0,0,0,5),
+(4540424564.23,3343303441.0,12,13,6),
+(15,17,23,100000,7);
+Warnings:
+Warning	1264	Out of range value for column 'd1' at row 6
+EXPLAIN SELECT DISTINCT n1+n2 FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary
+SELECT DISTINCT n1+n2 FROM t1;
+n1+n2
+0.0000
+100023.0000
+123469.0000
+123470.0000
+123470.3456
+25.0000
+3.0000
+DROP TABLE t1;
+CREATE TABLE t1 (
+d1 DECIMAL(10,2),
+d2 DECIMAL(60,10),
+n1 NUMERIC,
+n2 NUMERIC(65,4),
+pk DECIMAL(20,10) PRIMARY KEY,
+INDEX (d2)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	d2	1	d2	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES
+(10.22,60.12345,123456,14.3456,1),
+(10.0,60.12345,123456,14,2),
+(11.14,15,123456,13,3),
+(100,100,1,2,4),
+(0,0,0,0,5),
+(4540424564.23,3343303441.0,12,13,6),
+(15,17,23,100000,7);
+Warnings:
+Warning	1264	Out of range value for column 'd1' at row 6
+EXPLAIN SELECT d2, COUNT(*) FROM t1 GROUP BY d2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT d2, COUNT(*) FROM t1 GROUP BY d2;
+d2	COUNT(*)
+0.0000000000	1
+100.0000000000	1
+15.0000000000	1
+17.0000000000	1
+3343303441.0000000000	1
+60.1234500000	2
+EXPLAIN SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2;
+d2	COUNT(*)
+0.0000000000	1
+100.0000000000	1
+15.0000000000	1
+17.0000000000	1
+3343303441.0000000000	1
+60.1234500000	2
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_fixed_indexes.test b/mysql-test/suite/rocksdb/type_fixed_indexes.test
new file mode 100644
index 0000000..e8677e9
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_fixed_indexes.test
@@ -0,0 +1,96 @@
+#
+# Fixed point columns with indexes
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+
+CREATE TABLE t1 (
+  d1 DECIMAL(10,2) PRIMARY KEY,
+  d2 DECIMAL(60,10),
+  n1 NUMERIC,
+  n2 NUMERIC(65,4)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (d1,d2,n1,n2) VALUES 
+(10.22,60.12345,123456,14.3456),
+(10.0,60.12345,123456,14),
+(11.14,15,123456,13),
+(100,100,1,2),
+(0,0,0,0),
+(4540424564.23,3343303441.0,12,13),
+(15,17,23,100000);
+
+EXPLAIN SELECT d1 FROM t1 ORDER BY d1 DESC;
+SELECT d1 FROM t1 ORDER BY d1 DESC;
+
+EXPLAIN SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC;
+SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC;
+
+DROP TABLE t1;
+
+# --error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  d1 DECIMAL(10,2),
+  d2 DECIMAL(60,10),
+  n1 NUMERIC,
+  n2 NUMERIC(65,4),
+  pk NUMERIC PRIMARY KEY,
+  UNIQUE INDEX n1_n2 (n1,n2)
+) ENGINE=rocksdb;
+
+# --disable_parsing
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES
+(10.22,60.12345,123456,14.3456,1),
+(10.0,60.12345,123456,14,2),
+(11.14,15,123456,13,3),
+(100,100,1,2,4),
+(0,0,0,0,5),
+(4540424564.23,3343303441.0,12,13,6),
+(15,17,23,100000,7);
+
+EXPLAIN SELECT DISTINCT n1+n2 FROM t1;
+--sorted_result
+SELECT DISTINCT n1+n2 FROM t1;
+
+DROP TABLE t1;
+
+#--enable_parsing
+
+CREATE TABLE t1 (
+  d1 DECIMAL(10,2),
+  d2 DECIMAL(60,10),
+  n1 NUMERIC,
+  n2 NUMERIC(65,4),
+  pk DECIMAL(20,10) PRIMARY KEY,
+  INDEX (d2)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES
+(10.22,60.12345,123456,14.3456,1),
+(10.0,60.12345,123456,14,2),
+(11.14,15,123456,13,3),
+(100,100,1,2,4),
+(0,0,0,0,5),
+(4540424564.23,3343303441.0,12,13,6),
+(15,17,23,100000,7);
+
+EXPLAIN SELECT d2, COUNT(*) FROM t1 GROUP BY d2;
+--sorted_result
+SELECT d2, COUNT(*) FROM t1 GROUP BY d2;
+
+EXPLAIN SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2;
+--sorted_result
+SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2;
+
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/rocksdb/type_float_indexes.result b/mysql-test/suite/rocksdb/type_float_indexes.result
new file mode 100644
index 0000000..eb3b1c1
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_float_indexes.result
@@ -0,0 +1,123 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+f FLOAT PRIMARY KEY,
+r REAL(20,3),
+d DOUBLE,
+dp DOUBLE PRECISION (64,10)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	f	A	NULL	NULL	NULL		BTREE		
+INSERT INTO t1 (f,r,d,dp) VALUES
+(1.2345,1422.22,1.2345,1234567.89),
+(0,0,0,0),
+(-1,-1,-1,-1),
+(17.5843,4953453454.44,29229114.0,1111111.23),
+(4644,1422.22,466664.999,0.5);
+EXPLAIN SELECT f FROM t1 ORDER BY f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	1000	Using index
+SELECT f FROM t1 ORDER BY f;
+f
+-1
+0
+1.2345
+17.5843
+4644
+EXPLAIN SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using filesort
+SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f;
+f
+-1
+0
+1.2345
+17.5843
+4644
+DROP TABLE t1;
+CREATE TABLE t1 (
+f FLOAT,
+r REAL(20,3),
+d DOUBLE,
+dp DOUBLE PRECISION (64,10),
+pk DOUBLE PRIMARY KEY,
+UNIQUE KEY r_dp (r,dp)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	0	r_dp	1	r	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	r_dp	2	dp	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (f,r,d,dp,pk) VALUES
+(1.2345,1422.22,1.2345,1234567.89,1),
+(0,0,0,0,2),
+(-1,-1,-1,-1,3),
+(17.5843,4953453454.44,29229114.0,1111111.23,4),
+(4644,1422.22,466664.999,0.5,5);
+EXPLAIN SELECT r, dp FROM t1 WHERE r > 0 or dp > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	r_dp	NULL	NULL	NULL	1000	Using where
+SELECT r, dp FROM t1 WHERE r > 0 or dp > 0;
+r	dp
+1422.220	0.5000000000
+1422.220	1234567.8900000000
+4953453454.440	1111111.2300000000
+DROP TABLE t1;
+CREATE TABLE t1 (
+f FLOAT,
+r REAL(20,3),
+d DOUBLE,
+dp DOUBLE PRECISION (64,10),
+pk FLOAT PRIMARY KEY,
+UNIQUE KEY(d)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	0	d	1	d	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (f,r,d,dp,pk) VALUES
+(1.2345,1422.22,1.2345,1234567.89,1),
+(0,0,0,0,2),
+(-1,-1,-1,-1,3),
+(17.5843,4953453454.44,29229114.0,1111111.23,4),
+(4644,1422.22,466664.999,0.5,5);
+EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT DISTINCT d FROM t1 ORDER BY d;
+d
+-1
+0
+1.2345
+466664.999
+29229114
+DROP TABLE t1;
+CREATE TABLE t1 (
+f FLOAT,
+r REAL(20,3),
+d DOUBLE,
+dp DOUBLE PRECISION (64,10),
+pk FLOAT PRIMARY KEY,
+KEY(d)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	d	1	d	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (f,r,d,dp,pk) VALUES
+(1.2345,1422.22,1.2345,1234567.89,1),
+(0,0,0,0,2),
+(-1,-1,-1,-1,3),
+(17.5843,4953453454.44,29229114.0,1111111.23,4),
+(4644,1422.22,466664.999,0.5,5);
+EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT DISTINCT d FROM t1 ORDER BY d;
+d
+-1
+0
+1.2345
+466664.999
+29229114
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_float_indexes.test b/mysql-test/suite/rocksdb/type_float_indexes.test
new file mode 100644
index 0000000..c118624
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_float_indexes.test
@@ -0,0 +1,107 @@
+#
+# Float type columns with indexes
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  f FLOAT PRIMARY KEY,
+  r REAL(20,3),
+  d DOUBLE,
+  dp DOUBLE PRECISION (64,10)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (f,r,d,dp) VALUES
+(1.2345,1422.22,1.2345,1234567.89),
+(0,0,0,0),
+(-1,-1,-1,-1),
+(17.5843,4953453454.44,29229114.0,1111111.23),
+(4644,1422.22,466664.999,0.5);
+
+EXPLAIN SELECT f FROM t1 ORDER BY f;
+SELECT f FROM t1 ORDER BY f;
+
+EXPLAIN SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f;
+SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f;
+
+DROP TABLE t1;
+
+#--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  f FLOAT,
+  r REAL(20,3),
+  d DOUBLE,
+  dp DOUBLE PRECISION (64,10),
+  pk DOUBLE PRIMARY KEY,
+  UNIQUE KEY r_dp (r,dp)
+) ENGINE=rocksdb;
+
+#--disable_parsing
+
+SHOW INDEX IN t1;
+  
+INSERT INTO t1 (f,r,d,dp,pk) VALUES
+(1.2345,1422.22,1.2345,1234567.89,1),
+(0,0,0,0,2),
+(-1,-1,-1,-1,3),
+(17.5843,4953453454.44,29229114.0,1111111.23,4),
+(4644,1422.22,466664.999,0.5,5);
+
+EXPLAIN SELECT r, dp FROM t1 WHERE r > 0 or dp > 0;
+--sorted_result
+SELECT r, dp FROM t1 WHERE r > 0 or dp > 0;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  f FLOAT,
+  r REAL(20,3),
+  d DOUBLE,
+  dp DOUBLE PRECISION (64,10),
+  pk FLOAT PRIMARY KEY,
+  UNIQUE KEY(d)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (f,r,d,dp,pk) VALUES
+(1.2345,1422.22,1.2345,1234567.89,1),
+(0,0,0,0,2),
+(-1,-1,-1,-1,3),
+(17.5843,4953453454.44,29229114.0,1111111.23,4),
+(4644,1422.22,466664.999,0.5,5);
+
+EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d;
+SELECT DISTINCT d FROM t1 ORDER BY d;
+
+DROP TABLE t1;
+
+#--enable_parsing
+
+CREATE TABLE t1 (
+  f FLOAT,
+  r REAL(20,3),
+  d DOUBLE,
+  dp DOUBLE PRECISION (64,10),
+  pk FLOAT PRIMARY KEY,
+  KEY(d)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (f,r,d,dp,pk) VALUES
+(1.2345,1422.22,1.2345,1234567.89,1),
+(0,0,0,0,2),
+(-1,-1,-1,-1,3),
+(17.5843,4953453454.44,29229114.0,1111111.23,4),
+(4644,1422.22,466664.999,0.5,5);
+
+EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d;
+SELECT DISTINCT d FROM t1 ORDER BY d;
+
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/rocksdb/type_set_indexes.result b/mysql-test/suite/rocksdb/type_set_indexes.result
new file mode 100644
index 0000000..be0d098
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_set_indexes.result
@@ -0,0 +1,77 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
+b SET('test1','test2','test3','test4','test5'),
+c SET('01','22','23','33','34','39','40','44','50','63','64') PRIMARY KEY
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	c	A	NULL	NULL	NULL		BTREE		
+INSERT INTO t1 (a,b,c) VALUES 
+('','test2,test3','01,34,44,23'),
+('',5,2),
+('N.America,Asia','test4,test2',''),
+('Africa,Europe,Asia','test2,test3','01'),
+('Antarctica','test3','34,44'),
+('Asia','test5','50'),
+('Europe,S.America','test1,','39');
+Warnings:
+Warning	1265	Data truncated for column 'b' at row 7
+EXPLAIN SELECT c FROM t1 ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	2	NULL	1000	Using index
+SELECT c FROM t1 ORDER BY c;
+c
+
+01
+22
+39
+34,44
+01,23,34,44
+50
+EXPLAIN SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	2	NULL	1000	Using index; Using filesort
+SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
+c
+
+01
+22
+39
+34,44
+01,23,34,44
+50
+INSERT INTO t1 (a,b,c) VALUES ('Antarctica','test3','02');
+ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
+INSERT INTO t1 (a,b,c) VALUES ('','test1','34,44');
+ERROR 23000: Duplicate entry '34,44' for key 'PRIMARY'
+DROP TABLE t1;
+CREATE TABLE t1 (
+a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
+b SET('test1','test2','test3','test4','test5'),
+c SET('01','22','23','33','34','39','40','44','50','63','64'),
+pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
+INDEX(a)
+) ENGINE=rocksdb;
+SHOW INDEX IN t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	NULL	NULL	NULL		BTREE		
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+INSERT INTO t1 (a,b,c,pk) VALUES
+('','test2,test3','01,34,44,23',1),
+('',5,2,2),
+('N.America,Asia','test4,test2','',3),
+('Africa,Europe,Asia','test2,test3','01',4),
+('Antarctica','test3','34,44',5),
+('Asia','test5','50',6),
+('Europe,S.America','test1,','39',7);
+Warnings:
+Warning	1265	Data truncated for column 'b' at row 7
+EXPLAIN SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
+SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
+a
+Africa,Europe,Asia
+S.America,Europe
+DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/type_set_indexes.test b/mysql-test/suite/rocksdb/type_set_indexes.test
new file mode 100644
index 0000000..8c2620d
--- /dev/null
+++ b/mysql-test/suite/rocksdb/type_set_indexes.test
@@ -0,0 +1,94 @@
+#
+# SET columns with indexes
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
+  b SET('test1','test2','test3','test4','test5'),
+  c SET('01','22','23','33','34','39','40','44','50','63','64') PRIMARY KEY
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c) VALUES 
+('','test2,test3','01,34,44,23'),
+('',5,2),
+('N.America,Asia','test4,test2',''),
+('Africa,Europe,Asia','test2,test3','01'),
+('Antarctica','test3','34,44'),
+('Asia','test5','50'),
+('Europe,S.America','test1,','39');
+
+EXPLAIN SELECT c FROM t1 ORDER BY c;
+SELECT c FROM t1 ORDER BY c;
+
+EXPLAIN SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
+SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
+
+--error ER_DUP_ENTRY
+INSERT INTO t1 (a,b,c) VALUES ('Antarctica','test3','02');
+
+--error ER_DUP_ENTRY
+INSERT INTO t1 (a,b,c) VALUES ('','test1','34,44');
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
+  b SET('test1','test2','test3','test4','test5'),
+  c SET('01','22','23','33','34','39','40','44','50','63','64'),
+  pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
+  INDEX(a)
+) ENGINE=rocksdb;
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c,pk) VALUES
+('','test2,test3','01,34,44,23',1),
+('',5,2,2),
+('N.America,Asia','test4,test2','',3),
+('Africa,Europe,Asia','test2,test3','01',4),
+('Antarctica','test3','34,44',5),
+('Asia','test5','50',6),
+('Europe,S.America','test1,','39',7);
+
+EXPLAIN SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
+--sorted_result
+SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
+
+DROP TABLE t1;
+
+--disable_parsing
+#--error ER_GET_ERRMSG
+CREATE TABLE t1 (
+  a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
+  b SET('test1','test2','test3','test4','test5'),
+  c SET('01','22','23','33','34','39','40','44','50','63','64'),
+  pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
+  UNIQUE INDEX b_a (b,a)
+) ENGINE=innodb;
+
+
+SHOW INDEX IN t1;
+
+INSERT INTO t1 (a,b,c) VALUES
+('','test2,test3','01,34,44,23'),
+('',5,2),
+('N.America,Asia','test4,test2',''),
+('Africa,Europe,Asia','test2,test3','01'),
+('Antarctica','test3','34,44'),
+('Asia','test5','50'),
+('Europe,S.America','test1,','39');
+
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a;
+--sorted_result
+SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a;
+
+DROP TABLE t1;
+
+--enable_parsing
+


More information about the commits mailing list