Bug #41043 SELECT colname AS alias broken
Submitted: 26 Nov 2008 8:20 Modified: 26 Mar 2013 12:19
Reporter: Ross Johnson Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.7 OS:Any
Assigned to: CPU Architecture:Any

[26 Nov 2008 8:20] Ross Johnson
Description:
As 5.0.8 works fine this issue is new in 5.1.7.

Using the connector with OpenOffice.org 3.0 Base/JRE 1.6.0_07 to connect to a MySQL 5 server, a query of the form

SELECT c1 AS a1 FROM table

appears to ignore the alias and displays c1 as the column header instead.

On a more complex query the column data is corrupted or appears to be from another column and the order of column headers is different to that of the SELECT column list. The following is such a query:-

SELECT `Server Inventory`.`Hostname`, `Vendors`.`Vendor Name` AS `HW Vendor`, `Server Inventory`.`Date Acquired`, `Machine Models`.`Mnemonic` AS `HW Model`, `Server Inventory`.`Serial Number` AS `HW Serial Number`, `Server Inventory`.`Host ID`, `Machine Models`.`Number of CPUs`, `Server Inventory`.`CPUs Installed`, `Server Inventory`.`Memory (GB)`, `CPU Types`.`Word Size` AS `CPU Word Size`, `CPU Types`.`Cores` AS `CPU Cores`, `CPU Types`.`Speed (GHz)` AS `CPU Speed`, `Cache L1 (MB)` AS `CPU Cache`, `Operating Systems`.`OS Name`, `Operating Systems`.`OS Version`, `Server Inventory`.`Description`, `Server Inventory`.`Comments`, `CPU Types`.`Description` AS `CPU Type`, `Cores` * `CPUs Installed` AS `Total Processors`, `Server Inventory`.`Asset Number`, `Status`.`Short Name` AS `Status`, `Power (VA)` FROM `servers`.`Vendors` AS `Vendors`, `servers`.`Server Inventory` AS `Server Inventory`, `servers`.`CPU Types` AS `CPU Types`, `servers`.`Operating Systems` AS `Operating Systems`, `servers`.`Machine Models` AS `Machine Models`, `servers`.`Status` AS `Status` WHERE `servers`.`Vendors`.`ID` = `servers`.`Server Inventory`.`Vendor ID` AND `servers`.`CPU Types`.`ID` = `servers`.`Server Inventory`.`CPU Type ID` AND `servers`.`Operating Systems`.`ID` = `servers`.`Server Inventory`.`OS ID` AND `servers`.`Machine Models`.`ID` = `servers`.`Server Inventory`.`Model ID` AND `servers`.`Status`.`ID` = `servers`.`Server Inventory`.`Status ID` AND `servers`.`Status`.`Short Name` <> 'Retired' ORDER BY `servers`.`Server Inventory`.`Hostname` ASC

I will attach two files of results from the above query:-

1) showing the correct result using version 5.08
2) showing the corrupted result using version 5.1.7

How to repeat:
Create a simple query in OOo Base similar to

SELECT c1 AS a1 FROM table

Run the query and check the column headers.
[26 Nov 2008 8:58] Ross Johnson
As I was copy+pasting from the OOo Base query result table as seen in the second screen dump PNG (Incorrect result) to OOo Calc to generate CSV files to attach here I noticed that the results as seen in Calc are correct (for version MySQL Connector/J 5.1.7, i.e. the same as for version 5.0.8), so I've attached that CSV file for reference.

Although I'm changing only the connector jar to generate these results I guess this could as easily be a bug in OOo, triggered by a change of some kind within MySQL Connector/J 5.1.7. Apologies if that''s the case.

The incorrect result as shown in the attachment appears both in OOo Base results tables and in the OOo Calc data source pane, but not when  either of these are dragged and dropped as a complete table into a Calc sheet.
[3 Dec 2008 7:26] Tonci Grgin
Hi Ross and thanks for your report.

Since I do not know which options you used in connection string I assume your report is a duplicate of following (take your pick):
Bug#35150	Inconsistency in column name causes Hibernate to fail.
Bug#31499	Alias lost when selecting from LEFT JOIN involving Derived Table
Bug#35610	Retrieving values by column label doesn't work
Bug#35753	jdbc useOldAliasMetadataBehavior connection property not set
Bug#36841	java.sql.SQLException: Invalid column name
and many more. Fix for Bug#35753 is not yet released but you can get it from http://svn.mysql.com/svnpublic/connector-j/branches/branch_5_1/ or from http://downloads.mysql.com/snapshots/mysql-connector-java-5.1/mysql-connector-java-5.1-nig....

Bottom line s in 5.1.6 Changelog (fixed):
The JDBC driver uses a different method for evaluating column names in resultsetmetadata.getColumnName() and when looking for a column in resultset.getObject(columnName). This caused Hibernate to fail in queries where the two methods yield different results, for example in queries that use alias names: 

SELECT column AS aliasName from table
[4 Jan 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[26 Mar 2013 12:19] Alexander Soklakov
Hi Ross,

Version reported is too old and there is no new feedback since Dec 2008, so I close this report as "Can't repeat".

Feel free to reopen it if you have the same problem with latest c/j.