Debian Codegraph @

mysql-test/suite/innodb_fts/t/ngram_debug.test

raw
1 #
2 # InnoDB FULLTEXT SEARCH: CJK support - ngram parser
3 #
4 #
5
6
7 # Must have debug code to use SET DEBUG
8 --source include/have_debug.inc
9
10 # Enable diag print to print query parse tree
11 SET GLOBAL innodb_ft_enable_diag_print = 1;
12
13 -- echo # Test Case 1: Test English with Default(latin1) Charset
14 # Ignore ngram token check for latin1.
15 SET DEBUG="+d,fts_instrument_ignore_ngram_check";
16
17 CREATE TABLE articles (
18 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
19 title VARCHAR(200)
20 ) ENGINE=InnoDB;
21
22 ALTER TABLE articles ADD FULLTEXT INDEX (title) WITH PARSER ngram;
23
24 INSERT INTO articles (title) VALUES
25 ('what is M'),
26 ('Myname is wonder'),
27 ('My SQL is good'),
28 ('MySQL database'),
29 ('Mind a good thing'),
30 ('Mind good idea');
31
32 SELECT * FROM articles WHERE
33 MATCH(title) AGAINST('m*' IN BOOLEAN MODE);
34
35 SELECT * FROM articles WHERE
36 MATCH(title) AGAINST('my*' IN BOOLEAN MODE);
37
38 SELECT * FROM articles WHERE
39 MATCH(title) AGAINST('+mind -mysql' IN BOOLEAN MODE);
40
41 SELECT * FROM articles WHERE
42 MATCH(title) AGAINST('+(mind thing) -idea' IN BOOLEAN MODE);
43
44 --sorted_result
45 SELECT * FROM articles WHERE
46 MATCH(title) AGAINST('mysql');
47
48 SELECT * FROM articles WHERE
49 MATCH(title) AGAINST('mysql' IN BOOLEAN MODE);
50
51 SELECT * FROM articles WHERE
52 MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE);
53
54 SELECT * FROM articles WHERE
55 MATCH(title) AGAINST('"my sql"' IN BOOLEAN MODE);
56
57 SET DEBUG="-d,fts_instrument_ignore_ngram_check";
58
59 DROP TABLE articles;
60
61 SET NAMES utf8mb4;
62
63 -- echo # Test Case 2: Test Stopwords
64 # Compare with results without stopword check.
65 SET DEBUG="+d,fts_instrument_ignore_ngram_check";
66
67 # Without stopwords
68 CREATE TABLE articles (
69 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
70 title VARCHAR(200),
71 body TEXT
72 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
73
74 CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;
75
76 INSERT INTO articles (title, body) VALUES
77 ('从简单购物清单到画展','或企业网络中的海量数据'),
78 ('要想将数据添加到数据库','访问、处理计算机数据库中保存的数据'),
79 ('从简单的购物清单到画展','或企业网络中的海量数据'),
80 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
81 ('数据库是数据的结构化集合','它可以是任何东西');
82
83 --sorted_result
84 SELECT * FROM articles WHERE
85 MATCH(title, body) AGAINST('的* 或* 东西' IN BOOLEAN MODE);
86
87 SELECT * FROM articles WHERE
88 MATCH(title, body) AGAINST('简单的' IN BOOLEAN MODE);
89
90 SELECT * FROM articles WHERE
91 MATCH(title, body) AGAINST('或访问' IN BOOLEAN MODE);
92
93 SELECT * FROM articles WHERE
94 MATCH(title, body) AGAINST('"数据的结构化"' IN BOOLEAN MODE);
95
96 SET DEBUG="-d,fts_instrument_ignore_ngram_check";
97
98 DROP TABLE articles;
99
100 # With Stopwords
101 CREATE TABLE articles (
102 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
103 title VARCHAR(200),
104 body TEXT
105 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
106
107 # Define a user stopword table and set to it
108 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
109 DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
110
111 INSERT INTO user_stopword VALUES('的'),('或'),('东西'),('ab');
112
113 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
114
115 INSERT INTO articles (title, body) VALUES
116 ('从简单购物清单到画展','或企业网络中的海量数据'),
117 ('要想将数据添加到数据库','访问、处理计算机数据库中保存的数据');
118
119 CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;
120
121 INSERT INTO articles (title, body) VALUES
122 ('从简单的购物清单到画展','或企业网络中的海量数据'),
123 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
124 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据 abc xyz'),
125 ('数据库是数据的结构化集合','它可以是任何东西');
126
127 SELECT * FROM articles WHERE
128 MATCH(title, body) AGAINST('的* 或* 东西' IN BOOLEAN MODE);
129
130 SELECT * FROM articles WHERE
131 MATCH(title, body) AGAINST('简单的' IN BOOLEAN MODE);
132
133 SELECT * FROM articles WHERE
134 MATCH(title, body) AGAINST('或访问' IN BOOLEAN MODE);
135
136 SELECT * FROM articles WHERE
137 MATCH(title, body) AGAINST('"数据的结构化"' IN BOOLEAN MODE);
138
139 SELECT * FROM user_stopword WHERE value like 'ab' ;
140 SELECT * FROM articles WHERE
141 MATCH(title, body) AGAINST('"ab"' );
142 SELECT * FROM articles WHERE
143 MATCH(title, body) AGAINST('数据 +ab' IN BOOLEAN MODE);
144 --sorted_result
145 SELECT * FROM articles WHERE
146 MATCH(title, body) AGAINST('数据 (+xyz -bc)' IN BOOLEAN MODE);
147
148 DROP TABLE user_stopword;
149 DROP TABLE articles;
150 SET GLOBAL innodb_ft_server_stopword_table=default;
151
152 -- echo # Test Case 3: Check with FTS debug points
153 CREATE TABLE articles (
154 seq INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
155 c1 VARCHAR(200) CHARACTER SET big5 COLLATE big5_chinese_ci,
156 c2 CHAR(200) CHARACTER SET gbk COLLATE gbk_chinese_ci,
157 c3 CHAR(200) CHARACTER SET utf8,
158 j1 TEXT CHARACTER SET ujis COLLATE ujis_japanese_ci,
159 j2 VARCHAR(200) CHARACTER SET sjis COLLATE sjis_japanese_ci,
160 j3 VARCHAR(200) CHARACTER SET ujis,
161 k1 CHAR(200) CHARACTER SET euckr COLLATE euckr_korean_ci,
162 k2 CHAR(200) CHARACTER SET utf8,
163 e1 CHAR(200) CHARACTER SET latin1,
164 FULLTEXT KEY `con1` (c1),
165 FULLTEXT KEY `con2` (j1,j3),
166 FULLTEXT KEY `con3` (k2) WITH PARSER ngram
167 ) ENGINE=InnoDB ;
168 SET DEBUG='+d,alter_table_rollback_new_index';
169 -- error ER_UNKNOWN_ERROR
170 ALTER TABLE articles ADD FULLTEXT INDEX (c2(64));
171 -- error ER_UNKNOWN_ERROR
172 ALTER TABLE articles ADD FULLTEXT INDEX (j1,j3);
173 -- error ER_UNKNOWN_ERROR
174 ALTER TABLE articles ADD FULLTEXT INDEX (k1);
175 -- error ER_UNKNOWN_ERROR
176 ALTER TABLE articles ADD FULLTEXT INDEX (e1);
177 SET DEBUG='-d,alter_table_rollback_new_index';
178
179 # Abort the operation in dict_create_index_step by setting
180 # return status of dict_create_index_tree_step() to DB_OUT_OF_MEMORY
181 # The newly create dict_index_t should be removed from fts cache
182 SET DEBUG="+d,ib_dict_create_index_tree_fail";
183 --error ER_OUT_OF_RESOURCES
184 ALTER TABLE articles ADD FULLTEXT INDEX (c2(64));
185 --error ER_OUT_OF_RESOURCES
186 ALTER TABLE articles ADD FULLTEXT INDEX (j1,j3);
187 --error ER_OUT_OF_RESOURCES
188 ALTER TABLE articles ADD FULLTEXT INDEX (k1);
189 --error ER_OUT_OF_RESOURCES
190 ALTER TABLE articles ADD FULLTEXT INDEX (e1);
191 SET DEBUG="-d,ib_dict_create_index_tree_fail";
192
193 --error ER_INNODB_FT_LIMIT
194 ALTER TABLE articles ADD FULLTEXT INDEX (e1), ADD FULLTEXT INDEX (k1);
195 DROP TABLE articles;
196
197 SET GLOBAL innodb_ft_enable_diag_print = default;