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 8:05]
linfeng chen
[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)