Last Comment Bug 616981 - sql_string_until called by whine.pl fails with PostgreSQL when profiles.login_name does not contain '@'
: sql_string_until called by whine.pl fails with PostgreSQL when profiles.login...
Status: RESOLVED FIXED
:
Product: Bugzilla
Classification: Server Software
Component: Database (show other bugs)
: unspecified
: All All
: -- normal (vote)
: Bugzilla 3.6
Assigned To: sam morris
: default-qa
:
Mentors:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2010-12-06 04:04 PST by sam morris
Modified: 2011-02-14 14:15 PST (History)
1 user (show)
mkanat: approval+
mkanat: approval4.0+
mkanat: approval3.6+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
quick workaround (628 bytes, patch)
2010-12-06 06:05 PST, sam morris
mkanat: review-
Details | Diff | Splinter Review
PG-specific fix (869 bytes, patch)
2011-02-13 13:41 PST, sam morris
mkanat: review+
Details | Diff | Splinter Review

Description sam morris 2010-12-06 04:04:03 PST
User-Agent:       Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.63 Safari/534.3
Build Identifier: 4.0rc1

I have configured Bugzilla to use users names without an @ in them, in conjunction with the emailsuffix parameter. wine.pl fails with:

ERROR:  negative substring length not allowed

This is because POSITION('@' IN map_assigned_to.login_name::text) returns 0 when @ is not found.

I'm using PostgreSQL for my database, perhaps the SUBSTRING function in other databases is more permissive.

Reproducible: Always

Steps to Reproduce:
1. Use PostgreSQL(?)
2. Set emailsuffix to @example.com
3. Create a user 'foo'
4. Create a saved search that uses %user% in a boolean chart
5. Create a whine that runs that for all members of a group
Actual Results:  
DBD::Pg::st execute failed: ERROR:  negative substring length not allowed [for Statement "SELECT bugs.bug_id AS bug_id, bugs.bug_severity AS bug_severity, bugs.priority AS priority, bugs.rep_platform AS rep_platform, SUBSTRING(map_assigned_to.login_name FROM 1 FOR POSITION('@' IN map_assigned_to.login_name::text) - 1) AS assigned_to, bugs.bug_status AS bug_status, bugs.resolution AS resolution, bugs.short_desc AS short_desc FROM bugs  INNER JOIN profiles AS map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) LEFT JOIN profiles AS map_qa_contact ON (bugs.qa_contact = map_qa_contact.userid) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (10,8,17)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 6 WHERE (( bugs.bug_status IN ('RESOLVED') )) AND (((bugs.qa_contact = 6) OR (bugs.reporter = 6))) AND NOT((COALESCE(map_qa_contact.login_name,'')::text ~* '.') AND (bugs.reporter = 6) AND (bugs.qa_contact != 6)) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 6)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 6) OR (bugs.qa_contact = 6) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.rep_platform, SUBSTRING(map_assigned_to.login_name FROM 1 FOR POSITION('@' IN map_assigned_to.login_name::text) - 1), bugs.bug_status, bugs.resolution, bugs.short_desc"] at ./whine.pl line 454
        main::run_queries('HASH(0x2c56a18)') called at ./whine.pl line 321
Comment 1 sam morris 2010-12-06 06:05:43 PST
Created attachment 495493 [details] [diff] [review]
quick workaround

Only take the substring of login_name if it contains an @
Comment 2 Max Kanat-Alexander 2010-12-06 09:51:00 PST
Comment on attachment 495493 [details] [diff] [review]
quick workaround

This looks pretty good, I just don't like having to do POSITION twice on the same string. It's not a huge deal, but...can you think of any way around that?

What version of Pg are you running, BTW?
Comment 3 sam morris 2010-12-06 11:19:13 PST
I'm using PostgreSQL 8.4.5.

I'm not sure how to avoid calling POSITION twice, without doing something more expensive (regular expressions or something).
Comment 4 Max Kanat-Alexander 2011-01-04 16:57:54 PST
Comment on attachment 495493 [details] [diff] [review]
quick workaround

Okay, I think you're right. This is the only way to do this.
Comment 5 Max Kanat-Alexander 2011-01-04 16:58:29 PST
Comment on attachment 495493 [details] [diff] [review]
quick workaround

Oh wait, except that this needs to go into Bugzilla::DB::Pg, not Bugzilla::DB.
Comment 6 sam morris 2011-02-13 13:41:07 PST
Created attachment 512055 [details] [diff] [review]
PG-specific fix

Moved sql_string_until override into Bugzilla/DB/Pg.pm.
Commented purpose of override.
Comment 7 Max Kanat-Alexander 2011-02-14 00:03:22 PST
Comment on attachment 512055 [details] [diff] [review]
PG-specific fix

Okay, looks good to me. :-)
Comment 8 Max Kanat-Alexander 2011-02-14 12:12:31 PST
Thanks for the patch and figuring out the whole thing, Sam! I really appreciate it. :-)

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/trunk/                       
modified Bugzilla/DB/Pg.pm
Committed revision 7713.

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/4.0/                         
modified Bugzilla/DB/Pg.pm
Committed revision 7551.

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/3.6/                         
modified Bugzilla/DB/Pg.pm
Committed revision 7235.

Note You need to log in before you can comment on or make changes to this bug.