Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => PostgreSQL and SQLite Support => Topic started by: habakuk on September 25, 2009, 11:17:34 AM

Title: Weird search results
Post by: habakuk on September 25, 2009, 11:17:34 AM
Hi all.

Using smf 2.0rc1 running on MacOSX, Apache and PostgreSQL, with just one mod (sticky topics) and tried with the default theme too, I do get weird results using the search function.

First of all, the sort order is totally nuts:
Using the advanced search form, I search for "Rating" (match all words), by user *, Most relevant results first (no other option), check all boards. I get the following sort order:
150%, 90%, 120%, 390%, 240%, 150%, 60%, 60%, 40%, 210%, 270% etc... Totally erratic...

Using "Most recent topic first" sorts by Date posted DESCENDING! Which is 180° the wrong way.

Funny enough, the relevance with everything as in the case before, the relevance reading is now:

1560%, 32%, 45%, 60%

Ok, now... the 1560% relevant posting... doesn't contain the word "Rating". Not in any part of the message, not even in a meta element. It's simply not there in that posting. Yet, it is 1560% relevant.  8) :D


I tried deleting and rebulding the search index. THat didn't do anything. It just showed up another bug: the progress displayed on that page jumped from 2 to 135%, back to 7%, 45%, 0.5% and so forth... again: very erratically.

What works is that in posts that contain "Rating", the word is neatly highlighted.

Searching for subject only works too, although the sort order still is nuts.

any hint on that? Yet another postgres specific bug in teh implementation?

cheers
®



Title: Re: Weird search results
Post by: tyty1234 on September 30, 2009, 03:31:14 AM
I'm no master at PostgreSQL, but can you please tell us what errors you are getting in the forum error log?
Title: Re: Weird search results
Post by: habakuk on September 30, 2009, 03:46:38 AM
Thanks for coming back. I feel less lonesome now. :)

There seems to be a problem with creating temp tables:

ERROR:  table "psf_tmp_log_search_topics" does not exist
STATEMENT: 
                     DROP TABLE  psf_tmp_log_search_topics
ERROR:  syntax error at or near "unsigned" at character 79
STATEMENT: 
                     CREATE TEMPORARY TABLE psf_tmp_log_search_topics (
                        id_topic int unsigned NOT NULL default '0',
                        PRIMARY KEY (id_topic)
                     )
ERROR:  table "psf_tmp_log_search_messages" does not exist
STATEMENT: 
                     DROP TABLE  psf_tmp_log_search_messages
ERROR:  syntax error at or near "(" at character 78
STATEMENT: 
                     CREATE TEMPORARY TABLE psf_tmp_log_search_messages (
                        id_msg int(10) unsigned NOT NULL default '0',
                        PRIMARY KEY (id_msg)


cheers
®
Title: Re: Weird search results
Post by: Oldiesmann on October 02, 2009, 01:03:28 PM
The problem creating that temp table is that PostgreSQL doesn't support unsigned integers (sadly). Will go ahead and fix that one.

Not sure what could be causing the weird relevance or why the completion percentage when creating the index is so strange. I've seen similar issues with the relevance from time to time in MySQL, but it seems to be a random thing that we can't pin down to a specific issue.