Debian Codegraph @

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

raw
1 # Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT
2 CREATE TABLE articles (
3 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 title VARCHAR(200),
5 body TEXT,
6 FULLTEXT (title,body),
7 FULLTEXT (body))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 # Query involves Ranking
16 SELECT * FROM articles
17 WHERE MATCH (title,body)
18 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
19 id title body
20 6 MySQL Security When configured properly, MySQL ...
21 # Without optimization
22 SET DEBUG = '+d,fts_union_limit_off';
23 SELECT * FROM articles
24 WHERE MATCH (title,body)
25 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
26 id title body
27 6 MySQL Security When configured properly, MySQL ...
28 SET DEBUG = '-d,fts_union_limit_off';
29 # Query involves No Ranking and fts_union operations
30 SELECT * FROM articles
31 WHERE MATCH (title,body)
32 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
33 id title body
34 1 MySQL Tutorial DBMS stands for DataBase ...
35 # Without optimization
36 SET DEBUG = '+d,fts_union_limit_off';
37 SELECT * FROM articles
38 WHERE MATCH (title,body)
39 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
40 id title body
41 6 MySQL Security When configured properly, MySQL ...
42 SET DEBUG = '-d,fts_union_limit_off';
43 # Query involves No ranking and fts_union, fts_ignore
44 SELECT * FROM articles
45 WHERE MATCH (title,body)
46 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
47 id title body
48 6 MySQL Security When configured properly, MySQL ...
49 # Without optimization
50 SET DEBUG = '+d,fts_union_limit_off';
51 SELECT * FROM articles
52 WHERE MATCH (title,body)
53 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
54 id title body
55 6 MySQL Security When configured properly, MySQL ...
56 SET DEBUG = '-d,fts_union_limit_off';
57 # Query with fts_intersect
58 SELECT * FROM articles
59 WHERE MATCH (title,body)
60 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
61 id title body
62 5 MySQL vs. YourSQL In the following database comparison ...
63 # Without optimization
64 SET DEBUG = '+d,fts_union_limit_off';
65 SELECT * FROM articles
66 WHERE MATCH (title,body)
67 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
68 id title body
69 5 MySQL vs. YourSQL In the following database comparison ...
70 SET DEBUG = '-d,fts_union_limit_off';
71 INSERT INTO articles (title,body) VALUES
72 ('MySQL Tutorial','request [email protected] ...'),
73 ('MySQL Tutorial','request [email protected] ...'),
74 ('Trial version','query performace @1255 minute on 2.1Hz
75 Memory 2GB...'),
76 ('when To Use MySQL Well','for free faq [email protected] ...');
77 # Query with @distance
78 SELECT * FROM articles
79 WHERE MATCH (title,body)
80 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
81 id title body
82 7 MySQL Tutorial request [email protected] ...
83 # Without optimization
84 SET DEBUG = '+d,fts_union_limit_off';
85 SELECT * FROM articles
86 WHERE MATCH (title,body)
87 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
88 id title body
89 7 MySQL Tutorial request [email protected] ...
90 SET DEBUG = '-d,fts_union_limit_off';
91 # Query with subexpression
92 SELECT * FROM articles
93 WHERE MATCH (title,body)
94 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
95 id title body
96 7 MySQL Tutorial request [email protected] ...
97 # Without optimization
98 SET DEBUG = '+d,fts_union_limit_off';
99 SELECT * FROM articles
100 WHERE MATCH (title,body)
101 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
102 id title body
103 7 MySQL Tutorial request [email protected] ...
104 SET DEBUG = '-d,fts_union_limit_off';
105 # limit num1 OFFSET num2
106 SELECT * FROM articles
107 WHERE MATCH (title,body)
108 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
109 id title body
110 2 How To Use MySQL Well After you went through a ...
111 3 Optimizing MySQL In this tutorial we will show ...
112 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
113 5 MySQL vs. YourSQL In the following database comparison ...
114 # Without optimization
115 SET DEBUG = '+d,fts_union_limit_off';
116 SELECT * FROM articles
117 WHERE MATCH (title,body)
118 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
119 id title body
120 2 How To Use MySQL Well After you went through a ...
121 3 Optimizing MySQL In this tutorial we will show ...
122 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
123 5 MySQL vs. YourSQL In the following database comparison ...
124 SET DEBUG = '-d,fts_union_limit_off';
125 # wild card search
126 SELECT * FROM articles
127 WHERE MATCH (title,body)
128 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
129 id title body
130 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
131 # Without optimization
132 SET DEBUG = '+d,fts_union_limit_off';
133 SELECT * FROM articles
134 WHERE MATCH (title,body)
135 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
136 id title body
137 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
138 SET DEBUG = '-d,fts_union_limit_off';
139 # phrase search
140 SELECT * FROM articles
141 WHERE MATCH (title,body)
142 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
143 id title body
144 8 MySQL Tutorial request [email protected] ...
145 # Without optimization
146 SET DEBUG = '+d,fts_union_limit_off';
147 SELECT * FROM articles
148 WHERE MATCH (title,body)
149 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
150 id title body
151 8 MySQL Tutorial request [email protected] ...
152 SET DEBUG = '-d,fts_union_limit_off';
153 DROP TABLE articles;
154 #
155 # Bug #31228694 FTS QUERY WITH LIMIT CRASH
156 #
157 # restart: --ngram_token_size=1
158 select @@ngram_token_size;
159 @@ngram_token_size
160 1
161 CREATE TABLE t1 (
162 id INT(10) unsigned NOT NULL AUTO_INCREMENT,
163 name VARCHAR(45) NOT NULL,
164 PRIMARY KEY (id) USING BTREE,
165 FULLTEXT KEY search_name (name) WITH PARSER ngram
166 ) ENGINE=InnoDB;
167 Warnings:
168 Warning 1681 Integer display width is deprecated and will be removed in a future release.
169 INSERT INTO t1(name) VALUES('x'), ('mx'), ('mx'), ('mx'), ('mx');
170 SELECT * FROM t1 WHERE MATCH (`name`) AGAINST ('mx*' IN BOOLEAN MODE) LIMIT 3;
171 id name
172 2 mx
173 3 mx
174 4 mx
175 DROP TABLE t1;
176 # restart: