Same query but returned different records

[Blu]

I ran the following query through sqplus and got 109 records returned, I login to another session and ran the same query but got 47 records returned. If I used the distinct for the select clause, I will get 47 records for both sessions. I like to know what causing the inconsistent reponse from the same query.

SELECT /*+ RULE */
old_tl.document_id,
old_e.top_level_element_id,
old_e.element_state_id,
old_e.element_id AS source_element_id
FROM
element old_e, top_level_element old_tl
WHERE
old_tl.document_id=6488 and
old_tl.responder_document_id = 42896 AND
old_e.top_level_element_id = old_tl.element_id AND
old_e.element_state_id IN (6, 7)
ORDER BY old_e.source_element_id;

[slimdave]

Sounds like a bug ... what happens if you take off the RULE hint?

[DaPi]

Are you 100% sure that you are connecting to the same db each time?

[blush] I once worked a full day on the wrong db. Didn't notice till I found I'd "lost" all the changes the next day. [/blush]

[Blu]

Yes, I am pretty sure I'm connecting to the same schema of the same database. That problem only happened when we migrated to the Oracle 9i ( 9.2.0.1.0) and I'm thinking is that the bug related or not.

[tamilselvan]

I suspect "IN" clause.
Remove this line old_e.element_state_id IN (6, 7)
And try again.

Tamil