1 files changed, 413 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
new file mode 100644
index 00000000..cef8df2d
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
@@ -0,0 +1,413 @@
+# This is the DDL function tests for innodb FTS
+
+-- source include/have_innodb.inc
+-- source include/maybe_versioning.inc
+
+# Create FTS table
+CREATE TABLE fts_test (
+	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+	title VARCHAR(200),
+	body TEXT
+	) ENGINE=InnoDB;
+
+# Insert six rows
+INSERT INTO fts_test (title,body) VALUES
+	('MySQL Tutorial','DBMS stands for DataBase ...')  ,
+	('How To Use MySQL Well','After you went through a ...'),
+	('Optimizing MySQL','In this tutorial we will show ...'),
+	('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+	('MySQL vs. YourSQL','In the following database comparison ...'),
+	('MySQL Security','When configured properly, MySQL ...');
+
+# Table does rebuild when fts index builds for the first time
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
+
+# Create the FTS index
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=INPLACE;
+
+# Select word "tutorial" in the table
+SELECT * FROM fts_test WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+# Drop the FTS idx
+DROP INDEX idx ON fts_test;
+
+# Continue insert some rows
+INSERT INTO fts_test (title,body) VALUES
+	('MySQL Tutorial','DBMS stands for DataBase ...')  ,
+	('How To Use MySQL Well','After you went through a ...'),
+	('Optimizing MySQL','In this tutorial we will show ...'),
+	('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+	('MySQL vs. YourSQL','In the following database comparison ...'),
+	('MySQL Security','When configured properly, MySQL ...');
+
+# FTS_DOC_ID hidden column and FTS_DOC_ID index exist
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
+
+# Select word "tutorial" in the table
+SELECT * FROM fts_test WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+# Boolean search
+# Select rows contain "MySQL" but not "YourSQL"
+SELECT * FROM fts_test WHERE MATCH (title,body)
+        AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+
+# Truncate table
+let $vers=$MTR_COMBINATION_VERS+$MTR_COMBINATION_VERS_TRX;
+
+if ($vers)
+{
+--disable_query_log
+CREATE TABLE fts_test2 LIKE fts_test;
+DROP TABLE fts_test;
+RENAME TABLE fts_test2 TO fts_test;
+--enable_query_log
+--echo TRUNCATE TABLE fts_test;
+}
+if (!$vers)
+{
+TRUNCATE TABLE fts_test;
+}
+
+DROP INDEX idx ON fts_test;
+
+# Continue insert some rows
+INSERT INTO fts_test (title,body) VALUES
+	('MySQL Tutorial','DBMS stands for DataBase ...')  ,
+	('How To Use MySQL Well','After you went through a ...'),
+	('Optimizing MySQL','In this tutorial we will show ...'),
+	('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+	('MySQL vs. YourSQL','In the following database comparison ...'),
+	('MySQL Security','When configured properly, MySQL ...');
+
+# Recreate the FTS index
+CREATE FULLTEXT INDEX idx on fts_test (title, body);
+
+# Select word "tutorial" in the table
+SELECT * FROM fts_test WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+DROP TABLE fts_test;
+
+# Create FTS table
+CREATE TABLE fts_test (
+	FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+	title VARCHAR(200),
+	body TEXT
+	) ENGINE=InnoDB;
+
+# Insert six rows
+INSERT INTO fts_test (title,body) VALUES
+	('MySQL Tutorial','DBMS stands for DataBase ...')  ,
+	('How To Use MySQL Well','After you went through a ...'),
+	('Optimizing MySQL','In this tutorial we will show ...'),
+	('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+	('MySQL vs. YourSQL','In the following database comparison ...'),
+	('MySQL Security','When configured properly, MySQL ...');
+
+# Create the FTS index
+# We could support online fulltext index creation when a FTS_DOC_ID
+# column already exists. This has not been implemented yet.
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
+ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
+
+SELECT * FROM fts_test WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+# Drop and recreate
+drop index idx on fts_test;
+
+CREATE FULLTEXT INDEX idx on fts_test (title, body);
+
+SELECT * FROM fts_test WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+# Drop the FTS_DOC_ID_INDEX and try again
+drop index idx on fts_test;
+
+CREATE FULLTEXT INDEX idx on fts_test (title, body);
+
+SELECT * FROM fts_test WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+drop table fts_test;
+
+# Test FTS_DOC_ID and FTS_DOC_ID_INDEX all in the create table clause
+CREATE TABLE fts_test (
+   FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+   title varchar(255) NOT NULL DEFAULT '',
+   text mediumtext NOT NULL,
+   PRIMARY KEY (FTS_DOC_ID),
+   UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
+   FULLTEXT KEY idx (title,text)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+
+set @@auto_increment_increment=10;
+
+INSERT INTO fts_test (title, text) VALUES
+        ('MySQL Tutorial','DBMS stands for DataBase ...'),
+        ('How To Use MySQL Well','After you went through a ...'),
+        ('Optimizing MySQL','In this tutorial we will show ...'),
+        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+        ('MySQL vs. YourSQL','In the following database comparison ...'),
+        ('MySQL Security','When configured properly, MySQL ...');
+-- disable_result_log
+ANALYZE TABLE fts_test;
+-- enable_result_log
+set @@auto_increment_increment=1;
+
+select *, match(title, text)  AGAINST ('database') as score
+from fts_test order by score desc;
+
+drop index idx on fts_test;
+
+drop table fts_test;
+
+# This should fail:
+# Create a FTS_DOC_ID of the wrong type (should be bigint)
+--error 1166
+CREATE TABLE fts_test (
+   FTS_DOC_ID int(20) unsigned NOT NULL AUTO_INCREMENT,
+   title varchar(255) NOT NULL DEFAULT '',
+   text mediumtext NOT NULL,
+   PRIMARY KEY (FTS_DOC_ID),
+   UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
+   FULLTEXT KEY idx (title,text)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+
+# This should fail:
+# Create a FTS_DOC_ID_INDEX of the wrong type (should be unique)
+--error ER_INNODB_FT_WRONG_DOCID_INDEX
+CREATE TABLE fts_test (
+   FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+   title varchar(255) NOT NULL DEFAULT '',
+   text mediumtext NOT NULL,
+   PRIMARY KEY (FTS_DOC_ID),
+   KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
+   FULLTEXT KEY idx (title,text)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+
+CREATE TABLE articles (
+	FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
+	title VARCHAR(200),
+	body TEXT
+) ENGINE=InnoDB;
+
+INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
+        (9, 'MySQL Tutorial','DBMS stands for DataBase ...'),
+        (10, 'How To Use MySQL Well','After you went through a ...'),
+        (12, 'Optimizing MySQL','In this tutorial we will show ...'),
+        (14,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+        (19, 'MySQL vs. YourSQL','In the following database comparison ...'),
+        (20, 'MySQL Security','When configured properly, MySQL ...');
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE articles ADD FULLTEXT INDEX idx (title),
+		     ADD FULLTEXT INDEX idx3 (title), ALGORITHM=INPLACE;
+--enable_info
+ALTER TABLE articles ADD FULLTEXT INDEX idx (title),
+		     ADD FULLTEXT INDEX idx3 (title);
+--disable_info
+
+ALTER TABLE articles ADD INDEX t20 (title(20)), LOCK=NONE;
+ALTER TABLE articles DROP INDEX t20;
+
+INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
+        (29, 'MySQL Tutorial','DBMS stands for DataBase ...'),
+        (30, 'How To Use MySQL Well','After you went through a ...'),
+        (32, 'Optimizing MySQL','In this tutorial we will show ...'),
+        (34,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+        (39, 'MySQL vs. YourSQL','In the following database comparison ...'),
+        (40, 'MySQL Security','When configured properly, MySQL ...');
+
+SELECT * FROM articles WHERE MATCH (title)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+DROP INDEX idx ON articles;
+
+SELECT * FROM articles WHERE MATCH (title)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+CREATE FULLTEXT INDEX idx on articles (title, body);
+
+SELECT * FROM articles WHERE MATCH (title, body)
+        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+DROP TABLE articles;
+
+create table articles(`FTS_DOC_ID` serial,
+`col32` timestamp not null,`col115` text) engine=innodb;
+
+create fulltext index `idx5` on articles(`col115`) ;
+
+alter ignore table articles add primary key  (`col32`)  ;
+
+drop table articles;
+
+# Create a table with FTS index, this will create hidden column FTS_DOC_ID
+CREATE TABLE articles (
+	id INT UNSIGNED NOT NULL,
+	title VARCHAR(200),
+	body TEXT
+	) ENGINE=InnoDB;
+
+INSERT INTO articles VALUES
+	(1, 'MySQL Tutorial','DBMS stands for DataBase ...')  ,
+	(2, 'How To Use MySQL Well','After you went through a ...'),
+	(3, 'Optimizing MySQL','In this tutorial we will show ...'),
+	(4, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+	(5, 'MySQL vs. YourSQL','In the following database comparison ...'),
+	(6, 'MySQL Security','When configured properly, MySQL ...');
+
+CREATE FULLTEXT INDEX idx on articles (title, body);
+
+# Drop the FTS index, however, this will keep the FTS_DOC_ID hidden
+# column (to avoid a table rebuild)
+DROP INDEX idx ON articles;
+
+# Now create cluster index on id online; The rebuild should still
+# have the FTS_DOC_ID
+CREATE UNIQUE INDEX idx2 ON articles(id);
+
+# Recreate FTS index, this should not require a rebuild,
+# since the FTS_DOC_ID is still there
+CREATE FULLTEXT INDEX idx on articles (title, body);
+
+SELECT * FROM articles WHERE MATCH (title, body)
+	AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
+
+DROP TABLE articles;
+
+--echo #
+--echo # MDEV-22811 DDL fails to drop and re-create FTS index
+--echo #
+CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY,
+		 f1 VARCHAR(200),FULLTEXT fidx(f1))engine=innodb;
+ALTER TABLE t1 DROP index fidx, ADD FULLTEXT INDEX(f1);
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-21478 Inplace alter fails to report error when
+--echo #	FTS_DOC_ID is added
+
+SET NAMES utf8;
+
+CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB;
+ALTER TABLE t1 ADD FTS_DOC_ıD BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
+ALTER TABLE t1 DROP COLUMN FTS_DOC_ıD;
+ALTER TABLE t1 ADD FTS_DOC_ıD BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
+DROP TABLE t1;
+
+CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD FTS_DOC_İD BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD FTS_DOC_İD BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD fts_doc_id INT, ALGORITHM=COPY;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD fts_doc_id INT, ALGORITHM=INPLACE;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD fts_doc_id BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD fts_doc_id BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD FTS_DOC_ID INT UNSIGNED NOT NULL, ALGORITHM=COPY;
+
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 ADD FTS_DOC_ID INT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
+
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-25271 Double free of table when inplace alter
+--echo #        FTS add index fails
+--echo #
+call mtr.add_suppression("InnoDB: Operating system error number .* in a file operation.");
+call mtr.add_suppression("InnoDB: Error number .* means");
+call mtr.add_suppression("InnoDB: Cannot create file");
+call mtr.add_suppression("InnoDB: Failed to create");
+
+let MYSQLD_DATADIR=`select @@datadir`;
+CREATE TABLE t1(a TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL UNIQUE) ENGINE=InnoDB;
+let $fts_aux_file= `select concat('FTS_',right(concat(repeat('0',16), lower(hex(TABLE_ID))),16),'_BEING_DELETED.ibd') FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1'`;
+write_file $MYSQLD_DATADIR/test/$fts_aux_file;
+EOF
+--replace_regex /".*" from/"Resource temporarily unavailable" from/
+--error ER_GET_ERRNO
+ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE;
+DROP TABLE t1;
+remove_file $MYSQLD_DATADIR/test/$fts_aux_file;
+
+# Add more than one FTS index
+CREATE TABLE t1 (a VARCHAR(3)) ENGINE=InnoDB;
+ALTER TABLE t1 ADD FULLTEXT KEY(a), ADD COLUMN b VARCHAR(3), ADD FULLTEXT KEY(b);
+
+# Cleanup
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-18152 Assertion 'num_fts_index <= 1' failed
+--echo # in prepare_inplace_alter_table_dict
+--echo #
+CREATE TABLE t1
+(a VARCHAR(128), b VARCHAR(128), FULLTEXT INDEX(a), FULLTEXT INDEX(b))
+ENGINE=InnoDB;
+ALTER TABLE t1 ADD c SERIAL;
+DROP TABLE t1;
+
+--echo # End of 10.3 tests
+
+--echo #
+--echo #  MDEV-27582 Fulltext DDL decrements the FTS_DOC_ID value
+--echo #
+CREATE TABLE t1 (
+	f1 INT NOT NULL PRIMARY KEY,
+        f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+connect(con1,localhost,root,,,);
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+
+connection default;
+DELETE FROM t1 WHERE f1 = 2;
+ALTER TABLE t1 DROP INDEX ft;
+ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
+INSERT INTO t1 VALUES (3, 'innodb fts search');
+SET GLOBAL innodb_optimize_fulltext_only=ON;
+OPTIMIZE TABLE t1;
+SET GLOBAL innodb_ft_aux_table = 'test/t1';
+SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
+DROP TABLE t1;
+disconnect con1;
+SET GLOBAL innodb_optimize_fulltext_only=OFF;
+SET GLOBAL innodb_ft_aux_table = default;
+
+--echo #
+--echo #  MDEV-32017  Auto-increment no longer works for
+--echo #		 explicit FTS_DOC_ID
+--echo #
+CREATE TABLE t (
+  FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  f1 char(255), f2 char(255), f3 char(255), fulltext key (f3)
+) ENGINE=InnoDB;
+INSERT INTO t (f1,f2,f3) VALUES ('foo','bar','baz');
+ALTER TABLE t ADD FULLTEXT INDEX ft1(f1);
+ALTER TABLE t ADD FULLTEXT INDEX ft2(f2);
+INSERT INTO t (f1,f2,f3) VALUES ('bar','baz','qux');
+DROP TABLE t;