Mailing-List: contact empire-db-user-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-user@incubator.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: re: Unexpected behaviour while using notIn
Date: Thu, 29 Sep 2011 21:42:02 +0200
Message-ID: <B297D4737108A0459B66C3A85BB7F90133B26F@kn.esteam.de>
In-Reply-To: <4E8458B2.3000001@embl.de>
Thread-Topic: re: Unexpected behaviour while using notIn
Thread-Index: Acx+nF/XuVV6HW/DTJCMiusdZW2EgwAP9F6Q
References: <4E8458B2.3000001@embl.de>
From: =?iso-8859-1?Q?Rainer_D=F6bele?= <doebele@esteam.de>
To: <empire-db-user@incubator.apache.org>

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=20

	DBColumnExpr Q_PUB_ID =3D =
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=3D27) q on q.PUBLICATION_ID=3Dt11.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=3Dt11.ID and =
t13.GROUP_ID=3D27
	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
>=20
> Hello all,
>      I have been trying to write a simple query with a notIn:
>=20
>=20
>=20
> DBCommand cmdFindPubForPerson =3D dbPerson.createCommand();
>=20
> 	cmdFindPubForPerson.select(publication4Group.C_PUBLICATION_ID);
>=20
> cmdFindPubForPerson.where(publication4Group.C_GROUP_ID.is(group_Id));
>=20
> DBQuery queryForAssociatedPub =3D new DBQuery(cmdFindCollForPerson);
>=20
>=20
> DBCommand cmdFindPub =3D dbPerson.createCommand();
>=20
> cmdFindPub.selectDistinct();
> cmdFindPub.select(publicationsFromDb.getColumns());
> =
cmdFindPub.where(publicationsFromDb.C_ID.notIn(queryForAssociatedColl))
> ;
>=20
> And was expecting this query:
>=20
> SELECT DISTINCT t11.CREATEDBY, ...
> FROM PUBLICATIONS t11
> WHERE t11.ID NOT IN ((SELECT DISTINCT t13.PUBLICATION_ID
> FROM PUBLICATION4GROUP t13
> WHERE t13.GROUP_ID=3D27))
>=20
> but got that:
>=20
> 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=3D27))
>=20
> which is wrong and pretty slow (well should be).
>=20
> 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 :)
>=20
> //Alain