Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
-
12.2.2-MariaDB-ubu2404 docker container
Description
very related: MDEV-20797
when I insert rows with FULLTEXT values that contain apostrophes where there is no contiguous portion of the word that contains equal to or greater than innodb_ft_min_token_size, subsequent MATCH don't work.
> SHOW VARIABLES LIKE 'innodb_ft%';
|
+---------------------------------+------------------------+
|
| Variable_name | Value |
|
+---------------------------------+------------------------+
|
| innodb_ft_aux_table | leftbank_main/customer |
|
| innodb_ft_cache_size | 8000000 |
|
| innodb_ft_enable_diag_print | OFF |
|
| innodb_ft_enable_stopword | ON |
|
| innodb_ft_max_token_size | 84 |
|
| innodb_ft_min_token_size | 3 |
|
| innodb_ft_num_word_optimize | 2000 |
|
| innodb_ft_result_cache_limit | 2000000000 |
|
| innodb_ft_server_stopword_table | |
|
| innodb_ft_sort_pll_degree | 2 |
|
| innodb_ft_total_cache_size | 640000000 |
|
| innodb_ft_user_stopword_table | |
|
+---------------------------------+------------------------+
|
12 rows in set (0.001 sec)
|
|
|
|
|
> CREATE TABLE customer
|
name TINYTEXT NOT NULL,
|
FULLTEXT (name)
|
) ENGINE = InnoDB;
|
Query OK, 0 rows affected (0.011 sec)
|
> INSERT INTO customer VALUES ('One');
|
Query OK, 1 row affected (0.003 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("One" IN BOOLEAN MODE);
|
+------+
|
| name |
|
+------+
|
| One |
|
+------+
|
1 row in set (0.001 sec)
|
> INSERT INTO customer VALUES ('O''Brien');
|
Query OK, 1 row affected (0.006 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("O''Brien" IN BOOLEAN MODE);
|
+---------+
|
| name |
|
+---------+
|
| O'Brien |
|
+---------+
|
1 row in set (0.001 sec)
|
> INSERT INTO customer VALUES ('Tw''o');
|
Query OK, 1 row affected (0.002 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("Tw''o" IN BOOLEAN MODE);
|
Empty set (0.001 sec)
|
> INSERT INTO customer VALUES ('Th''re');
|
Query OK, 1 row affected (0.001 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("Th''re" IN BOOLEAN MODE);
|
Empty set (0.000 sec)
|
> INSERT INTO customer VALUES ('Fo''ree');
|
Query OK, 1 row affected (0.002 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("Fo''ree" IN BOOLEAN MODE);
|
+--------+
|
| name |
|
+--------+
|
| Fo'ree |
|
+--------+
|
1 row in set (0.001 sec)
|
> INSERT INTO customer VALUES ('Fi''ve''rr');
|
Query OK, 1 row affected (0.006 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("Fi''ve''rr" IN BOOLEAN MODE);
|
Empty set (0.001 sec)
|
after changing min_length:
> SHOW VARIABLES LIKE 'innodb_ft%';
|
+---------------------------------+------------+
|
| Variable_name | Value |
|
+---------------------------------+------------+
|
| innodb_ft_aux_table | |
|
| innodb_ft_cache_size | 8000000 |
|
| innodb_ft_enable_diag_print | OFF |
|
| innodb_ft_enable_stopword | ON |
|
| innodb_ft_max_token_size | 84 |
|
| innodb_ft_min_token_size | 1 |
|
| innodb_ft_num_word_optimize | 2000 |
|
| innodb_ft_result_cache_limit | 2000000000 |
|
| innodb_ft_server_stopword_table | |
|
| innodb_ft_sort_pll_degree | 2 |
|
| innodb_ft_total_cache_size | 640000000 |
|
| innodb_ft_user_stopword_table | |
|
+---------------------------------+------------+
|
12 rows in set (0.001 sec)
|
|
|
> INSERT INTO customer VALUES ('An''t');
|
Query OK, 1 row affected (0.006 sec)
|
|
|
> SELECT * FROM customer WHERE MATCH (name) AGAINST ("An''t" IN BOOLEAN MODE);
|
+------+
|
| name |
|
+------+
|
| An't |
|
+------+
|
1 row in set (0.001 sec)
|