Andy Jefferson [02/Jan/08 05:11 PM] RDBMS-76 now eliminates the duplicated WHERE clauses within the EXISTS. As far as the 'type' and 'id' parts, here's a workaround : Change the query to SELECT this.id, this.path, avg(c.rating) FROM org.jpox.test.A  WHERE     (this.setB.contains(b1) && b1.propertyName == :propName1 && b1.value == :propValue1) &&     (this.setA.contains(a0) && a0.id == :a0) &&     this.id == c.aId && this.type == :type VARIABLES org.jpox.test.C c; org.jpox.test.B b1; org.jpox.test.A a0 GROUP BY this.id, c.aId ORDER BY avg(c.rating) DESC RANGE 0,10 i.e putting the 'type' and 'id' parts at the end will mean that they are not "swallowed" by the EXISTS hence the SQL becomes SELECT `THIS`.`ID`,`THIS`.`PATH`,AVG(`UNBOUND_C`.`RATING`) FROM `A` `THIS` CROSS JOIN `C` `UNBOUND_C` WHERE (EXISTS (     SELECT 1 FROM `B` `THIS_SETB_B1`     WHERE `THIS_SETB_B1`.`A_ID` = `THIS`.`ID`     AND `THIS_SETB_B1`.`PROPERTY_NAME` = <'EditorialItemTitle'>     AND `THIS_SETB_B1`.`VALUE` = <'Plan the Perfect Alaskan Vacation'>)) AND (EXISTS (     SELECT 1 FROM `A_AS` `THIS_SETA_A0` CROSS JOIN `A` `UNBOUND_A0`     WHERE `THIS_SETA_A0`.`A_ID` = `THIS`.`ID`     AND `UNBOUND_A0`.`ID` = `THIS_SETA_A0`.`LINKED_A_ID`     AND `UNBOUND_A0`.`ID` = <318641>)) AND `THIS`.`ID` = `UNBOUND_C`.`AID` AND `THIS`.`TYPE` = <'E'> GROUP BY `THIS`.`ID`,`UNBOUND_C`.`AID` ORDER BY AVG(`UNBOUND_C`.`RATING`) DESC LIMIT 0,10

Cyril Bouteille [10/Jan/08 11:48 PM] Andy, this workaround unfortunately seems to work only for this particular query we used as an example. In cases when the first setB contains clause is missing, it also sucks in the cross join clauses into the exist subquery for setA, even though they're at the end. :( SELECT this.id, this.path, avg(c.rating) FROM org.jpox.test.A  WHERE     (this.setA.contains(a0) && a0.id == :a0) &&     this.id == c.aId && this.type == :type VARIABLES org.jpox.test.C c; org.jpox.test.B b1; org.jpox.test.A a0 GROUP BY this.id, c.aId ORDER BY avg(c.rating) DESC RANGE 0,10 SELECT `THIS`.`ID`,`THIS`.`WCMS_PATH`,AVG(`UNBOUND_A_RATING`.`RATING`) FROM `WEB_CONTENT` `THIS` CROSS JOIN `WEB_CONTENT_RATING` `UNBOUND_A_RATING` WHERE (EXISTS (SELECT 1 FROM `WEB_CONTENT_EXTRACTED_LINK` `THIS_LINKSET_ALINKEDWEBCONTENT0` CROSS JOIN `WEB_CONTENT` `UNBOUND_A_LINKED_WEB_CONTENT0` WHERE `THIS_LINKSET_ALINKEDWEBCONTENT0`.`WEB_CONTENT_ID` = `THIS`.`ID` AND `THIS_LINKSET_ALINKEDWEBCONTENT0`.`WEB_CONTENT_ID` = `THIS`.`ID` AND `UNBOUND_A_LINKED_WEB_CONTENT0`.`ID` = `THIS_LINKSET_ALINKEDWEBCONTENT0`.`LINKED_WEB_CONTENT_ID` AND `UNBOUND_A_LINKED_WEB_CONTENT0`.`ID` = 318604 AND `THIS`.`ID` = `UNBOUND_A_RATING`.`WEB_CONTENT_ID` AND `THIS`.`WCMS_TYPE` = 'P')) GROUP BY `THIS`.`ID`,`UNBOUND_A_RATING`.`WEB_CONTENT_ID` ORDER BY AVG(`UNBOUND_A_RATING`.`RATING`) DESC LIMIT 0,10 If I move back the id & type clause to the beginning, I've the same issue where it's sucked into the exist query. This is a showstopper for our project and we'll need a fix for this bug or a workaround which works for all the flavors of this query. Please advise.

