[Maria-developers] issue with Index Condition Pushdown in MariaDB 5.5.28a
Hello, I have found an interesting issue with index condition pushdown. I do not know if it is a bug, or if I am misusing the feature. I have found that in one scenario, when performing a join, an index condition is pushed down, but because handler->end_range is not set, handler_index_cond_check never returns ICP_OUT_OF_RANGE, even though we are out of range. Here is the relevant stack trace, I cannot yet send the entire trace as it may include customer information, and I need to double check it is ok to send: #15 0x00002aaac4f33593 in ha_tokudb::index_next_same (this=0x2fb33d8, buf=0x2fb53b8 "\302\177\254\353/\036", key=0x31b36d0 "Y\001", keylen=395) ---Type <return> to continue, or q <return> to quit--- at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/storage/tokudb/ha_tokudb.cc:4768 #16 0x00000000005e782c in handler::ha_index_next_same (this=0x2fb33d8, buf=0x2fb53b8 "\302\177\254\353/\036", key=0x31b36d0 "Y\001", keylen=395) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_class.h:4246 #17 0x000000000065d103 in join_read_next_same (info=0x31b2f68) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16929 #18 0x000000000065b54a in sub_select (join=0x319f588, join_tab=0x31b2eb8, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16142 #19 0x000000000065bb5f in evaluate_join_record (join=0x319f588, join_tab=0x31b2b98, error=0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16342 #20 0x000000000065b628 in sub_select (join=0x319f588, join_tab=0x31b2b98, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16162 #21 0x000000000065bb5f in evaluate_join_record (join=0x319f588, join_tab=0x31b2878, error=0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16342 #22 0x000000000065b498 in sub_select (join=0x319f588, join_tab=0x31b2878, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16123 #23 0x000000000065bb5f in evaluate_join_record (join=0x319f588, join_tab=0x31b2558, error=0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16342 #24 0x000000000065b628 in sub_select (join=0x319f588, join_tab=0x31b2558, end_of_records=false) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:16162 #25 0x000000000065ad52 in do_select (join=0x319f588, fields=0x0, table=0x31bf368, procedure=0x0) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:15793 #26 0x0000000000639c5d in JOIN::exec (this=0x319f588) at /home/zardosht/maria/mysql-build/mariadb-5.5.28a/sql/sql_select.cc:2396 In this stack, we are performing an index_next_same, and a condition has been pushed down. When we call handler_index_cond_check. we always get ICP_NO_MATCH and never get ICP_OUT_OF_RANGE, even though we go past the key that we should be retrieving. Is this by design? Is this a bug? Thanks -Zardosht
Hi Zardosht, On Thu, Mar 07, 2013 at 11:08:01PM -0500, Zardosht Kasheff wrote:
I have found an interesting issue with index condition pushdown. I do not know if it is a bug, or if I am misusing the feature.
I have found that in one scenario, when performing a join, an index condition is pushed down, but because handler->end_range is not set, handler_index_cond_check never returns ICP_OUT_OF_RANGE, even though we are out of range.
Confirm. I have looked how it works in MyISAM and InnoDB. Indeed, they consider scan over equality to be a special case. They remember the lookup key and check it themselves. That is, if one does h->index_read(key='foo')=0 h->index_next_same() then index_next_same() will check whether the next index tuple has key='foo'. If the key is different, it will return HA_ERR_END_OF_FILE without checking the index condition. I'm hesitant to say that this is "by design", I'd say that the available storage engines happened to work this way, and then ICP used that. Maybe, this means that implementation of handler::index_next_same() should set handler::end_range. If it did, a storage engine will be able to support ICP without implementing index_next_same() call. The question for MariaDB is, does it make sense for us to make this change in MariaDB, when Oracle doesn't support it? (As far as I understand, TokuDB will still need to support MySQL, and so will have to work around the problem by supporting index_next_same() and comparing the keys like MyISAM/InnoDB do. When you have to do that anyway, there is no benefit from having this problem fixed in MariaDB...)
Here is the relevant stack trace, I cannot yet send the entire trace as it may include customer information, and I need to double check it is ok to send:
<skip>
In this stack, we are performing an index_next_same, and a condition has been pushed down. When we call handler_index_cond_check. we always get ICP_NO_MATCH and never get ICP_OUT_OF_RANGE, even though we go past the key that we should be retrieving.
Is this by design? Is this a bug?
-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
What you say is accurate. I have fixed this issue in our engine, but I just wanted to make you aware of it. On Mon, Mar 11, 2013 at 4:56 PM, Sergei Petrunia <psergey@askmonty.org> wrote:
Hi Zardosht,
On Thu, Mar 07, 2013 at 11:08:01PM -0500, Zardosht Kasheff wrote:
I have found an interesting issue with index condition pushdown. I do not know if it is a bug, or if I am misusing the feature.
I have found that in one scenario, when performing a join, an index condition is pushed down, but because handler->end_range is not set, handler_index_cond_check never returns ICP_OUT_OF_RANGE, even though we are out of range.
Confirm. I have looked how it works in MyISAM and InnoDB. Indeed, they consider scan over equality to be a special case. They remember the lookup key and check it themselves.
That is, if one does
h->index_read(key='foo')=0 h->index_next_same()
then index_next_same() will check whether the next index tuple has key='foo'. If the key is different, it will return HA_ERR_END_OF_FILE without checking the index condition.
I'm hesitant to say that this is "by design", I'd say that the available storage engines happened to work this way, and then ICP used that.
Maybe, this means that implementation of handler::index_next_same() should set handler::end_range. If it did, a storage engine will be able to support ICP without implementing index_next_same() call.
The question for MariaDB is, does it make sense for us to make this change in MariaDB, when Oracle doesn't support it?
(As far as I understand, TokuDB will still need to support MySQL, and so will have to work around the problem by supporting index_next_same() and comparing the keys like MyISAM/InnoDB do. When you have to do that anyway, there is no benefit from having this problem fixed in MariaDB...)
Here is the relevant stack trace, I cannot yet send the entire trace as it may include customer information, and I need to double check it is ok to send:
<skip>
In this stack, we are performing an index_next_same, and a condition has been pushed down. When we call handler_index_cond_check. we always get ICP_NO_MATCH and never get ICP_OUT_OF_RANGE, even though we go past the key that we should be retrieving.
Is this by design? Is this a bug?
-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
participants (2)
-
Sergei Petrunia -
Zardosht Kasheff