Description:
An incorrect result of the window function when use fulltext search.
Query like a below, returns an incorrect result:
select
max(field) over(),
min(field) over(),
sum(field) over()
from
(select
MATCH (..) AGAINST ('..') as field
from table) a
more details:https://stackoverflow.com/questions/46845850/mysql-8-window-functions-full-text-searching
How to repeat:
Create table and fulltext index:
CREATE TABLE `title` ( `id` smallint(4) unsigned NOT NULL PRIMARY KEY, `name` text COLLATE utf8_unicode_ci, FULLTEXT idx (name) WITH PARSER ngram ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Insert some data:
insert into `title` values(14,"I'm flying in for the game (one night in Niagara Falls, NY and one night in Buffalo then back home).");
insert into `title` values(23,"I've never been to the area.");
insert into `title` values(43,"Where and what must I eat (Canadian side of Niagara, American side and Buffalo)?");
insert into `title` values(125,"Don't really have much planned other than the Falls and the game.");
Execute to see the error:
select
id,
max(scope) over(),
min(scope) over(),
sum(scope) over()
from
(
select id, round(MATCH (name) AGAINST ('other than the'),2) scope
from title
) a;
The result will be (INCORRECT):
id | max | min | sum
------------------------
14 | 1.15 | 1.15 | 4.60
23 | 1.15 | 1.15 | 4.60
43 | 1.15 | 1.15 | 4.60
125| 1.15 | 1.15 | 4.60
But query:
select
max(scope),
min(scope),
sum(scope)
from
(
select id, round(MATCH (name) AGAINST ('other than the'),2) scope
from title
) a;
returns (CORRECT):
max | min | sum
----------------
1.15 | 0.12 | 1.96
Suggested fix:
i don't know how fix it.
Description: An incorrect result of the window function when use fulltext search. Query like a below, returns an incorrect result: select max(field) over(), min(field) over(), sum(field) over() from (select MATCH (..) AGAINST ('..') as field from table) a more details:https://stackoverflow.com/questions/46845850/mysql-8-window-functions-full-text-searching How to repeat: Create table and fulltext index: CREATE TABLE `title` ( `id` smallint(4) unsigned NOT NULL PRIMARY KEY, `name` text COLLATE utf8_unicode_ci, FULLTEXT idx (name) WITH PARSER ngram ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Insert some data: insert into `title` values(14,"I'm flying in for the game (one night in Niagara Falls, NY and one night in Buffalo then back home)."); insert into `title` values(23,"I've never been to the area."); insert into `title` values(43,"Where and what must I eat (Canadian side of Niagara, American side and Buffalo)?"); insert into `title` values(125,"Don't really have much planned other than the Falls and the game."); Execute to see the error: select id, max(scope) over(), min(scope) over(), sum(scope) over() from ( select id, round(MATCH (name) AGAINST ('other than the'),2) scope from title ) a; The result will be (INCORRECT): id | max | min | sum ------------------------ 14 | 1.15 | 1.15 | 4.60 23 | 1.15 | 1.15 | 4.60 43 | 1.15 | 1.15 | 4.60 125| 1.15 | 1.15 | 4.60 But query: select max(scope), min(scope), sum(scope) from ( select id, round(MATCH (name) AGAINST ('other than the'),2) scope from title ) a; returns (CORRECT): max | min | sum ---------------- 1.15 | 0.12 | 1.96 Suggested fix: i don't know how fix it.