Debian Codegraph @

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

raw
1 #
2 # InnoDB FULLTEXT SEARCH: CJK support - ngram parser
3 #
4 #
5
6
7 #Test 3 scenarios:
8 #1. CREATE a CJK fulltext index with ngram parser when ngram parser is loaded,
9 # restart without ngram parser, do SELECT/INSERT;
10 #2. CREATE a CJK fulltext index with ngram parser when ngram parser is not loaded.
11 #3. CREATE a CJK fulltext index without ngram parser when ngram parser is not loaded.
12
13 SET NAMES utf8mb4;
14
15 # t1 with default parser
16 CREATE TABLE t1 (
17 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
18 title VARCHAR(200),
19 content TEXT,
20 FULLTEXT INDEX ft_content (content)
21 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
22
23 INSERT INTO t1 (title, content) VALUES ('中国', '中国'), ('中国上海', '中国上海');
24
25 ALTER TABLE t1 ADD FULLTEXT INDEX ft_title (title);
26
27 SHOW CREATE TABLE t1;
28
29 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
30 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
31
32 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国') ORDER BY id;
33 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海') ORDER BY id;
34
35 DROP TABLE t1;
36
37 # t1 with ngram parser
38 CREATE TABLE t1 (
39 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
40 title VARCHAR(200),
41 content TEXT,
42 FULLTEXT INDEX ft_content (content) WITH PARSER ngram
43 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
44
45 INSERT INTO t1 (title, content) VALUES ('中国', '中国'), ('中国上海', '中国上海');
46
47 ALTER TABLE t1 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
48
49 SHOW CREATE TABLE t1;
50
51 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
52 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
53
54 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国') ORDER BY id;
55 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海') ORDER BY id;
56
57 # t2 with ngram parser
58 CREATE TABLE t2 (
59 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
60 title VARCHAR(200),
61 content TEXT,
62 FULLTEXT INDEX ft_content (content) WITH PARSER ngram
63 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
64
65 ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
66
67 # Restart without ngram.
68 let $restart_parameters = restart: --ngram=OFF;
69 --source include/restart_mysqld.inc
70
71 # t1
72 CHECK TABLE t1;
73
74 --error ER_PLUGIN_IS_NOT_LOADED
75 SELECT * FROM t1;
76
77 --error ER_PLUGIN_IS_NOT_LOADED
78 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国');
79
80 --error ER_PLUGIN_IS_NOT_LOADED
81 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海');
82
83 --error ER_PLUGIN_IS_NOT_LOADED
84 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国');
85
86 --error ER_PLUGIN_IS_NOT_LOADED
87 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海');
88
89 --error ER_PLUGIN_IS_NOT_LOADED
90 INSERT INTO t1 (title, content) VALUES ('中国', '中国');
91
92 --error ER_PLUGIN_IS_NOT_LOADED
93 DROP INDEX ft_title ON t1;
94
95 --error ER_PLUGIN_IS_NOT_LOADED
96 DROP INDEX ft_content ON t1;
97
98 --error ER_PLUGIN_IS_NOT_LOADED
99 TRUNCATE TABLE t1;
100
101 --error ER_PLUGIN_IS_NOT_LOADED
102 ALTER TABLE t1 RENAME TO t3;
103
104 # t2
105 RENAME TABLE t2 to t3;
106
107 DROP TABLE t3;
108
109 --error ER_FUNCTION_NOT_DEFINED
110 CREATE TABLE t2 (
111 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
112 title VARCHAR(200),
113 FULLTEXT INDEX ft_title (title) WITH PARSER ngram
114 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
115
116 CREATE TABLE t2 (
117 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
118 title VARCHAR(200)
119 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
120
121 --error ER_FUNCTION_NOT_DEFINED
122 ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
123
124 ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title);
125
126 INSERT INTO t2 (title) VALUES ('中国');
127
128 SELECT * FROM t2 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
129 SELECT * FROM t2 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
130
131 DROP TABLE t2;
132
133 # Restart with ngram.
134 let $restart_parameters = restart;
135 --source include/restart_mysqld.inc
136
137 CHECK TABLE t1;
138
139 SELECT * FROM t1;
140
141 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
142 SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
143
144 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国') ORDER BY id;
145 SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海') ORDER BY id;
146
147 DROP TABLE t1;