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

incorrect result of innodb_fts fulltext.test without exist2in

        Details

        • Type: Bug
        • Status: Stalled
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0.11
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          None
        • Sprint:
          10.0.20

          Description

          innodb_fts: fulltext.rsult

          # should return 0
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)
);
count(*)
1

          (pay attention to the comment)

          test suite:

          
--source include/have_innodb.inc

set optimizer_switch='exists_to_in=off';

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);

--echo # t2 should use full text index
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)
  );

# INNODB_FTS: INVESTIGATE
--echo # should return 0
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)
  );

--error ER_FT_MATCHING_KEY_NOT_FOUND
SELECT count(*) FROM t1 WHERE 
  not exists(
   SELECT 1 FROM t2 IGNORE INDEX (b2), t3
   WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
  );

DROP TABLE t1,t2,t3;

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                With in2exists it returns corrrect result. Incorrect result was written in the test before.

                Show
                sanja Oleksandr Byelkin added a comment - With in2exists it returns corrrect result. Incorrect result was written in the test before.

                  People

                  • Assignee:
                    sanja Oleksandr Byelkin
                    Reporter:
                    sanja Oleksandr Byelkin
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:

                      Agile