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

Wrong result upon forcing fulltext index on InnoDB table

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t (a varchar(128), fulltext(a)) engine=InnoDB;
      INSERT INTO t VALUES ('Alabama'),('Washington');
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      ALTER TABLE t ADD PRIMARY KEY(a);
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
       
      # Cleanup
      DROP TABLE t;
      

      10.4 b54e4bf0

      CREATE TABLE t (a varchar(128), fulltext(a)) engine=InnoDB;
      INSERT INTO t VALUES ('Alabama'),('Washington');
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      a
      Alabama
      Washington
      ALTER TABLE t ADD PRIMARY KEY(a);
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      a
      DROP TABLE t;
      

      So, on a table without PK the query result is correct, but with a PK it is not.
      The plans show that without PK the hint is silently ignored, while with PK the key is used:

      INSERT INTO t VALUES ('Alabama'),('Washington');
      EXPLAIN EXTENDED SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	2	100.00	Using filesort
      Warnings:
      Note	1003	select `test`.`t`.`a` AS `a` from `test`.`t` FORCE INDEX (`a`) order by `test`.`t`.`a`
      ALTER TABLE t ADD PRIMARY KEY(a);
      EXPLAIN EXTENDED SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	index	NULL	a	130	NULL	2	100.00	
      Warnings:
      Note	1003	select `test`.`t`.`a` AS `a` from `test`.`t` FORCE INDEX (`a`) order by `test`.`t`.`a`
      

      Reproducible on all existing versions and on MySQL 8.0.28.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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