• Sign In
  1. Messages

query result that doesnt make any sense

LabKey Support Forum (Inactive)

View Message

view list print
query result that doesnt make any sense Ben Bimber  2011-03-31 03:10
Status: Closed
 
Both my laptop and our test server are cloned from a postgres backup from our production server. they were taken at slightly different times, so they're not quite identical, but they're close.

I have a query that runs fine locally, but is returning a completely confusing reuslt on test-ehr. The query attempts to identify the amount of blood drawn in the last 30 days for an animal:

SELECT b.id, sum(b.quantity) as BloodLast30, count(*) as te, max(lsid) as lsid
FROM study."Blood Draws" b
WHERE b.id='r95061' AND b.date >= '2011-03-01' AND b.date <= '2011-03-31'
GROUP BY b.id

It should return a small number. Instead this returns 7380, which makes no sense. I added the count(*) and LSID to get more info. That query also says the LSID is:

urn:lsid:primate.wisc.edu:Study.Data-108:1008.2.0110322E7.r95061.f593e0ef-370a-102e-803d-079f4fb02595

but when i try to find that:
https://test-ehr.primate.wisc.edu:8443/labkey/query/WNPRC/EHR/executeQuery.view?schemaName=study&query.queryName=Blood%20Draws&query.Id~startswith=r95061&query.lsid~eq=urn:lsid:primate.wisc.edu:Study.Data-108:1008.2.0110322E7.r95061.f593e0ef-370a-102e-803d-079f4fb02595

that record doesnt exist. likewise for studydata:

https://test-ehr.primate.wisc.edu:8443/labkey/query/WNPRC/EHR/executeQuery.view?schemaName=study&query.queryName=StudyData&query.Id~startswith=r95061&query.lsid~eq=urn:lsid:primate.wisc.edu:Study.Data-108:1008.2.0110322E7.r95061.f593e0ef-370a-102e-803d-079f4fb02595

any idea what could be happening here?

thanks for the help.
 
 
Matthew Bellew responded:  2011-03-31 09:25
This is puzzling. Can you make me a 'developer' on test-ehr, and add this to the log4j.xml

    <logger name="org.labkey.api.data">
         <level value="debug"/>
        <appender-ref ref="SESSION"/>
    </logger>

This will make it possible for me to see the postgres SQL being generated.

I'm not sure how the date strings are being converted. I would use an explicit CAST('2011-03-01' AS TIMESTAMP), however, I don't think that explains what's going on.
 
Matthew Bellew responded:  2011-03-31 11:32
It turns out there is a really messed up row in the "Blood Draws" table. Go here

https://test-ehr.primate.wisc.edu:8443/labkey/query/WNPRC/EHR/executeQuery.view?query.Id~eq=r95061&query.queryName=Blood%20Draws&query.sort=-date%2C-quantity&schemaName=study&query.ignoreFilter=1

And you'll see the row right at the top. I didn't see the row initially because the default view has a filter on QCState, and this row has QCState="In Progress"
 
Ben Bimber responded:  2011-03-31 11:42
ah, yes. i put base filters on all the default views to filter out records with non-public QC states. that's why i wasnt seeing it. on the bright side, if these filters foiled me they might do their jobs for the rest of the users.