MySQL Bugs: #40256: column alias doesnt work in v5.1.6 connector

Bug #40256	column alias doesnt work in v5.1.6 connector
Submitted:	22 Oct 2008 15:29	Modified:	13 Sep 2012 13:40
Reporter:	vathsalya sangam	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	v5.1.6	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
java code is not recognizing the column alias names, it is referring original column names instead. To use column alias, we have to explicitly set useAliasMetadaBehavior property to true. We would rather prefer to have it have default value of true.

How to repeat:
try SELECT company_name as customer from db_customers.
'cusotmer' is not recognized in java, but company_name is recognized.

Suggested fix:
force the driver url to have useOldAliasMetadataBehavior as true (which has default value of false in v5.1.6)

ex :
url = "jdbc:mysql://localhost:3306/mydb?useOldAliasMetadataBehavior=true"

Hi Vathsalya and thanks for your report.

I can not consider "column alias doesnt work" as a bug report. I do not see any test case attached and I will not guess how you've written the code. Also because there is no test case I can not mark this report as duplicate although I know there are several reports regarding this in bugsdb, please check them (start from Bug#31499 for example).

For now, I'll quote 5.1.7 change-log:
       - Fixed BUG#35610, BUG#35150- ResultSet.findColumn() and  ResultSet.get...(String) doesn't allow  column names to be used, and  isn't congruent with ResultSetMetadata.getColumnName().

          By default, we follow the JDBC Specification here, in that  the 4.0 behavior  is correct. Calling programs should use ResultSetMetaData.getColumnLabel() to dynamically determine the  correct "name" to pass to ResultSet.findColumn() or ResultSet.get... (String) whether or not the query specifies an alias via "AS" for the  column. ResultSetMetaData.getColumnName() will return the actual name  of the column, if it exists, and this name can *not* be used as input to ResultSet.findColumn() or ResultSet.get...(String).
    
     The JDBC-3.0 (and earlier) specification has a bug, but you can get  the buggy behavior (allowing column names *and* labels to be used for ResultSet.findColumn() and get...(String)) by setting   "useColumnNamesInFindColumn" to "true".

If what you want is for old alias behaviour to be default then this report can not be S2 but S4 (feature request).

Note: The JDBC-compliant way of getting the information you're asking for, i.e. the "alias" for the column is by calling ResultSetMetaData.getColumnLabel(), not getColumnName().
The reason that getColumnName() is _not_ supposed to return the alias, is because it is supposed to be possible for a consumer of this API to generate a DML statement based on the metadata present in the ResultSet and ResultSetMetaData, and thus getColumnName() should return the _physical_ column name, not the alias.

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".

I currently migrating database from Ms SQL Server to MySQL server.
MySQL 5.1.29rc-community, Connecter/j 5.1.7. Windows.
My example is:
DELIMITER $$

DROP PROCEDURE IF EXISTS `chf`.`sp_forRep_AverageSalary` $$
CREATE PROCEDURE `chf`.`sp_forRep_AverageSalary` (  vdatefrom datetime,
  vdateto datetime,
  vsBranch varchar(200))
BEGIN

call sp_CheckMatchedSalary(vdatefrom, vdateto, vsBranch);

SELECT
  fn_GetValueByID(bus_dev_center) as 'Bus. Center',
  fn_GetValueByID(branch) as 'Branch Office',
  matched_month as `Month`,
  salary as 'Average starting Salary for Month',
  service_id as 'Number of Placements'
from
  MatchedSalary
where
  (cast(substring('Month', 1, 4) as decimal) * 100) + (cast(substring('Month', 6, 2) as decimal)) between
    year(vdatefrom) * 100 + month(vdatefrom) and year(vdateto) * 100 + month(vdateto) and
  /*--cast(substring(matched_month, 1, 4) as int) between year(vdatefrom) and year(vdateto) and
  --cast(substring(matched_month, 6, 2) as int) between month(vdatefrom) and month(vdateto) and*/
  (vsbranch REGEXP concat("^",branch,",.*|",branch,"|.*,",branch,"$")) = 1;
  /*branch in (select cValue from dbo.fn_SplitToArray(vsBranch, ','))*/
END $$

DELIMITER ;

Works fine tested on MySQL Query Browser. In Crystal Report XI R2, 'Bus. Center', 'Branch Office' columns are can recognized, but matched_month as `Month`, salary as 'Average starting Salary for Month', service_id as 'Number of Placements' columns are lost column alias and showed original names.

Is this bug?

Hi Bayarsaikhan.

You report suffers of same problems as original post:
  o) None responded on me quoting specs and mentioning connect string options that manipulate Alias/True column names.
  o) In regards to above, none showed me any connection string to start with.
  o) I do not have your database structure not sample data to be able to tell anything.
  o) What I do know is that using spaces in column names is not good practice. Combining it with "." (dots) is even worse practice (although it should not matter in your case but still). Namely,  ...'Bus. Center',...
  o) I have not been given any general query logs to see what CR actually sent to MySQL server
