[MDEV-14607] storage_engine-rocksdb.type_bit_indexes fails after latest pushes Created: 2017-12-07  Updated: 2017-12-15  Resolved: 2017-12-15

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: 10.2
Fix Version/s: 10.2.12

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

Issue Links:
PartOf
is part of MDEV-14563 Wrong query plan for query with no PK Closed

 Description   

After recent pushes (see below) storage_engine-rocksdb.type_bit_indexes test started to fail like so:

--- /home/psergey/dev-git/10.2-mariarocks-r4/mysql-test/suite/storage_engine/type_bit_indexes.result    2017-10-13 16:35:16.642453564 +0300
 
+++ /home/psergey/dev-git/10.2-mariarocks-r4/mysql-test/suite/storage_engine/type_bit_indexes.reject    2017-12-07 23:37:45.628012903 +0300
 
@@ -69,7 +69,7 @@
 
 (1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF);
 
 EXPLAIN SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-#      #       #       #       #       b_c     #       #       #       #
 
+#      #       #       #       #       NULL    #       #       #       #
 
 SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF;
 
 HEX(b+c)
 
 10
 
@@ -98,7 +98,7 @@
 
 (1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF);
 
 EXPLAIN SELECT DISTINCT a+0 FROM t1 ORDER BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-#      #       #       #       #       a       #       #       #       #
 
+#      #       #       #       #       NULL    #       #       #       #
 
 SELECT DISTINCT a+0 FROM t1 ORDER BY a;
 
 a+0
 
 0

The recent pushes are these three:

commit 2c1e4d4d7a174c180cfcac5e245840aec8458b77
 
Author: Sergei Petrunia <psergey@askmonty.org>
 
Date:   Tue Dec 5 16:33:38 2017 +0300
 
 
 
    MDEV-14563: Wrong query plan for query with no PK
 
    
    Part #2: Don't use the new code for the clustered PK, it is handled
 
    in the special way right above.
 
 
 
commit a6254e5e7d0ed5d951158c43d95b41d18b71e210
 
Author: Sergei Petrunia <psergey@askmonty.org>
 
Date:   Mon Dec 4 15:01:57 2017 +0300
 
 
 
    MDEV-14563: Wrong query plan for query with no PK
 
    
    TABLE_SHARE::init_from_binary_frm_image() calls handler_file->index_flags()
 
    before it has set TABLE_SHARE::primary_key (it is 0 while it should be
 
    MAX_KEY in my example).
 
    This causes MyRocks to report wrong index flags (it thinks it's a PK while
 
    it is not), which causes invalid query plans later on.
 
    
    Do the only thing that seems feasible: adjust field->part_of key to have
 
    correct value in ha_rocksdb::open.
 
 
 
commit c3803914c5c5029ac6117618fcf98b6617263970
 
Author: Sergei Petrunia <psergey@askmonty.org>
 
Date:   Sat Dec 2 17:26:37 2017 +0000
 
 
 
    MDEV-14433: RocksDB may show empty or incorrect output with rocksdb_strict_collation_check=off
 
    
    Part#1: Set field->part_of_key correctly for PK fields.



 Comments   
Comment by Sergei Petrunia [ 2017-12-07 ]

Debugged. Before the mentioned fixes, EXPLAIN does show type=index and "Using index"

MariaDB [j201]> 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    | index | NULL          | a    | 2       | NULL |    0 | Using index; Using temporary; Using filesort |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+

