* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "cmail" LINE 1: ...TINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail cmail, u.m... ^ in /var/www/includes/database.pgsql.inc on line 138.
    * user warning: query: SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail cmail, u.mail umail, u.init uinit, c.uid, c.name, cn.notify_hash mymd5 FROM comments c INNER JOIN comment_notify cn on c.cid = cn.cid LEFT OUTER JOIN users u ON c.uid = u.uid WHERE nid = 29 AND cn.notify = 1 AND c.status = 0 AND (u.status = 1 or u.status = '') in /var/www/sites/all/modules/comment_notify/comment_notify.module on line 302.

patches:
-c.mail cmail
+c.mail AS cmail
-u.mail umail
+u.mail AS umail
....

and

--(u.status OR u.status = '')
++(u.status OR u.status is null)

Comments

Status:Active» Needs review
StatusFileSize
new1.32 KB

Hello Helg - I was curious if that worked so thanks for your feedback.

That last piece won't work for MySQL, so I've attached a patch which incorporates all three tests. It's not a perfect solution, but I can't think of a better one.

Can you test this out?

no! this not valid code for PostgreSQL

@@ -296,9 +296,9 @@ function _comment_notify_mailalert($comm
$comment_mail = $comment->mail;
}

- $result = db_query("SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail cmail, u.mail umail, u.init uinit, c.uid, c.name, cn.notify_hash mymd5
+ $result = db_query("SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail AS cmail, u.mail AS umail, u.init AS uinit, c.uid, c.name, cn.notify_hash AS mymd5
FROM {comments} c INNER JOIN {comment_notify} cn on c.cid = cn.cid LEFT OUTER JOIN {users} u ON c.uid = u.uid
- WHERE nid = %d AND cn.notify = 1 AND c.status = 0 AND (u.status = 1 or u.status = '')", $nid
+ WHERE nid = %d AND cn.notify = 1 AND c.status = 0 AND (u.status = 1 OR u.status = '' OR u.status IS NULL)", $nid
);

PostrgreSQL not valid code u.status = ''
u.status => integer
'' => varchar

Status:Needs review» Needs work

Does pgsql give an error message when comparing an integer to a varchar?

Unfortunately mysql requires us to use u.status = '' for an equivalent test to the pgsql u.status is null.

Do you have a proposal which will work on both MySQL and PostgreSQL?

Status:Needs work» Needs review
StatusFileSize
new6.41 KB

Here's an alternate approach - we can do the filtering in php rather than the DB. It's probably slightly slower so I document the weakness in a code comment...

Status:Needs review» Needs work

I committed the "AS" parts of this query since those seem reasonable to me.

I'm still waiting to hear from a pgsql person regarding the proper way to fix this that is cross-db.

@greggles Your last patch is working for me.

Drupal anonymous management is such a mess... I mean the all lot, your module helps a lot !!!

@ulhume - thanks for the feedback, that's interesting. I don't like that patch as much for performance reasons. Can you tell me if the patch in #1 gives an error?

@greggles
I tried #1 :

1/ At the beginning of the query, there is 2 missing "AS" for uinit and mymd5:
2/ In postgresql u.status='' will always fail as u.status is an integer, not a varchar.
3/ I don't really understand why you have this " OR u.status = '' OR u.status IS NULL". When you first install drupal, you'll have 2 rows in {users} table. Admin (uid=1) and Anonymous (uid=0). The only thing you will catch with outer join and your test is users that has been deleted with {comment}.uid that is not anymore in {users} table. If your problem is that anonymous user has status=0, why no simply add "or uid=0)" ?

For me this is just enough :

    SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail AS cmail, u.mail AS umail, u.init AS uinit, c.uid, c.name, cn.notify_hash AS mymd5
    FROM                  {comments}          c
    INNER JOIN          {comment_notify} cn on c.cid = cn.cid
    LEFT OUTER JOIN {users}                   u  on c.uid = u.uid
    WHERE
        nid = %d  AND
        cn.notify = 1 AND
        c.status = 0 AND
        (u.status = 1 or u.uid=0)

* if c.uid=0 => OK
* if c.uid<>0 => (u.status = 1 ? OK : drop )
* if c.uid don't exists in {users} =>u.status=null => u.status<>1 AND u.uid<>0 => drop

I hope this is clear enough and that I'm not missing something :-)

Status:Needs work» Needs review
StatusFileSize
new1.23 KB

@ulhume - brilliant! I believe that will work perfectly. That was very creative to think of using uid = 0 for this csae.

Here's a patch against the latest 5.x code. Can you confirm it?

I fixed the " AS " problems earlier so now we just need to fix the u.status = '' problem.

I confirm this is working at least on my test & production servers.

Happy I could help :)

Status:Needs review» Fixed

Fixed in both 5.x-2 and 6.x.

Thanks again, ulhume!

Status:Fixed» Closed (fixed)

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