FilterQuery problems

Hi,

We found quite weird case where filterQuery makes JChemSearch to crash with
java.lang.ArrayIndexOutOfBoundsException: 1
        at chemaxon.jchem.db.JChemSearch.generateResultTable(JChemSearch.java:2124)
        at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:2089)
        at chemaxon.jchem.db.JChemSearch.access$3200(JChemSearch.java:141)
        at chemaxon.jchem.db.JChemSearch$SearchThread.run(JChemSearch.java:963)

The conditions are
Structure table with duplicate filtering and following four structures
"CD_ID","CD_SMILES"
1,CC1=NNC(O)=C1 |c:5,t:1|
2,CC1=CC(=O)NN1 |t:1|
3,CC1=NNC(=O)C1 |t:1|
4,CC1=CC(O)=NN1 |c:4,t:1|

Data table with the following records
"ID","COL_A","COL_B","CD_ID"
1,1,0,1
2,2,-1,1
3,3,1,1
4,1,0,2
5,2,0,2
6,3,1,2
7,1,-1,3
8,2,0,3
9,1,-1,4

And the following code

JChemSearch searcher = new JChemSearch();
JChemSearchOptions searchOptions = new JChemSearchOptions(SearchConstants.FULL);
searchOptions.setFilterQuery("SELECT S.CD_ID FROM STR_TEST S INNER JOIN DATA_TEST D ON (S.CD_ID = D.CD_ID) WHERE D.COL_B >= 0 GROUP BY S.CD_ID ORDER BY S.CD_ID");
searcher.setSearchOptions(searchOptions);
searcher.setStructureTable("STR_TEST");
searcher.setQueryStructure("CC1=CC(O)=NN1");
searcher.setOrder(JChemSearch.ORDERING_BY_ID_OR_SIMILARITY);
searcher.setResultTableMode(JChemSearch.CREATE_OR_REPLACE_RESULT_TABLE);
searcher.setResultTable("TEST_RESULTS");
try {
    ch.connectToDatabase();
    ch.checkException();
    searcher.setConnectionHandler(ch);
    searcher.setInfoToStdError(true);
    searcher.setRunMode(JChemSearch.RUN_MODE_SYNCH_COMPLETE);
    searcher.run();
    searcher.checkError();
    searcher.checkException();
    ch.close();
} catch(Exception e) {
    e.printStackTrace();
} 

Another, maybe not related, case where jc_compare ignores filterQuere if search type is set to Full. On the same structure table

SELECT cd_id FROM str_test WHERE jchem.jc_compare(cd_smiles, 'CC1=CC(O)=NN1', 'sep=! t:f!filterQuery:SELECT cd_id FROM str_test WHERE cd_id < 2') = 1; 

Returns two hits, structures with CD_ID 3 and 4.

Please let me know if you can reproduce all of the above.

Thank you in advance,
Valery

The environment is
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_20
Java VM version: 16.3-b01
JChem version: 5.6.0.4
JChem Index version: 5060000
JDBC driver version: 11.1.0.7.0-Production

Hi,

Please let us know your exact JChem version.

(required for all suport requests)

Best regards,

Szilard

Hi Szilard,

Sorry for that, I updated the post.

Regards,
Valery

Hi Valery,

We were able to reproduce the issue. Found it's source, when performing FULL or DUPLICATE search the filter query was handled differently.

We have fixed the issue, since 5.7 is already towards the end of its testing phase i don't know for sure which version will contain the fix, i will get back to you on this.

Meanwhile, there is a workaround i could suggest:

- obtain the cd_id's by the filterquery

- make an int[] out of them

- use JChemSearch.setFilterIDList(int[] ids)

Regards,

Vencel

Hi Vencel,

Thank you for the swift response and the suggestion.

The fix you implemented, does it apply filterQuery before or after structure search?

Regards,
Valery

Hi,

For the workaround the filter query should be executed before the search using the SearchConstants.NO_ABAS or SearchConsants.SUBSTRUCTURE search type. In this case all cd_ids whould be returned by JChemSearch which are substructure candidates and and fulfill the fiter query condition as well. The result can be supplied then to JChemSearch for the second search (using SearchConstants.FULL search type) with the setFilterIDList(int[] ids) method. 

The results of the two approaches are the same. The workaround might be a little bit slower, but if you use the NO_ABAS search type the difference should be barely noticable.

As i promised i contacted my colleagues and the fix will be available in the upcoming 5.7 release due in a couple of weeks.

Regards,

Vencel