Searching not returning correct results for table entity

User 8688ffe688

29-08-2013 22:19:56

Instant JChem Version: 6.0.2 (build: 201307020715-372436)
JChem Version: 6.0.2
JChem Table Version: 6000000
Marvin Version: 6.0.2 (build date: 2013-06-25)
Java: 1.7.0_25; Java HotSpot(TM) Client VM 23.25-b01
System: Windows 7 version 6.1 running on x86; Cp1252; en_US (instantjchem)
NetBeans build: a903faa1d680-ijc-patched-9
Userdir: C:\Users\mpustelnik\.instantjchem\ws_lnxprd1_6.0.2
Java Web Start: Yes


Searching within promoted database entities e.g. table/view returns incorrect records when searching within some text fields.  I was able to reproduce this in both the grid view, form view and query builder.  The strange thing is that some fields work and many fields do not work.

ChemAxon 2bdd02d1e5

03-09-2013 22:12:23

This sound really bad. Could you please define more your search scenario? Are the field returning incorrect results in a child entity (with relation 1:N in child)?


Thanks in advance. 


Filip

User 8688ffe688

04-09-2013 00:14:01

I'm searching directly in the entity without any relational connections.  I've attached screenshots.

ChemAxon 2bdd02d1e5

09-09-2013 04:57:26

We were unable to identify this issue. Could you please help us by sending the log file with sql logging turned on? SQL logging can be turned on as described at http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/debug_info.html#sql on that page, there is also chapter describing where to find IJC log file.


Thank you!

User 8688ffe688

09-09-2013 15:15:56

Log info below:


INFO [com.im.ijc.core.api.views.AbstractViewTopComponent]: Last executed query changed. Switching to browse mode. View=Test Grid View, id=F4FE980BF9BC7AB4336709501678128F TC hash=9a2ad4
INFO [jdbc.sqlonly]: SELECT SAR.ENZYME_DATA."COMPOUND_ID" FROM SAR.ENZYME_DATA WHERE SAR.ENZYME_DATA."ASSAY_TYPE" LIKE '%Chromogenic%';


I would expect the following sql based upon the display fields and configured foreign key:


select compound_id, assay_id, sample_type, assay_type, instrument, plate_format, percent_control, f_test, notes, verified, file_sid from enzyme_data where assay_type = 'Chromogenic';


What I suspect here is that even when you query at the entity level, the compound_id which is a foreign key to the parent compound table is retrieved to filter parent records which then is used to pull back child(entity) records.  This would then explain the query statement above and why I would see non "Chromogenic' records.  Counter intuitive and potentially misleading.  Having views at the entity level should directly query the entity and nothing more.  Thoughts?

ChemAxon 2bdd02d1e5

11-09-2013 15:53:54

The select statement returns only compound_id because it's initial select statement returning all IDs fulfilling the search criteria. The data are then fetch in subsequent selects only for visible IDs sequentially.


If you search fields on entity master table, it SHOULD directly query the entity and return only results fulfilling the query. If not there is something wrong.


I'm sorry, did not understand fully to your last comment. The SQL select does not correspond to the screenshot, where all visible records have "Chromogenic", but search is different. Also you are saying that compound_id is a foreign key. Does it mean that you have some relational data tree indeed?


Could you please send me the output of Tools->Experimental->Export IJC Schema? You may rather want to use my email and not attaching here directly. This will help us to understand your database schema model and give the opportunity to simulate your observations. It does not export any data.


Thanks.


Filip