Full-text bug or idiot coder?

  • Hi,

    I have the following SQL statement to return images that contain the word winter but NOT snow:

    
    
    SELECT as RefNo, RANK, Description, Property, County, Format, Photographer, Artist, Classification, FiledUnder, Agency, Credit
    FROM CONTAINSTABLE(Images, Description, 'WINTER AND NOT SNOW') S LEFT OUTER JOIN Images I ON S.[Key] = I.RefNo
    ORDER BY Rank DESC

    The above seems to work fine, lots of winter related results without any snow!. However, if I want to do the same search across all fields using:

    
    
    SELECT as RefNo, RANK, Description, Property, County, Format, Photographer, Artist, Classification, FiledUnder, Agency, Credit
    FROM CONTAINSTABLE(Images, *, 'WINTER AND NOT SNOW') S LEFT OUTER JOIN Images I ON S.[Key] = I.RefNo
    ORDER BY Rank DESC

    For some reason, with the above I end up with results containing the word SNOW. Why is this??????

    Thanks for your help.

    Tom

  • Hello.

    I believe CONTAINSTABLE is combining the results of individual searches on all of the columns that have been registered for searching. So, if Record 1 in 'OtherColumn' contains Winter and not Snow but does contain both Winter and Snow in 'Description', it will still be returned since the search in 'OtherColumn' was successful.



    Everett Wilson
    ewilson10@yahoo.com

  • So is there any way around that behaviour? Clearly it's not what's required so why does it work like this?

    Thanks

    Tom

  • How many of the columns within your table have full text indexes built on them?

  • All of them. 🙂

    It's a conversion of an existing flat file system where all fields must be searchable.

    Thanks

  • One solution would be to build a distinct list of Key values in a subquery using a union statement for each column you need to search. Then join this subquery onto your original table (and any others you require) and return the full result set.

    SELECT KEY as RefNo, RANK, Description, .....

    FROM (SELECT as RefNo

    FROM Images

    WHERE CONTAINS(column1, 'WINTER AND NOT SNOW')

    UNION

    SELECT as RefNo

    FROM Images

    WHERE CONTAINS(column2, 'WINTER AND NOT SNOW')

    UNION SELECT as RefNo

    FROM Images

    WHERE CONTAINS(column3, 'WINTER AND NOT SNOW')

    UNION ........) s LEFT OUTER JOIN Images i

    ON s. = i.RefNo

    ORDER BY Rank Desc

  • Will this not be incredibly slow? Given that there are approx 20,000 records? And surely the full text search system will run considerably faster using * than separate queries for each field?

    That being said, if it doesn't work, it doesn't work so slow is better than not at all I guess.

  • Maybe an alternative solution could be to construct a composite column, containing a concatenation of all of your fields in the table.

    If you only include that column for the full text index, you shouldn't get erroneous results.

    You'll be eating up a lot of disk space though, if there are a lot of big columns.

  • Yikes - don't like the sound of that solution 🙂

    Unfortunately it is a requirement, at times, to only search specific columns. The query is constructed dynamically :S

    Thanks

    Tom

  • I don't have a lot experience using the * with the Full-Text Search. But would performing two * searches be faster than what has been suggested? For example:

    SELECT as RefNo, RANK, Description, Property, County, Format, Photographer, Artist, Classification, FiledUnder, Agency, Credit

    FROM CONTAINSTABLE(Images, *, 'WINTER AND NOT SNOW') S

    LEFT OUTER JOIN Images I

    ON S.[Key] = I.RefNo

    LEFT OUTER JOIN

    (SELECT as RefNo

    FROM CONTAINSTABLE(Images, *, 'SNOW') AS SnowValues

    LEFT OUTER JOIN Images I ON SnowValues.[Key] = I.RefNo)

    AS NotIncluded

    ON I.RefNo = NotIncluded.RefNo

    WHERE NotIncluded.RefNo Is Null

    ORDER BY Rank DESC



    Everett Wilson
    ewilson10@yahoo.com

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply