699760 - [Oracle] quicksearch does not work for simple search (1 word) - ORA-00920

Status: RESOLVED DUPLICATE of bug 654496

(Bugzilla :: Database, defect)

Description

[Benjamin S.]

User Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20100101 Firefox/7.0.1
Build ID: 20110928134238

Steps to reproduce:

Steps to Reproduce:
1. Install bugzilla 4.0.2 using Oracle 10g (and German language pack Bugzilla-de Version 4.0.2-1)

2. Quicksearch for one word




Actual results:

Internal Server Error

with applied Search.pm correction of bug 560545:
DBD::Oracle::db prepare failed: ORA-00920: invalid relational operator (DBD ERROR: error possibly near <*> indicator at char 1083 in 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, map_assigned_to.login_name  assigned_to, bugs.alias  alias, map_assigned_to.realname  assigned_to_realname, map_components.name  component, bugs.short_desc  short_desc, bugs.cf_aufwandsschaetzung  cf_aufwandsschaetzung, bugs.status_whiteboard  status_whiteboard FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) INNER JOIN components  map_components ON (bugs.component_id = map_components.id) LEFT JOIN bugs_fulltext  bugs_fulltext_0 ON (bugs.bug_id = bugs_fulltext_0.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (12,10,9,8,6,16,31,21,15,22,23,17,19,35,20,30,24,25,32,29,26,34,33,27,18,28,14)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 2 WHERE (( bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') )) AND (((1<*>=2) OR (1=2) OR (INSTR(LOWER(COALESCE(bugs.alias, '__BZ_EMPTY_STR__')), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.short_desc), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.status_whiteboard), LOWER('word1')) > 0) OR (CONTAINS(bugs_fulltext_0.comments,'"word1"',0) OR CONTAINS(bugs_fulltext_0.short_desc,'"word1"',1)))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 2)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 2) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, map_assigned_to.login_name, bugs.alias, map_assigned_to.realname, map_components.name, bugs.short_desc, bugs.cf_aufwandsschaetzung, bugs.status_whiteboard ORDER BY bug_id') [for Statement "SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, map_assigned_to.login_name  assigned_to, bugs.alias  alias, map_assigned_to.realname  assigned_to_realname, map_components.name  component, bugs.short_desc  short_desc, bugs.cf_aufwandsschaetzung  cf_aufwandsschaetzung, bugs.status_whiteboard  status_whiteboard FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) INNER JOIN components  map_components ON (bugs.component_id = map_components.id) LEFT JOIN bugs_fulltext  bugs_fulltext_0 ON (bugs.bug_id = bugs_fulltext_0.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (12,10,9,8,6,16,31,21,15,22,23,17,19,35,20,30,24,25,32,29,26,34,33,27,18,28,14)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 2 WHERE (( bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') )) AND (((1=2) OR (1=2) OR (INSTR(LOWER(COALESCE(bugs.alias, '__BZ_EMPTY_STR__')), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.short_desc), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.status_whiteboard), LOWER('word1')) > 0) OR (CONTAINS(bugs_fulltext_0.comments,'"word1"',0) OR CONTAINS(bugs_fulltext_0.short_desc,'"word1"',1)))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 2)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 2) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, map_assigned_to.login_name, bugs.alias, map_assigned_to.realname, map_components.name, bugs.short_desc, bugs.cf_aufwandsschaetzung, bugs.status_whiteboard ORDER BY bug_id"] at Bugzilla/DB/Oracle.pm line 495
	Bugzilla::DB::Oracle::prepare('undef', 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, ...') called at /var/www/bugzilla/buglist.cgi line 907

with standard Search.pm:
DBD::Oracle::db prepare failed: ORA-00920: invalid relational operator (DBD ERROR: error possibly near <*> indicator at char 1083 in 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, map_assigned_to.login_name  assigned_to, bugs.alias  alias, map_assigned_to.realname  assigned_to_realname, map_components.name  component, bugs.short_desc  short_desc, bugs.cf_aufwandsschaetzung  cf_aufwandsschaetzung, bugs.status_whiteboard  status_whiteboard FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) INNER JOIN components  map_components ON (bugs.component_id = map_components.id) LEFT JOIN bugs_fulltext  bugs_fulltext_0 ON (bugs.bug_id = bugs_fulltext_0.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (12,10,9,8,6,16,31,21,15,22,23,17,19,35,20,30,24,25,32,29,26,34,33,27,18,28,14)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 2 WHERE (( bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') )) AND (((1<*>=2) OR (1=2) OR (INSTR(LOWER(COALESCE(bugs.alias, '__BZ_EMPTY_STR__')), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.short_desc), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.status_whiteboard), LOWER('word1')) > 0) OR (CONTAINS(bugs_fulltext_0.comments,'"word1"',1) OR CONTAINS(bugs_fulltext_0.short_desc,'"word1"',2)))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 2)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 2) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, map_assigned_to.login_name, bugs.alias, map_assigned_to.realname, map_components.name, bugs.short_desc, bugs.cf_aufwandsschaetzung, bugs.status_whiteboard ORDER BY bug_id') [for Statement "SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, map_assigned_to.login_name  assigned_to, bugs.alias  alias, map_assigned_to.realname  assigned_to_realname, map_components.name  component, bugs.short_desc  short_desc, bugs.cf_aufwandsschaetzung  cf_aufwandsschaetzung, bugs.status_whiteboard  status_whiteboard FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) INNER JOIN components  map_components ON (bugs.component_id = map_components.id) LEFT JOIN bugs_fulltext  bugs_fulltext_0 ON (bugs.bug_id = bugs_fulltext_0.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (12,10,9,8,6,16,31,21,15,22,23,17,19,35,20,30,24,25,32,29,26,34,33,27,18,28,14)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 2 WHERE (( bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') )) AND (((1=2) OR (1=2) OR (INSTR(LOWER(COALESCE(bugs.alias, '__BZ_EMPTY_STR__')), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.short_desc), LOWER('word1')) > 0) OR (INSTR(LOWER(bugs.status_whiteboard), LOWER('word1')) > 0) OR (CONTAINS(bugs_fulltext_0.comments,'"word1"',1) OR CONTAINS(bugs_fulltext_0.short_desc,'"word1"',2)))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 2)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 2) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, map_assigned_to.login_name, bugs.alias, map_assigned_to.realname, map_components.name, bugs.short_desc, bugs.cf_aufwandsschaetzung, bugs.status_whiteboard ORDER BY bug_id"] at Bugzilla/DB/Oracle.pm line 495
	Bugzilla::DB::Oracle::prepare('undef', 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, ...') called at /var/www/bugzilla/buglist.cgi line 907




Expected results:

search results (or empty result) should have been shown - but no error should occur.

Comment 1

[Benjamin S.]

btw: we did not changed to the new statuses (workflow) for now.

Comment 2

[Frédéric Buclin]

Fixed in Bugzilla 4.0.3.