[PostgreSQL] COALESCE requires both arguments to be of similar types

RESOLVED FIXED in Bugzilla 2.20

Status

()

P1
normal
RESOLVED FIXED
14 years ago
14 years ago

People

(Reporter: dkl, Assigned: mkanat)

Tracking

2.19.2
Bugzilla 2.20
Bug Flags:
approval +

Details

Attachments

(1 attachment, 1 obsolete attachment)

(Reporter)

Description

14 years ago
User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.7) Gecko/20050416 Fedora/1.0.3-1.3.1 Firefox/1.0.3
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.7) Gecko/20050416 Fedora/1.0.3-1.3.1 Firefox/1.0.3

COALESCE needs both datatypes to match. In the case of GetBugActivity(), one
column is of type varchar and the other is integer. The integer can be cast into
 a text string using chr() in PostgreSQL. Not sure if chr() is supported in
Mysql or not. Someone else can comment. Attaching patch fixing problem on
PostgreSQL.


-- Error Log Snippet --

[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200] DBD::Pg::st execute
failed: ERROR:  COALESCE types text and integer cannot be matched, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]  [for Statement ",
referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]         SELECT
COALESCE(fielddefs.description, bugs_activity.fieldid),, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]               
fielddefs.name, bugs_activity.attach_id, TO_CHAR(bugs_activity.bug_when,
'YYYY.MM.DD HH24:MI:SS'), bugs_activity.removed, bugs_activity.added,
profiles.login_name, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]           FROM
bugs_activity, referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]                ,
referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]      LEFT JOIN
fielddefs, referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]             ON
bugs_activity.fieldid = fielddefs.fieldid, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]     INNER JOIN
profiles, referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]             ON
profiles.userid = bugs_activity.who, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]          WHERE
bugs_activity.bug_id = 1, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]                ,
referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]                ,
referer: http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]       ORDER BY
bugs_activity.bug_when"] at Bugzilla/DB.pm line 80, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]
!Bugzilla::DB::SendSQL('\x{a}        SELECT COALESCE(fielddefs.description,
bugs_activity...') called at CGI.pl line 280, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1
[Tue May 03 14:57:46 2005] [error] [client 172.16.48.200]
!main::GetBugActivity(1) called at
/var/www/bugzilla/mozilla-tip/show_activity.cgi line 49, referer:
http://bugzilla.redhat.com/mozilla-tip/show_bug.cgi?id=1





Reproducible: Always

Steps to Reproduce:
1. View show_activity.cgi of a particular bug.
2. Internal Server Error
(Assignee)

Comment 2

14 years ago
Comment on attachment 182519 [details] [diff] [review]
Adds chr() to integer value inside COALESCE

The right way to do it would be CAST, which is ANSI SQL.
Attachment #182519 - Flags: review-
(Assignee)

Updated

14 years ago
Blocks: 98304
Priority: -- → P1
Target Milestone: --- → Bugzilla 2.20
Version: unspecified → 2.19.2
(Assignee)

Updated

14 years ago
Status: NEW → ASSIGNED
Component: Creating/Changing Bugs → Database
Summary: GetBugActivity() in CGI.pl generates COALESCE error on PostgreSQL using latest CVS → [PostgreSQL] COALESCE requires both arguments to be of similar types
(Assignee)

Comment 3

14 years ago
OK! Here's a cute little hack to make a number into a string that works on both
MySQL and PostgreSQL. Basically, you just concatenate it with an empty string.

MySQL 4 supports CAST, but it doesn't support it properly, really... and MySQL
3 doesn't support CAST at all.
Assignee: create-and-change → mkanat
Attachment #182519 - Attachment is obsolete: true
Attachment #188078 - Flags: review?(dkl)
(Assignee)

Comment 4

14 years ago
Comment on attachment 188078 [details] [diff] [review]
Fix GetBugActivity in a Cross-DB way

I'm not actually certain that dkl will actually be able to review the patch
before we release RC1 (probably on Tuesday), so I'm asking glob. Glob -- it's
really trivial.
Attachment #188078 - Flags: review?(dkl) → review?(bugzilla)
Comment on attachment 188078 [details] [diff] [review]
Fix GetBugActivity in a Cross-DB way

r=glob assuming you've tested it :)
Attachment #188078 - Flags: review?(bugzilla) → review+
(Assignee)

Comment 6

14 years ago
Yep, I tested it.
Flags: approval?
(Reporter)

Comment 7

14 years ago
Comment on attachment 188078 [details] [diff] [review]
Fix GetBugActivity in a Cross-DB way

Works for me on my test installation. I set one of the descriptions of the
fielddefs to NULL (temporarily removed the null constraint), and add some
activity. The integer field id properly displayed instead of the description.

r=dkl
Attachment #188078 - Flags: review+
Flags: approval? → approval+
(Assignee)

Comment 8

14 years ago
Checking in CGI.pl;
/cvsroot/mozilla/webtools/bugzilla/CGI.pl,v  <--  CGI.pl
new revision: 1.242; previous revision: 1.241
done
Status: ASSIGNED → RESOLVED
Last Resolved: 14 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.