pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: 30 Oct 2003 16:32:43 -0500
Message-ID: <87ad7icsxw.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <3FA34827.1030705@trade-india.com>
References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com>
	<20031029202920.GZ7337@pallas.fsck.com>
	<33402.203.145.130.142.1067460429.squirrel@mail.trade-india.com>
	<200310301702.00275.mallah@trade-india.com>
	<m33cdakhv7.fsf@wolfe.cbbrowne.com>
	<13042.61.16.154.82.1067523120.squirrel@mail.trade-india.com>
	<m3smlaiww8.fsf@wolfe.cbbrowne.com>
	<3FA323C2.2060302@trade-india.com>
	<87r80ud0nw.fsf@stark.dyndns.tv>
	<3FA34827.1030705@trade-india.com>

Rajesh Kumar Mallah <mallah@trade-india.com> writes:

> Nopes the query are not Equiv , earlier one returns 4 rows and the below one
> none,

Sorry, i lowercased a string constant and dropped the lower() on email. 

Try this:

SELECT *
  FROM tickets
 WHERE id IN (
       SELECT groups.instance
         FROM groups
         JOIN principals ON (groups.id = principals.objectid)
         JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid)
         JOIN users ON (cachedgroupmembers.memberid = users.id)
        WHERE lower(users.emailaddress) = 'mallah_rajesh@yahoo.com'
          AND groups.domain = 'RT::Ticket-Role'
          AND groups.type   = 'Requestor'
          AND principals.principaltype = 'group'
  )
  AND type = 'ticket'
  AND effectiveid = tickets.id
  AND (status = 'new' OR status = 'open')
ORDER BY priority DESC
LIMIT 10;

-- 
greg




view thread (16+ messages)  latest in thread

Message-ID: <87ad7icsxw.fsf@stark.dyndns.tv>
Permalink:  ../87ad7icsxw.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87ad7icsxw.fsf@stark.dyndns.tv

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, mallah@trade-india.com, cbbrowne@acm.org, jesse@bestpractical.com
  Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
  In-Reply-To: <87ad7icsxw.fsf@stark.dyndns.tv>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox