Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
Attachments
Activity
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
|
Ok
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.
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.
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.
kentoku, if you disagree, feel free to re-open the bug and provide an example where this patch is useful.
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;