Can't display spot-data columns that are based on formulas returning string values

[Jari Häkkinen]

See also #1067.

I was looking at spot data and with a few columns selected but I also needed the 'chromosome' column visible. Adding this column returns a blank page except for the top tool bar (File View Array LIMS ...). I cannot deselect the column creating the problem so every time I return to the listing the empty page appears. The only error output I get is in the tomcat log:

Jun 25, 2008 10:47:39 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Number
	at net.sf.basedb.util.formatter.NumberFormatter.format(NumberFormatter.java:35)
	at net.sf.basedb.clients.web.taglib.table.Cell.doEndTag(Cell.java:236)
	at org.apache.jsp.views.experiments.spotdata.list_005fspotdata_jsp._jspService(list_005fspotdata_jsp.java:1060)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at net.sf.basedb.clients.web.servlet.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:72)
	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:175)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
	at java.lang.Thread.run(Thread.java:619)
Jun 25, 2008 10:47:39 AM org.apache.catalina.core.StandardHostValve custom
SEVERE: Exception Processing ErrorPage[exceptionType=java.lang.Throwable, location=/exception/exception.jsp]
java.lang.IllegalStateException
	at org.apache.coyote.Response.reset(Response.java:297)
	at org.apache.catalina.connector.Response.reset(Response.java:658)
	at org.apache.catalina.connector.Response.reset(Response.java:925)
	at org.apache.catalina.core.StandardHostValve.custom(StandardHostValve.java:417)
	at org.apache.catalina.core.StandardHostValve.throwable(StandardHostValve.java:271)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
	at java.lang.Thread.run(Thread.java:619)

[Nicklas Nordborg]

It seems like the chromosome column in question comes from a formula item, that simply maps to rep('chromosome'). The chromosome column is a string column, but formulas are expected to return numeric values. This is causing the ClassCastException.

Formulas should be able to also return string values (or date/integer/double/float/boolean.. etc.) values. To fix this problem we need to introduce a returnType property of the Formula in much the same way as we already have on the ExtraValueType items. A major problem is what return type to assign to existing formulas. We have to investigate the return type for the pre-installed formulas (but it may differ depending on the formula).

In any case, a database schema change is needed, so it is not possible to fix this before 2.8.

[Nicklas Nordborg]

I made a quick check and the result is that the return type is varying depending on the formula:

M and A: Double Ratio, Ch1, Ch2, etc: Float

This is for Postgres. I will test with MySQL later.

In any case... it may be problematic for users to know the exact return type of a formula. A possible workaround in code is to use the java.lang.Number class which has methods like floatValue(), doubleValue(), etc. This should convert values to the specified type if it is needed.

Another workaround may be to insert CAST:s in the generated SQL.

[Nicklas Nordborg]

I have now checked MySQL also and the result is different from Postgres for the ratio column:

M, A and Ratio: Double
Ch1, Ch2: Float

This is not good if we want to create database-independent code.

Any ideas for solving this problem are welcome.

[Nicklas Nordborg]

I think the fix in #1067 is better.

• Using CAST in the SQL will not work since MySQL has limited support for it (It is not possible to cast to floating-point numbers).
• Letting the user select a return type for a formula may work, but it is not easy to select the correct one among float, double, int or long. Particularly since there is a difference between databases.

I am closing this ticket as duplicate.