I can reproduce this
Initial tests
Assuming a GO..SELECT..GO pattern and some blank lines
Before After Rows
y n 6
y y 4
n y 4
n n 4
If I change the order of events...
Before After Rows
y n 6
y y 4
n n 4
n y 4
And again
Before After Rows
y y 4
n n 4
n y 4
y n 6
Before After Rows
y y 4
n n 4
y n 6
n y 4
Update after @MartinSmith's answer
If I remove the WHERE, then I get the same results and the same rank value for four queries regardless of blank lines. With the WHERE I get the same results as above.
Martin showed that rank is arbitrary, but his same link also states
This explains why the same query can return different rank results over time as full-text indexed data is added, modified, and deleted, and as the smaller indexes are merged.
In this case, data is static and the query is identical except for blank lines.
Changing the query text
- With
WHERE KEY_TBL.RANK > 0 = identical row count and rank values
- No WHERE clause = identical row count and rank values
- With the CONTAINSTABLE wrapped in
TOP 200000..ORDER BY RANK = identical row count and rank values, regardless of WHERE
With original WHERE KEY_TBL.RANK > 2
- Change the case of the first
FT_TBL alias to be different each time (fT_TBL, Ft_TBL, FT_tBL, FT_TbL) which means a different query plan = identical row count and rank values
- Changing all
FT_TBL aliases to FT_TBl = identical row count and rank values
- Changing all
FT_TBl aliases back to FT_TBL = different rank values based on blank lines -> different row counts
For the last query (different rank values) I get 4 identical execution plans. The filter is applied to the TVF before the JOIN.
- Adding a blank line after the ON before the WHERE (keeping leading and trailing blank lines) = identical row count and rank values
- Adding a blank line before FROM = identical row count and rank values
- Adding 3 spaces after JOIN = identical row count and rank values
Adding DBCC DROPCLEANBUFFERS
- Adding DBCC DROPCLEANBUFFERS at the top 4-4-6-4 rows
- Same if I add DBCC DROPCLEANBUFFERS before each query
- Adding an extra blank lines (with the DBCC) before or after the first query gives more rows (6-4-6-4). Found this by mistake, but leads back to the first observation that blank lines affect results
After Paul's comment
Note the trailing and leading blank lines.
This fails reliably. The second query gives 4 rows
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
This gives the same every time after I shift+TAB the entire query to remove indents
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)'
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO
Still looking
Ok, time to search MS Connect. New one: https://connect.microsoft.com/SQLServer/feedback/details/788691/blank-lines-before-and-after-full-text-query-change-results
