Page 1 of 1
Query not returning results on certain words
Posted: Thu Feb 02, 2006 6:53 am
by malcolmboston
Hi guys,
I have created a parser for cv’s and am having problems retrieving records when searching using the keywords ‘c#’, or ‘c++’. I use a boolean full text search and if a cv contains the keyword it should be retrieved.
The query is…
Code: Select all
SELECT cv.id, cv.candidate_id, MATCH (cv.cv_text) AGAINST ('C++') AS score FROM db.cv_test cv, fb.candidates_test c WHERE cv.candidate_id = c.id AND c.hide_from_search = 'N' AND c.active = 'Y' AND MATCH (cv.cv_text) AGAINST (CONVERT( _utf8 '+C++' USING latin1 ) IN BOOLEAN MODE) order by score desc
It is not a minimium letters problem as that is set to 2
But nothing is returned.
Any idea's / suggestions would be appreciated
Mal
Posted: Fri Feb 03, 2006 3:28 am
by malcolmboston
:bump:
feyd and burrito have both personally looked into this and cannot find a solution.
Anyone else got any fresh ideas?
Posted: Fri Feb 03, 2006 3:31 am
by Weirdan
feyd and burrito have both personally looked into this and cannot find a solution.
nor I am able to...
Posted: Fri Feb 03, 2006 4:52 am
by raghavan20
in C++ is ++ considered to be a keyword. If it assumes as keyword, then the two plus expects something after them have to be matched with records which may not yield results...just wild guess...
Posted: Fri Feb 03, 2006 5:07 am
by malcolmboston
is there some sort of escape character / method i can do to stop this?
Posted: Fri Feb 03, 2006 6:50 am
by raghavan20
I think we are right at that point..
it assumes +c++ as...
+ - compulsory...looks for compulsory word which is just 'c'
the other two plus has no character after it so they will have no effect anyway.
I have not found the fix yet.
example: A few observations..
Code: Select all
mysql> select * from test4;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | hello |
| 3 | sfljc++ |
| 4 | c# |
| 5 | google |
+----+---------+
5 rows in set (0.00 sec)
mysql> select * from test4 where match(name) against('+google++' in boolean mode); //google word found
+----+--------+
| id | name |
+----+--------+
| 5 | google |
+----+--------+
1 row in set (0.00 sec)
mysql> select * from test4 where match(name) against('+googl++' in boolean mode); //google word not found
Empty set (0.00 sec)
mysql> select * from test4 where match(name) against('+(google++)' in boolean mode); // no use of brackets
+----+--------+
| id | name |
+----+--------+
| 5 | google |
+----+--------+
1 row in set (0.00 sec)
mysql> select * from test4 where match(name) against('+(googl\+\+)');//no use of back slashes
Empty set (0.00 sec)
mysql> select * from test4 where match(name) against('(google++)'); // no use of non-boolean mode
+----+--------+
| id | name |
+----+--------+
| 5 | google |
+----+--------+
1 row in set (0.00 sec)
Posted: Tue Feb 07, 2006 8:10 am
by malcolmboston
follow-up,
official word from MySQL.com: it cannot be done using current syntax
looks like we'll have to employ a 'semi-hack' to allow this
i thought it would be worth noting for the future
Posted: Tue Feb 07, 2006 8:45 am
by feyd
good to know. Thanks.

Did they give an ETA on a fix?
Posted: Tue Feb 07, 2006 8:48 am
by malcolmboston
basically, no, they said that they were aware of the problem and it was planned in a future version, however, lets think about this..
It would mean quite a dramatic change in SQL syntax so it aint gonna be happening anytime soon
Posted: Tue Feb 07, 2006 8:51 am
by feyd
it shouldn't have to be a dramatic change. Simple escaping should be possible with little to no fuss I'd think.
Posted: Tue Feb 07, 2006 8:54 am
by malcolmboston
well the way i see it is that this problem, as was said by a MySQL developer with MySQL, is brought up all the time at official conferences and is a very well known bug (not to me or anyone i know though!) its obviously been like this for a while as that syntax has been around for ages, it still hasnt been fixed, there muct be a good reason for this as FULLTEXT searches are obviously going to need to be able to do things like this
my $0.02