Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
possible_keys=NULL in MariaDB, while it is 'd' in MySQL.
MySQL's output looks more logical. Need to investigate where the difference comes from.
rocksdb.type_float_indexes [ fail ]
|
Test ended at 2016-12-02 12:30:35
|
|
|
CURRENT_TEST: rocksdb.type_float_indexes
|
--- /home/psergey/dev-git/10.2-mariarocks/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result 2016-10-06 17:30:25.595958776 +0000
|
+++ /home/psergey/dev-git/10.2-mariarocks/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.reject 2016-12-02 12:30:35.681517143 +0000
|
@@ -85,7 +85,7 @@
|
(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 d d 9 NULL # Using index
|
+1 SIMPLE t1 index NULL d 9 NULL # Using index
|
SELECT DISTINCT d FROM t1 ORDER BY d;
|
d
|
-1
|
@@ -114,7 +114,7 @@
|
(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 d d 9 NULL # Using index
|
+1 SIMPLE t1 index NULL d 9 NULL # Using index
|
SELECT DISTINCT d FROM t1 ORDER BY d;
|
d
|
-1
|
@@ -146,7 +146,7 @@
|
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 f f 5 NULL # Using index
|
+1 SIMPLE t1 index NULL f 5 NULL # Using index
|
SELECT DISTINCT f FROM t1 ORDER BY f;
|
f
|
-1
|
@@ -177,7 +177,7 @@
|
(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 index f f 5 NULL # Using index
|
+1 SIMPLE t1 index NULL f 5 NULL # Using index
|
SELECT DISTINCT f FROM t1 ORDER BY f;
|
f
|
-1
|
Attachments
Issue Links
- is part of
-
MDEV-9658 Make MyRocks in MariaDB stable
-
- Closed
-
Activity
Checked Field::part_of_key, Field::key_start - no MariaDB vs MySQL differences.
Debugged to
#0 add_group_and_distinct_keys (join=0x7fffe00067e8, join_tab=0x7fffe0006d48) at /home/psergey/dev-git/mysql-5.6-rocksdb-look210/sql/sql_optimizer.cc:5817
|
#1 0x0000000000e6c2e3 in make_join_statistics (join=0x7fffe00067e8, tables_arg=0x7fffe00056a8, conds=0x0, keyuse_array=0x7fffe0006a10, first_optimization=true) at /home/psergey/dev-git/mysql-5.6-rocksdb-look210/sql/sql_optimizer.cc:3673
|
#2 0x0000000000e63575 in JOIN::optimize (this=0x7fffe00067e8) at /home/psergey/dev-git/mysql-5.6-rocksdb-look210/sql/sql_optimizer.cc:363
|
MySQL has:
if (!possible_keys.is_clear_all() &&
|
!possible_keys.is_subset(join_tab->const_keys))
|
{
|
trace_indices_added_group_distinct(&join->thd->opt_trace, join_tab,
|
possible_keys, cause);
|
join_tab->const_keys.merge(possible_keys);
|
join_tab->keys.merge(possible_keys);
|
}
|
MariaDB has just:
if (!possible_keys.is_clear_all())
|
join_tab->const_keys.merge(possible_keys);
|
the line that modifies tab->keys comes from:
https://github.com/mysql/mysql-server/commit/51145cbf7c52bc9d4e837df04297093062a1f942
BUG#15849135: KEYS USABLE ONLY FOR GROUPING ADDED TO
|
JOIN_TAB::CONST_KEYS BUT NOT J_T::KEYS
|
If I run ANALYZE TABLE, I get type=range for both servers. possible_keys still has the same difference:
MariaDB [j2]> 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 | 3 | Using index for group-by |+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+MySQL [j2]> 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 | d | d | 9 | NULL | 3 | Using index for group-by |+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+