[MDEV-29871] innodb_fts.fulltext_misc unexpectedly reports a result Created: 2022-10-25  Updated: 2023-02-21  Resolved: 2023-02-21

Status: Closed
Project: MariaDB Server
Component/s: Full-text Search, Storage Engine - InnoDB, Tests
Affects Version/s: 10.6
Fix Version/s: 10.11.3, 11.0.1, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Critical
Reporter: Marko Mäkelä Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: wrong_result


 Description   

On a merge to 10.6, the probability that the test innodb_fts.fulltext_misc occasionally returns a different result is significantly increasing:

CURRENT_TEST: innodb_fts.fulltext_misc
 
--- /home/buildbot/amd64-ubuntu-2004-debug/build/mysql-test/suite/innodb_fts/r/fulltext_misc.result	2022-10-25 11:30:21.000000000 +0000
 
+++ /home/buildbot/amd64-ubuntu-2004-debug/build/mysql-test/suite/innodb_fts/r/fulltext_misc.reject	2022-10-25 11:45:15.970847583 +0000
 
@@ -131,6 +131,7 @@
 
 test
 
 select * from t1 where match a against ("te*" in boolean mode)+0;
 
 a
 
+test
 
 drop table t1;
 
 # 
 # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 
mysqltest: Result length mismatch

I will work around this issue by disabling this part of the test. The reason of the failure needs to be investigated and fixed.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2022-12-05 ]

Test case to repeat the problem :

--source include/have_innodb.inc
 
create table t1 (a varchar(10), primary key(a(10)), fulltext (a)) ENGINE = InnoDB;
 
insert into t1 values ("a"), ("abc"), ("abcd"), ("hello"), ("test");
 
# InnoDB_FTS: we don't support the postfix "+0"
 
# Work around MDEV-29871 (FIXME: remove this)
 
select * from t1 where match a against ("te*" in boolean mode);
 
drop table t1;

Above select should throw "test" value as query result. But it fails to do so
constitently. Let me explain how select works in

i) Server calls ha_innobase::ft_init_ext() which calls fts_query() and it finds the matching string
and calculate the ranking for the query results. Ranking tree (intermediate rb tree) which
has matching string doc id and its rank (our case, doc_id is 5 and rank is 0.488)

ii) Server does full index scan, reads single row and assigns FTS_DOC_ID in prebuilt->fts_doc_id

iii) In join operation, server calls fts_retrieve_ranking() and check whether the
intermediate rank tree has the same doc id as prebuilt->fts_doc_id

Here the problem is that server converts the floating value to real value. It basically
converts the value less than 0.5 as 0 and it leads to this failure

select_cond_result= MY_TEST(select_cond->val_int());

Let me paste the stack trace where we convert the float to real value:

(rr) where
 
#0  fts_retrieve_ranking (result=0x7f5bec1b8bb8, doc_id=5) at /home/thiru/mariarepo/10.6/10.6-sample/storage/innobase/fts/fts0que.cc:3629
 
#1  0x000055b48a60b209 in innobase_fts_find_ranking (fts_hdl=0x7f5bec071948) at /home/thiru/mariarepo/10.6/10.6-sample/storage/innobase/handler/ha_innodb.cc:18291
 
#2  0x000055b48a27c33a in Item_func_match::val_real (this=0x7f5bec016568) at /home/thiru/mariarepo/10.6/10.6-sample/sql/item_func.cc:6430
 
#3  0x000055b48a268707 in Item_func_plus::real_op (this=0x7f5bec016720) at /home/thiru/mariarepo/10.6/10.6-sample/sql/item_func.cc:1097
 
#4  0x000055b48a267a6b in Item_func_hybrid_field_type::val_int_from_real_op (this=0x7f5bec016720) at /home/thiru/mariarepo/10.6/10.6-sample/sql/item_func.cc:848
 
#5  0x000055b48a0be6c6 in Type_handler_real_result::Item_func_hybrid_field_type_val_int (this=0x55b48bb02720 <type_handler_double>, item=0x7f5bec016720) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_type.cc:5478
 