however actual execution fetches the whole rows:

  #0  myrocks::ha_rocksdb::get_row_by_rowid (this=0x7fff6c0228b8, buf=0x7fff6c01d608 "\377", rowid=0x7fff6c025f60 "", rowid_size=12, skip_lookup=false, skip_ttl_check=true) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/storage/rocksdb/ha_rocksdb.cc:7752
 
  #1  0x00007fffe765dbf1 in myrocks::ha_rocksdb::get_row_by_rowid (this=0x7fff6c0228b8, buf=0x7fff6c01d608 "\377", rowid=0x7fff6c025f60 "", rowid_size=12, skip_lookup=false, skip_ttl_check=true) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/storage/rocksdb/./ha_rocksdb.h:649
 
  #2  0x00007fffe7642894 in myrocks::ha_rocksdb::secondary_index_read (this=0x7fff6c0228b8, keyno=0, buf=0x7fff6c01d608 "\377") at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/storage/rocksdb/ha_rocksdb.cc:7122
 
  #3  0x00007fffe764519f in myrocks::ha_rocksdb::index_next_with_direction (this=0x7fff6c0228b8, buf=0x7fff6c01d608 "\377", move_forward=true) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/storage/rocksdb/ha_rocksdb.cc:7889
 
  #4  0x00007fffe76455a3 in myrocks::ha_rocksdb::index_first_intern (this=0x7fff6c0228b8, buf=0x7fff6c01d608 "\377") at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/storage/rocksdb/ha_rocksdb.cc:7999
 
  #5  0x00007fffe7645275 in myrocks::ha_rocksdb::index_first (this=0x7fff6c0228b8, buf=0x7fff6c01d608 "\377") at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/storage/rocksdb/ha_rocksdb.cc:7906
 
  #6  0x0000555555d78c73 in handler::ha_index_first (this=0x7fff6c0228b8, buf=0x7fff6c01d608 "\377") at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/sql/handler.cc:2717
 
  #7  0x0000555555b7324f in join_read_first (tab=0x7fff6c012fa0) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/sql/sql_select.cc:19623
 
  #8  0x0000555555b70ce6 in sub_select (join=0x7fff6c011d00, join_tab=0x7fff6c012fa0, end_of_records=false) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/sql/sql_select.cc:18635
 
  #9  0x0000555555b702e1 in do_select (join=0x7fff6c011d00, procedure=0x0) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/sql/sql_select.cc:18182
 
  #10 0x0000555555b4a9e7 in JOIN::exec_inner (this=0x7fff6c011d00) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/sql/sql_select.cc:3514
 
  #11 0x0000555555b49e96 in JOIN::exec (this=0x7fff6c011d00) at /home/psergey/dev-git/10.2-mariarocks-r4-lb2/sql/sql_select.cc:3309

MyRocks doesn't have a way to decode BIT columns from secondary indexes (this is actually doable but not implemented).

The reason it choses to do index-only read is the same as MDEV-14563 - it thinks the first table in the index is a clustered PK (while it is not)

Comment by Sergei Petrunia [ 2017-12-11 ]

After the fix, there is this failure on Windows:
http://buildbot.askmonty.org/buildbot/builders/qa-win-rel/builds/4299/steps/storage_engine/logs/stdio

--- D:/qa-win-rel/build/mysql-test/suite/storage_engine/type_bit_indexes.result	2017-12-11 11:30:49.279578800 +0000
 
+++ D:\qa-win-rel\build\mysql-test\suite\storage_engine\type_bit_indexes.reject	2017-12-11 11:46:49.462077000 +0000
 
@@ -69,7 +69,7 @@
 
 (1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF);
 
 EXPLAIN SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF;
 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
-#	#	#	#	#	NULL	#	#	#	#
 
+#	#	#	#	#	b_c	#	#	#	#

The plan is changed back to the old one...

Comment by Sergei Petrunia [ 2017-12-12 ]

Does not fail for me when I try a Debug build of the current 10.2 tree (tip cset 86c69263a441dd6fb2de180dba846eb4fc53a5f0)

Comment by Sergei Petrunia [ 2017-12-12 ]

... and the same goes for a release build.

Comment by Sergei Petrunia [ 2017-12-12 ]

storage_engine-rocksdb.type_blob_indexes – collations
storage_engine-rocksdb.type_enum_indexes – plan
storage_engine-rocksdb.type_set_indexes – plan
storage_engine-rocksdb.type_binary_indexes – collations

elenst, I've checked. MyRocks' change for type_enum_indexes and type_set_indexes is ok. It is caused by this MyRocks' property: https://mariadb.com/kb/en/library/myrocks-and-index-only-scans/ which makes it different from InnoDB/MyISAM.

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