MySQL Bugs: #114951: fulltext search return wrong results with generated columns

Bug #114951	fulltext search return wrong results with generated columns
Submitted:	10 May 2024 7:07	Modified:	10 May 2024 7:32
Reporter:	Yang Yu	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.7.44	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Fulltext search forget to generate virtual columns. As a result, client receive with romdom results in there columns.

How to repeat:
CREATE TABLE `sample` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `agent_info` json DEFAULT NULL,
  `mtq_umid` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`agent_info`,'$.mtq_umid'))) VIRTUAL COMMENT 'mtq umid',
  `policy_list` text,
  PRIMARY KEY (`id`),
  KEY `idx_serach7` (`mtq_umid`),
  FULLTEXT KEY `idx_policy_search` (`policy_list`)
) ENGINE=InnoDB AUTO_INCREMENT=10532056653 DEFAULT CHARSET=utf8mb4;

insert into sample(agent_info,policy_list) values('{"mtq_umid":"111"}','11111,22222,33333');
insert into sample(agent_info,policy_list) values('{"mtq_umid":"222"}','11111,22222,33333');
insert into sample(agent_info,policy_list) values('{"mtq_umid":"333"}','11111,22222,33333');
insert into sample(agent_info,policy_list) values('{"mtq_umid":"444"}','11111,22222,33333');

SELECT mtq_umid FROM `sample`  WHERE MATCH ( `policy_list`) AGAINST ( '11111') ;

Suggested fix:
ha_ft_read interface in handler should be ported from 8.0

support a fix

diff --git a/sql/handler.cc b/sql/handler.cc
index f3aca4197d71..88245da435e9 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -3004,6 +3004,24 @@ int handler::ha_rnd_pos(uchar *buf, uchar *pos)
   DBUG_RETURN(result);
 }
 
+int handler::ha_ft_read(uchar *buf) {
+  int result;
+  DBUG_ENTER("handler::ha_ft_read");
+  assert(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK);
+  /* TODO: Find out how to solve ha_rnd_pos when finding duplicate update. */
+  /* assert(inited == RND); */
+
+  // Set status for the need to update generated fields
+  m_update_generated_read_fields = table->has_gcol();
+
+  result = ft_read(buf);
+  if (!result && m_update_generated_read_fields) {
+    result = update_generated_read_fields(buf, table);
+    m_update_generated_read_fields = false;
+  }
+  DBUG_RETURN(result);
+}
+
 
 /**
   Read [part of] row via [part of] index.
diff --git a/sql/handler.h b/sql/handler.h
index 490211e4a052..40e8343076a9 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -2887,6 +2887,7 @@ public:
   {
     return ft_init_ext(hints->get_flags(), inx, key);
   }
+  int ha_ft_read(uchar *buf);
   virtual int ft_read(uchar *buf) { return HA_ERR_WRONG_COMMAND; }
 protected:
   /// @returns @see index_read_map().
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 8055805e8839..78278e3811e3 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -1028,7 +1028,7 @@ public:
     return file->ft_init();
   }
   int reset() { return 0; }
-  int get_next() { return file->ft_read(record); }
+  int get_next() { return file->ha_ft_read(record); }
   int get_type() const { return QS_TYPE_FULLTEXT; }
   virtual bool is_loose_index_scan() const { return false; }
   virtual bool is_agg_loose_index_scan() const { return false; }
diff --git a/sql/sql_executor.cc b/sql/sql_executor.cc
index 0972dadbbc83..34fbb3effb5b 100644
--- a/sql/sql_executor.cc
+++ b/sql/sql_executor.cc
@@ -2726,7 +2726,7 @@ join_ft_read_first(QEP_TAB *tab)
   }
   table->file->ft_init();
 
-  if ((error= table->file->ft_read(table->record[0])))
+  if ((error= table->file->ha_ft_read(table->record[0])))
     return report_handler_error(table, error);
   return 0;
 }
@@ -2735,7 +2735,7 @@ static int
 join_ft_read_next(READ_RECORD *info)
 {
   int error;
-  if ((error= info->table->file->ft_read(info->table->record[0])))
+  if ((error= info->table->file->ha_ft_read(info->table->record[0])))
     return report_handler_error(info->table, error);
   return 0;
 }

Hello Yang Yu,

Thank you for the report and feedback.
Per Oracle's Lifetime Support policy, as of October 25, 2023, MySQL 5.7 is covered under Oracle Sustaining Support. Thus, Users are encouraged to upgrade to MySQL 8.0. - More details at https://www.mysql.com/support/eol-notice.html

This issue has already been fixed in 8.0.x version of that product, which you can download at

  http://www.mysql.com/downloads/

-- 5.7.44
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `sample` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `agent_info` json DEFAULT NULL,
    ->   `mtq_umid` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`agent_info`,'$.mtq_umid'))) VIRTUAL COMMENT 'mtq umid',
    ->   `policy_list` text,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_serach7` (`mtq_umid`),
    ->   FULLTEXT KEY `idx_policy_search` (`policy_list`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10532056653 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"111"}','11111,22222,33333');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"222"}','11111,22222,33333');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"333"}','11111,22222,33333');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"444"}','11111,22222,33333');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT mtq_umid FROM `sample`  WHERE MATCH ( `policy_list`) AGAINST ( '11111') ;
+----------+
| mtq_umid |
+----------+
| 444      |
| 444      |
| 444      |
| 444      |
+----------+
4 rows in set (0.00 sec)

-- 8.0.11+
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `sample` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `agent_info` json DEFAULT NULL,
    ->   `mtq_umid` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`agent_info`,'$.mtq_umid'))) VIRTUAL COMMENT 'mtq umid',
    ->   `policy_list` text,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_serach7` (`mtq_umid`),
    ->   FULLTEXT KEY `idx_policy_search` (`policy_list`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10532056653 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql>
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"111"}','11111,22222,33333');
Query OK, 1 row affected (0.01 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"222"}','11111,22222,33333');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"333"}','11111,22222,33333');
Query OK, 1 row affected (0.01 sec)

mysql> insert into sample(agent_info,policy_list) values('{"mtq_umid":"444"}','11111,22222,33333');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT mtq_umid FROM `sample`  WHERE MATCH ( `policy_list`) AGAINST ( '11111') ;
+----------+
| mtq_umid |
+----------+
| 111      |
| 222      |
| 333      |
| 444      |
+----------+
4 rows in set (0.00 sec)

regards,
Umesh