Debian Codegraph @

mysql-test/suite/innodb_fts/r/fic.result

raw
1 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* A new Doc ID must be supplied while updating FTS indexed columns.");
2 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* FTS Doc ID must be larger than [0-9]+ for table `test`.`articles`");
3 CREATE TABLE articles (
4 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
5 title VARCHAR(200),
6 body TEXT
7 ) ENGINE=InnoDB;
8 INSERT INTO articles (title,body) VALUES
9 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
10 ('How To Use MySQL Well','After you went through a ...'),
11 ('Optimizing MySQL','In this tutorial we will show ...'),
12 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
13 ('MySQL vs. YourSQL','In the following database comparison ...'),
14 ('MySQL Security','When configured properly, MySQL ...');
15 CREATE FULLTEXT INDEX idx on articles (title, body);
16 Warnings:
17 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
18 SELECT * FROM articles WHERE MATCH (title, body)
19 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
20 id title body
21 1 MySQL Tutorial DBMS stands for DataBase ...
22 3 Optimizing MySQL In this tutorial we will show ...
23 SELECT COUNT(*) FROM articles
24 WHERE MATCH (title, body)
25 AGAINST ('database' IN NATURAL LANGUAGE MODE);
26 COUNT(*)
27 2
28 SELECT * FROM articles
29 WHERE MATCH (title, body)
30 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
31 id title body
32 1 MySQL Tutorial DBMS stands for DataBase ...
33 3 Optimizing MySQL In this tutorial we will show ...
34 SELECT COUNT(IF(MATCH (title, body)
35 AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
36 AS count FROM articles;
37 count
38 2
39 ANALYZE TABLE articles;
40 Table Op Msg_type Msg_text
41 test.articles analyze status OK
42 SELECT * FROM articles WHERE MATCH (title,body)
43 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
44 id title body
45 1 MySQL Tutorial DBMS stands for DataBase ...
46 2 How To Use MySQL Well After you went through a ...
47 3 Optimizing MySQL In this tutorial we will show ...
48 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
49 6 MySQL Security When configured properly, MySQL ...
50 SELECT * FROM articles WHERE MATCH (title,body)
51 AGAINST ('DBMS Security' IN BOOLEAN MODE);
52 id title body
53 1 MySQL Tutorial DBMS stands for DataBase ...
54 6 MySQL Security When configured properly, MySQL ...
55 SELECT * FROM articles WHERE MATCH (title,body)
56 AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
57 id title body
58 5 MySQL vs. YourSQL In the following database comparison ...
59 DROP INDEX idx ON articles;
60 CREATE FULLTEXT INDEX idx on articles (title, body);
61 CREATE FULLTEXT INDEX idx1 on articles (title);
62 SELECT * FROM articles WHERE MATCH (title, body)
63 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
64 id title body
65 1 MySQL Tutorial DBMS stands for DataBase ...
66 3 Optimizing MySQL In this tutorial we will show ...
67 DROP INDEX idx ON articles;
68 DROP INDEX idx1 ON articles;
69 CREATE FULLTEXT INDEX idx1 on articles (title);
70 SELECT * FROM articles WHERE MATCH (title)
71 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
72 id title body
73 1 MySQL Tutorial DBMS stands for DataBase ...
74 drop table articles;
75 CREATE TABLE articles (
76 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
77 title VARCHAR(200),
78 body TEXT
79 ) ENGINE=InnoDB;
80 create unique index FTS_DOC_ID_INDEX on articles(FTS_DOC_ID);
81 INSERT INTO articles (title,body) VALUES
82 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
83 ('How To Use MySQL Well','After you went through a ...'),
84 ('Optimizing MySQL','In this tutorial we will show ...'),
85 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
86 ('MySQL vs. YourSQL','In the following database comparison ...'),
87 ('MySQL Security','When configured properly, MySQL ...');
88 CREATE FULLTEXT INDEX idx on articles (title, body);
89 SELECT * FROM articles WHERE MATCH (title, body)
90 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
91 FTS_DOC_ID title body
92 1 MySQL Tutorial DBMS stands for DataBase ...
93 3 Optimizing MySQL In this tutorial we will show ...
94 drop table articles;
95 CREATE TABLE articles (
96 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
97 title VARCHAR(200),
98 body TEXT
99 ) ENGINE=InnoDB;
100 INSERT INTO articles (title,body) VALUES
101 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
102 ('How To Use MySQL Well','After you went through a ...'),
103 ('Optimizing MySQL','In this tutorial we will show ...'),
104 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
105 ('MySQL vs. YourSQL','In the following database comparison ...'),
106 ('MySQL Security','When configured properly, MySQL ...');
107 CREATE FULLTEXT INDEX idx on articles (title);
108 Warnings:
109 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
110 CREATE FULLTEXT INDEX idx2 on articles (body);
111 SELECT * FROM articles WHERE MATCH (title, body)
112 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
113 ERROR HY000: Can't find FULLTEXT index matching the column list
114 SELECT * FROM articles WHERE MATCH (title)
115 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
116 id title body
117 1 MySQL Tutorial DBMS stands for DataBase ...
118 SELECT * FROM articles WHERE MATCH (body)
119 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
120 id title body
121 3 Optimizing MySQL In this tutorial we will show ...
122 drop index idx2 on articles;
123 SELECT * FROM articles WHERE MATCH (body)
124 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
125 ERROR HY000: Can't find FULLTEXT index matching the column list
126 CREATE FULLTEXT INDEX idx2 on articles (body);
127 SELECT * FROM articles WHERE MATCH (body)
128 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
129 id title body
130 3 Optimizing MySQL In this tutorial we will show ...
131 UPDATE articles set title = 'aaaa'
132 WHERE MATCH(title) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
133 SELECT * FROM articles WHERE MATCH (title)
134 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
135 id title body
136 SELECT * FROM articles WHERE MATCH (title)
137 AGAINST ('aaaa' IN NATURAL LANGUAGE MODE);
138 id title body
139 1 aaaa DBMS stands for DataBase ...
140 drop table articles;
141 CREATE TABLE articles (
142 FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
143 title VARCHAR(200),
144 body TEXT
145 ) ENGINE=InnoDB;
146 CREATE FULLTEXT INDEX idx on articles (title);
147 INSERT INTO articles VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');
148 UPDATE articles set title = 'bbbb' WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
149 ERROR HY000: Invalid InnoDB FTS Doc ID
150 UPDATE articles set title = 'bbbb', FTS_DOC_ID=8 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
151 ERROR HY000: Invalid InnoDB FTS Doc ID
152 UPDATE articles set title = 'bbbb', FTS_DOC_ID=10 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
153 SELECT * FROM articles WHERE MATCH (title) AGAINST ('bbbb' IN NATURAL LANGUAGE MODE);
154 FTS_DOC_ID title body
155 10 bbbb DBMS stands for DataBase ...
156 SELECT * FROM articles WHERE MATCH (title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
157 FTS_DOC_ID title body
158 CREATE FULLTEXT INDEX idx2 ON articles (body);
159 SELECT * FROM articles WHERE MATCH (body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
160 FTS_DOC_ID title body
161 10 bbbb DBMS stands for DataBase ...
162 UPDATE articles set body = 'bbbb', FTS_DOC_ID=11 WHERE MATCH(body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
163 drop table articles;
164 create table `articles`(`a` varchar(2) not null)engine=innodb;
165 create fulltext index `FTS_DOC_ID_INDEX` on `articles`(`a`);
166 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
167 create unique index `a` on `articles`(`a`);
168 drop table articles;
169 CREATE TABLE wp(
170 FTS_DOC_ID bigint PRIMARY KEY,
171 title VARCHAR(255) NOT NULL DEFAULT '',
172 text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
173 INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
174 (1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
175 (2, 'How To Use MySQL Well','After you went through a ...');
176 CREATE FULLTEXT INDEX idx ON wp(title, text);
177 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
178 DROP TABLE wp;
179 CREATE TABLE wp(
180 FTS_DOC_ID bigint unsigned PRIMARY KEY,
181 title VARCHAR(255) NOT NULL DEFAULT '',
182 text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
183 INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
184 (1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
185 (2, 'How To Use MySQL Well','After you went through a ...');
186 CREATE FULLTEXT INDEX idx ON wp(title, text);
187 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database')
188 FROM wp;
189 FTS_DOC_ID MATCH(title, text) AGAINST ('database')
190 1 0.0906190574169159
191 2 0
192 DROP TABLE wp;