#1 NEAR does not work in reverse order

[Steve Halasz]

"word1 NEAR word2" will only work if the words in question are in the same order in the database. "word1 word2" will be found, but not "word2 word1".

[Loic Dachary]

Is it with mifluz/postgres/mysql or simple string search ?
Each implementation is different and, yes it does not
work with mysql although it\'s supposed to work with
string and mifluz.

[Steve Halasz]

It is with the MySQL search:

$query = Text::Query->new();
$test=\"field1,field2,field3: ( $search )\";
my $q=new Text::Query($test,
-parse => \'Text::Query::ParseAdvanced\',
-solve => \'Text::Query::SolveSQL\',
-build => \'Text::Query::BuildSQLMySQL\',
-select => \'SELECT * FROM table WHERE __WHERE__ ORDER BY field4 DESC,field2\');

die \"bad query expression\" if not defined $q;

my $dbh = DBI->connect(\"DBI:mysql:database=db;host=host\", \"user\", \"password\");

my @rows = $q->match($dbh);

[Loic Dachary]

Well, it does not work with MySQL, that true. The regular
expression implementation of MySQL does not allow to do
this easily. It would require to generate all possible
regular expressions which is a big performance bottleneck.

[Loic Dachary]

This cannot be fixed due to limitations of MySQL.

[Steve Halasz]

Here is a patch against BuildSQLMySQL.pm that makes NEAR work in reverse for me. It does seem to slow the query down considerably(takes about twice as long?). Is this the kind of performance problem you were referring to, or does this solve the problem? The kind of performance this gives is acceptable to me, and I prefer it being slow to not working correctly. I don't know what the big5 encoding is, and this does nothing to change how that is handled.

--- BuildSQLMySQL.pm Thu Jul 01 13:32:12 1999
+++ BuildSQLskh.pm Mon May 01 09:45:34 2000
@@ -43,7 +43,7 @@
} else {
my($max) = $self->{parseopts}{-near};
my($op) = "([[:space:]]+[[:alnum:]]+){0,$max}[[:space:]]+";
- $t = "$$l[2]$op$$r[2]";
+ $t = "(($$l[2]$op$$r[2])|($$r[2]$op$$l[2]))";
}
return [ 'literal', $$l[1], $t ];
}

[Loic Dachary]

Thanks for the patch. I've applied it and it's available on
the CVS tree, with credits in the ChangeLog. This will slow
down all near operations without distinctions but the query is already quite slow anyway. We will see if people complain.