MySQL Bugs: #57591: Incongruous column name behavior breaks SQL taglib (and probably other code)

Bug #57591	Incongruous column name behavior breaks SQL taglib (and probably other code)
Submitted:	20 Oct 2010 6:45	Modified:	26 Sep 2011 7:15
Reporter:	Alessandro Polverini	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	standard taglibs, taglib

Description:
Since 5.1.6, and as reported here:
http://lists.mysql.com/commits/47149
the behavior of some methods is changed and this breaks JSP pages developed with the SQL standard tag library.

For example a simple page using this code stops to work:

SELECT col1 as x FROM Table

because since version 5.1.6 and up to at least 5.1.13 the column name is "col1" instead of "x" as requested.

That can be fixed with a workaround like this:

SELECT concat('',col1) as x FROM Table

because using a function makes the column name right again, i.e.: 'x'

I find this behavior highly problematic and incongruos and hence a bug to be fixed.

The problem has already been reported to Jakarta and closed because of a problem of this driver:
https://issues.apache.org/bugzilla/show_bug.cgi?id=45183

How to repeat:
Create a JSP page with those two queries using the SQL taglib:

1) SELECT col1 as x FROM Table

2) SELECT concat('',col1) as x FROM Table

and make it print column names of the result.
In the first case we get "col1", in the second one "x".

Hi Alessandro and thanks for your report.

It is not clear to me what are you doing here... Do you use ResultsetMetadata.getColumnName() or ResultSetMetadata.getColumnLabel() or something else.

Please attach a standalone test case making note of what you expected and what you actually got so I can check.

Here is a complete test case:

== First create a test table and insert a couple rows ==

create table t1(s1 varchar(100));
insert into t1 values('one'),('two');

== Then configure appropriate datasource on tomcat and create this JSP page ==

<%@ page contentType="text/html; charset=utf-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<c:set var="dataSource" value="jdbc/test"/>
<c:set var="sql" value="SELECT s1 as x FROM t1"/>
<sql:query var="result" dataSource="${dataSource}">
  ${sql}
</sql:query>
Query: ${sql} <br/>
<c:forEach var="object" items="${result.rows}" varStatus="status">
  ${object} <br/>
</c:forEach>

<c:set var="sql" value="SELECT concat('',s1) as x FROM t1"/>
<sql:query var="result" dataSource="${dataSource}">
  ${sql}
</sql:query>
 <br/> Query: ${sql} <br/>
<c:forEach var="object" items="${result.rows}" varStatus="status">
  ${object} <br/>
</c:forEach>

== The output of the page ==

Query: SELECT s1 as x FROM t1
{s1=one}
{s1=two}

Query: SELECT concat('',s1) as x FROM t1
{x=one}
{x=two}

== Conclusions ==

We can see that the same construct (select <expr> AS x) but with a different expression gives two different results:

- in the first case the column name is 's1' (wrong)

- in the second case the column name is 'x' (right)

We follow what the JDBC specification requires, file a bug against the tag library for not following the JDBC specification or file a bug against JDBC at bugs.sun.com if you believe this is incorrect behavior. 

The driver does have a switch that reverts this behavior, adding "useColumnNamesInFindColumn=true" to your JDBC URL should give you the old, non-compliant with JDBC-4.0 behavior.

If "useColumnNamesInFindColumn=true" doesn't fix this for you, please re-open this bug and we'll assign it to the server, because the driver is using metadata that comes from the server to determine column names and labels. If that's wrong, there's nothing that the JDBC driver can do about it.

Mysql developers tells me that they follow the JDBC specs.

Tomcat developers tells me that this is a "driver artifact".

I, as a user, only know that things do not work, application breaks, and an incongruous behavior happens.

Since I provided a complete test case to verify and reproduce the problem I can only link the two bug reports together so you can speak one to another and better understand who has to fix his code.

https://issues.apache.org/bugzilla/show_bug.cgi?id=45183
http://bugs.mysql.com/bug.php?id=57591

Thanks for your work,
Alex

Alex, "If "useColumnNamesInFindColumn=true" doesn't fix this for you, please re-open this bug".

Yes, using useColumnNamesInFindColumn=true fixes the problem, but if this makes the driver follow the specs, shouldn't it be the default?