An Issue with a DQL query

Uttakarsha_Gupta

Hi,

I am facing an issue with a DQL query.

I was checking whether a document is having any workflow. The objectId of document is 09030d428000ebff.

Following are some query results:

1) select * from my_doc where r_object_id = '09030d428000ebff'

result: Returns one row

2)select * from dmi_package where any r_component_id = '09030d428000ebff'

result: Returns zero rows

3)select distinct r_object_id,object_name from my_doc doc, dmi_package pak where any pak.r_component_id != doc.r_object_id

result: Returns multiple rows with 09030d428000ebff as one of them.

Its all fine till here.

4)select * from my_doc where r_object_id not in (select distinct r_component_id from dmi_package)

result: Returns zero rows

I am unable to understand the behavior of the 4th query. As per my understanding the result of 3rd and 4th query should have been same.

Is it behaving like this because r_component_id is repeating?

Can someone help me understand this?

Uttkarsh

DCTM_Guru

Yes, using NOT IN in conjunction with a repeating attribute will give you this kind of "unexpected results".  You should try to rewrite the query to avoid using the NOT IN.  Its very expensive in terms of database performance.