Bug #39061 Incorrect Results Using Connector/J Version 5.1.6

• Submitted: 27 Aug 2008 2:45
• Modified: 27 Aug 2008 2:50
• Reporter: Glyn Thomas
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.6
• OS: Windows (Vista)
• CPU Architecture: Any
• Tags: join, tomcat, vista

[27 Aug 2008 2:45] Glyn Thomas

Description:
I get different results for the same query executed using Connector/J 5.0.8, and 5.1.6 calling from Tomcat on Vista.

Software stack is MySQL Version 5.0.51a, Tomcat 5.5.26, JRE
1.6.0_05, and J/Connector 5.1.6.

In this configuration I am unable to display any parts of a result set that are retrieved from JOINed tables.

I corrected this problem by back-dating Connector/J to Version 5.0.8.

How to repeat:
For example, try this query:

SELECT i.ImageID,i.DateDay,i.DateMonth,i.DateYear,i.Location1,i.Location2,i.ScannedYN,i.MediaID,c.Description as country, s.SubjectID, s.SubjectTypeID, i.CountryID,i.PhotographerID, i.VolumeNumber FROM image_tbl i, lu_country_tbl c, xrf_image_subject_tbl si, subject_tbl s WHERE (i.countryid=c.countryid) AND (si.Subjectid=s.subjectid) AND (si.Imageid=i.imageid) AND (i.imageid=1234)

In JSP, I retrieve the results of this query into an rsImages variable, and
then iterate through the rows using:

<c:forEach var="row" items="${rsImage.rows}">

Rows that retrieve directly from the image_tbl work correctly e.g.
${row.ImageID} will display the image ID.

However, elements from the joined tables, such as ${row.Country} and
${row.SubjectID} don't get retrieved.

Suggested fix:
I corrected this problem by back-dating Connector/J to Version 5.0.8.

[27 Aug 2008 2:50] Mark Matthews

Version 5.1 changed the behavior of ResultSet.getFoo(String columnLabel) to match that of the JDBC-4.0 specification, which was changed because of an actual bug in the specification. The string passed to getters on ResultSet is the column label (if it exists). Your code asks for the column name.

Version 5.1.7 (yet to be released, but available as a nightly snapshot build from http://downloads.mysql.com/snapshots.php) has a configuration option that reverts this behavior (useColumnNamesInFindColumn=true), but in general it's better to update your application if possible to use the new, as specified in JDBC-4.0 behavior.