Andy Jefferson [11/Jan/08 12:33 PM] Translating your comment into a real *minimised* testcase you have SELECT this.id FROM org.jpox.test.A WHERE (this.setA.contains(a1) && a1.id == :aId) && this.type == :type VARIABLES org.jpox.test.A a1 and this becomes SELECT `THIS`.`ID` FROM `A` `THIS` WHERE (EXISTS (     SELECT 1 FROM `A_AS` `THIS_SETA_A1` CROSS JOIN `A` `UNBOUND_A1`     WHERE `THIS_SETA_A1`.`A_ID` = `THIS`.`ID`     AND `UNBOUND_A1`.`ID` = `THIS_SETA_A1`.`LINKED_A_ID`     AND `UNBOUND_A1`.`ID` = 318941     AND `THIS`.`TYPE`= <'E'>) ) which is not optimal. But then I already said there is a TODO in BooleanExpression. I also said that fixes to the query mechanism are not likely to be fast due to lack of people who understand it and hence time taken to understand the effect of changing different aspects, and also due to other work - of which JPA1/JDO2.1 is top priority. Everything will be fixed at some point, but we can't offer guarantees of time. "Workaround" : Use an SQL query for the particular variants that cause problems, using the same JDO query interface. If you have some process generating these queries then this may not be an option (or you could identify what the key feature of these "problem queries" is, and in those cases send through an alternative process generating SQL queries), but that's all I can offer right now.

