PgSql Error (Comment Notify + PostgreSql)

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?

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?

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...

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 :-)

@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 :)

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

Thanks again, ulhume!