| Bug #111067 | Query result error | ||
|---|---|---|---|
| Submitted: | 18 May 2023 8:05 | Modified: | 18 May 2023 9:28 |
| Reporter: | linfeng chen | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.25, 8.0.33 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 May 2023 9:28]
MySQL Verification Team
Hello linfeng chen, Thank you for the report and test case. regards, Umesh
[19 May 2023 12:55]
Knut Anders Hatlen
Posted by developer:
This is not directly related to setting the secondary storage engine of the table. It can also be seen with other ALTER TABLE statements, like ALTER TABLE ADD COLUMN:
mysql> CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB;
Query OK, 0 rows affected (0,54 sec)
mysql> INSERT INTO t1 VALUES('aaa15');
Query OK, 1 row affected (0,04 sec)
mysql> SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
+-------------------------------------------------------+
| MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) |
+-------------------------------------------------------+
| 0.000000001885928302414186 |
+-------------------------------------------------------+
1 row in set (0,01 sec)
mysql> ALTER TABLE t1 ADD COLUMN y INT;
Query OK, 1 row affected (0,72 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
+-------------------------------------------------------+
| MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
1 row in set (0,00 sec)
A call to ANALYZE TABLE restores the original result:
mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0,05 sec)
mysql> SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
+-------------------------------------------------------+
| MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) |
+-------------------------------------------------------+
| 0.000000001885928302414186 |
+-------------------------------------------------------+
1 row in set (0,01 sec)
Description: Symptom The query result is incorrect after the secondary storage engine is configured for the table. mysql> CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB; Query OK, 0 rows affected (2.72 sec) mysql> INSERT INTO t1 VALUES('aaa15'); Query OK, 1 row affected (0.10 sec) mysql> SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; +-------------------------------------------------------+ | MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) | +-------------------------------------------------------+ | 0.000000001885928302414186 | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE t1 SECONDARY_ENGINE = test; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; +-------------------------------------------------------+ | MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) | +-------------------------------------------------------+ | 0 | +-------------------------------------------------------+ 1 row in set (0.00 sec) How to repeat: CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('aaa15'); SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; ALTER TABLE t1 SECONDARY_ENGINE = test; SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;