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

Wrong result with join query and innodb fulltext search

    XMLWordPrintable

Details

    Description

      Testcase:

      --source include/have_innodb.inc
        
      CREATE TABLE t1 (a int) ENGINE = InnoDB;
      INSERT INTO t1 VALUES (1), (2);
      CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB;
      INSERT INTO t2 VALUES (1,'Scargill');
      CREATE TABLE t3 (a int, b int) ENGINE = InnoDB;
      INSERT INTO t3 VALUES (1,1), (2,1);
      SELECT * FROM t2 where MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE);
      

      set optimizer_switch='materialization=on';
      EXPLAIN
      SELECT count(*) FROM t1 WHERE
      not exists(
      SELECT 1 FROM t2, t3
      WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
      );
       
      SELECT count(*) FROM t1 WHERE
      not exists(
      SELECT 1 FROM t2, t3
      WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
      );
      

      Produces a correct result:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
      2       MATERIALIZED    t2      fulltext        b2      b2      0               1       Using where
      2       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       Using where
      ...
      count(*)
      0
      

      Note that this query plan executes the full text scan once.

      Now, try a query plan executes the full text scan twice:

      set optimizer_switch='materialization=off';
      EXPLAIN
      SELECT count(*) FROM t1 WHERE
      not exists(
      SELECT 1 FROM t2, t3
      WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
      );
       
      SELECT count(*) FROM t1 WHERE
      not exists(
      SELECT 1 FROM t2, t3
      WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
      );
      

      Produces:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
      2       DEPENDENT SUBQUERY      t2      fulltext        b2      b2      0               1       Using where
      2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where
      ...
      count(*)
      1
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            1 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.