[drupal-devel] [bug] if postgresql function
plj
drupal-devel at drupal.org
Fri Aug 5 16:58:25 UTC 2005
Issue status update for
http://drupal.org/node/23499
Post a follow up:
http://drupal.org/project/comments/add/23499
Project: Drupal
-Version: 4.6.0
+Version: cvs
Component: forum.module
Category: bug reports
Priority: normal
Assigned to: patrickslee
Reported by: cchamilt
Updated by: plj
Status: patch (code needs review)
I'm advocating it too. Just tested the fix in comment #13 against HEAD.
Works well.
I suggest that Dries would just commit it; at least the situation
improves dramatically, because as now, the failure is certain.
Perhaps there is something wrong in igric's setup, if the patch does
not work? What is your Postgres version?
plj
Previous comments:
------------------------------------------------------------------------
Tue, 24 May 2005 04:32:18 +0000 : cchamilt
Postgresql function "if" is defined to return a boolean value, yet forum
requests an integer - last_comment_uid.
user error:
query: SELECT n.nid, l.last_comment_timestamp, IF(l.last_comment_uid,
cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid
FROM node n, node_comment_statistics l /*! USE INDEX
(node_comment_timestamp) */, users cu, term_node r WHERE n.nid = r.nid
AND r.tid = 1 AND n.status = 1 AND n.type = 'forum' AND
l.last_comment_uid = cu.uid AND n.nid = l.nid ORDER BY
l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in
/var/www/hosts/www.ambigc.com/htdocs/includes/database.pgsql.inc on
line 62.
warning: pg_query() [function.pg-query]: Query failed: ERROR: function
if(integer, character varying, character varying) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts. in
/var/www/hosts/www.ambigc.com/htdocs/includes/database.pgsql.inc on
line 45.
------------------------------------------------------------------------
Tue, 24 May 2005 22:07:49 +0000 : patrickslee
Attachment: http://drupal.org/files/issues/forum.module_3.patch (2.69 KB)
I have also encountered this bug and made a possible patch by replacing
IF statements to CASE.
A patch file is attached.
------------------------------------------------------------------------
Tue, 24 May 2005 22:28:20 +0000 : adrian
There is an IF function defined in the database.pgsql function. On what
page is this happening, as what might be happening is that the type
isn't being picked up correctly.
It's defined as IF(anyelement, anyelement, text) returns anyelement.
IIRC. What I have seen happen is that it wants the text field to return
integer, or vice versa.
------------------------------------------------------------------------
Tue, 24 May 2005 22:32:56 +0000 : patrickslee
I found the definition in database.pgsql. It is like this:
CREATE FUNCTION "if"(boolean, anyelement, anyelement) RETURNS
anyelement AS '
SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
' LANGUAGE 'sql';
So it is actually wanting boolean.
------------------------------------------------------------------------
Tue, 24 May 2005 22:43:22 +0000 : patrickslee
So my patch will work, and we can make it easier by replacing the IF
function to:
IF(l.last_comment_uid 0 AND l.last_comment_uid NULL, cu.name,
l.last_comment_name)
BTW there is another place in forum.module use IF like this.
------------------------------------------------------------------------
Tue, 24 May 2005 22:45:41 +0000 : patrickslee
Oops... my <>s are stolen.
IF(l.last_comment_uid <> 0 AND l.last_comment_uid <> NULL, cu.name,
l.last_comment_name)
------------------------------------------------------------------------
Wed, 25 May 2005 05:20:34 +0000 : cchamilt
Yes that line works great.
Thanks for your help!
------------------------------------------------------------------------
Wed, 25 May 2005 05:52:24 +0000 : patrickslee
This patch shows another problem.
The last post user will always show "Anonymous" or whatever you
defined.
Looking for solution...
------------------------------------------------------------------------
Wed, 25 May 2005 06:00:55 +0000 : patrickslee
Use this line instead:
IF(l.last_comment_uid <> 0 AND l.last_comment_uid <> NULL, cu.name,
l.last_comment_name)
Don't forget there are two places in forum.module need to be changed.
------------------------------------------------------------------------
Wed, 25 May 2005 06:10:26 +0000 : patrickslee
Oh what have I done! I changed nothing!
IF(l.last_comment_uid <> 0 AND l.last_comment_uid IS NOT NULL, cu.name,
l.last_comment_name)
------------------------------------------------------------------------
Wed, 25 May 2005 10:12:14 +0000 : adrian
perhaps we could also just add more wrapper functions for the specific
types ?
create or replace function IF (integer, anyelement, anyelement) returns
anyelement as '
SELECT if (($1 0) AND NOT nullvalue($1), $2, $3);
' LANGUAGE 'sql';
Or something.
------------------------------------------------------------------------
Wed, 25 May 2005 10:41:18 +0000 : plj
'' was missing. So what adrian actually means is this:
CREATE OR REPLACE FUNCTION IF (integer, anyelement, anyelement) RETURNS
anyelement AS '
SELECT if (($1 <> 0) AND NOT nullvalue($1), $2, $3);
' LANGUAGE 'sql';
I applied that and now forums work -- no module patches. PostgreSQL
7.4.7.
------------------------------------------------------------------------
Wed, 25 May 2005 10:45:07 +0000 : plj
Sheesh. I meant that '<>' was missing. Gotta learn to use that preview
button...
------------------------------------------------------------------------
Wed, 25 May 2005 21:53:11 +0000 : patrickslee
Is nullvalue() a function? I cannot find it in neither postgres manual
nor mysql manual.
Use "IS NOT NULL" would be better as it is more standard-compatible.
SO we have this:
CREATE OR REPLACE FUNCTION IF (integer, anyelement, anyelement) RETURNS
anyelement AS '
SELECT IF(($1 <> 0) AND $1 IS NOT NULL, $2, $3);
' LANGUAGE 'sql';
Looks we have a satisfying fix here. Is this fix going to be committed
in CVS for next release?
------------------------------------------------------------------------
Thu, 26 May 2005 00:26:58 +0000 : adrian
hostmaster=# \df nullvalue
List of functions
Result data type | Schema | Name | Argument data types
------------------+------------+-----------+---------------------
boolean | pg_catalog | nullvalue | "any"
But it doesn't matter to me =)
------------------------------------------------------------------------
Thu, 26 May 2005 19:21:57 +0000 : Dries
Can you guys provide a patch? I don't have a PostgreSQL setup to test
it against. Will commit to DRUPAL-4-6 and HEAD.
------------------------------------------------------------------------
Thu, 26 May 2005 20:29:38 +0000 : patrickslee
Attachment: http://drupal.org/files/issues/database.pgsql_3.patch (363 bytes)
Here you have it.
------------------------------------------------------------------------
Wed, 15 Jun 2005 10:18:32 +0000 : igrcic
Dont know about you guys but that patch doesnt work for me in drupal
4.6.1!? Im using PostgreSQL.
Instead im using following function that works just fine
if(integer,text,text)
BEGIN
IF $1 THEN
RETURN $2;
END IF;
IF NOT $1 THEN
RETURN $3;
END IF;
END;
------------------------------------------------------------------------
Sun, 19 Jun 2005 08:55:54 +0000 : Dries
Just so you know: I'm not committing this patch yet because one user
commented that it didn't work. Waiting for a follow-up from the
PostgreSQL front.
------------------------------------------------------------------------
Sun, 19 Jun 2005 11:28:04 +0000 : igrcic
Ok, here how it is now. First of all, forget my previous if FUNCTION :)
Currenty im using drupal 4.6.1 and I had that problems with forums. Now
Im using two if functions, because one is required for forums and I
forgot for another another one, but I know that it was called because
of different function parameters (bool, int).
So one for the forum is :
CREATE OR REPLACE FUNCTION "if"(integer, text, text) RETURNS text AS '
BEGIN
IF $1 IS NOT NULL AND $1 > 0 THEN
RETURN $2;
ELSE
RETURN $3;
END IF;
END;
' LANGUAGE 'plpgsql';
The another one is standard function that comes with drupal
database.inc.pgsql file
CREATE FUNCTION "if"(boolean, anyelement, anyelement) RETURNS
anyelement AS '
SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
' LANGUAGE 'sql';
Im using it for a while and everything works nice!
Before I posted this topic I replaced function with that one in
patch and it worked nice with this one too. But I know that once I
had some error with it (didn't write it down grrr), so im stickin with
the first function, because it work perfectly for me!
And we have to have 2 functions, one for boolean and one for integer!
In mysql it isnt so.
Hope everything's clear now!
------------------------------------------------------------------------
Sun, 19 Jun 2005 11:30:15 +0000 : igrcic
Oops, maybe I screwed up something with formatting my post :( Hope you
can fix it, sorry
------------------------------------------------------------------------
Thu, 23 Jun 2005 03:43:59 +0000 : Arto
Drupal 4.6.1 on PostgreSQL 7.4.7:
The patch (database.pgsql_3.patch) fixes the problem; igrcic's method
would certainly fix it as well.
Advocate committing the patch.
More information about the drupal-devel
mailing list