diff options
Diffstat (limited to '')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result | 189 |
1 files changed, 189 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result new file mode 100644 index 00000000..ca6ed6d5 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result @@ -0,0 +1,189 @@ +SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK; +SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1; +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 1000 NULL NULL LSMTREE +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 # 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 # 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 1000 NULL NULL LSMTREE +t1 0 r_dp 1 r A 500 NULL NULL YES LSMTREE +t1 0 r_dp 2 dp A 1000 NULL NULL YES LSMTREE +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 index r_dp r_dp 18 NULL # Using where; Using index +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 1000 NULL NULL LSMTREE +t1 0 d 1 d A 1000 NULL NULL YES LSMTREE +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 index NULL d 9 NULL # Using index +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 1000 NULL NULL LSMTREE +t1 1 d 1 d A 500 NULL NULL YES LSMTREE +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 range NULL d 9 NULL # Using index for group-by +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, +UNIQUE KEY(f) +) 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 1000 NULL NULL LSMTREE +t1 0 f 1 f A 1000 NULL NULL YES LSMTREE +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); +INSERT INTO t1 (f,r,d,dp,pk) VALUES +(1.2345,0,0,0,6); +ERROR 23000: Duplicate entry '1.2345' for key 'f' +EXPLAIN SELECT DISTINCT f FROM t1 ORDER BY f; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL f 5 NULL # Using index +SELECT DISTINCT f FROM t1 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 FLOAT PRIMARY KEY, +KEY(f) +) 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 1000 NULL NULL LSMTREE +t1 1 f 1 f A 500 NULL NULL YES LSMTREE +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); +INSERT INTO t1 (f,r,d,dp,pk) VALUES +(1.2345,0,0,0,6); +EXPLAIN SELECT DISTINCT f FROM t1 ORDER BY f; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL f 5 NULL # Using index for group-by +SELECT DISTINCT f FROM t1 ORDER BY f; +f +-1 +0 +1.2345 +17.5843 +4644 +DROP TABLE t1; +SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK; |