#6  0x000055b489e317a7 in Item_func_hybrid_field_type::val_int (this=0x7f5bec016720) at /home/thiru/mariarepo/10.6/10.6-sample/sql/item_func.h:905
 
#7  0x000055b489ed3218 in evaluate_join_record (join=0x7f5bec0170e0, join_tab=0x7f5bec018bc8, error=0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:21470
 
#8  0x000055b489ed2fbd in sub_select (join=0x7f5bec0170e0, join_tab=0x7f5bec018bc8, end_of_records=false) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:21411
 
#9  0x000055b489ed226c in do_select (join=0x7f5bec0170e0, procedure=0x0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:20915
 
#10 0x000055b489ea5751 in JOIN::exec_inner (this=0x7f5bec0170e0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:4796
 
#11 0x000055b489ea47cd in JOIN::exec (this=0x7f5bec0170e0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:4574
 
#12 0x000055b489ea60fa in mysql_select (thd=0x7f5bec000d78, tables=0x7f5bec015c68, fields=@0x7f5bec015930: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f5bec015c20, last = 0x7f5bec015c20, elements = 1}, <No data fields>}, conds=0x7f5bec016720, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f5bec0170b8, unit=0x7f5bec0050e8, select_lex=0x7f5bec015690) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:5053
 
#13 0x000055b489e9514a in handle_select (thd=0x7f5bec000d78, lex=0x7f5bec005020, result=0x7f5bec0170b8, setup_tables_done_option=0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_select.cc:555
 
#14 0x000055b489e56651 in execute_sqlcom_select (thd=0x7f5bec000d78, all_tables=0x7f5bec015c68) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:6256
 
#15 0x000055b489e4d86f in mysql_execute_command (thd=0x7f5bec000d78, is_called_from_prepared_stmt=false) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:3946
 
#16 0x000055b489e5b3c1 in mysql_parse (thd=0x7f5bec000d78, rawbuf=0x7f5bec0155b0 "select * from t1 where match a against (\"te*\" in boolean mode)+0", length=64, parser_state=0x7f5c500593a0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:8016
 
#17 0x000055b489e47953 in dispatch_command (command=COM_QUERY, thd=0x7f5bec000d78, packet=0x7f5bec00b869 "", packet_length=64, blocking=true) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:1896
 
#18 0x000055b489e4623f in do_command (thd=0x7f5bec000d78, blocking=true) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:1409
 
#19 0x000055b48a000ff9 in do_handle_one_connection (connect=0x55b48d24ec78, put_in_cache=true) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_connect.cc:1415
 
#20 0x000055b48a000d60 in handle_one_connection (arg=0x55b48d24e8c8) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_connect.cc:1317
 
#21 0x000055b48a516ad9 in pfs_spawn_thread (arg=0x55b48d1a8e48) at /home/thiru/mariarepo/10.6/10.6-sample/storage/perfschema/pfs.cc:2201
 
#22 0x00007f5c5ecad6db in start_thread (arg=0x7f5c5005a700) at pthread_create.c:463
 
#23 0x00007f5c5de0f61f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Sporadic failure is happening because of rank value is greater than 0.5 (ie. 0.7). So it displays the "test" as a result.
Reason for rank to be more than 0.5 due to total_doc_count value. Total document count value relies on table->stat_n_rows
and it throws approximate value, not accurate value.

Assigning this to serg for handling the problem in server layer.

Comment by Sergei Golubchik [ 2023-02-13 ]

yes, in an integer context a floating point is rounded, so if it's > 0.5 it's 1, that is true. Otherwise it's 0, that is false.

The function MATCH itself is treated differently, any non-zero value is treated as true, but your +0 in the WHERE clause removes this behavior.

In other words, it's a test issue. To fix it, you can either remove +0 from the WHERE clause or fix the test to produce consistent results, that is, increase or decrease the number of rows to have the result always greater than or less than 0.5

Comment by Thirunarayanan Balathandayuthapani [ 2023-02-21 ]

Patch is in bb-10.6-MDEV-29871

Comment by Marko Mäkelä [ 2023-02-21 ]

OK to push the test-only fix.

Generated at Thu Feb 08 10:11:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.