Exception on Labkey SQL Parse - Can't edit query source: /home/Support/LabKey Support Forum

Exception on Labkey SQL Parse - Can't edit query source

LabKey Support Forum (Inactive)

View Message

Exception on Labkey SQL Parse - Can't edit query source

Anthony Corbett

2013-09-23 09:26

Status: Closed

I have a query built in Labkey's SQL and it worked fine in a previous version of Labkey, however in 13.1 it throws an exception while trying to parse this query. See the attached screenshot.

I am unable to edit the source, properties, or metadata as they all end up in a 500 Error: Unexpected Server error with the same NPE; Here are the details:

org.labkey.query.sql.Query$QueryInternalException: Internal error while parsing "SELECT
demo.ParticipantId,
contactInfo.infant_name,
demo.hospital_enroll_site,
demo.ParticipantId.Cohort,
contactInfo.medRec_num,
hsd.admission_date,
hsd.discharge_date,
demo.enroll_date,
st.date as StudyTerminationDate
FROM Demographics demo
LEFT JOIN "Contact Sheet" contactInfo ON contactInfo.ParticipantId = demo.ParticipantId
LEFT JOIN "Hospital Summary Dates" hsd ON hsd.ParticipantId = demo.ParticipantId
LEFT JOIN "Study Termination" st ON st.ParticipantId = demo.ParticipantId
WHERE demo.ParticipantId.Cohort.Label = 'hospital'
  OR hsd.ParticipantId IN (
      SELECT pcrResults.ParticipantId
      FROM "RSV PCR Results" pcrResults
      WHERE lower(pcrResults.result) <> 'negative'
      UNION
      SELECT AncCultureResults.ParticipantId
      FROM "Ancillary Illness Culture Results" AncCultureResults
      WHERE AncCultureResults.viral_culture = 1; -- 1 = RSV
  )"
       at org.labkey.query.sql.Query.wrapRuntimeException(Query.java:523)
       at org.labkey.query.sql.Query.getTableInfo(Query.java:497)
       at org.labkey.query.QueryDefinitionImpl.createTable(QueryDefinitionImpl.java:400)
       at org.labkey.query.QueryDefinitionImpl.getTable(QueryDefinitionImpl.java:369)
       at org.labkey.api.query.UserSchema.createView(UserSchema.java:442)
       at org.labkey.study.query.StudyQuerySchema.createView(StudyQuerySchema.java:521)
       at org.labkey.api.query.UserSchema.createView(UserSchema.java:432)
       at org.labkey.api.query.QueryForm.createSchema(QueryForm.java:213)
       at org.labkey.api.query.QueryForm.getSchema(QueryForm.java:293)
       at org.labkey.api.query.QueryForm.getQuerySettings(QueryForm.java:237)
       at org.labkey.api.query.QueryForm.getQueryName(QueryForm.java:310)
       at org.labkey.api.query.QueryForm.getQueryDef(QueryForm.java:315)
       at org.labkey.query.controllers.QueryController$SourceQueryAction.getView(QueryController.java:650)
       at org.labkey.query.controllers.QueryController$SourceQueryAction.getView(QueryController.java:638)
       at org.labkey.api.action.FormViewAction.handleRequest(FormViewAction.java:104)
       at org.labkey.api.action.FormViewAction.handleRequest(FormViewAction.java:75)
       at org.labkey.api.action.BaseViewAction.handleRequestInternal(BaseViewAction.java:177)
       at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
       at org.labkey.api.action.SpringActionController.handleRequest(SpringActionController.java:354)
       at org.labkey.api.module.DefaultModule.dispatch(DefaultModule.java:928)
       at org.labkey.api.view.ViewServlet.service(ViewServlet.java:164)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.labkey.api.data.TransactionFilter.doFilter(TransactionFilter.java:36)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:118)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.labkey.api.module.ModuleLoader.doFilter(ModuleLoader.java:806)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:171)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
       at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
       at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:311)
       at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:776)
       at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:705)
       at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:898)
       at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
       at java.lang.Thread.run(Thread.java:724)
Caused by: java.lang.NullPointerException
request attributes
LABKEY.StartTime = 1379953447252
javax.servlet.request.ssl_session = C7274216E8E78CBDCD7DCA69BAEAE56C283A0B1FAA57296421C3058C2EE9F2AD
LABKEY.container = /RPRC/Studies & Labs/12-0004 Peds RSV
LABKEY.action = sourceQuery
LABKEY.RequestURL = /labkey/query/RPRC/Studies%20%26%20Labs/12-0004%20Peds%20RSV/sourceQuery.view?schemaName=study&query.queryName=ParticipantsDatesOfServiceDetails
javax.servlet.request.key_size = 256
LABKEY.OriginalURLHelper = /labkey/query/RPRC/Studies%20%26%20Labs/12-0004%20Peds%20RSV/sourceQuery.view?schemaName=study&query.queryName=ParticipantsDatesOfServiceDetails
LABKEY.controller = query
javax.servlet.request.cipher_suite = DHE-RSA-AES256-SHA
X-LABKEY-CSRF = 25c4aae477fe3c7f871707f400fa33f7
LABKEY.OriginalURL = https://blis.urmc.rochester.edu/labkey/query/RPRC/Studies%20%26%20Labs/12-0004%20Peds%20RSV/sourceQuery.view?schemaName=study&query.queryName=ParticipantsDatesOfServiceDetails
LABKEY.Counter = 0

