TransWikia.com

Incorrect results when combining full-text search and regular comparison

Database Administrators Asked by Hooman Valibeigi on November 5, 2021

create table "Foo"
(
"id" integer identity (1000, 1) not null,
"name" nvarchar(100) not null
)
alter table "Foo" add constraint "Foo_PK" primary key ("id");
create fulltext index on "Foo" ("name" language 0x0) key index "Foo_PK" on "catalog" with stoplist = off;

The following query returns no rows

select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1082;

While there are 26 rows matched by contains("name", ' "پ*" ') and 87 matched by "id" > 1082 individually. The following two queries have 23 rows in common, oddly, their intersection returns no rows.

select * from "Foo" where contains("name", ' "پ*" ');
select * from "Foo" where "id" > 1082;

And more strangely, the following query returns exactly 23 rows. All "id"s are above 1082.

select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1081;

Someone please explain what is going on in SQL Server.

2 Answers

I could fix it simply by dropping and creating the index again. Still don't know why it happened and how to prevent it from happening in the future.

Answered by Hooman Valibeigi on November 5, 2021

I think your primary problem is that you are using non-Unicode string delimiters for Unicode data.

Compare:

SELECT  ' "پ*" ';
SELECT N' "پ*" ';
-------^ this N is important

Answered by Aaron Bertrand on November 5, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP