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

RESOLVED FIXED in Bugzilla 3.6

Status

()

Bugzilla
Database
RESOLVED FIXED
7 years ago
6 years ago

People

(Reporter: sam morris, Assigned: sam morris)

Tracking

unspecified
Bugzilla 3.6
Bug Flags:
approval +
approval4.0 +
approval3.6 +

Details

Attachments

(1 attachment, 1 obsolete attachment)

869 bytes, patch
Max Kanat-Alexander
: review+
Details | Diff | Splinter Review
(Assignee)

Description

7 years ago
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
(Assignee)

Comment 1

7 years ago
Created attachment 495493 [details] [diff] [review]
quick workaround

Only take the substring of login_name if it contains an @
Attachment #495493 - Flags: review?(mkanat)

Updated

7 years ago
Keywords: qawanted

Comment 2

7 years ago
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?

Updated

7 years ago
Target Milestone: --- → Bugzilla 3.6
(Assignee)

Comment 3

7 years ago
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

6 years ago
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+

Updated

6 years ago
Status: UNCONFIRMED → NEW
Ever confirmed: true

Comment 5

6 years ago
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-
(Assignee)

Comment 6

6 years ago
Created attachment 512055 [details] [diff] [review]
PG-specific fix

Moved sql_string_until override into Bugzilla/DB/Pg.pm.
Commented purpose of override.
Attachment #495493 - Attachment is obsolete: true
Attachment #512055 - Flags: review?(mkanat)

Comment 7

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

Okay, looks good to me. :-)
Attachment #512055 - Flags: review?(mkanat) → review+

Updated

6 years ago
Assignee: database → sam
Flags: approval4.0+
Flags: approval3.6+
Flags: approval+

Comment 8

6 years ago
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
Last Resolved: 6 years ago
Resolution: --- → FIXED

Updated

6 years ago
Keywords: qawanted
You need to log in before you can comment on or make changes to this bug.