[MDEV-11462] MariaRocks: rocksdb.type_float_indexes fails Created: 2016-12-02  Updated: 2016-12-02  Resolved: 2016-12-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-9658 Make MyRocks in MariaDB stable Closed

 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



 Comments   
Comment by Sergei Petrunia [ 2016-12-02 ]

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 |
 
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Comment by Sergei Petrunia [ 2016-12-02 ]

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

Comment by Sergei Petrunia [ 2016-12-02 ]

Fix pushed to work tree.

Generated at Thu Feb 08 07:50:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.