I have a grouped SQL statement. The query has a field with the participantId and a field with a text description. I would like to group the query on Id and group_concat the description. This seems simple enough, but labkey is giving me this odd error saying " java.sql.Date cannot be cast to java.lang.String". I am having a hard time seeing where the date part is coming from. This is not the actual SQL I used, but will repro it and should work on any machine:
SELECT
i.name,
group_concat(i.name) as yesterdaysObs,
FROM core.principals i
group by name
I have found that the issue is actually the alias 'yesterdaysObs'. It gives this error:
ERROR WebPartView 2011-05-10 17:05:50,525 http-8080-Processor24 : renderView() exception in org.labkey.api.query.QueryView$4 while responding to /labkey/query/WNPRC/EHR/executeQuery.view?schemaName=study&query.queryName=demographicsObs
java.lang.ClassCastException: java.sql.Date cannot be cast to java.lang.String
at org.labkey.api.data.MultiValuedRenderContext.<init>(MultiValuedRenderContext.java:45)
at org.labkey.api.data.MultiValuedDisplayColumn.renderGridCellContents(MultiValuedDisplayColumn.java:48)
at org.labkey.api.data.DisplayColumn.renderGridDataCell(DisplayColumn.java:659)
at org.labkey.api.data.DataRegion.renderTableRow(DataRegion.java:1456)
at org.labkey.api.data.DataRegion.renderTableContents(DataRegion.java:1420)
at org.labkey.api.data.DataRegion._renderTable(DataRegion.java:893)
at org.labkey.api.data.DataRegion.render(DataRegion.java:2431)
at org.labkey.api.data.DataRegion.renderTable(DataRegion.java:701)
at org.labkey.api.view.GridView._renderDataRegion(GridView.java:64)
at org.labkey.api.view.DataView.renderView(DataView.java:144)
at org.labkey.api.view.DataView.renderView(DataView.java:33)
at org.labkey.api.view.WebPartView.renderView(WebPartView.java:778)
at org.labkey.api.view.WebPartView.renderInternal(WebPartView.java:289)
at org.labkey.api.view.HttpView.render(HttpView.java:139)
at org.labkey.api.view.HttpView.render(HttpView.java:121)
at org.labkey.api.view.HttpView.include(HttpView.java:542)
at org.labkey.api.view.HttpView.include(HttpView.java:519)
at org.labkey.api.query.QueryView.renderDataRegion(QueryView.java:1462)
at org.labkey.api.query.QueryView.renderView(QueryView.java:1227)
at org.labkey.api.view.WebPartView.renderView(WebPartView.java:778)
at org.labkey.api.query.QueryView.renderView(QueryView.java:1252)
at org.labkey.api.view.WebPartView.renderInternal(WebPartView.java:289)
at org.labkey.api.view.HttpView.render(HttpView.java:139)
at org.labkey.api.view.HttpView.render(HttpView.java:121)
at org.labkey.api.view.HttpView.include(HttpView.java:542)
at org.labkey.api.view.HttpView.include(HttpView.java:519)
at org.labkey.jsp.compiled.org.labkey.api.view.template.CommonTemplate_jsp._jspService(CommonTemplate_jsp.java:222)
at org.labkey.api.view.JspView.renderView(JspView.java:121)
at org.labkey.api.view.WebPartView.renderInternal(WebPartView.java:289)
at org.labkey.api.view.HttpView.render(HttpView.java:139)
at org.labkey.api.view.HttpView.render(HttpView.java:121)
at org.labkey.api.action.SpringActionController.renderInTemplate(SpringActionController.java:447)
at org.labkey.api.action.SpringActionController.handleRequest(SpringActionController.java:356)
at org.labkey.api.module.DefaultModule.dispatch(DefaultModule.java:756)
at org.labkey.api.view.ViewServlet.service(ViewServlet.java:164)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.api.data.TransactionFilter.doFilter(TransactionFilter.java:36)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:118)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.api.module.ModuleLoader.doFilter(ModuleLoader.java:609)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:143)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:873)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:619)
ERROR ExceptionUtil 2011-05-10 17:05:50,567 http-8080-Processor24 : Exception detected and logged to mothership
java.lang.ClassCastException: java.sql.Date cannot be cast to java.lang.String
at org.labkey.api.data.MultiValuedRenderContext.<init>(MultiValuedRenderContext.java:45)
at org.labkey.api.data.MultiValuedDisplayColumn.renderGridCellContents(MultiValuedDisplayColumn.java:48)
at org.labkey.api.data.DisplayColumn.renderGridDataCell(DisplayColumn.java:659)
at org.labkey.api.data.DataRegion.renderTableRow(DataRegion.java:1456)
at org.labkey.api.data.DataRegion.renderTableContents(DataRegion.java:1420)
at org.labkey.api.data.DataRegion._renderTable(DataRegion.java:893)
at org.labkey.api.data.DataRegion.render(DataRegion.java:2431)
at org.labkey.api.data.DataRegion.renderTable(DataRegion.java:701)
at org.labkey.api.view.GridView._renderDataRegion(GridView.java:64)
at org.labkey.api.view.DataView.renderView(DataView.java:144)
at org.labkey.api.view.DataView.renderView(DataView.java:33)
at org.labkey.api.view.WebPartView.renderView(WebPartView.java:778)
at org.labkey.api.view.WebPartView.renderInternal(WebPartView.java:289)
at org.labkey.api.view.HttpView.render(HttpView.java:139)
at org.labkey.api.view.HttpView.render(HttpView.java:121)
at org.labkey.api.view.HttpView.include(HttpView.java:542)
at org.labkey.api.view.HttpView.include(HttpView.java:519)
at org.labkey.api.query.QueryView.renderDataRegion(QueryView.java:1462)
at org.labkey.api.query.QueryView.renderView(QueryView.java:1227)
at org.labkey.api.view.WebPartView.renderView(WebPartView.java:778)
at org.labkey.api.query.QueryView.renderView(QueryView.java:1252)
at org.labkey.api.view.WebPartView.renderInternal(WebPartView.java:289)
at org.labkey.api.view.HttpView.render(HttpView.java:139)
at org.labkey.api.view.HttpView.render(HttpView.java:121)
at org.labkey.api.view.HttpView.include(HttpView.java:542)
at org.labkey.api.view.HttpView.include(HttpView.java:519)
at org.labkey.jsp.compiled.org.labkey.api.view.template.CommonTemplate_jsp._jspService(CommonTemplate_jsp.java:222)
at org.labkey.api.view.JspView.renderView(JspView.java:121)
at org.labkey.api.view.WebPartView.renderInternal(WebPartView.java:289)
at org.labkey.api.view.HttpView.render(HttpView.java:139)
at org.labkey.api.view.HttpView.render(HttpView.java:121)
at org.labkey.api.action.SpringActionController.renderInTemplate(SpringActionController.java:447)
at org.labkey.api.action.SpringActionController.handleRequest(SpringActionController.java:356)
at org.labkey.api.module.DefaultModule.dispatch(DefaultModule.java:756)
at org.labkey.api.view.ViewServlet.service(ViewServlet.java:164)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.api.data.TransactionFilter.doFilter(TransactionFilter.java:36)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:118)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.api.module.ModuleLoader.doFilter(ModuleLoader.java:609)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:143)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:873)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:619)
and the SQL. i have simplified it somewhat: |
| |
| Ben Bimber responded: |
2011-05-10 15:11 |
| Title: unusual SQL error with group_concat |
the title is supposed to be 'unusual error'. usually group_concat works just fine. |
| |
| Ben Bimber responded: |
2011-05-10 15:24 |
ok, so that wasnt quite right, but i think I found 2 bugs:
1. the issue wasnt the group_concat itself. I had metadata that tried to give a URL to that column. the original XML was:
<column columnName="previousObs">
<columnTitle>Previous Obs</columnTitle>
<inputType>textarea</inputType>
<url>/query/executeQuery.view?schemaName=study&
query.queryName=irregularObsById&
query.Id~eq=${Id}
query.date~dateeq=${yesterday}
</url>
</column>
The column 'yesterday' is a date. apparently that is what was throwing the date-to-string error. Seems like that is a bug.
2. If i get rid of that column and just use:
<column columnName="previousObs">
<columnTitle>Previous Obs</columnTitle>
<inputType>textarea</inputType>
<url>/query/executeQuery.view?schemaName=study&
query.queryName=irregularObsById&
query.Id~eq=${Id}
</url>
</column>
then some cells render correctly. they render correctly so long as the group_concat isnt actually concating anything together. In other words if there was only 1 value for that Id, the field renders correctly, with a URL. if more than 1 value is returned for that Id, in which case those values would get joined, then I get the error. seems like a separate bug. |
| |
| Ben Bimber responded: |
2011-05-11 05:35 |
overnight it occurred to me that #1 is probably caused b/c i cast the field 'yesterday' as DATE, in order to do a date-only comparison. if i change that line from:
cast(TIMESTAMPADD('SQL_TSI_DAY', -1, now()) AS DATE) as yesterday,
to:
cast(cast(TIMESTAMPADD('SQL_TSI_DAY', -1, now()) AS DATE) AS TIMESTAMP) as yesterday,
that will probably work. #2 is still a problem though. |
| |
|
|