Server IP : 104.21.38.3 / Your IP : 172.68.164.28 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/innodb_fts/t/ |
Upload File : |
--source include/have_innodb.inc # # test of new fulltext search features # # # two-level tree # call mtr.add_suppression("Trying to free a missing index of table 'test/t1'"); call mtr.add_suppression("Failed to create index with index id .* of table 'test/t1'"); --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 ( i int(10) unsigned not null auto_increment primary key, a varchar(255) not null, FULLTEXT KEY (a) ) ENGINE = INNODB; # two-level entry, second-level tree with depth 2 --disable_query_log let $1=260; while ($1) { eval insert t1 (a) values ('aaaxxx'); dec $1; } # two-level entry, second-level tree has only one page let $1=255; while ($1) { eval insert t1 (a) values ('aaazzz'); dec $1; } # one-level entry (entries) let $1=250; while ($1) { eval insert t1 (a) values ('aaayyy'); dec $1; } --enable_query_log # converting to two-level # INNODB_FTS: Do not support repair #repair table t1 quick; check table t1; #optimize table t1; # BUG#5327 - mi_sort_index() of 2-level tree #check table t1; select count(*) from t1 where match a against ('aaaxxx'); select count(*) from t1 where match a against ('aaayyy'); select count(*) from t1 where match a against ('aaazzz'); select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz'); select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode); select count(*) from t1 where match a against ('aaax*' in boolean mode); select count(*) from t1 where match a against ('aaay*' in boolean mode); select count(*) from t1 where match a against ('aaa*' in boolean mode); # mi_write: insert t1 (a) values ('aaaxxx'),('aaayyy'); # call to enlarge_root() below insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'); select count(*) from t1 where match a against ('aaaxxx'); select count(*) from t1 where match a against ('aaayyy'); select count(*) from t1 where match a against ('aaazzz'); # mi_delete insert t1 (a) values ('aaaxxx 000000'); select count(*) from t1 where match a against ('000000'); delete from t1 where match a against ('000000'); select count(*) from t1 where match a against ('000000'); select count(*) from t1 where match a against ('aaaxxx'); delete from t1 where match a against ('aaazzz'); select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); # double-check without index select count(*) from t1 where a = 'aaaxxx'; select count(*) from t1 where a = 'aaayyy'; select count(*) from t1 where a = 'aaazzz'; # update insert t1 (a) values ('aaaxxx 000000'); select count(*) from t1 where match a against ('000000'); update t1 set a='aaazzz' where match a against ('000000'); select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); update t1 set a='aaazzz' where a = 'aaaxxx'; update t1 set a='aaaxxx' where a = 'aaayyy'; select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); drop table t1; CREATE TABLE t1 ( i int(10) unsigned not null auto_increment primary key, a varchar(255) not null, FULLTEXT KEY (a) ) ENGINE = INNODB; # # now same as about but w/o repair table # 2-level tree created by mi_write # # two-level entry, second-level tree with depth 2 --disable_query_log let $1=260; while ($1) { eval insert t1 (a) values ('aaaxxx'); dec $1; } let $1=255; while ($1) { eval insert t1 (a) values ('aaazzz'); dec $1; } let $1=250; while ($1) { eval insert t1 (a) values ('aaayyy'); dec $1; } --enable_query_log select count(*) from t1 where match a against ('aaaxxx'); select count(*) from t1 where match a against ('aaayyy'); select count(*) from t1 where match a against ('aaazzz'); select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz'); select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode); select count(*) from t1 where match a against ('aaax*' in boolean mode); select count(*) from t1 where match a against ('aaay*' in boolean mode); select count(*) from t1 where match a against ('aaa*' in boolean mode); # mi_write: insert t1 (a) values ('aaaxxx'),('aaayyy'); insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'); select count(*) from t1 where match a against ('aaaxxx'); select count(*) from t1 where match a against ('aaayyy'); select count(*) from t1 where match a against ('aaazzz'); # mi_delete insert t1 (a) values ('aaaxxx 000000'); select count(*) from t1 where match a against ('000000'); delete from t1 where match a against ('000000'); select count(*) from t1 where match a against ('000000'); select count(*) from t1 where match a against ('aaaxxx'); delete from t1 where match a against ('aaazzz'); select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); # double-check without index select count(*) from t1 where a = 'aaaxxx'; select count(*) from t1 where a = 'aaayyy'; select count(*) from t1 where a = 'aaazzz'; # update insert t1 (a) values ('aaaxxx 000000'); select count(*) from t1 where match a against ('000000'); update t1 set a='aaazzz' where match a against ('000000'); select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); update t1 set a='aaazzz' where a = 'aaaxxx'; update t1 set a='aaaxxx' where a = 'aaayyy'; select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); drop table t1; # # BUG#11336 # # for uca collation isalnum and strnncollsp don't agree on whether # 0xC2A0 is a space (strnncollsp is right, isalnum is wrong). # # they still don't, the bug was fixed by avoiding strnncollsp # set names utf8; eval create table t1(a text,fulltext(a)) ENGINE = INNODB collate=utf8_swedish_ci; insert into t1 values('test test '),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'); delete from t1 limit 1; # # BUG#16489: utf8 + fulltext leads to corrupt index file. # truncate table t1; insert into t1 values('ab c d'); update t1 set a='ab c d'; select * from t1 where match a against('ab c' in boolean mode); select * from t1 where match a against('ab c' in boolean mode); drop table t1; set names latin1; # End of 4.1 tests # # BUG#19580 - FULLTEXT search produces wrong results on UTF-8 columns # INNODB_FTS: Investigate SET NAMES utf8; CREATE TABLE t1(a VARCHAR(255), FULLTEXT(a)) ENGINE = INNODB DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES('„MySQL“'); SELECT a FROM t1 WHERE MATCH a AGAINST('“MySQL„' IN BOOLEAN MODE); DROP TABLE t1; SET NAMES latin1; # # Bug #20597981 - WRONG RELEVANCE RANKING FOR FULL TEXT SEARCHES # WHEN FTS_DOC_ID IS PRIMARY KEY CREATE TABLE t1 ( FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, id int(10) not null , first_name varchar(50) NOT NULL, last_name varchar(50) NOT NULL, PRIMARY KEY (FTS_DOC_ID), UNIQUE KEY idx_1 (first_name, last_name), FULLTEXT KEY `idx_2` (first_name) ) ENGINE=InnoDB; INSERT INTO t1 (id, first_name, last_name) VALUES (10, 'Bart', 'Simpson'), (11, 'Homer', 'Simpson'), (12, 'Marge', 'Simpson'), (13, 'Lisa', 'Simpson'), (14, 'Maggie', 'Simpson'), (15, 'Ned', 'Flanders'), (16, 'Nelson', 'Muntz'); analyze table t1; SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1; DROP TABLE t1;