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

[18 Nov 2018 5:48] Jonathan Balinski
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.
[18 Nov 2018 5:54] Jonathan Balinski
Updating synopsis
[18 Nov 2018 9:49] Shane Bester
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 ;
[18 Nov 2018 9:50] Shane Bester
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    +  ;
[18 Nov 2018 10:02] Shane Bester
none-simplified-testcase....

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

[18 Nov 2018 10:07] Shane Bester
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`