[#MDEV-7739] Spiral patch 043_mariadb-10.0.15.SQL_SELECT_test_quick

Comment by Sergei Petrunia [ 2015-10-15 ]

A testcase that allows to get the patch location in debugger:

create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int);

insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;

CREATE TABLE t1 (

  a text,

  b int(11) DEFAULT NULL,

  KEY b (b),

  FULLTEXT KEY a (a)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 select a,a from one_k;

explain select * from t1 where a='oo' and b <333;

Comment by Sergei Petrunia [ 2015-10-15 ]

I put a breakpoint in SQL_SELECT::test_quick_select, and when I get there, I get:

(gdb) p keys_to_use

  $21 = {map = 3}

(gdb) p head->key_info[0].name

  $22 = 0x7fff54059c11 "b"

(gdb) p head->key_info[0].flags & HA_FULLTEXT

  $23 = 0

(gdb) p head->key_info[1].name

  $24 = 0x7fff54059c13 "a"

(gdb) p head->key_info[1].flags & HA_FULLTEXT

  $25 = 128

Now, let's follow till get_mm_tree() call, and see what get_mm_tree() returns:

(gdb) p tree

  $26 = (SEL_TREE *) 0x7fff54067a20

(gdb) p *tree

  $27 = {<Sql_alloc> = {<No data fields>}, type = SEL_TREE::KEY, keys = {0x7fff54067c80, 0x0 <repeats 63 times>}, keys_map = {map = 1}, merges = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x555556ce7160, last = 0x7fff54067c30, elements = 0}, <No data fields>}, ror_scans_map = {map = 11936128518282651045}, n_ror_scans = 2779096485, index_scans = 0xa5a5a5a5a5a5a5a5, index_scans_end = 0xa5a5a5a5a5a5a5a5, ror_scans = 0xa5a5a5a5a5a5a5a5, ror_scans_end = 0xa5a5a5a5a5a5a5a5}

(gdb)

(gdb) p param.real_keynr[0]

  $28 = 0

So, range optimizer only got range on key number 0, which is KEY b (b). We don't get ranges on fulltext keys.

Comment by Sergei Petrunia [ 2015-10-15 ]

Trying a fulltext predicate

explain select * from t1 where match(a) against ('1' in boolean mode) and b <333;

(gdb) p keys_to_use

  $42 = {map = 1}

One can see that fulltext index is not in the list of indexes being considered.

Comment by Sergei Petrunia [ 2015-10-15 ]

Ok, here is the query that hits the case that this patch has in mind:

explain select * from t1 where a='oo';

Here, we arrive in SQL_SELECT::test_quick_select() with keys_to_use=

{map = 2}

. That is, the bitmap only includes the fulltext index.

The problem is that I don't see why further analysis is harmful. Check out below:

      if (key_info->flags & HA_FULLTEXT)

	continue;    // ToDo: ft-keys in non-ft ranges, if possible   SerG

This means range analysis will not attempt to analyze full text keys. We will have zero keys for which to do range analysis for:

(gdb)

  #2  0x0000555555d9dda1 in SQL_SELECT::test_quick_select (this=0x7fff5400a1f8, thd=0x55555acbdd20, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true) at /home/psergey/dev-git/10.1-dbg3/sql/opt_range.cc:2542

(gdb)

(gdb) p param.key_parts_end

  $95 = (KEY_PART *) 0x7fff5406e158

(gdb) p param.key_parts_end - param.key_parts

  $97 = 0

Range analyzer will traverse the WHERE condition down to its individual predicates. But then will not do anything. For the example above, we enter here:

  #0  Item_bool_func::get_mm_parts (this=0x7fff54008ef8, param=0x7ffff42b60b0, field=0x7fff5404b310, type=Item_func::EQ_FUNC, value=0x7fff54008e50) at /home/psergey/dev-git/10.1-dbg3/sql/opt_range.cc:7634

  #1  0x0000555555da8cb7 in Item_equal::get_mm_tree (this=0x7fff54008ef8, param=0x7ffff42b60b0, cond_ptr=0x7fff5400a200) at /home/psergey/dev-git/10.1-dbg3/sql/opt_range.cc:7568

  #2  0x0000555555d9dda1 in SQL_SELECT::test_quick_select (this=0x7fff5400a1f8, thd=0x55555acbdd20, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true) at /home/psergey/dev-git/10.1-dbg3/sql/opt_range.cc:2542

  #3  0x0000555555a8e3f9 in get_quick_record_count (thd=0x55555acbdd20, select=0x7fff5400a1f8, table=0x7fff540424f0, keys=0x7fff540092b0, limit=18446744073709551615) at /home/psergey/dev-git/10.1-dbg3/sql/sql_select.cc:3478

and then there is a loop

  for (; key_part != end ; key_part++)

which we execute 0 times.

Comment by Sergei Petrunia [ 2015-10-15 ]

I conclude that this patch is not needed.

Comment by Sergei Petrunia [ 2015-10-15 ]

hours logged

Comment by Sergei Petrunia [ 2015-10-15 ]

kentoku, if you disagree, feel free to re-open the bug and provide an example where this patch is useful.

[MDEV-7739] Spiral patch 043_mariadb-10.0.15.SQL_SELECT_test_quick_select.diff Created: 2015-03-11 Updated: 2016-12-21 Resolved: 2015-10-15
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Fix Version/s:	N/A

[MDEV-7739] Spiral patch 043_mariadb-10.0.15.SQL_SELECT_test_quick_select.diff Created: 2015-03-11 Updated: 2016-12-21 Resolved: 2015-10-15