and so on.

Thus I can not even start working on this. Please put yourself in my shoes and see how you would respond to your own post.

The person responding to this request obviously knows their stuff. 
However, why couldn't they just respond with their wealth of information (which without a test case understood the problem completely and offered exactly the right solution), rather than harp on there not being a test case. The respondent was clearly able to show that the problem has nothing to do with user data or table columns.

I could make a test case for this in 2 minutes:
CREATE TABLE test ( realCol VARCHAR(255) );
INSERT INTO test SET realCol='some data';

Using the Sun JSTL core and SQL libraries:
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="jstl" %>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>
<sql:setDataSource dataSource="jdbc/website" driver="com.mysql.jdbc.Driver" />
<sql:query var="result">SELECT realCol AS aliasName FROM test WHERE 1</sql>
<jstl:forEach items="${result.rows}" var="row" >
The whole object: <jstl:out value="${row}"/><br />
The column alias: <jstl:out value="${row.aliasName}"/><br />
The real column: <jstl:out value="${row.realCol}"/><br />
</jstl:forEach>

Pretty simple to see there that what happens is that the row variable ends up with a realCol property in the result, and no aliasName.

Daniel, I do not understand what you're advocating for... Would you like me to just write here: All my tests work, this is simple CREATE SELECT... 
and close the report?

There are several points here:
  o c/J version 5.1.7 is way too old to be taken in account. I have just released 5.1.16. If the new version exhibits the unwanted behavior too, please attach small but complete test case proving your point.
  o Bug report without attached repeatable test case is not a valid one. This is simply because there are numerous configuration parameters both in server as well as in driver. Thus, picking the right option, after reading the configuration page in manual, usually takes care of such problems.
  o Bug report without attached repeatable test case is not a valid one. This is simply because you are stealing time from other bug reporters who actually bothered to prove their case.
  o Bug report without attached repeatable test case is not a valid one. This is simply because one reporting such "problem" shows no regard to free support he/she is receiving.

To finish on the lazy side, we ship complete test suite with our driver and you can find a prototype of code you need there.

This is expected behavior. The JSTL implementation loads up the "names" like this:

// Create the column name array
columnNames = new String[noOfColumns];
for (int i = 1; i <= noOfColumns; i++) {
columnNames[i-1] = rsmd.getColumnName(i);

If one wanted the aliases to be the keys, the implementation would have to call rsmd.getColumnLabel() due to the following language in the JDBC-4.0 specification at http://download.oracle.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#getColumnLabe...)

Unfortunately, the implementation of this tag library didn't seem to take into account how ResultSet.findColumn(), ResultSetMetaData.getColumnName() and ResultSet.getColumnLabel() are actually specified to work.

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".

All position and finishes of Regional local Regional local local indian local, overall look and moving companies are done by professionals and especially by professional arms in simple way.  Packers and Movers India @ http://www.expert5th.in/ 
Packers and Movers in Hyderabad @ http://www.expert5th.in/packers-and-movers-hyderabad/
Packers and Movers in Pune @ http://www.expert5th.in/packers-and-movers-pune/
Packers and Movers in Bangalore @ http://www.expert5th.in/packers-and-movers-bangalore/

Packers and Movers in Mumbai @ http://www.expert5th.in/packers-and-movers-mumbai/
Packers and Movers in Delhi @ http://www.expert5th.in/packers-and-movers-delhi/
Packers and Movers in Chennai @ http://www.expert5th.in/packers-and-movers-chennai/
Packers and Movers in Gurgaon @ http://www.expert5th.in/packers-and-movers-gurgaon/

I downloaded the most recent mysql-connector-java-5.1.44 from
https://dev.mysql.com/downloads/connector/

Connector/J
Standardized database driver for Java platforms and development.

In sql developer I go to tools->preferences->database->Third Party JDBC Driver where I added entry pointing to the downloaded .jar file.

I'm using XAMPP localhost mysql database

create table A(
  name varchar(20)
)
insert into A value ("val 1");
select name as ColumnA from A;
select name as "ColumnA" from A;
select name as 'ColumnA' from A;
select name ColumnA from A;
select name "ColumnA" from A;
select name 'ColumnA' from A;

all of the above select queries return:
    name
1   val 1

instead of:
    ColumnA
1   val 1

I have no idea what I should do step by step to make it work. Saying set useAliasMetadaBehavior or useOldAliasMetadataBehavior to true didn't help me as I don't know where or how.

I Pulse Benefits 
https://thenutritionhelp.com/i-pulse

Hindi Mein Hai

Tech Related Articles

https://hindimeinhai.com/
https://hindimeinhai.com/software-kya-hai/
https://hindimeinhai.com/html-in-hindi/