Enviroment
WinXP
PostgreSQL 8.0.3
Drupal 4.6.(1-3)
Brand new install

I create some Forum container (q=admin/forum/add/container)
and some forum (?q=admin/forum/add/forum)
Then visit this url : ?q=forum
I got many errors

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 c:\...\drupal\includes\database.pgsql.inc on line 45.

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, 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 C:\...\drupal\includes\database.pgsql.inc on line 62.

I solved this problem whit this function:

CREATE OR REPLACE FUNCTION "if"(integer, text, text)
RETURNS text AS
'
  SELECT CASE WHEN ($1<>0) THEN $2 ELSE $3 END;
'
LANGUAGE 'sql';

When i make some forum with the first account ((Administrator) users.uid=1)
and logout, and login like a pure user (not anonymus) and post a message to the forum.
error message:

warning: pg_query() [function.pg-query]: Query failed: ERROR:  invalid input syntax for integer: "" in C:\apache2triad\htdocs\drupal\includes\database.pgsql.inc on line 45.

user error: 
query: INSERT INTO node (title, body, format, uid, type, teaser, status, moderate, promote, sticky, comment, created, changed, nid) VALUES('sdafds ', 'dsf saf ', '1', '2', 'forum', 'dsf saf ', '1', '', '1', '', '2', '1124227407', '1124227407', '43') in C:\...\drupal\includes\database.pgsql.inc on line 62.

warning: Cannot modify header information - headers already sent by (output started at C:\...\drupal\includes\common.inc:384) in C:\...\drupal\includes\common.inc on line 192.

IMPORTANT
Have no error If I use the Administrator account, just with the authenticed users.

I think the INSERT statement is not correct.
Tabale declaration:

moderate   int4           not null default 0,
sticky     int4           not null default 0,

but the INSERT sql
moderate=''
sticky=''

I modyfi the node.module file

//Line +-410 in function node_save()
  //I have many INSERT at 1 page request
  static $db_url_array=NULL;
  if($db_url_array==NULL){
    global $db_url;
    $db_url_array = parse_url($db_url);
  }//if

  // Apply filters to some default node fields:
  if (empty($node->nid)) {
    // Insert a new node.

    // Set some required fields:
    if (!$node->created) {
      $node->created = time();

and modify both "Prepare the query:" section (Insert and update)
insert:

    foreach ($node as $key => $value) {
      if (in_array((string) $key, $fields)) {
        if(($url['scheme'] = 'pgsql')
        && ($key == 'moderate' || $key == 'sticky')
        && ($value == '' || $value == NULL)
        ){
          $value=0;
        }
        $k[] = db_escape_string($key);
        $v[] = $value;
        $s[] = "'%s'";
      }
    }

update

    foreach ($node as $key => $value) {
      if (in_array($key, $fields)) {
        if(($url['scheme'] = 'pgsql')
        && ($key == 'moderate' || $key == 'sticky')
        && ($value == '' || $value == NULL)
        ){
          $value=0;
        }
        $q[] = db_escape_string($key) ." = '%s'";
        $v[] = $value;
      }
    }

Now work fine.

Comments

sweetchuck’s picture

Ohh sorry
I did a mistake
replace this line
if(($url['scheme'] = 'pgsql')
to this
if(($db_url_array['scheme'] = 'pgsql')

Uwe Hermann’s picture

Can you please provide a proper patch? http://drupal.org/patch

essig’s picture

One small suggestion. In the following code:

CREATE OR REPLACE FUNCTION "if"(integer, text, text)
RETURNS text AS
'
  SELECT CASE WHEN ($1<>0) THEN $2 ELSE $3 END;
'
LANGUAGE 'sql';

I think this should be instead:

CREATE OR REPLACE FUNCTION "if"(integer, anyelement, anyelement)
RETURNS anyelement AS
'
  SELECT CASE WHEN ($1<>0) THEN $2 ELSE $3 END;
'
LANGUAGE 'sql';

I think the second and third arguments should both be 'anyelement' instead of 'text', and the function should return 'anyelement' instead of 'text'. This would be more general and match the existing if() function defined in database.pgsql. With this change, you would then have 2 if() functions- the existing one with a boolean as the first argument and this new one with an integer as the first argument. This would be ok because PostgreSQL allows multiple functions to be defined with the same name but different argument types.

George Essig

Dende-1’s picture

First say "Thank you for the patch" :D :D :D
I've added a little modification in the patch. In the line where you specify if the key is null put 0 i've added
($key == 'moderate' || $key == 'sticky') =====> ($key == 'moderate' || $key == 'sticky' || $key=='promoted')

Cheers :D

WhiteFire’s picture

I believe it's supposed to be "promote", not "promoted", but yea...

There needs to be a more general solution to this problem. Like, say, fix the code calling node.module to not put empty strings in where it means '0'. Just because mysql is mornic and seems to think that '' == 0 does not mean it's right.

Yet another reason to hate mysql... keeps encoraging really bad coding practices.

Cvbge’s picture

Hi,

does this happens on 4.6.4 too? At least IF problem should be fixed.

Ciou-Tong’s picture

Version: 4.6.3 » 4.6.5

Debian-sarge
postgresql 7.4.7

It happens again, and I modify the node.module as you did. It doesn't help.
Another question "where is the patch file?"

Cvbge’s picture

Component: postgresql database » node.module
Assigned: sweetchuck »
Priority: Normal » Critical
Status: Active » Needs review
StatusFileSize
new1.85 KB

You are right. There is a problem when auth user posts a forum topic.

1. When all "Default options" are unchecked then in node_validate()
$node_options = variable_get('node_options_'. $node->type, array('status', 'promote')); returns 0 which produces
warning: in_array(): Wrong datatype for second argument in /var/www/dt/d/modules/node.module on line 1251.

The same happens for admin when creating new forum topic (or probably any node type?) in node_form().

Fixed this by adding

    if (!is_array($node_options)) {
      $node_options = array();
    }

after variable_get().

Might be better to make sure that the variable is allways set correctly though... But I'm sorry, maybe someone else will implement this solution.

2. The second problem is node_save(). For users without 'administer nodes' permission (or similar) if a checkbox in "Default options" (status, moderate, promote or sticky) was not checked the value of is set to FALSE, if it was checked - to TRUE.

The fix is to explicitly cast it to (int), which will change FALSE to 0 and TRUE to 1. Fortunately the values are correct (i.e. "sticky" node has value 1, etc - http://drupal.org/node/43355)

Please check attached patch.

Cvbge’s picture

StatusFileSize
new1.95 KB

I've made http://drupal.org/node/27643 and http://drupal.org/node/18552 duplicates of this bug.

As one of the comments said, the (2) error happens also when editing a forum topic. This patch fixes this too.

Cvbge’s picture

Status: Needs review » Closed (duplicate)

Marking as duplicate of http://drupal.org/node/48591 as that issue has better patch