I received the following errors with subscriptions 5.x-2.1 on Durpal 5.7 using Postgresql 8.3

* warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: character varying = int_unsigned at character 531 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /var/www/html/drupal/includes/database.pgsql.inc on line 130.
* user warning: query: INSERT INTO subscriptions_queue (uid, name, mail, language, module, field, value, author_uid, send_interval, digest, last_sent, load_function, load_args, is_new) SELECT u.uid, u.name, u.mail, u.language, s.module, s.field, s.value, s.author_uid, s.send_interval, su.digest, su.last_sent, 'subscriptions_content_node_load', '12', '0' FROM subscriptions s INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid INNER JOIN users u USING(uid) INNER JOIN term_node t ON s.value = t.tid WHERE s.module = 'node' AND s.field = 'tid' AND s.author_uid IN (2, -1) AND t.nid = 12 AND s.send_updates = 1 in /var/www/html/drupal/includes/database.pgsql.inc on line 149.

This appears to be due to

PostgreSQL 8.3 no longer allows automatic type casting as per this comment:

http://groups.drupal.org/node/9103#comment-28800

The query you have above fails most likely because of the JOIN of
LEFT JOIN acl acl ON acl.name = t.tid because acl.name is a
VARCHAR and t.tid is an INTEGER

You should post to the issue queue for forum_access and/or acl to address this. All
that should be needed here is to CAST the tid to CHAR such as:

LEFT JOIN acl acl ON acl.name = CAST(t.tid AS CHAR)

Doing this on the join on term_node in subscriptions_taxonomy.module

A patch is attached, hopefully in a useful format

Comments

chx’s picture

chx commented

I do not see a patch?

dpmillerau’s picture

dpmillerau commented

Hmm uploading isn't working for me....

However I've since found some other places in the code and in subscription_og that also have problems.

Here's the one I found above. The others all have s.value on the right of the expression, just to be different.

*** subscriptions_taxonomy.module.orig 2008-06-06 14:24:57.000000000 +1000
--- subscriptions_taxonomy.module 2008-06-06 14:25:27.000000000 +1000
***************
*** 29,35 ****
if ($arg0['module'] == 'node') {
$node = $arg0['node'];
$params['node']['tid'] = array(
! 'join' => 'INNER JOIN {term_node} t ON s.value = t.tid',
'where' => 't.nid = %d',
'args' => array($node->nid),
);
--- 29,35 ----
if ($arg0['module'] == 'node') {
$node = $arg0['node'];
$params['node']['tid'] = array(
! 'join' => 'INNER JOIN {term_node} t ON s.value = CAST(t.tid AS CHAR)',
'where' => 't.nid = %d',
'args' => array($node->nid),
);

chx’s picture

chx commented

I highly doubt CAST would work on MySQL. I also think PostgreSQL is a totally inadequate database for a web application. This was always so and with 8.3 they nailed it -- the PostgreSQL folks care more about being theoretically correct than producing useable software. Hence their failure.

See http://drupal.org/node/249375 this and other issues. Why do we need to deal with broken software, I wonder.

develcuy’s picture

develcuy commented

@dpmillerau I will appreciate your feedback in subscriptions_og issue queue.

Blessings!

salvis’s picture

salvis
he/his
commented
Status: Active » Fixed

I researched CAST on MySQL before committing it to Forum Access, and it's documented to work for MySQL starting 4.1.1 at http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html. They even say

CAST() and CONVERT(... USING ...) are standard SQL syntax.

chx is right about pgsql, of course, but since dpmillerau asked nicely, I've committed this to -dev, so that he and the other pgsql 8.3 user can use Subscriptions without having to patch it.

BTW, the same situation occurred in two other places as well.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.