Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29728

Wrong result for FULLTEXT InnoDB search

Details

    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

          Activity

            alice Alice Sherepa added a comment -

            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 
             
            CREATE TABLE t1 ( id int, a int, b int, s1 varchar(255),
              PRIMARY KEY (id,a,b),
              KEY ind1 (b,s1),
              FULLTEXT KEY (s1)
            ) ENGINE=InnoDB ;
             
             
            INSERT INTO t1 VALUES (1, 1, 1, 'Previous Title'), (1, 2, 1, 'Harry Potter');
            analyze table t1;
             
            SELECT c0.s1
            FROM t1 
            JOIN t1 AS c0 ON c0.id = t1.id
            AND c0.b = 1
            AND MATCH (c0.s1) against ('potter') ;
             
            SELECT c0.s1
            FROM t1 
            JOIN t1 AS c0 ON c0.id = t1.id
            #AND c0.b = 1
            AND MATCH (c0.s1) against ('potter') ;
             
            SELECT c0.s1
            FROM t1 
            JOIN t1 AS c0 ON c0.id = t1.id
            AND c0.b = 1
            AND MATCH (c0.s1) against ('potter') ;
             
            drop table t1;
            

            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)
            
            

            alice Alice Sherepa added a comment - 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   CREATE TABLE t1 ( id int , a int , b int , s1 varchar (255), PRIMARY KEY (id,a,b), KEY ind1 (b,s1), FULLTEXT KEY (s1) ) ENGINE=InnoDB ;     INSERT INTO t1 VALUES (1, 1, 1, 'Previous Title' ), (1, 2, 1, 'Harry Potter' ); analyze table t1;   SELECT c0.s1 FROM t1 JOIN t1 AS c0 ON c0.id = t1.id AND c0.b = 1 AND MATCH (c0.s1) against ( 'potter' ) ;   SELECT c0.s1 FROM t1 JOIN t1 AS c0 ON c0.id = t1.id # AND c0.b = 1 AND MATCH (c0.s1) against ( 'potter' ) ;   SELECT c0.s1 FROM t1 JOIN t1 AS c0 ON c0.id = t1.id AND c0.b = 1 AND MATCH (c0.s1) against ( 'potter' ) ;   drop table t1; 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)

            Hi Team, is there any update/plans on this? Seems like this is quite a major issue, and a regression as well

            andreyr Andrey Razumovsky added a comment - Hi Team, is there any update/plans on this? Seems like this is quite a major issue, and a regression as well

            People

              thiru Thirunarayanan Balathandayuthapani
              Tubanov Stepan
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.