Query not returning results on certain words

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Query not returning results on certain words

Post 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
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

:bump:

feyd and burrito have both personally looked into this and cannot find a solution.

Anyone else got any fresh ideas?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

feyd and burrito have both personally looked into this and cannot find a solution.
nor I am able to...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

is there some sort of escape character / method i can do to stop this?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

good to know. Thanks. :) Did they give an ETA on a fix?
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it shouldn't have to be a dramatic change. Simple escaping should be possible with little to no fuss I'd think.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
Post Reply