diff options
Diffstat (limited to 'mysql-test/suite/innodb_fts/r/innodb-fts-fic.result')
| -rw-r--r-- | mysql-test/suite/innodb_fts/r/innodb-fts-fic.result | 190 |
1 files changed, 190 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result b/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result new file mode 100644 index 00000000..f998881f --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result @@ -0,0 +1,190 @@ +call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); +call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`articles`"); +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT +) ENGINE=InnoDB; +INSERT INTO articles (title,body) VALUES +('MySQL Tutorial','DBMS stands for DataBase ...') , +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), +('MySQL vs. YourSQL','In the following database comparison ...'), +('MySQL Security','When configured properly, MySQL ...'); +CREATE FULLTEXT INDEX idx on articles (title, body); +SELECT * FROM articles WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title, body) +AGAINST ('database' IN NATURAL LANGUAGE MODE); +COUNT(*) +2 +SELECT * FROM articles +WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT COUNT(IF(MATCH (title, body) +AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) +AS count FROM articles; +count +2 +ANALYZE TABLE articles; +Table Op Msg_type Msg_text +test.articles analyze status Engine-independent statistics collected +test.articles analyze Warning Engine-independent statistics are not collected for column 'body' +test.articles analyze status OK +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('DBMS Security' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +DROP INDEX idx ON articles; +CREATE FULLTEXT INDEX idx on articles (title, body); +CREATE FULLTEXT INDEX idx1 on articles (title); +SELECT * FROM articles WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +DROP INDEX idx ON articles; +DROP INDEX idx1 ON articles; +CREATE FULLTEXT INDEX idx1 on articles (title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +drop table articles; +CREATE TABLE articles ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT +) ENGINE=InnoDB; +create unique index FTS_DOC_ID_INDEX on articles(FTS_DOC_ID); +INSERT INTO articles (title,body) VALUES +('MySQL Tutorial','DBMS stands for DataBase ...') , +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), +('MySQL vs. YourSQL','In the following database comparison ...'), +('MySQL Security','When configured properly, MySQL ...'); +CREATE FULLTEXT INDEX idx on articles (title, body); +SELECT * FROM articles WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +FTS_DOC_ID title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +drop table articles; +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT +) ENGINE=InnoDB; +INSERT INTO articles (title,body) VALUES +('MySQL Tutorial','DBMS stands for DataBase ...') , +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), +('MySQL vs. YourSQL','In the following database comparison ...'), +('MySQL Security','When configured properly, MySQL ...'); +CREATE FULLTEXT INDEX idx on articles (title); +CREATE FULLTEXT INDEX idx2 on articles (body); +SELECT * FROM articles WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +ERROR HY000: Can't find FULLTEXT index matching the column list +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +SELECT * FROM articles WHERE MATCH (body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +3 Optimizing MySQL In this tutorial we will show ... +drop index idx2 on articles; +SELECT * FROM articles WHERE MATCH (body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +ERROR HY000: Can't find FULLTEXT index matching the column list +CREATE FULLTEXT INDEX idx2 on articles (body); +SELECT * FROM articles WHERE MATCH (body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +3 Optimizing MySQL In this tutorial we will show ... +UPDATE articles set title = 'aaaa' +WHERE MATCH(title) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('aaaa' IN NATURAL LANGUAGE MODE); +id title body +1 aaaa DBMS stands for DataBase ... +drop table articles; +CREATE TABLE articles ( +FTS_DOC_ID BIGINT UNSIGNED NOT NULL , +title VARCHAR(200), +body TEXT +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx on articles (title); +INSERT INTO articles VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...'); +UPDATE articles set title = 'bbbb' WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE); +ERROR HY000: Invalid InnoDB FTS Doc ID +UPDATE articles set title = 'bbbb', FTS_DOC_ID=8 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE); +ERROR HY000: Invalid InnoDB FTS Doc ID +UPDATE articles set title = 'bbbb', FTS_DOC_ID=10 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE); +SELECT * FROM articles WHERE MATCH (title) AGAINST ('bbbb' IN NATURAL LANGUAGE MODE); +FTS_DOC_ID title body +10 bbbb DBMS stands for DataBase ... +SELECT * FROM articles WHERE MATCH (title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE); +FTS_DOC_ID title body +CREATE FULLTEXT INDEX idx2 ON articles (body); +SELECT * FROM articles WHERE MATCH (body) AGAINST ('database' IN NATURAL LANGUAGE MODE); +FTS_DOC_ID title body +10 bbbb DBMS stands for DataBase ... +UPDATE articles set body = 'bbbb', FTS_DOC_ID=11 WHERE MATCH(body) AGAINST ('database' IN NATURAL LANGUAGE MODE); +drop table articles; +create table `articles`(`a` varchar(2) not null)engine=innodb; +create fulltext index `FTS_DOC_ID_INDEX` on `articles`(`a`); +ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index +create unique index `a` on `articles`(`a`); +drop table articles; +CREATE TABLE wp( +FTS_DOC_ID bigint PRIMARY KEY, +title VARCHAR(255) NOT NULL DEFAULT '', +text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB; +INSERT INTO wp (FTS_DOC_ID, title, text) VALUES +(1, 'MySQL Tutorial','DBMS stands for DataBase ...'), +(2, 'How To Use MySQL Well','After you went through a ...'); +CREATE FULLTEXT INDEX idx ON wp(title, text); +ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index +DROP TABLE wp; +CREATE TABLE wp( +FTS_DOC_ID bigint unsigned PRIMARY KEY, +title VARCHAR(255) NOT NULL DEFAULT '', +text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB; +INSERT INTO wp (FTS_DOC_ID, title, text) VALUES +(1, 'MySQL Tutorial','DBMS stands for DataBase ...'), +(2, 'How To Use MySQL Well','After you went through a ...'); +CREATE FULLTEXT INDEX idx ON wp(title, text); +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database') +FROM wp; +FTS_DOC_ID MATCH(title, text) AGAINST ('database') +1 0.0906190574169159 +2 0 +DROP TABLE wp; |