Andy Jefferson [11/Jan/08 05:50 PM] JPOX SVN now allows PMF property, or Query extension "org.jpox.rdbms.jdoql.existsIncludesConstraints" which you can set to "false". This will then result in the query :- SELECT this.id, this.path, avg(c.rating) FROM org.jpox.test.A WHERE this.id == c.aId && this.type == :type && (this.setB.contains(b1) && b1.propertyName == :propName1 && b1.value == :propValue1) && (this.setA.contains(a0) && a0.id == :a0) VARIABLES org.jpox.test.C c; org.jpox.test.B b1; org.jpox.test.A a0 GROUP BY this.id, c.aId ORDER BY avg(c.rating) DESC RANGE 0,10 being compiled to SELECT `THIS`.`ID`,`THIS`.`PATH`,AVG(`UNBOUND_C`.`RATING`) FROM `A` `THIS` CROSS JOIN `C` `UNBOUND_C` WHERE `THIS`.`ID` = `UNBOUND_C`.`AID` AND `THIS`.`TYPE` = <'E'> AND (EXISTS (     SELECT 1 FROM `B` `THIS_SETB_B1`     WHERE `THIS_SETB_B1`.`A_ID` = `THIS`.`ID`     AND `THIS_SETB_B1`.`PROPERTY_NAME` = <'EditorialItemTitle'>     AND `THIS_SETB_B1`.`VALUE` = <'Plan the Perfect Alaskan Vacation'>)) AND (EXISTS (     SELECT 1 FROM `A_AS` `THIS_SETA_A0` CROSS JOIN `A` `UNBOUND_A0`     WHERE `THIS_SETA_A0`.`A_ID` = `THIS`.`ID`     AND `UNBOUND_A0`.`ID` = `THIS_SETA_A0`.`LINKED_A_ID`     AND `UNBOUND_A0`.`ID` = <318641>)) GROUP BY `THIS`.`ID`,`UNBOUND_C`.`AID` ORDER BY AVG(`UNBOUND_C`.`RATING`) DESC LIMIT 0,10 and the query SELECT this.id FROM org.jpox.test.A WHERE this.type == :type && (this.setA.contains(a1) && a1.id == :aId) VARIABLES org.jpox.test.A a1 is compiled to SELECT `THIS`.`ID` FROM `A` `THIS` WHERE `THIS`.`TYPE` = <'E'> AND (EXISTS (     SELECT 1 FROM `A_AS` `THIS_SETA_A1` CROSS JOIN `A` `UNBOUND_A1`     WHERE `THIS_SETA_A1`.`A_ID` = `THIS`.`ID`     AND `UNBOUND_A1`.`ID` = `THIS_SETA_A1`.`LINKED_A_ID`     AND `UNBOUND_A1`.`ID` = <318941>) So can we close this issue now ? If you use this property, and make sure you use parentheses around contains and any such variable then things should be fine. A separate issue is getting the JDOQL compiler to get it "right" always which is not an easy job!

Cyril Bouteille [12/Jan/08 02:48 AM] I downloaded the latest 1.2 nightly build and set that property to false but it didn't move the constraints out of the exists clause. Did your change not make it into the nightly build? Or am missing something? I used the following code to set the extension:             Query query = pm.newQuery(WebContent.class);             Map extensionMap = new HashMap();             extensionMap.put("org.jpox.rdbms.jdoql.existsIncludesConstraints", "false");             log.fine("Setting extensions " + extensionMap + " on " + query.toString());             query.setExtensions(extensionMap); FINE 17:40:50 WebContentFinder.findAllTopRatedByTypeMetadata: Setting extensions {org.jpox.rdbms.jdoql.existsIncludesConstraints=false} on org.jpox.jdo.JDOQuery@272961 FINE 17:40:50 JDOQLQuery.compileInternal: [JPOX-021044] JDOQL Query : Compiling "SELECT this.id, this.wcmsPath, avg(aRating.rating) FROM calliope.wcm.WebContent WHERE this.id == aRating.webContentId && this.wcmsType == :wcmsType && (this.linkSet.contains(aLinkedWebContent0) && aLinkedWebContent0.id == :linkedWebContentId0) VARIABLES calliope.wcm.WebContentRating aRating; calliope.wcm.WebContent aLinkedWebContent0 GROUP BY this.id, aRating.webContentId ORDER BY avg(aRating.rating) DESC RANGE 0,10" [execution] INFO 17:40:50 JDOQLQueryCompiler.performCompile: >> compileFilter filter=this.id == aRating.webContentId && this.wcmsType == :wcmsType && (this.linkSet.contains(aLinkedWebContent0) && aLinkedWebContent0.id == :linkedWebContentId0) INFO 17:40:50 JDOQLQueryCompiler.performCompile: >> compileFilter DONE FINE 17:40:50 JDOQLQuery.performExecute: [JPOX-021045] JDOQL Query : Compile Time = 32 ms FINE 17:40:50 JDOQLQuery.performExecute: [JPOX-021046] JDOQL Query : Executing "SELECT this.id, this.wcmsPath, avg(aRating.rating) FROM calliope.wcm.WebContent WHERE this.id == aRating.webContentId && this.wcmsType == :wcmsType && (this.linkSet.contains(aLinkedWebContent0) && aLinkedWebContent0.id == :linkedWebContentId0) VARIABLES calliope.wcm.WebContentRating aRating; calliope.wcm.WebContent aLinkedWebContent0 GROUP BY this.id, aRating.webContentId ORDER BY avg(aRating.rating) DESC RANGE 0,10" - ResultFactory = org.jpox.store.rdbms.query.ResultClassROF@9b6220 FINE 17:40:50 ConnectionManagerImpl.allocateConnection: Connection added to the pool : [org.jpox.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl@1474e45, null] FINE 17:40:50 ConnectionFactoryImpl$ManagedConnectionImpl.getConnection: [JPOX-052002] Connection "com.mysql.jdbc.Connection@1401d28" opened with isolation level "READ_COMMITTED" FINE 17:40:50 SQLController.executeStatementQuery: SELECT `THIS`.`ID`,`THIS`.`WCMS_PATH`,AVG(`UNBOUND_A_RATING`.`RATING`) FROM `WEB_CONTENT` `THIS` CROSS JOIN `WEB_CONTENT_RATING` `UNBOUND_A_RATING` WHERE (EXISTS (SELECT 1 FROM `WEB_CONTENT_EXTRACTED_LINK` `THIS_LINKSET_ALINKEDWEBCONTENT0` CROSS JOIN `WEB_CONTENT` `UNBOUND_A_LINKED_WEB_CONTENT0` WHERE `THIS_LINKSET_ALINKEDWEBCONTENT0`.`WEB_CONTENT_ID` = `THIS`.`ID` AND `UNBOUND_A_LINKED_WEB_CONTENT0`.`ID` = `THIS_LINKSET_ALINKEDWEBCONTENT0`.`LINKED_WEB_CONTENT_ID` AND `UNBOUND_A_LINKED_WEB_CONTENT0`.`ID` = <318604> AND `THIS`.`ID` = `UNBOUND_A_RATING`.`WEB_CONTENT_ID` AND `THIS`.`WCMS_TYPE` = <'E'>)) GROUP BY `THIS`.`ID`,`UNBOUND_A_RATING`.`WEB_CONTENT_ID` ORDER BY AVG(`UNBOUND_A_RATING`.`RATING`) DESC LIMIT 0,10 Also, is there a JIRA issue tracking the "getting the JDOQL compiler to get it right always"? What is the #?

Andy Jefferson [12/Jan/08 08:58 AM] There was a bug in Query.setExtensions(Map) that prevented it seeing your extension (presumably nobody had used that method, instead using Query.addExtension(...)). That is fixed also. You can use Query.addExtension(...) to set the flag use the same flag as a PMF property. Or in the next nightly build, use Query.setExtensions(...) There is no generic JIRA for "Query refactor" but there is a page in the Wiki. It wont happen before 1.2 ... unless teams of people suddenly appear to work on it

Cyril Bouteille [14/Jan/08 02:12 AM] Ah. That works now. Thank you very much!