Unstable fuzzy results with default layouts after disk chunk flush

[sanikolaev]

Bug Description:

The same select returns an empty result after flush ramchunk.

Example:

mysql -P9306 -h0 -v -e "drop table if exists t; create table t(name text) min_infix_len='2' diskchunk_flush_write_timeout = '9999'; insert into t values(1, 'LIMITED LIABILIYT COMPANY TVK'); insert into t values(2, 'LIPITED LIABILITY COMPANY ABCDEF'); insert into t values(3, 'LIXITED LIABILITY COMPANY XXXXXX'); select * from t where match('LIMITED LIABILITY COMPANY TVK') option fuzzy=1; flush ramchunk t; select * from t where match('LIMITED LIABILITY COMPANY TVK') option fuzzy=1;"
--------------
drop table if exists t
--------------

--------------
create table t(name text) min_infix_len='2' diskchunk_flush_write_timeout = '9999'
--------------

--------------
insert into t values(1, 'LIMITED LIABILIYT COMPANY TVK')
--------------

--------------
insert into t values(2, 'LIPITED LIABILITY COMPANY ABCDEF')
--------------

--------------
insert into t values(3, 'LIXITED LIABILITY COMPANY XXXXXX')
--------------

--------------
select * from t where match('LIMITED LIABILITY COMPANY TVK') option fuzzy=1
--------------

+------+-------------------------------+
| id   | name                          |
+------+-------------------------------+
|    1 | LIMITED LIABILIYT COMPANY TVK |
+------+-------------------------------+
--------------
flush ramchunk t
--------------

--------------
select * from t where match('LIMITED LIABILITY COMPANY TVK') option fuzzy=1
--------------

Expected: second select finds the same result as before flushing the disk chunk.

Notes:

• adding ,layouts='us,ru' or just ,layouts='' helps

Manticore Search Version:

Manticore 7.0.1 eaca877f1@25021401 dev (columnar 4.0.1 9dbdc6c@25020317) (secondary 4.0.1 9dbdc6c@25020317) (knn 4.0.1 9dbdc6c@25020317)

Operating System Version:

Ubuntu Jammy (dev2)

Have you tried the latest development version?

Yes

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

• Implementation completed
• Tests developed
• Documentation updated
• Documentation reviewed
• Changelog updated

[donhardman]

The issue is not reproducible after the related fix with setting new defaults for layouts: #493

But still there is a problem on the daemon side and inconsistency of the results. Flushing RAM chunks affects the results.

Here is adjusted MRE.

mysql -P9306 -h0 -v -e "drop table if exists t; create table t(name text) min_infix_len='2' diskchunk_flush_write_timeout = '9999'; insert into t values(1, 'LIMITED LIABILIYT COMPANY TVK'); insert into t values(2, 'LIPITED LIABILITY COMPANY ABCDEF'); insert into t values(3, 'LIXITED LIABILITY COMPANY XXXXXX'); call keywords('LI\\?ITED LIQBILITY CO\\?PQNY TVK', 't'); flush ramchunk t; call keywords('LI\\?ITED LIQBILITY CO\\?PQNY TVK', 't')"

There is a difference in output before and after RamChunk flush:

--------------
call keywords('LI\?ITED LIQBILITY CO\?PQNY TVK', 't')
--------------

+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | li?ited   | limited    |
| 1    | li?ited   | lixited    |
| 1    | li?ited   | lipited    |
| 2    | liqbility | liqbility  |
| 3    | co?pqny   | co?pqny    |
| 4    | tvk       | tvk        |
+------+-----------+------------+
--------------
flush ramchunk t
--------------

--------------
call keywords('LI\?ITED LIQBILITY CO\?PQNY TVK', 't')
--------------

+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | li?ited   | limited    |
| 1    | li?ited   | lipited    |
| 1    | li?ited   | lixited    |
| 1    | li?ited   | li?ited    |
| 2    | liqbility | liqbility  |
| 3    | co?pqny   | co?pqny    |
| 4    | tvk       | tvk        |
+------+-----------+------------+

[tomatolog]

that is usual behavior - then no terms matched to disk chunk \ ram chunk dictionary original tokenized \ normalized term added - that is why why all data are in ram segments there is no

| 1    | li?ited   | li?ited    |

then you flushed data into disk chunk call keywords works for every disk chunks \ ram segments and if there are no match there it adds original tokenized \ normalized term and

| 1    | li?ited   | li?ited    |

appears.

There are possible fixes:

• add original tokenized \ normalized term all the time even tokenized term got expanded into multiple other terms
• does not add original tokenized \ normalized term if there is expanded terms as it does now however at the end need to track back added terms from the empty disk chunks \ chunks without matches and remove original token if there are other tokens (expanded \ lematized ?) at the same qpos

[tomatolog]

I pushed the fix into manticoresoftware/manticoresearch#3113 there added original tokenized \ normalized term all the time even tokenized term got expanded into multiple other terms. I also changed some ubertests to match the new result set.

Need to decide do we keep this fix or try something different.

[sanikolaev]

Another example of wrong fuzzy search results. Pls check after the fix:

mysql> drop table if exists t; CREATE TABLE t(f text ) min_infix_len='2' expand_keywords='1'; INSERT INTO t VALUES (1,'TH'),(2,'THIBODEAUX'); select * from t where match('1234567890smyth') option fuzzy=1;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
CREATE TABLE t(f text ) min_infix_len='2' expand_keywords='1'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT INTO t VALUES (1,'TH'),(2,'THIBODEAUX')
--------------

Query OK, 2 rows affected (0.01 sec)

--------------
select * from t where match('1234567890smyth') option fuzzy=1
--------------

+------+------------+
| id   | f          |
+------+------------+
|    1 | TH         |
|    2 | THIBODEAUX |
+------+------------+
2 rows in set (0.01 sec)

[sanikolaev]

that is usual behavior - then no terms matched to disk chunk \ ram chunk dictionary original tokenized \ normalized term added - that is why why all data are in ram segments there is no

Demo of this:

mysql> drop table if exists t; create table t(f text) min_infix_len='2'; insert into t values(1, 'abcdef'); call keywords('abc*', 't'); flush ramchunk t; call keywords('abc*', 't');
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
create table t(f text) min_infix_len='2'
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into t values(1, 'abcdef')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
call keywords('abc*', 't')
--------------

+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | abc*      | abcdef     |
+------+-----------+------------+
1 row in set (0.00 sec)

--------------
flush ramchunk t
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
call keywords('abc*', 't')
--------------

+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | abc*      | abcdef     |
| 1    | abc*      | abc*       |
+------+-----------+------------+
2 rows in set (0.00 sec)

We need to think further on the behaviour of CALL KEYWORDS in the infix context.