SQL question - problem with INTERSECT

On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote:

"(SELECT messages.msgid FROM messages, subject_index WHERE
((subject_index.word='Hello' or subject_index.word='There') and
(subject_index.msgid = messages.msgid))
GROUP BY messages.msgid HAVING count(messages.msgid)=2)
INTERSECT
(SELECT messages.msgid FROM messages, author_index WHERE
((author_index.word='Jim' or author_index.word='Jones') and
(author_index.msgid = messages.msgid))
GROUP BY messages.msgid HAVING count(messages.msgid)=2);"

Try removing first "GROUP BY messages.msgid HAVING count(messages.msgid)=2)"

--
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga",
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

"Keith L. Musser" <kmusser@idisys.com> writes:

PGSQL doesn't accept the combination. It gives me this error message:
"SELECT / HAVING requires aggregates to be valid".

What PG version are you running? AFAICT this works in 7.0.2, but
I wouldn't be too surprised that you might see a failure like that
in 6.5 or before.

regards, tom lane

On Thu, Nov 02, 2000 at 06:50:07AM -0500, Keith L. Musser wrote:

upper(a.word) = 'JIM' or upper(a.word) = 'JONES'
upper(s.word) = 'HELLO' or upper(s.word) = 'THERE'

Interesting possibility. Unfortunately, the outer select ends up being
a sequential scan over the entire messages table, where at each row the
message id is looked up sequentially in the nested query.

did you try my quesry with "upper's" in it?
if so then is your index on a.word or on upper(a.word)?
try making functional indies, and of course vacuum'im tables to make indices
work.
this query *should* work with indices.

depesz

--
hubert depesz lubaczewski
------------------------------------------------------------------------
najwspanialsz� rzecz� jak� da�o nam nowoczesne spo�ecze�stwo,
jest niesamowita wr�cz �atwo�� unikania kontakt�w z nim ...