core schema database configuration
Server URL jdbc:postgresql://localhost/labkey
Product Name PostgreSQL
Product Version 8.4.9
Driver Name PostgreSQL Native Driver
Driver Version PostgreSQL 9.2 JDBC4 (build 1001)

How can I get to the source of this query so I can debug it and get it working again. I"m thinking that one of the dataset schema's or something has changed that I need to update.

Thanks,

Anthony

SQL Parse Error.png

Matthew Bellew responded:	2013-09-23 10:36
Sorry about this. Could you copy the last bit of the stack trace? I'd like to see the bit after "Caused by: java.lang.NullPointerException "... Also the SVN revision number would help as well. Thanks, Matt

Anthony Corbett responded:	2013-09-23 11:08
Matt, The build of Labkey we have on our server is LabKey13.1-25846. Unfortunately there is no other output after the "Caused by: java.lang.NullPointerException" in neither the details section or the labkey.log file. So I can't help with the real root cause. Is there any other way to edit SQL queries? Thanks for your help, Anthony

Matthew Bellew responded:	2013-09-23 12:27
I entered a bug to track this https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=18666 In this case, since you know the source of the query, I'd recommend deleting it and recreating a new query. Matt

Anthony Corbett responded:	2013-09-23 12:36
I tried clicking on 'DELETE QUERY' in the schema browser and even that is choking because of the NPE. So I cannot edit or delete this query. This exception is also causing some charting not to work because when the charting goes to look for all available measures/dimensions it must be able to parse all queries. (Jeanne spoke to a Labkey dev during the user conference about this and they submitted a bug). Since this isn't parsing correct the charting fails. Any suggestions on how to manually delete it? - Anthony

jeckels responded:	2013-09-23 12:40
Hi Anthony, There may be additional detail for the stack trace in your labkey.log. Can you check there too if you haven't already? Thanks, Josh

Anthony Corbett responded:	2013-09-23 12:43
Hey Josh, I mentioned above that the labkey.log also stops reporting the stack trace at "Caused By: java.lang.NullPointerException". - Anthony

jeckels responded:	2013-09-23 17:24
Hi Anthony, Thanks for checking. Sorry for missing that in the thread. Short of deleting the container, I don't think there's an alternative way to delete the query through the UI if you're getting that exception. You can, however, manually delete the row from the database. We normally don't recommend this, of course. It will be stored in the query.querydef table. You can find it based on the "name" (the query name itself) and "schema" columns. You will need to either bounce Tomcat or go to Admin Console->Memory Usage->Clear Caches and GC to un-cache it. Alternatively, you can wait for the cache entry to time out. Thanks, Josh

Anthony Corbett responded:	2013-09-25 14:34
Josh, Thanks for the help. I manually deleted the row from query.querydef and cleared the caches. This worked! Now I went about the task of re-creating the query and I got the same error on save. So I commenting things out to see where the parse issue was happening. I found that the UNION in sub-SELECT statement in the WHERE Clause is causing the issue, though again I can't see any of the stack trace beyond the Caused by: NPE. This worked in versions prior to 13.1, so this must be a regression? The SVN # of our 13.1 labkey instance is in the thread above. Here is the SQL: SELECT demo.ParticipantId, contactInfo.infant_name, demo.hospital_enroll_site, demo.ParticipantId.Cohort, contactInfo.medRec_num, hsd.admission_date, hsd.discharge_date, demo.enroll_date, st.date as StudyTerminationDate FROM Demographics demo LEFT JOIN "Contact Sheet" contactInfo ON contactInfo.ParticipantId = demo.ParticipantId LEFT JOIN "Hospital Summary Dates" hsd ON hsd.ParticipantId = demo.ParticipantId LEFT JOIN "Study Termination" st ON st.ParticipantId = demo.ParticipantId WHERE demo.ParticipantId.Cohort.Label = 'hospital' OR hsd.ParticipantId IN ( SELECT pcrResults.ParticipantId FROM "RSV PCR Results" pcrResults WHERE lower(pcrResults.result) <> 'negative' UNION SELECT AncCultureResults.ParticipantId FROM "Ancillary Illness Culture Results" AncCultureResults WHERE AncCultureResults.viral_culture = 1; -- 1 = RSV )

Anthony Corbett responded:	2013-09-25 14:50
I fixed this by putting the UNION query in a separate query, named "RSVPositiveTestsParticipants", and then used that in the sub-SELECT of the WHERE IN clause: SELECT demo.ParticipantId, contactInfo.infant_name, demo.hospital_enroll_site, demo.ParticipantId.Cohort, contactInfo.medRec_num, hsd.admission_date, hsd.discharge_date, demo.enroll_date, st.date as StudyTerminationDate FROM study.Demographics demo LEFT JOIN study."Contact Sheet" contactInfo ON contactInfo.ParticipantId = demo.ParticipantId LEFT JOIN study."Hospital Summary Dates" hsd ON hsd.ParticipantId = demo.ParticipantId LEFT JOIN study."Study Termination" st ON st.ParticipantId = demo.ParticipantId WHERE demo.ParticipantId.Cohort.Label = 'hospital' OR hsd.ParticipantId IN ( SELECT RSVPositiveTestsParticipants.ParticipantId FROM study.RSVPositiveTestsParticipants )

Matthew Bellew responded:	2013-09-25 16:19
Good news, the crashing sql editor is fixed. In 13.3 you should be able to edit and delete queries regardless of whatever havoc they create in the sql parser. I will enter a separate bug about UNION in nested selects.