query problem

User 9afd5de273

05-11-2013 13:30:07

Dear all,

I am running into a problem with the IJC database I am currently working with. In my form view, which combines several linked tables, I can query molecules based on their structure. No problems there. I can also query based on for example IC50 value, which also works fine.

However, if I query on both fields at the same time, I get the error below:

at com.im.df.impl.db.query2.BaseSelectBuilder.addJoinPath(BaseSelectBuilder.java:501)
at com.im.df.impl.db.query2.BaseSelectBuilder.addJoin(BaseSelectBuilder.java:546)
at com.im.df.impl.db.query2.BaseSelectBuilder.joinExistingTables(BaseSelectBuilder.java:693)
at com.im.df.impl.db.query2.BaseSelectBuilder.finish(BaseSelectBuilder.java:285)
at com.im.df.impl.db.query2.BaseSelectBuilder.getSelect(BaseSelectBuilder.java:327)
at com.im.df.impl.db.query2.QueryDefinitionBuilder.createStructureSearch2(QueryDefinitionBuilder.java:260)
at com.im.df.impl.db.query2.QueryDefinitionBuilder.compileSubQueries(QueryDefinitionBuilder.java:342)
at com.im.df.impl.db.query2.QueryDefinitionBuilder.build(QueryDefinitionBuilder.java:428)
at com.im.df.impl.db.DBSchemaDataProviderImpl.queryForValuesImpl(DBSchemaDataProviderImpl.java:442)
at com.im.df.impl.db.DBSchemaDataProviderImpl.queryForIds(DBSchemaDataProviderImpl.java:150)
at com.im.df.impl.db.DBSchemaDataProviderImpl.queryForIds(DBSchemaDataProviderImpl.java:138)
at com.im.df.impl.db.resultset.ResultSetImpl$2.executeQuery(ResultSetImpl.java:290)
at com.im.df.util.earlyresults.EarlyResultsBatcher.runQuery(EarlyResultsBatcher.java:40)
at com.im.df.impl.db.resultset.ResultSetImpl.applyQueryInternal(ResultSetImpl.java:295)
at com.im.df.impl.db.resultset.ResultSetImpl.applyWorkingQuery(ResultSetImpl.java:222)
at com.im.ijc.core.api.actions.ExecuteWorkingQueryAction.phase1(ExecuteWorkingQueryAction.java:52)
at com.im.ijc.core.api.actions.AbstractExecuteQueryAction$1.phase1InRequestProcessor(AbstractExecuteQueryAction.java:47)
at com.im.commons.progress.BackgroundRunner.processTheTask(BackgroundRunner.java:128)
at com.im.commons.progress.BackgroundRunner$1.run(BackgroundRunner.java:91)
at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1411)
at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1991)

This only occurs if the criteria include a structure query. I can multiple other fields at the same time without trouble.

Any ideas what might be going on?

Many thanks,


ChemAxon 2bdd02d1e5

12-11-2013 17:47:34

Dear Sander,

I'd like to know more about the data tree structure to identify what is wrong. Can you please tell me which relationship is between structure table and the table containing IC50, what it the main root table?



User 9afd5de273

14-11-2013 10:02:51

Dear Filip,

The main root table for the view is a table called Batches. This contains an structure identifier string, let's call it Cmpd_Cd, and a batch identifier string, let's call it Batch_Cd.

From the Cmpd_Cd field in the Batches table, there is a ManyToOne relationship from the Batches table to a structure table called Molecules (as one molecule can have multiple batches). 

From the Batch_Cd field in the Batches table, there is a OnToOne relationship to an AverageActivity table (as a single batch can only have a single average activity).

The problem arises if I query the structure field from the 'Molecules' table and the IC50 field from the AverageActivity table at the same time in a view which uses the Batches table as it's root table.

Writing this, I wonder if the problem might be the result of the fact that not for every molecule in the Molecules table, and entry needs to (and does) exist in the Batches table?

Regards, Sander.

ChemAxon 2bdd02d1e5

15-11-2013 13:59:58

Hi Sander,

many thanks for your description. I could reproduce the error, so we can start fixing this.

I must disprove your assumption. The error occur even when all child records (from Molecules or from AverageActivity table) have corresponding record in the Batches table.

We are sorry for this inconvenience. And will inform you here when the fix will be available.


User 9afd5de273

15-11-2013 14:15:22

Dear Filip,

Thanks, I look forward to your update.


ChemAxon 49d153c1b2

23-12-2013 15:57:25

Hi Sander,

the issue has been fixed and the fix will be part of upcoming 6.2 release.

Thanks a lot for your report and we're sorry for any inconvenience.

Best Regards,