OS v1.1 Query Discrepancies

[bob_lange]

We are trying to verify queries run under Advanced Query thought the backend queries against the MySQL database. We had some difficulty getting queries that matched, so we started very simple with the attached query. As you can see, the count comes back with 484, but the detail shows 490 rows. 490 matches my backend query:

select count(*)
from catissue_coll_prot_reg cpr
join catissue_collection_protocol cp
on cpr.COLLECTION_PROTOCOL_ID = cp.IDENTIFIER
where cp.SHORT_TITLE = 'BEM'
and cpr.ACTIVITY_STATUS = 'Active'
;

I know the queries are not built in SQL directly, but rather via Hibernate. Is there an easy way to see what is being queried by Advanced Query so that we can verify that what is being returned is what we really want? I would like to be able to validate queries and investiagte discrepencies like the one described above.

[John_Reber]

Bob,

You can turn query logging on in mysql directly.

Here’s a link to some information on it:

http://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log

John

[bob_lange]

Thanks, @John_Reber ! That worked like a charm!

[vpawar]

Hello Bob,

Points:

1. You can view SQLs generated by query module in audit logs section of query UI. This requires you to login as admin user.

2. This a known bug in count query interface. If all participant specimens are in disabled state then that participant is not counted. Therefore I believe your query result set might have 6 participants with all of their specimens in disabled state. If count discrepancy is because of any reason other than #2, please let us know.

3. In define view, if you include specimen label, you should see counts matching in both count and results view.

[Quote from Linus Torvalds: “Given enough eyeballs, all bugs are shallow”]

Thanks.

[bob_lange]

@vpawar,

1. Is this feature only avaialble to Admins or also Super Admins? I am a Super Admin, but did not see it. That being said, I found the query log on the backend and found what I need in there.

2. On one hand, this appears to be correct. I compared the queries and the count includes the specimen table while the full query does not. I modified the SQL of the full query to include the specimen table and check the status of the specimen and the counts matched. On the other hand, I changed the advanced query to account for that (see screenshots) and the counts still mismatch.

3. I tried this, but it then returned a row for each specimen rather than each participant.

I will run the raw SQL from the new discrepancy (#2) and let you know what I find, but I wanted to get back to you with the initial findings at least.

[bob_lange]

I looked at the queries and the full query returns a row for each specimen which I suppose is then reduced to distinct particpants. However, putting a distinct on participant ID to verify this returned 484 rows, so I’m still puzzled about the 418 number in my new query (see above screenshots).

[vpawar]

Hello Bob,

Responses:

1. There are 2 types of query audit logs - summary and detailed. The summary logs contain minimal information like time of run and time taken for query to run to completion. These logs are available per query to all users. The detailed logs contain summary log info + generated SQLs. The detailed audit logs are visible only to super admins at bottom left corner of query dashboard

2. The count query needs to show specimen count; therefore it includes specimen table. The data query will not contain specimen table unless needed (like filters on specimen or specimen fields in result view)

3. The result view indicates not all records of query output are processed. In fact the query output is limited to first 10K records, which are processed and shown on UI. In order to view all records, please export query results. The exported query results should have 484 participants…

Please let us know how it goes.

Thanks,
Vinayak