Debian Codegraph @

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

raw
1 SET NAMES utf8mb4;
2 #
3 # MDEV-11233 CREATE FULLTEXT INDEX with a token
4 # longer than 127 bytes crashes server
5 #
6 CREATE TABLE t(t TEXT CHARACTER SET utf8mb3) ENGINE=InnoDB;
7 INSERT INTO t SET t=REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5);
8 INSERT INTO t SET t=REPEAT(_utf8 0xefbc90,84);
9 INSERT INTO t SET t=REPEAT('befor',17);
10 INSERT INTO t SET t='BeforeTheIndexCreation';
11 CREATE FULLTEXT INDEX ft ON t(t);
12 INSERT INTO t SET t='this was inserted after creating the index';
13 INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84);
14 INSERT INTO t SET t=REPEAT('after',17);
15 INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
16 # The data below is not 3-byte UTF-8, but 4-byte chars.
17 INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
18 Warnings:
19 Warning 1366 Incorrect string value: '\xF0\x9F\x96\x95\xF0\x9F...' for column `test`.`t`.`t` at row 1
20 INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
21 Warnings:
22 Warning 1366 Incorrect string value: '\xF0\x9F\x96\x96\xF0\x9F...' for column `test`.`t`.`t` at row 1
23 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST
24 (REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5));
25 COUNT(*)
26 1
27 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
28 COUNT(*)
29 1
30 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17));
31 COUNT(*)
32 0
33 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after');
34 COUNT(*)
35 1
36 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17));
37 COUNT(*)
38 0
39 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83));
40 COUNT(*)
41 0
42 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84));
43 COUNT(*)
44 1
45 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85));
46 COUNT(*)
47 0
48 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83));
49 COUNT(*)
50 0
51 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84));
52 COUNT(*)
53 1
54 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85));
55 COUNT(*)
56 0
57 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83));
58 COUNT(*)
59 0
60 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84));
61 COUNT(*)
62 0
63 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84));
64 COUNT(*)
65 0
66 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85));
67 COUNT(*)
68 0
69 SELECT * FROM t;
70 t
71 ็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้
72 000000000000000000000000000000000000000000000000000000000000000000000000000000000000
73 beforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbefor
74 BeforeTheIndexCreation
75 this was inserted after creating the index
76 111111111111111111111111111111111111111111111111111111111111111111111111111111111111
77 afterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafter
78 甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文
79 ????????????????????????????????????????????????????????????????????????????????????
80 ?????????????????????????????????????????????????????????????????????????????????????
81 SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
82 len COUNT(*)
83 252 6
84 DROP TABLE t;
85 CREATE TABLE t(t TEXT CHARACTER SET utf8mb4) ENGINE=InnoDB;
86 INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
87 INSERT INTO t SET t=REPEAT(_utf8 0xefbc90,84);
88 INSERT INTO t SET t=REPEAT('befor',17);
89 INSERT INTO t SET t='BeforeTheIndexCreation';
90 CREATE FULLTEXT INDEX ft ON t(t);
91 INSERT INTO t SET t='this was inserted after creating the index';
92 INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84);
93 INSERT INTO t SET t=REPEAT('after',17);
94 INSERT INTO t SET t=REPEAT(concat(repeat(_utf8mb3 0xE0B987, 4), repeat(_utf8mb3 0xE0B989, 5)), 5);
95 INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
96 # The token below exceeds the 84-character limit.
97 INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
98 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15));
99 COUNT(*)
100 1
101 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
102 COUNT(*)
103 1
104 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17));
105 COUNT(*)
106 0
107 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after');
108 COUNT(*)
109 1
110 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17));
111 COUNT(*)
112 0
113 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83));
114 COUNT(*)
115 0
116 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84));
117 COUNT(*)
118 1
119 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85));
120 COUNT(*)
121 0
122 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83));
123 COUNT(*)
124 0
125 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84));
126 COUNT(*)
127 1
128 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85));
129 COUNT(*)
130 0
131 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83));
132 COUNT(*)
133 0
134 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84));
135 COUNT(*)
136 0
137 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84));
138 COUNT(*)
139 0
140 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85));
141 COUNT(*)
142 0
143 SELECT * FROM t;
144 t
145 甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文
146 000000000000000000000000000000000000000000000000000000000000000000000000000000000000
147 beforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbefor
148 BeforeTheIndexCreation
149 this was inserted after creating the index
150 111111111111111111111111111111111111111111111111111111111111111111111111111111111111
151 afterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafter
152 ็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้
153 🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕
154 🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖
155 SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
156 len COUNT(*)
157 336 6
158 DROP TABLE t;
159 CREATE TABLE t(t TEXT CHARACTER SET latin1, FULLTEXT INDEX(t))
160 ENGINE=InnoDB;
161 SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
162 len COUNT(*)
163 84 6
164 DROP TABLE t;
165 #
166 # MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in
167 # trx_undo_page_report_modify upon optimizing table
168 # under innodb_optimize_fulltext_only
169 #
170 CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB;
171 INSERT INTO t1 (f1) VALUES ('foo'),('bar');
172 DELETE FROM t1 LIMIT 1;
173 ALTER TABLE t1 ADD FULLTEXT KEY (f1);
174 SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only;
175 SET GLOBAL innodb_optimize_fulltext_only= 1;
176 OPTIMIZE TABLE t1;
177 Table Op Msg_type Msg_text
178 test.t1 optimize status OK
179 DROP TABLE t1;
180 SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save;
181 #
182 # MDEV-24403 Segfault on CREATE TABLE with explicit FTS_DOC_ID_INDEX by multiple fields
183 #
184 create table t1 (
185 f1 int, f2 text,
186 FTS_DOC_ID bigint unsigned not null,
187 unique key FTS_DOC_ID_INDEX(FTS_DOC_ID, f1),
188 fulltext (f2))
189 engine=innodb;
190 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
191 #
192 # MDEV-26938 Support descending indexes internally in InnoDB
193 #
194 CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
195 UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC), FULLTEXT(b))
196 ENGINE=InnoDB;
197 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
198 CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
199 UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC)) ENGINE=InnoDB;
200 SHOW CREATE TABLE t1;
201 Table Create Table
202 t1 CREATE TABLE `t1` (
203 `a` int(11) NOT NULL,
204 `b` text DEFAULT NULL,
205 `FTS_DOC_ID` bigint(20) unsigned NOT NULL,
206 PRIMARY KEY (`a`),
207 UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID` DESC)
208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
209 ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=INPLACE;
210 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Not implemented for system-versioned operations. Try ALGORITHM=COPY
211 ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
212 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
213 DROP TABLE t1;