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

Status: RESOLVED FIXED

(Bugzilla :: Database, defect, P1)

Description

[David Lawrence [:dkl]]

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

Comment 1

[David Lawrence [:dkl]]

Attachment: Adds chr() to integer value inside COALESCE

Comment 2

[Max Kanat-Alexander]

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.

Comment 3

[Max Kanat-Alexander]

Attachment: Fix GetBugActivity in a Cross-DB way

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.

Comment 4

[Max Kanat-Alexander]

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.

Comment 5

[:glob ✱]

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

r=glob assuming you've tested it :)

Comment 6

[Max Kanat-Alexander]

Yep, I tested it.

Comment 7

[David Lawrence [:dkl]]

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

Comment 8

[Max Kanat-Alexander]

Checking in CGI.pl;
/cvsroot/mozilla/webtools/bugzilla/CGI.pl,v  <--  CGI.pl
new revision: 1.242; previous revision: 1.241
done