Closed Bug 616981 Opened 11 years ago Closed 11 years ago
_string _until called by whine .pl fails with Postgre SQL when profiles .login _name does not contain '@'
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
Only take the substring of login_name if it contains an @
Attachment #495493 - Flags: review?(mkanat)
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?
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 on attachment 495493 [details] [diff] [review] quick workaround Okay, I think you're right. This is the only way to do this.
Attachment #495493 - Flags: review?(mkanat) → review+
Comment on attachment 495493 [details] [diff] [review] quick workaround Oh wait, except that this needs to go into Bugzilla::DB::Pg, not Bugzilla::DB.
Attachment #495493 - Flags: review+ → review-
Moved sql_string_until override into Bugzilla/DB/Pg.pm. Commented purpose of override.
Comment on attachment 512055 [details] [diff] [review] PG-specific fix Okay, looks good to me. :-)
Attachment #512055 - Flags: review?(mkanat) → review+
Assignee: database → sam
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.
Status: NEW → RESOLVED
Closed: 11 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.