616981 - sql_string_until called by whine.pl fails with PostgreSQL when profiles.login_name does not contain '@'

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[sam morris]

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]

Attachment: quick workaround

Only take the substring of login_name if it contains an @

Comment 2

[Max Kanat-Alexander]

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]

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]

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]

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]

Attachment: PG-specific fix

Moved sql_string_until override into Bugzilla/DB/Pg.pm.
Commented purpose of override.

Comment 7

[Max Kanat-Alexander]

Comment on attachment 512055 [details] [diff] [review]
PG-specific fix

Okay, looks good to me. :-)

Comment 8

[Max Kanat-Alexander]

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.