Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.6.9, 10.9.3, 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Description
Here's a description of what I'm observing:
1. Set up tables, add a few rows into the database.
2. Execute a select query using fulltext search: it returns 0 rows (which is incorrect, it should return 1).
3. Execute another select query (which is a modified version of (2))
4. Execute the same select query from (2): now it returns 2 rows (which is also incorrect, it should return 1).
Here's SQL script to reproduce (queries in question are 3 SELECTs at the bottom):
SET autocommit = 1; |
CREATE TABLE `users_docs_0` ( `internal_table_id` binary(16) NOT NULL, `record_id` binary(16) NOT NULL, `created_at` bigint NOT NULL, `updated_at` bigint NOT NULL, `doc` text NOT NULL, PRIMARY KEY (`internal_table_id`, `record_id`) ) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB; |
CREATE INDEX `users_docs_0_on_created_at` ON `users_docs_0` (`internal_table_id`, `created_at`); |
CREATE INDEX `users_docs_0_on_updated_at` ON `users_docs_0` (`internal_table_id`, `updated_at`); |
CREATE TABLE `users_keys_0` ( `internal_table_id` binary(16) NOT NULL, `record_id` binary(16) NOT NULL, `field_id` binary(16) NOT NULL, `string` varchar(255), `int` bigint, `double` double, PRIMARY KEY (`internal_table_id`, `record_id`, `field_id`) ) DEFAULT CHARSET=UTF8 ENGINE=InnoDB; |
CREATE INDEX `user_0_on_string` ON `users_keys_0` (`field_id`, `string`, `record_id`); |
CREATE INDEX `user_0_on_integer` ON `users_keys_0` (`field_id`, `int`, `record_id`); |
CREATE INDEX `user_0_on_double` ON `users_keys_0` (`field_id`, `double`, `record_id`); |
CREATE FULLTEXT INDEX `user_0_fulltext` ON `users_keys_0` (`string`); |
INSERT INTO |
`users_docs_0` (
|
`doc`,
|
`record_id`,
|
`internal_table_id`,
|
`created_at`,
|
`updated_at`
|
)
|
VALUES
|
('', UNHEX('1315f677da2343ebb668867397b07214'), UNHEX('fed68cf1227f4806b7a2f77f32243563'), 1664361776639022000, 1664361776639022000); |
INSERT INTO |
`users_keys_0` (
|
`internal_table_id`,
|
`record_id`,
|
`field_id`,
|
`string`,
|
`int`, |
`double` |
)
|
VALUES
|
(UNHEX('fed68cf1227f4806b7a2f77f32243563'), UNHEX('1315f677da2343ebb668867397b07214'), UNHEX('89427ccd256843d0be8bd466c6853960'), 'Tolkien', NULL, NULL), |
(UNHEX('fed68cf1227f4806b7a2f77f32243563'), UNHEX('1315f677da2343ebb668867397b07214'), UNHEX('73c5867ffab34e12bb9040b443d7d9e2'), 'Previous Title', NULL, NULL); |
INSERT INTO |
`users_docs_0` (
|
`doc`,
|
`record_id`,
|
`internal_table_id`,
|
`created_at`,
|
`updated_at`
|
)
|
VALUES
|
('', UNHEX('c25722a6bc754e2db5d64b0d7532aca0'), UNHEX('fed68cf1227f4806b7a2f77f32243563'), 1664361776690081000, 1664361776690081000); |
INSERT INTO |
`users_keys_0` (
|
`internal_table_id`,
|
`record_id`,
|
`field_id`,
|
`string`,
|
`int`, |
`double` |
)
|
VALUES
|
(UNHEX('fed68cf1227f4806b7a2f77f32243563'), UNHEX('c25722a6bc754e2db5d64b0d7532aca0'), UNHEX('89427ccd256843d0be8bd466c6853960'), 'JK Rowling', NULL, NULL), |
(UNHEX('fed68cf1227f4806b7a2f77f32243563'), UNHEX('c25722a6bc754e2db5d64b0d7532aca0'), UNHEX('73c5867ffab34e12bb9040b443d7d9e2'), 'Harry Potter', NULL, NULL); |
|
-- Run test query. It returns empty set (bug?).
|
\! echo "Running test query (expected 1 record with title='Harry Potter')"; |
SELECT
|
`o`.`record_id`,
|
`o`.`string` AS author, |
`c0`.`string` AS title |
FROM
|
`users_keys_0` AS `o` USE INDEX (user_0_on_string) |
INNER JOIN `users_keys_0` AS `c0` ON `c0`.`record_id` = `o`.`record_id` |
AND `c0`.`internal_table_id` = `o`.`internal_table_id` |
AND `c0`.`field_id` = UNHEX('73c5867ffab34e12bb9040b443d7d9e2') |
AND MATCH (c0.string) AGAINST ('potter') |
WHERE
|
`o`.`internal_table_id` = UNHEX('fed68cf1227f4806b7a2f77f32243563') |
AND `o`.`field_id` = UNHEX('89427ccd256843d0be8bd466c6853960') |
ORDER BY |
`o`.`string` ASC, |
`o`.`record_id` ASC |
LIMIT
|
1000;
|
|
-- Running same query, with `field_id` join condition removed.
|
\! echo "Running modified query"; |
SELECT
|
`o`.`record_id`,
|
`o`.`string` AS author, |
`c0`.`string` AS title |
FROM
|
`users_keys_0` AS `o` USE INDEX (user_0_on_string) |
INNER JOIN `users_keys_0` AS `c0` ON `c0`.`record_id` = `o`.`record_id` |
AND `c0`.`internal_table_id` = `o`.`internal_table_id` |
-- AND `c0`.`field_id` = UNHEX('73c5867ffab34e12bb9040b443d7d9e2') |
AND MATCH (c0.string) AGAINST ('potter') |
WHERE
|
`o`.`internal_table_id` = UNHEX('fed68cf1227f4806b7a2f77f32243563') |
AND `o`.`field_id` = UNHEX('89427ccd256843d0be8bd466c6853960') |
ORDER BY |
`o`.`string` ASC, |
`o`.`record_id` ASC |
LIMIT
|
1000;
|
|
-- Running test query again. This time it returns 2 records. Which is also incorrect.
|
\! echo "Running test query (expected 1 record with title='Harry Potter')"; |
SELECT
|
`o`.`record_id`,
|
`o`.`string` AS author, |
`c0`.`string` AS title |
FROM
|
`users_keys_0` AS `o` USE INDEX (user_0_on_string) |
INNER JOIN `users_keys_0` AS `c0` ON `c0`.`record_id` = `o`.`record_id` |
AND `c0`.`internal_table_id` = `o`.`internal_table_id` |
AND `c0`.`field_id` = UNHEX('73c5867ffab34e12bb9040b443d7d9e2') |
AND MATCH (c0.string) AGAINST ('potter') |
WHERE
|
`o`.`internal_table_id` = UNHEX('fed68cf1227f4806b7a2f77f32243563') |
AND `o`.`field_id` = UNHEX('89427ccd256843d0be8bd466c6853960') |
ORDER BY |
`o`.`string` ASC, |
`o`.`record_id` ASC |
LIMIT
|
1000;
|
Locally I can reproduce this quickly with Docker. Save SQL above as "statements.sql" and then:
docker run -d -e MARIADB_ROOT_PASSWORD=test -e MARIADB_DATABASE=test -p 3306:3306 mariadb:latest |
mysql -u root -ptest --protocol=tcp test |
mysql> source statements.sql |
Attachments
Issue Links
- is duplicated by
-
MDEV-29778 Having Unique index interference with MATCH from a FULLTEXT
-
- Closed
-
Thank you for the report and the test case!
I repeated on versions 10.5-10.10, 10.3 and 10.4 returned correct results.
--source include/have_innodb.inc
) ENGINE=InnoDB ;
MariaDB [f]> SELECT c0.s1
-> FROM t1
-> JOIN t1 AS c0 ON c0.id = t1.id
-> AND c0.b = 1
-> AND MATCH (c0.s1) against ('potter') ;
Empty set (0,002 sec)
MariaDB [f]> analyze
-> SELECT c0.s1
-> FROM t1
-> JOIN t1 AS c0 ON c0.id = t1.id
-> AND c0.b = 1
-> AND MATCH (c0.s1) against ('potter') ;
+------+-------------+-------+-------+-----------------+---------+---------+-------+------+--------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+-----------------+---------+---------+-------+------+--------+----------+------------+--------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY | PRIMARY | 12 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using index |
| 1 | SIMPLE | c0 | ref | PRIMARY,ind1,s1 | ind1 | 4 | const | 2 | 2.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+-----------------+---------+---------+-------+------+--------+----------+------------+--------------------------+
2 rows in set (0,002 sec)
MariaDB [f]> SELECT c0.s1
-> FROM t1
-> JOIN t1 AS c0 ON c0.id = t1.id
-> #AND c0.b = 1
-> AND MATCH (c0.s1) against ('potter') ;
+--------------+
| s1 |
+--------------+
| Harry Potter |
| Harry Potter |
+--------------+
2 rows in set (0,001 sec)
MariaDB [f]> SELECT c0.s1
-> FROM t1
-> JOIN t1 AS c0 ON c0.id = t1.id
-> AND c0.b = 1
-> AND MATCH (c0.s1) against ('potter') ;
+----------------+
| s1 |
+----------------+
| Harry Potter |
| Previous Title |
| Harry Potter |
| Previous Title |
+----------------+
4 rows in set (0,001 sec)
MariaDB [f]> analyze
-> SELECT c0.s1
-> FROM t1
-> JOIN t1 AS c0 ON c0.id = t1.id
-> AND c0.b = 1
-> AND MATCH (c0.s1) against ('potter') ;
+------+-------------+-------+-------+-----------------+---------+---------+-------+------+--------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+-----------------+---------+---------+-------+------+--------+----------+------------+--------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY | PRIMARY | 12 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using index |
| 1 | SIMPLE | c0 | ref | PRIMARY,ind1,s1 | ind1 | 4 | const | 2 | 2.00 | 100.00 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+-----------------+---------+---------+-------+------+--------+----------+------------+--------------------------+
2 rows in set (0,001 sec)