a BUG about predicate CONTAINS on different OSs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

a BUG about predicate CONTAINS on different OSs

Author

Topic

dursaliye
Starting Member

22 Posts

Posted - 2005-05-24 : 18:04:18

Hello,

I checked the SQL Server Service Pack 4 enhancements but I couldn't find a bug I discovered. I used Free-Text Search predicate CONTAINS on Windows 2000 and Windows XP and the result set I get on Windows XP is less than the ones on Windows 2000 and I consider it as bug. SQL Server 2000 on Windows 2000 and on Windows XP are configured the same with default options even the PC is the same to test programs on different OSs I had two HDD. What I want is your doing this test and writing your results.

----------------------------

USE pubs
GO

CREATE TABLE tempAddress
(ID INT IDENTITY PRIMARY KEY,
address varchar(75)
)
GO
INSERT INTO tempAddress(address) VALUES('T. Avenue Apple Street')
INSERT INTO tempAddress(address) VALUES('T. Av. Apple St.')
INSERT INTO tempAddress(address) VALUES('T. Ave. Apple S.')
INSERT INTO tempAddress(address) VALUES('T. Avenue Apple S)
INSERT INTO tempAddress(address) VALUES('Thirteenth Av. Apple St.')
INSERT INTO tempAddress(address) VALUES('Thirteenth Ave. Apple S.')
INSERT INTO tempAddress(address) VALUES('Thirteenth Avenue Apple S')
GO
----------------------------------------------

1. After executing those codes in Query Analyzer (pardon me gals and guys but I want anyone to do this test and so I am writing to describe what to do in case someone doesn't know it ).
2. Start "Microsoft Search" service by Service manager.
3. Open Enterprise manager and find "tempAddress" table in pubs database. Right-click on "tempAddress"-> Full-Text Index Table -> Define Full-Text Indexing on table.
4. Wizard will open. Next-> Next -> Select address column in the dialog "Select Table Columns". ->Next ->Next ->Next -> Finish
5. In Enterprise Manager right-click on "tempAddress"-> Full-Text Index Table -> Start Full Population
6. Execute below
SELECT address FROM tempAddress WHERE CONTAINS(address, ' ("Thirteenth" OR "T*") AND ("Avenue" OR "Av*") AND ("Apple") AND ("Street" OR FORMSOF (INFLECTIONAL, Street) OR "S*")')
On Windows XP you'll get this result
************************************
Result
.....................................
Thirteenth Ave. Apple S.
Thirteenth Av. Apple St.
T. Ave. Apple S.
T. Av. Apple St.
T. Avenue Apple Street
************************************

As you see, it didn't give us the rows below
Thirteenth Ave. Apple S
T. Ave. Apple S
######But if you do the same on Windows 2000, you'll get all the column listed.
Result
.....................................
Thirteenth Ave. Apple S.
Thirteenth Av. Apple St.
Thirteenth Ave. Apple S
T. Ave. Apple S.
T. Av. Apple St.
T. Avenue Apple Street
T. Ave. Apple S

************************************

I thought this as a bug. I don't know whether OS or SQL Server is the cause of it but I'm sure this is a bug.
After release of the 4th service pack and not seeing this in the advancements list, I posted this to Rob Volk and he wanted me to post it here. After a hesitation, I posted it. If we can prove this, they can approve this as a bug.
Maybe the address datas and abbreviations seemed so stupid but I don't know the address syntax in your countries so well. Here people types different abbreviations and we had had a database of those datas and our boss had wanted us to repair it. I had found this and we had solved it on the programming side. But the problem still lays there whether on OS or in SQL Server 2000.

Yours respectfully,

Dursaliye YILDIZ
dursaliye@hotmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources