[drupal-devel] [bug] Forum module's use of if in Postgresql

patrickslee drupal-devel at drupal.org
Thu May 26 20:29:43 UTC 2005


Issue status update for http://drupal.org/node/23499

 Project:      Drupal
 Version:      4.6.0
 Component:    forum.module
 Category:     bug reports
 Priority:     normal
 Assigned to:  patrickslee
 Reported by:  cchamilt
 Updated by:   patrickslee
 Status:       patch
 Attachment:   http://drupal.org/files/issues/database.pgsql_3.patch (363 bytes)

Here you have it.




patrickslee



Previous comments:
------------------------------------------------------------------------

May 24, 2005 - 16:32 : 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.




------------------------------------------------------------------------

May 25, 2005 - 10:07 : 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.




------------------------------------------------------------------------

May 25, 2005 - 10:28 : 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.




------------------------------------------------------------------------

May 25, 2005 - 10:32 : 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.




------------------------------------------------------------------------

May 25, 2005 - 10:43 : 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.




------------------------------------------------------------------------

May 25, 2005 - 10:45 : patrickslee

Oops... my <>s are stolen.


IF(l.last_comment_uid <> 0 AND l.last_comment_uid <> NULL, cu.name,
l.last_comment_name)




------------------------------------------------------------------------

May 25, 2005 - 17:20 : cchamilt

Yes that line works great.
Thanks for your help!




------------------------------------------------------------------------

May 25, 2005 - 17:52 : patrickslee

This patch shows another problem.
The last post user will always show "Anonymous" or whatever you
defined.
Looking for solution...




------------------------------------------------------------------------

May 25, 2005 - 18:00 : 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.




------------------------------------------------------------------------

May 25, 2005 - 18:10 : 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)




------------------------------------------------------------------------

May 25, 2005 - 22:12 : 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.




------------------------------------------------------------------------

May 25, 2005 - 22:41 : 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.




------------------------------------------------------------------------

May 25, 2005 - 22:45 : plj

Sheesh. I meant that '<>' was missing. Gotta learn to use that preview
button...




------------------------------------------------------------------------

May 26, 2005 - 09:53 : 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?




------------------------------------------------------------------------

May 26, 2005 - 12:26 : 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 =)




------------------------------------------------------------------------

May 27, 2005 - 07:21 : 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.







More information about the drupal-devel mailing list