summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb_fts/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb_fts/r')
-rw-r--r--mysql-test/suite/innodb_fts/r/crash_recovery.result2
-rw-r--r--mysql-test/suite/innodb_fts/r/ft_result_cache_limit.result2
-rw-r--r--mysql-test/suite/innodb_fts/r/fulltext.result11
-rw-r--r--mysql-test/suite/innodb_fts/r/fulltext_misc.result20
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers.rdiff10
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers_trx.rdiff10
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result4
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result5
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result3
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result16
-rw-r--r--mysql-test/suite/innodb_fts/r/misc_1.result2
-rw-r--r--mysql-test/suite/innodb_fts/r/opt.result5
12 files changed, 49 insertions, 41 deletions
diff --git a/mysql-test/suite/innodb_fts/r/crash_recovery.result b/mysql-test/suite/innodb_fts/r/crash_recovery.result
index b6319014..d7a8ef85 100644
--- a/mysql-test/suite/innodb_fts/r/crash_recovery.result
+++ b/mysql-test/suite/innodb_fts/r/crash_recovery.result
@@ -158,6 +158,8 @@ DROP TABLE mdev19073, mdev19073_2;
# MDEV-28706 Redundant InnoDB table fails during alter
#
SET @@global.innodb_file_per_table = 0;
+Warnings:
+Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
CREATE TABLE t1 (
col_int INTEGER, col_text TEXT,
col_text_1 TEXT
diff --git a/mysql-test/suite/innodb_fts/r/ft_result_cache_limit.result b/mysql-test/suite/innodb_fts/r/ft_result_cache_limit.result
index 74d60410..a42b08ae 100644
--- a/mysql-test/suite/innodb_fts/r/ft_result_cache_limit.result
+++ b/mysql-test/suite/innodb_fts/r/ft_result_cache_limit.result
@@ -19,7 +19,7 @@ END WHILE;
END//
CALL populate_t1;
SET autocommit=1;
-SET SESSION debug="+d,fts_instrument_result_cache_limit";
+SET SESSION debug_dbug="+d,fts_instrument_result_cache_limit";
ALTER TABLE t1 ADD FULLTEXT INDEX `text_content_idx` (`text_content`);
SELECT FTS_DOC_ID, text_content
FROM t1
diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result
index 32dc87da..2a6fb81f 100644
--- a/mysql-test/suite/innodb_fts/r/fulltext.result
+++ b/mysql-test/suite/innodb_fts/r/fulltext.result
@@ -537,6 +537,9 @@ CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB;
INSERT INTO t2 VALUES (1,'Scargill');
CREATE TABLE t3 (a int, b int) ENGINE = InnoDB;
INSERT INTO t3 VALUES (1,1), (2,1);
+SELECT * FROM t2 where MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE);
+a b2
+1 Scargill
# t2 should use full text index
EXPLAIN
SELECT count(*) FROM t1 WHERE
@@ -546,8 +549,8 @@ WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 MATERIALIZED t2 fulltext b2 b2 0 1 Using where
-2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t2 fulltext b2 b2 0 1 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where
# should return 0
SELECT count(*) FROM t1 WHERE
not exists(
@@ -721,6 +724,8 @@ DROP TABLE t1;
#
SET @save = @@global.innodb_file_per_table;
SET @@global.innodb_file_per_table = 0;
+Warnings:
+Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
CREATE TABLE t1 (
col_int INTEGER, col_text TEXT,
col_int_g INTEGER GENERATED ALWAYS AS (col_int)
@@ -730,6 +735,8 @@ ALTER TABLE t1 DROP KEY `ftidx` ;
INSERT INTO t1 (col_int, col_text) VALUES ( 1255, NULL);
DROP TABLE t1;
SET @@global.innodb_file_per_table = @save;
+Warnings:
+Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
#
# MDEV-20797 FULLTEXT search with apostrophe,
# and mandatory words
diff --git a/mysql-test/suite/innodb_fts/r/fulltext_misc.result b/mysql-test/suite/innodb_fts/r/fulltext_misc.result
index 69812ff8..3aa72dd5 100644
--- a/mysql-test/suite/innodb_fts/r/fulltext_misc.result
+++ b/mysql-test/suite/innodb_fts/r/fulltext_misc.result
@@ -8,9 +8,9 @@ EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
WHERE t1.f1 GROUP BY t1.f1));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
-2 SUBQUERY a index NULL f1_2 8 NULL 1 Using index
+2 SUBQUERY a ALL NULL NULL NULL NULL 1
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
@@ -18,14 +18,14 @@ PREPARE stmt FROM
WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
-2 SUBQUERY a index NULL f1_2 8 NULL 1 Using index
+2 SUBQUERY a ALL NULL NULL NULL NULL 1
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
-2 SUBQUERY a index NULL f1_2 8 NULL 1 Using index
+2 SUBQUERY a ALL NULL NULL NULL NULL 1
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
@@ -34,14 +34,14 @@ PREPARE stmt FROM
WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
-2 SUBQUERY a index NULL f1_2 8 NULL 1 Using index
+2 SUBQUERY a ALL NULL NULL NULL NULL 1
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
-2 SUBQUERY a index NULL f1_2 8 NULL 1 Using index
+2 SUBQUERY a ALL NULL NULL NULL NULL 1
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
drop table if exists t1;
diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers.rdiff b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers.rdiff
index d46275ff..d0967243 100644
--- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers.rdiff
+++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers.rdiff
@@ -1,5 +1,5 @@
---- innodb-fts-ddl.result
-+++ innodb-fts-ddl.reject
+--- innodb-fts-ddl.result 2023-11-23 13:40:18.932463721 +0100
++++ innodb-fts-ddl,vers.result 2024-04-09 11:34:42.968505497 +0200
@@ -11,8 +11,10 @@
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
@@ -27,14 +27,14 @@
@@ -76,8 +80,10 @@
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
- CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
+ ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE;
-ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
-ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
-+ERROR 0A000: LOCK=NONE is not supported. Reason: Not implemented for system-versioned operations. Try LOCK=SHARED
++ERROR 0A000: ALGORITHM=NOCOPY is not supported. Reason: Not implemented for system-versioned operations. Try ALGORITHM=COPY
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body);
+affected rows: 6
+info: Records: 6 Duplicates: 0 Warnings: 0
- ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
+ ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
@@ -162,7 +168,7 @@
diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers_trx.rdiff b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers_trx.rdiff
index d46275ff..515c1c22 100644
--- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers_trx.rdiff
+++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl,vers_trx.rdiff
@@ -1,5 +1,5 @@
---- innodb-fts-ddl.result
-+++ innodb-fts-ddl.reject
+--- innodb-fts-ddl.result 2023-11-23 13:40:18.932463721 +0100
++++ innodb-fts-ddl,vers_trx.result 2024-04-09 11:45:57.097484346 +0200
@@ -11,8 +11,10 @@
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
@@ -27,14 +27,14 @@
@@ -76,8 +80,10 @@
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
- CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
+ ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE;
-ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
-ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
-+ERROR 0A000: LOCK=NONE is not supported. Reason: Not implemented for system-versioned operations. Try LOCK=SHARED
++ERROR 0A000: ALGORITHM=NOCOPY is not supported. Reason: Not implemented for system-versioned operations. Try ALGORITHM=COPY
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body);
+affected rows: 6
+info: Records: 6 Duplicates: 0 Warnings: 0
- ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
+ ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
@@ -162,7 +168,7 @@
diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
index 15f5b295..05b9fa40 100644
--- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
+++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
@@ -75,10 +75,10 @@ INSERT INTO fts_test (title,body) VALUES
('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 FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
+ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
-ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
+ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
SELECT * FROM fts_test WHERE MATCH (title, body)
diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
index 52cbede7..6d7a5647 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
@@ -466,7 +466,8 @@ ROLLBACK;
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
s1 s2
DROP TABLE t2 , t1;
-set global innodb_file_per_table=1;
+SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
+SET GLOBAL innodb_read_only_compressed=OFF;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
@@ -649,7 +650,7 @@ id a b
6 MYSQL SECURITY when configured properly, mysql ...
7 TEST QUERY EXPANSION for database ...
DROP TABLE t1;
-SET GLOBAL innodb_file_per_table=1;
+SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result b/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result
index a53fca51..32bb1c80 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result
@@ -1,4 +1,3 @@
-drop table if exists t1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
@@ -133,7 +132,6 @@ AGAINST ('"xyz blob"@3' IN BOOLEAN MODE);
count(*)
2
DROP TABLE t1;
-set global innodb_file_per_table=1;
set names utf8;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
@@ -215,4 +213,3 @@ AGAINST ('"very blob"@3' IN BOOLEAN MODE);
count(*)
1
DROP TABLE t1;
-SET GLOBAL innodb_file_per_table=1;
diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result b/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result
index fc6a1d1a..f372652c 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result
@@ -534,8 +534,8 @@ INSERT INTO t1 (a,b) VALUES
('Sævör grét', 'áðan því úlpan var ónýt');
connect con2,localhost,root,,;
SET NAMES UTF8;
-select @@session.tx_isolation;
-@@session.tx_isolation
+select @@session.transaction_isolation;
+@@session.transaction_isolation
REPEATABLE-READ
CREATE FULLTEXT INDEX idx on t1 (a,b);
INSERT INTO t1 (a,b) VALUES
@@ -649,8 +649,8 @@ INSERT INTO t1 (a,b) VALUES
('Я могу есть стекло', 'оно мне Mне вредит');
connect con2,localhost,root,,;
SET NAMES UTF8;
-select @@session.tx_isolation;
-@@session.tx_isolation
+select @@session.transaction_isolation;
+@@session.transaction_isolation
REPEATABLE-READ
CREATE FULLTEXT INDEX idx on t1 (a,b);
INSERT INTO t1 (a,b) VALUES
@@ -794,8 +794,8 @@ INSERT INTO t1 (a,b) VALUES
('Sævör grét', 'áðan því úlpan var ónýt');
connect con2,localhost,root,,;
SET NAMES UTF8;
-select @@session.tx_isolation;
-@@session.tx_isolation
+select @@session.transaction_isolation;
+@@session.transaction_isolation
REPEATABLE-READ
CREATE FULLTEXT INDEX idx on t1 (a,b);
INSERT INTO t1 (a,b) VALUES
@@ -914,8 +914,8 @@ INSERT INTO t1 (a,b) VALUES
('Я могу есть стекло', 'оно мне Mне вредит');
connect con2,localhost,root,,;
SET NAMES UTF8;
-select @@session.tx_isolation;
-@@session.tx_isolation
+select @@session.transaction_isolation;
+@@session.transaction_isolation
REPEATABLE-READ
CREATE FULLTEXT INDEX idx on t1 (a,b);
INSERT INTO t1 (a,b) VALUES
diff --git a/mysql-test/suite/innodb_fts/r/misc_1.result b/mysql-test/suite/innodb_fts/r/misc_1.result
index 69a234fb..1087f25b 100644
--- a/mysql-test/suite/innodb_fts/r/misc_1.result
+++ b/mysql-test/suite/innodb_fts/r/misc_1.result
@@ -466,7 +466,6 @@ ROLLBACK;
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
s1 s2
DROP TABLE t2 , t1;
-set global innodb_file_per_table=1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
@@ -649,7 +648,6 @@ id a b
6 MYSQL SECURITY when configured properly, mysql ...
7 TEST QUERY EXPANSION for database ...
DROP TABLE t1;
-SET GLOBAL innodb_file_per_table=1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
diff --git a/mysql-test/suite/innodb_fts/r/opt.result b/mysql-test/suite/innodb_fts/r/opt.result
index 57375495..c050a4da 100644
--- a/mysql-test/suite/innodb_fts/r/opt.result
+++ b/mysql-test/suite/innodb_fts/r/opt.result
@@ -1372,7 +1372,7 @@ MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
a.FTS_DOC_ID = b.FTS_DOC_ID;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a fulltext PRIMARY,FTS_DOC_ID_INDEX,ft_idx ft_idx 0 1 Using where
-1 SIMPLE b eq_ref PRIMARY,FTS_DOC_ID_INDEX,ft_idx PRIMARY 8 test.a.FTS_DOC_ID 1 Using where
+1 SIMPLE b fulltext PRIMARY,FTS_DOC_ID_INDEX,ft_idx ft_idx 0 1 Using where
SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
FROM t1 a, t1 b
@@ -1558,6 +1558,7 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
+ "cost": 0.00009287,
"nested_loop": [
{
"table": {
@@ -1567,7 +1568,9 @@ EXPLAIN
"key": "f1",
"key_length": "0",
"used_key_parts": ["f1"],
+ "loops": 1,
"rows": 1,
+ "cost": 0.00009287,
"filtered": 100,
"attached_condition": "1.238585e+308 <= (match t1.f1 against ('1' in boolean mode))"
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-10-08 02:52:36 +0000