MySQL Bugs: #93241: Query against full text index with ORDER BY silently fails

Bug #93241	Query against full text index with ORDER BY silently fails
Submitted:	18 Nov 2018 5:48	Modified:	12 Feb 2019 0:39
Reporter:	Jonathan Balinski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.13	OS:	Windows (Windows 10 -1809)
Assigned to:		CPU Architecture:	x86 (Core i7-6700)

Description:
Using the following innodb full text settings:
innodb_ft_min_token_size = 1
innodb_enable_stopword = off

Running a query against a full text index and then ordering the results by a column silently fails and logs the following to the error log:

2018-11-18T05:41:01.653465Z 52 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `everywhen`.`person`. Last data field length 8 bytes, key ptr now exceeds key end by 4 bytes. Key value in the MySQL format:
 len 4; hex 32000000; asc 2   ;

How to repeat:
*Use the innodb settings outlined in the problem description
*Create a table with a full text index on column.
*Populate the table with the column with varying lengths of text from 1 to whatever you want.

Run a similarly designed query:
SELECT persionid, name FROM person 
WHERE MATCH(lookup_text) AGAINST ('jo' IN BOOLEAN MODE)
ORDER BY personid

Note that the query successfully executes without the ORDER BY clause but fails to return any results as soon as the ORDER BY clause is added and an error message is logged to the error log.

Updating synopsis

While I can confirm some problem does exist, but I cannot produce a nice short testcase yet..

My test table was:

mysql> show create table t0000\G
*************************** 1. row ***************************
       Table: t0000
Create Table: CREATE TABLE `t0000` (
  `c0000` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  FULLTEXT KEY `i0000` (`c0000`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

And the query structure was:
push_table select field_ident , field_ident from table_ident where match( fts_index ) against ( data_string in boolean mode) order by field_ident pop_table ;

Error log...
%%%%%2018-11-18T09:49:56.597451Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%2018-11-18T09:49:57.109016Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b7f6d; asc    + m;
%2018-11-18T09:49:57.523569Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%2018-11-18T09:49:58.696217Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%2018-11-18T09:49:59.975380Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%2018-11-18T09:50:00.783483Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%2018-11-18T09:50:01.991136Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%2018-11-18T09:50:02.713728Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%2018-11-18T09:50:03.363810Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%%%%%%%%%2018-11-18T09:50:04.085902Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%2018-11-18T09:50:04.600467Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%2018-11-18T09:50:05.585092Z 16 [Warning] [MY-012853] [InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t0000`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
 len 6; hex 0000012b83c5; asc    +  ;

none-simplified-testcase....

Attachment: t.sql (application/octet-stream, text), 99.68 KiB.

Interestingly, debug build asserts on this testcase..

Version: '8.0.15-tr-debug'  socket: ''  port: 3306  Built on 2018/11/16.
[InnoDB] Using a partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table `test`.`t`. Last data field length 8 bytes, key ptr now exceeds key end by 2 bytes. Key value in the MySQL format:
[ERROR] [MY-013183] [InnoDB] Assertion failure: row0sel.cc:2476:0 thread 5272

mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.cc:372]
  ucrtbased.dll!raise()
  ucrtbased.dll!abort()
mysqld-debug.exe!ut_dbg_assertion_failed()[ut0dbg.cc:92]
mysqld-debug.exe!row_sel_convert_mysql_key_to_innobase()[row0sel.cc:2476]
mysqld-debug.exe!ha_innobase::index_read()[ha_innodb.cc:9151]
mysqld-debug.exe!ha_innobase::rnd_pos()[ha_innodb.cc:9635]
mysqld-debug.exe!handler::ha_rnd_pos()[handler.cc:2855]
mysqld-debug.exe!SortFileIndirectIterator::UncachedRead()[sorting_iterator.cc:163]
mysqld-debug.exe!SortFileIndirectIterator::Read()[sorting_iterator.cc:157]
mysqld-debug.exe!SortingIterator::Read()[sorting_iterator.h:81]
mysqld-debug.exe!sub_select()[sql_executor.cc:1527]
mysqld-debug.exe!do_select()[sql_executor.cc:1162]
mysqld-debug.exe!JOIN::exec()[sql_executor.cc:282]
mysqld-debug.exe!Sql_cmd_dml::execute_inner()[sql_select.cc:749]
mysqld-debug.exe!Sql_cmd_dml::execute()[sql_select.cc:590]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:4351]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5150]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1720]
mysqld-debug.exe!do_command()[sql_parse.cc:1263]
mysqld-debug.exe!handle_connection()[connection_handler_per_thread.cc:302]
mysqld-debug.exe!pfs_spawn_thread()[pfs.cc:2838]
mysqld-debug.exe!win_thread_start()[my_thread.cc:51]

Query (25b8bc68): select `c0000` , `c0000` from t where match( c0000 ) against ( '47*/$*/?3D*36** e[_>$* -f _#1 *5C* & *FA$ *={ b*Aa>:' in boolean mode) order by `c0000`

Documented fix in the MySQL 8.0.16 changelog, as follows:

    A query using an ORDER BY clause failed silently when the table
    being queried had an implicit full-text index and and the sort
    buffer was of insufficient size to contain the sorted keys.

Closed.