incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: Unexpected behaviour while using notIn
Date Thu, 29 Sep 2011 19:42:02 GMT
Hi Alain,

the answer to your problem is simple:
You just made it a little more complicated than it needs to be.

Simply user your sub-query command directly with the notIn() expression like this:

	cmdFindPub.where(publicationsFromDb.C_ID.notIn( cmdFindPubForPerson ))

the DBQuery object is not required here at all.

However, you might want to consider a more effective statement by joining the two queries
rather than using the query inside a constraint. This is where DBQuery object comes in.
In that case you would replace

	cmdFindPub.where(publicationsFromDb.C_ID.notIn(...))

by 

	DBColumnExpr Q_PUB_ID = queryForAssociatedPub.findQueryColumn(publication4Group.C_PUBLICATION_ID);
	cmdFindPub.join(publicationsFromDb.C_ID, Q_PUB_ID, joinType.Left);
	cmdFindPub.where(Q_PUB_ID.is(null));

This should give you something like this:

	SELECT DISTINCT t11.CREATEDBY, ...
	FROM PUBLICATIONS t11
	  LEFT JOIN (SELECT DISTINCT t13.PUBLICATION_ID
			 FROM PUBLICATION4GROUP t13
			 WHERE t13.GROUP_ID=27) q on q.PUBLICATION_ID=t11.ID
	WHERE q.PUBLICATION_ID is null

...which performs way better than your approach.

Question is however, whether you need the subquery at all. Why not write:

	SELECT DISTINCT t11.CREATEDBY, ...
	FROM PUBLICATIONS t11
	  LEFT JOIN PUBLICATION4GROUP t13 on t13.PUBLICATION_ID=t11.ID and t13.GROUP_ID=27
	WHERE t13.PUBLICATION_ID is null

(haven't really tried it though)

The behavior you described is by design and is required for the second example to work.

Regards
Rainer


> from: Alain Becam [mailto:Alain.Becam@embl.de]
> to: empire-db-user@incubator.apache.org
> re: Unexpected behaviour while using notIn
> 
> Hello all,
>      I have been trying to write a simple query with a notIn:
> 
> 
> 
> DBCommand cmdFindPubForPerson = dbPerson.createCommand();
> 
> 	cmdFindPubForPerson.select(publication4Group.C_PUBLICATION_ID);
> 
> cmdFindPubForPerson.where(publication4Group.C_GROUP_ID.is(group_Id));
> 
> DBQuery queryForAssociatedPub = new DBQuery(cmdFindCollForPerson);
> 
> 
> DBCommand cmdFindPub = dbPerson.createCommand();
> 
> cmdFindPub.selectDistinct();
> cmdFindPub.select(publicationsFromDb.getColumns());
> cmdFindPub.where(publicationsFromDb.C_ID.notIn(queryForAssociatedColl))
> ;
> 
> And was expecting this query:
> 
> SELECT DISTINCT t11.CREATEDBY, ...
> FROM PUBLICATIONS t11
> WHERE t11.ID NOT IN ((SELECT DISTINCT t13.PUBLICATION_ID
> FROM PUBLICATION4GROUP t13
> WHERE t13.GROUP_ID=27))
> 
> but got that:
> 
> SELECT DISTINCT t11.CREATEDBY, ...
> FROM PUBLICATIONS t11, PUBLICATION4GROUP t13
> WHERE t11.ID NOT IN ((SELECT DISTINCT t13.PUBLICATION_ID
> FROM PUBLICATION4GROUP t13
> WHERE t13.GROUP_ID=27))
> 
> which is wrong and pretty slow (well should be).
> 
> Is there a reason I get the PUBLICATION4GROUP table in the main query
> from? Might be a very stupid question, I wonder if I am not missing
> something pretty simple here :)
> 
> //Alain

Mime
View raw message