SQL Server \ CONTAINS FORMSOF INFLECTIONAL Phrase \ Not Working as Expected

Question

I am trying to use the contains operator with FORMOF INFLECTIONAL, but I'm not getting what I think is the expected result.

Here is the query. This query is not returning the record Id 1.

SELECT * 
  FROM Message
 WHERE CONTAINS(*,'FORMSOF(INFLECTIONAL,"01 Hello this is")')

Database objects to reproduce my scenario.

CREATE DATABASE HelpSo
GO

USE HelpSo
GO

CREATE TABLE Message
(
    Id INT NOT NULL IDENTITY(1,1),
    Text NVARCHAR(MAX)
    CONSTRAINT PK_Message PRIMARY KEY (Id) 
);
GO

CREATE FULLTEXT CATALOG FtcHelpSo AS DEFAULT
GO

CREATE FULLTEXT INDEX ON Message(Text Language 1033  Statistical_Semantics  ) KEY INDEX PK_Message with change_tracking auto
GO


INSERT INTO Message (Text) VALUES ('01 Hello this is a test 20180522.');
INSERT INTO Message (Text) VALUES ('02 Hello this is a test 20180522.');
GO

SELECT * FROM Message;

SELECT * 
  FROM Message
 WHERE FREETEXT(*,'01 Hello this is a test 20180522.') --Returns 2 records. Expected.

SELECT * 
  FROM Message
 WHERE CONTAINS(*,'"01 Hello this is a test 20180522."') --Return 1 record. Expected.

SELECT * 
  FROM Message
 WHERE CONTAINS(*,'FORMSOF(INFLECTIONAL,"01 Hello this")') --Return 1 record. Expected.


-- Here is my problem
SELECT * 
  FROM Message
 WHERE CONTAINS(*,'FORMSOF(INFLECTIONAL,"01 Hello this is")') --Not Expected Result, I think that should return record Id 1.

What I am missing? Why the last query is not returning the record Id 1?

I tried to troubleshotting this issue with the following statements, but I was not able to figure out why i'm getting this result.

SELECT * FROM sys.dm_fts_parser ('FORMSOF(INFLECTIONAL,"01 Hello this is")', 1033, 0, 0)

SELECT * 
  FROM sys.dm_fts_index_keywords_by_document (DB_ID('HelpSo'), OBJECT_ID('Message'))  
 WHERE document_id = 1

There is any other tool in the SQL Server that help us solve, or understand better, this kind of issue?

SQL Server Version

SELECT @@VERSION
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 17134: ) (Hypervisor) 

Answer 1

I was able to work around this issue by disabling the STOPLIST

ALTER FULLTEXT INDEX ON MESSAGE SET STOPLIST = OFF;

Now I am getting the expected result, in both queries CONTAINS without FORMSOF INFLECTIONAL and CONTAINS FORMSOF INFLECTIONAL.

SELECT * 
  FROM Message
 WHERE CONTAINS(*,'"01 Hello this is a test 20180522."') --Return 1 record. Expected.

SELECT * 
  FROM Message
 WHERE CONTAINS(*,'FORMSOF(INFLECTIONAL,"01 Hello this is")') --Expected Result.

If I change the stop to SYSTEM, the unexpected result is back.

ALTER FULLTEXT INDEX ON MESSAGE SET STOPLIST = SYSTEM;

But I still not understanding why this scenario happened, and what is the proper way to troubleshooting this scenario and connect the dots between the query result and the dm_fts_parser result.

References: Answer - MSDN SQL Server Forum - A problem with using full text search with wildcard query