292119 - [PostgreSQL] POSITION(...) clause "must be type boolean, not type integer"

Status: RESOLVED FIXED

(Bugzilla :: Bugzilla-General, defect, P1)

Description

[Andreas Franke (gone)]

If I enter anything (e.g.: foo) in the Quicksearch box on
http://landfill.bugzilla.org/bugzilla-tip-pg/
and submit the query, then Bugzilla will die with a 
Software error:

DBD::Pg::st execute failed: ERROR:  Argument of WHERE must be type boolean, not
type integer [for Statement "SELECT products.id FROM products WHERE
POSITION('foo' IN LOWER(products.name))"] at Bugzilla/DB.pm line 80
	Bugzilla::DB::SendSQL('SELECT products.id FROM products WHERE POSITION(\'foo\'
IN LO...') called at Bugzilla/Search.pm line 1487
	Bugzilla::Search::build_subselect('bugs.product_id','products.id','products','POSITION(\'foo\'
IN LOWER(products.name))') called at Bugzilla/Search.pm line 865
	Bugzilla::Search::__ANON__ called at Bugzilla/Search.pm line 1269
	Bugzilla::Search::init('Bugzilla::Search=HASH(0xa329890)') called at
Bugzilla/Search.pm line 85
	Bugzilla::Search::new('Bugzilla::Search','fields','ARRAY(0xa04e6d4)','params','Bugzilla::CGI=HASH(0xa2ff730)','order','ARRAY(0xa057504)')
called at /var/www/html/bugzilla-tip-pg/buglist.cgi line 763

Comment 1

[Tomas Kopal]

The fix for this is part of the patch at bug 285695. Try it again with that
patch applied...

Comment 2

[Max Kanat-Alexander]

I've removed the code that will fix this from bug 285695, and am going to put it
in this bug, instead.

Comment 3

[Max Kanat-Alexander]

Attachment: v1

OK, I've asked LpSolit for too many reviews, so this one's for Joel. :-) (If
you have the time, Joel.)

Basically, we just need to make sure that the POSITION clause has some boolean
comparison operator when it's used in a boolean fashion. So that's what we do.

Comment 4

[Max Kanat-Alexander]

Comment on attachment 188074 [details] [diff] [review]
v1

Hey, dkl, here's another one, if you've got a bit. I'd imagine you've probably
also run into this one if you've been using 2.19.3.

Comment 5

[David Lawrence [:dkl]]

Comment on attachment 188074 [details] [diff] [review]
v1

Patch looks sane, applies cleanly and fixes the problem during my testing.

r=dkl

Comment 6

[Max Kanat-Alexander]

Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.411; previous revision: 1.410
done
Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.98; previous revision: 1.97
done
Checking in Bugzilla/User.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/User.pm,v  <--  User.pm
new revision: 1.60; previous revision: 1.59
done

Comment 7

[David D. Kilzer (ddk)]

Did you prepend " > 0" to every place that calls sql_position()?

If so, why didn't you add the " > 0" within the method itself?  The code would have read a lot better that 
way.

Comment 8

[Max Kanat-Alexander]

(In reply to comment #7)
> If so, why didn't you add the " > 0" within the method itself?  The code would 
> have read a lot better that way.

  Because we don't always use sql_position as a boolean condition. So the answer
to the first question there is actually "no."