MySQL select statement with alias fails with column not found

Question

Development Environment Reference:

Fedora FC18
Tomcat Server 7.0.39 (localhost config)
Eclipse Juno Release 2
Mysql-connecor-java Ver. 5.1.26 (jar)
Mysql-server Ver. 5.5.32 (jar)

The following select statement fails with "column 'depth' not found":

"select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) - 1) as depth
 from ENTITY as node, ENTITY as parent
 where node.lft between parent.lft and parent.rgt
 group by node.subEntityID order by node.lft";

This select statement succeeds using command-level mysql as

use dbName;
select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) - 1) as depth
  from ENTITY as node, ENTITY as parent
  where node.lft between parent.lft and parent.rgt
  group by node.subEntityID order by node.lft;

The select statement also succeeds when using an SQL query in MySQL Workbench ver. 5.2.4.7.

The relevant Java code fragment is given below:

rs = stmt.executeQuery(typeEntityList);
// The depth alias does not print out in this for loop
for (int i=1; i<rs.getMetaData().getColumnCount()+1; i++) {
    System.out.println(rs.getMetaData().getColumnName(i));
    System.out.println(rs.getMetaData().getColumnLabel(i));
}
while (rs.next()) {
    // "depth" fails at the following statement
    System.out.println("depth: " + rs.getInt("depth"));
    String s = rs.getString("lft") + "  "
        + rs.getString("subEntityID") + "  "
        + rs.getString("rgt");
    System.out.println(s);
    entityList.add(s);
}

Also, I found on a Google search that MySQL's alias behavior changed in recent versions. A suggested fix was to append "?useOldAliasMetadataBehavior=true" to the MySQL connection string to revert back to the original behavior, but that did not correct the problem.

Here's the stack trace:

java.sql.SQLException: Column 'depth' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5733) at EntityAssessmentEntityActions.EntityAssessmentEntityListAction.detailedEntityList(EntityAssessmentEntityListAction.java:184) at EntityAssessmentEntityActions.EntityAssessmentEntityListAction.execute(EntityAssessmentEntityListAction.java:69) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450) at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252) at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265) at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:73) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:91) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:252) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54) at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:546) at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77) at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724)

Oberland

Your error does not make sense. depth is never referenced in the query. It is defined as a column alias. Are you sure that is the SQL you are executing? – Aflcio

Hi Chris, Thanks for your quick response. I tried to reduce the above servlet java code to be as simple as possible in order to make the problem easily stated. In doing so I probably made it more confusing. Anyway, the "typeEntityList" is a String variable and is defined as "select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) - 1) as depth from ENTITY as node, ENTITY as parent where node.lft between parent.lft and parent.rgtgroup by node.subEntityID order by node.lft". Do you have an example on how to retrieve a column alias in a java servlet? Thanks so much. Roy – Oberland

Did you try rs.getInt(4) instead of rs.getInt("depth") ? Also try rs.getString(4) instead of getInt, because count(*) in MySql is of type bigint(22) and it doesn't fit into integer java type and a call to getInt surely ends with the conversion error . – Nilsson

Hi Kordirkko: Thanks for your suggestions. Unfortunately, in both cases, rs.getInt(4) and rs.getString(4), ended in an error with "Column Index out of range, 4 > 3." I believe the problem lies in the underlying software, i.e., the JDBC driver or the mysql interface with the JDBC driver. As stated previously, the sql statement works using generic sql commands using the Fedora terminal. So it leads me to believe that the problem lies not in the SQL database software but the Java connection software to the mysql database server. Any other suggestions you have are welcomed, to be sure. – Oberland

Answer 1

Well StackOverflow members, I found the problem, but now I'm bald from pulling my hair out all these hours.

On Eclipse Juno, there is a selection to "Organnize Imports" of the Java source." So when I have an undefined variable, I use this convenient option to clear up the imports. Eclipse Juno imported the mysql connector, "java.sql.connection" instead of the "com.mysql.jdbc.connection" connector. I changed the imports of the java sources, and now I'm good to move forward with this servlet. But not after I go out and get some Propecia or Rogaine or ?. ... Roy W.

Oberland

Answer 2

I encounter the similar problem in my project (i'm using Eclipse Luna).

The way i overcome it, is to set the MysqlDataSource to setUseOldAliasMetadataBehavior(true);

Tedtedd